This documentation is archived and is not being maintained.

Refining Database Development with Microsoft Visual Studio 2005 Team Edition for Database Professionals

Visual Studio 2005
 

Matt Nunn and Steven Powell
Visual Studio Team System for Database Professionals

Microsoft Corporation

March 2007

Applies to:
   Microsoft Visual Studio 2005 Team Edition for Database Professionals

Summary: Implementing tools and processes to solve database-development challenges can help reduce unexpected issues with database change, increase team productivity, and improve the quality of application software. Microsoft Visual Studio 2005 Team Edition for Database Professionals gives you the tools and processes necessary to manage changes to the databases that are a key part of your applications. (12 printed pages)

Contents

The Changing Face of Database Development
How Microsoft Visual Studio 2005 Team Edition for Database Professionals Helps
Taking Control of Database Change
Automating Database Testing to Improve Software Quality
Making Changes to a Version-Controlled Database Schema
Improving Collaboration and Communication
Tools and Processes
Conclusion

The Changing Face of Database Development

Approximately 60 percent of all applications being developed today have a database component. However, most Application Life-Cycle Management (ALM) solutions and methodologies pay little or no attention to this important aspect of development. Implementing tools and processes to solve database-development challenges can help reduce unexpected issues with database change, increase team productivity, and improve the quality of application software.

How Microsoft Visual Studio 2005 Team Edition for Database Professionals Helps

Microsoft Visual Studio 2005 Team Edition for Database Professionals provides tools and recommends process changes that together redefine database development. You can manage database change with an offline, project-centered version-control system and a suite of integrated tools within Visual Studio 2005. Schemas, DML, and metadata can be versioned; unit tests can be created and executed, to ensure quality; and comparisons can be made with test and production systems prior to deployment. Integration with Team Foundation Server helps increase your productivity by enabling you to be an integral part of the application team.

Taking Control of Database Change

Managing changes to your database schema throughout the application-development process can be one of the most challenging parts of creating a database application. In the past, you might have used complex, manual synchronization processes to try to ensure that everyone was using the most recent version of the database.

Traditionally, the "one true version" of the database data and schema was the version on the production server. This is the version that is updated in real time, and any changes must be replicated back to development, test, and staging systems. It was difficult to know that the version of the database being used for development matched what was in production. If a change caused an application to break, rolling back that change across multiple servers required considerable effort and was an error-prone manual process.

Visual Studio 2005 Team Edition for Database Professionals allows the logical separation of data and schema allowing database schema to be versioned using a new Visual Studio project. Changes can be made to the schema under version control and then deployed to development, test, staging, or production servers through the use of custom T-SQL deployment scripts. By keeping the database schema under version control, Visual Studio 2005 Team Edition for Database Professionals can help reduce the risk that is involved with schema change and allow you to track all changes that are made to the schema.

Roles and Database Access

One common question about Visual Studio 2005 Team Edition for Database Professionals is, "How do we keep the developers from changing the production server?" To answer that question, you must understand the relationship between the production server, the staging server, the "sandbox" server, and the database project. There are two major roles involved in the process: the role of the database administrator and the role of the database developer.

The database administrator is responsible for the portions of the process where access to the production server (or staging server) is required. This includes when the database schema is imported from the production server into a database project, as well as deploying a version of the database project to the production or staging server.

The database developers work with private "sandbox" copies of the database, populated with test data. While working in their private sandbox, they can make schema changes and run database and application unit tests to verify that the changes work correctly and that no existing functionality was broken. Changes are shared between developers by checking tested changes back into the version-control system.

Putting a Database Schema Under Version Control

To put a database schema under version control, you (as a database administrator) perform the following steps in Visual Studio Team Edition for Database Professionals:

  1. On the File menu, point to New, and then click Project.

    The New Project dialog box appears, as shown in Figure 1.

     

    Click here for larger image

    Figure 1. New Project dialog box (Click on the picture for a larger image)

    Team Edition for Database Professionals adds a new Database Projects node to the list of project types. Within that node, you see Microsoft SQL Server, which is the only supported database type at this time. In the list of templates, you have four new project templates:

    • SQL Server 2000
    • SQL Server 2005
    • SQL Server 2000 Wizard
    • SQL Server 2005 Wizard

    The first two templates create an empty database project. You can import a database schema later, or you can create one from scratch if you want to create a new database. You must remember also to configure your database-project settings before you build and deploy.

    The wizard-based projects create the database project and then allow you to quickly configure your database-project settings and import a database schema when you create the database project.

  2. Specify a name for the database project, and optionally specify a name for the solution that contains that project, then click OK.

    The New Database Project Wizard appears. It consists of five pages, which are broken down as follows:

    • Welcome page—Provides a description of why you might use the wizard.
    • Project Properties—Allows you to control some aspects of your database project, including the file organization on disk (by schema or by object type), the default schema for new objects, whether you want the schema name included in the file name, and whether the database project must support full-text search or SQLCLR.
    Important   You can only specify the file organization when you create the database project.
    • Set Database Options—Allows you to specify a common group of database properties.
    • Import Database Schema—Allows you to import a database schema from a database. Typically, you would begin most database-development projects with a schema that you import from one of your production servers (or that your administrator imports for you).

       

      Click here for larger image

      Figure 2. New Database Project Wizard: Import Database Schema page (Click on the picture for a larger image)

    There are a few options that control the schema import. You can script all column collations, or only those that are different from the database collation. You can skip all extended properties during the import operation. You can choose to import database settings from the server, instead of using those that you might have configured on the Set Database Options page.

    • Configure Build and Deploy—Allows you to configure how your database project will be built and deployed.

       

      Click here for larger image

      Figure 3. New Database Project Wizard: Configure Build and Deploy page (Click on the picture for a larger image)

    These settings control the default build and deployment behavior for the developers who work with this database project. Each developer could modify the settings for their needs. In particular, each developer might have to update the Target connection to point to the database server that they use to host their "sandbox" copy of the database.

    • Summary—Appears when you click Finish. This page provides progress feedback and indicates whether errors or warnings occurred during project creation.
  3. When you have specified the desired settings for your database project, click Finish to create the database project.

    The new database project appears in Solution Explorer, as shown in Figure 4.

    Bb310560.dbprodev04(en-US,VS.80).gif

    Figure 4. Solution Explorer

    Solution Explorer provides a view of the files that are contained in your database project. Use Solution Explorer when you want to perform operations on the files in your database project. For example, to add your database project to version control, use Solution Explorer.

    You can also work with a view of the objects contained in the database project. To see a view of the database objects, on the View menu, click Schema View. Schema View appears, as shown in Figure 5.

    Bb310560.dbprodev05(en-US,VS.80).gif

    Figure 5. Schema View

    Use Schema View when you are working with the database objects, instead of the files that contain them. For example, to rename database objects or create unit tests for functions, stored procedures, or triggers, use Schema View.

At this point, you would add the solution to version control and make the database project available to the team. Before iterative development work begins, the development team typically would establish a baseline set of unit tests for the existing database functionality.

Automating Database Testing to Improve Software Quality

Developers have long had access to tools that allow them to create unit tests for their code. These unit tests allow them to ensure that as they make changes, they are not inadvertently breaking something else or the code that they have written works as expected in a variety of scenarios. Visual Studio Team Edition for Database Professionals now brings this functionality to the database by providing a full unit-testing infrastructure for your database. Unit tests can be built in T-SQL or managed code and can be built to test any aspect of the database. In addition, these database tests can be combined with the application tests to provide a more holistic view over the impact of a change.

Tests can be combined with data-generation plans and schema deployment, so that whenever a test or set of test is run, they can be set up initially to deploy the most recent version of the database schema, populate that schema with a full set of test data, and then execute the database unit tests.

Generating Test Data

One unique problem you face when testing a database is the choice of test data. In order to test a database meaningfully, it is important to have test data that is structured and acts in the same manner as the production data. Because of this, the ideal data to test against is the actual production data. Unfortunately, in most cases, it is not possible to use production data for testing, due primarily to the sensitive nature of much of this data. While there are many workarounds for this problem, such as obfuscating the sensitive data or manually creating all the test data, none is particularly good at creating a representative data set against which you can test.

A second problem that you face with test data is ensuring that every time a set of tests is run, the tests are running against the same data set. If the tests change the data, they will be accessing a different data set and will produce different results the next time that they are run. You can use Team Edition for Database Professionals to solve this problem by using the provided data-generation capabilities to create custom plans for data generation. You can set up these plans to represent accurately the shape and distribution that you see in the production data by using values created exclusively by the generators, or you can integrate portions of the production data with values created by the custom generators. A data-generation plan is also repeatable; every time that it runs, it will create exactly the same data set if it uses the same seed values.

Creating Random Test Data Using Data Generator

Team Edition for Database Professionals provides the Data Generator. You can use it to create one or more data-generation plans for a database project. You can configure aspects of data generation for each column in each table. For an example, see Figure 6.

Click here for larger image

Figure 6. Data-generation plan (Click on the picture for a larger image)

This allows you to generate test data that is more realistic than just random strings. Also, if the provided data generators do not meet your data-generation needs, you can create custom data generators using the provided extensibility interfaces.

Adding Static Data as Part of the Post-Deployment Script

If you have lookup tables or other tables in your database that you want to test with specific data, you can modify the post-deployment script for the database project to include a script that inserts data into one or more tables.

Synchronizing Data from a Test Server to the Sandbox Database by Using Data Compare

If you want to populate your sandbox database with data that already exists in another database with the same schema (for example, in a test database), you can use the Data Compare features of Team Edition for Database Professionals to compare the data between two databases. After the comparison has completed, you can choose which tables and views should be synchronized and even select which rows within a table are synchronized. You can use this to compare a test database to your empty sandbox database and synchronize everything to turn your sandbox database into a copy of the test database.

Creating Database Unit Tests

In addition to the test data, you must create some number of database unit tests. Team Edition for Database Professionals leverages the same unit-testing infrastructure with which you might be familiar from Team Edition for Testers. You can right-click on any stored procedure, function, or trigger in Schema View, and then click Create Unit Tests. The Create Unit Tests dialog box appears, as shown in Figure 7.

Click here for larger image

Figure 7. Create Unit Tests dialog box (Click on the picture for a larger image)

When you create a unit-test project, you must choose either Microsoft Visual Basic or Microsoft Visual C#. You can author simple unit tests in Transact-SQL. For more complex tests, you must modify the Visual Basic or Visual C# code. You can choose one of the provided test conditions, or you can create custom test conditions by using the provided extensibility APIs. The provided test conditions are the following:

  • Execution Time—Allows you to fail a unit test if it takes more than a specified amount of time to complete
  • Empty ResultSet—Allows you to fail a unit test if the returned result set is not empty
  • Not Empty ResultSet—Allows you to fail a unit test if the returned result set is empty
  • Row Count—Allows you to check for a specific number of rows being returned
  • Scalar Value—Allows you to check for a specific scalar value being returned

After you have written one or more database unit tests, you can set them up to run from Team Foundation Build if you want to run those tests as part of an automated run. As part of your unit tests, you can deploy the database schema and generate test data.

Making Changes to a Version-Controlled Database Schema

Making a database schema change can be challenging in a development or production environment. Database objects are invariably tightly coupled; making a change to the name of an object in the database can lead to the same change being needed in many other areas of the database. This has been addressed in the application space through the introduction of refactoring tools that allow you to ensure that any change is reflected in all objects that are affected. With Team Edition for Database Professionals, this same functionality is available to the database developer through the inclusion of rename refactoring. This refactoring functionality allows a database developer to make a change to a database object and then see, review, and apply the necessary changes to all affected objects within the same database.

To rename refactor an object, right-click that object in Schema View (for example, a column name), point to Refactor, and click Rename. You must then provide the new object name in the Rename dialog box. Make sure that the Preview changes check box is selected, if you want to view the changes that will be made before you apply those changes. Click OK to view the Preview Changes dialog box, as shown in Figure 8.

Click here for larger image

Figure 8. Preview Changes - Rename dialog box (Click on the picture for a larger image)

The Preview Changes dialog box displays a list of the schema objects that must be updated as part of the rename refactoring operation. If you select any of the objects, the details of the change will be displayed in the Preview changes details.

To perform any iterative development task, you synchronize your local copy of the database project to the version that is checked in to the version-control system. For any change, you must build and deploy the database project to their sandbox environment. You then run any unit tests against that sandbox to verify the correctness of those changes, before you check the changes into the version-control system and share them with the team.

Improving Collaboration and Communication

Collaboration between developers, testers, and the DBAs has long been an issue in the software industry. Visual Studio Team Edition for Database Professionals is designed to make it easier for you to work with the development team, no matter what your role might be.

In addition to providing the ability for database developers or DBAs to work easily on—and control change to—the database, Visual Studio Team Edition for Database Professionals also gives the ability for other members of the team to access the database, as needed. Because the schema is stored in a version-control system, other members of the development and test teams can easily download and deploy new versions of the schema to their local environments. Test teams also can access and integrate all of the database unit tests, if Visual Studio Team Edition for Database Professionals is combined with Visual Studio 2005 Team Edition for Software Testers.

This deeper integration of all members of the team will let you find and resolve issues earlier—allowing you to make better decisions and resulting in a higher-quality application being produced.

Tools and Processes

Application Life-Cycle Management (ALM) comprises tools, technologies, and processes to integrate disciplines such as requirements management, project planning and management, and architecture. Traditional ALM tools and processes have failed to address the importance of databases in most application-development projects. Process is an important aspect of any form of development, and with the release of Visual Studio Team Edition for Database Professionals, there is a need for guidance that is specific to the integration of the database roles into the application life cycle.

To address this need, you can download updates to the Microsoft Solution Framework (MSF) that specifically target the roles of the database developer and the database administrator in the application life cycle. These updates are available as part of either the MSF for Agile Software Development or MSF for CMMI Process Improvement and provide guidance that relates to how to integrate the database-development and administration tasks into the overall life cycle.

Conclusion

Visual Studio Team Edition for Database Professionals gives you the tools and processes necessary to manage changes to the databases that are a key part of your applications. The database project provides an offline representation of your database schema that can be placed under version control. Extension of existing tools and concepts, like rename refactoring and unit testing to apply to database development, gives you control over the changes you make to the database schema.

For more information about Visual Studio Team Edition for Database Professionals, contact your local Microsoft representative or Microsoft Partner, and learn more at the Visual Studio 2005 Team Edition for Database Professionals site.

Show: