Walkthrough: Performing Iterative Database Development in an Isolated Environment

  • Article

In Walkthrough: Creating an Isolated Database Development Environment, you acted as a database administrator, creating a database project and importing the schema and database object definitions from a production server. In the second walkthrough, you created database unit tests to verify existing functionality and establish a clean baseline. In this walkthrough, the third and last in this series, you perform a typical iterative development task: renaming two database columns. You make these changes in an isolated development environment to minimize the risk to the rest of the team until you are ready to share your changes.

Tasks illustrated in this walkthrough include:

  • How to use database refactoring to rename columns, reducing the time that is required to update all references to the columns and reducing the chance of error.

  • Run database unit tests to verify that the changes did not break existing functionality.

  • Check in the changes to version control to make them available to the rest of the team.

Prerequisites

You must have installed Microsoft Visual Studio Team Edition for Database Professionals and have access to the AdventureWorks sample database for Microsoft SQL Server 2005. In addition, this walkthrough assumes that you have completed Walkthrough: Creating an Isolated Database Development Environment and Walkthrough: Establishing a Baseline for the Isolated Development Environment.

Rename Database Columns

A typical development task involves renaming database objects. In this walkthrough, you rename the EmployeeID column in the [HumanResources].[Employee] table to be called EmployeeNumber. The EmployeeID column is referenced in several foreign keys and stored procedures. To make the change manually, you would have to modify multiple files. Before you make modifications, you should verify that you have the most recent version of the database project.

To get the latest version of the database project

  1. In Team Explorer, expand the Team Project node that contains the database project.

  2. Open Source Control Explorer by double-clicking the Source Control node.

  3. In Source Control Explorer, right-click your database project, and click Get Latest Version.

    The most recent version of the solution that contains the database project is retrieved to your computer.

To rename EmployeeID to EmployeeNumber

  1. If the AdvWorksSandbox solution is open in Solution Explorer, go to step 4.

  2. On the File menu, point to Open, and click Project/Solution.

    The Open Project dialog box appears.

  3. Click the AdvWorksSandbox solution, and click Open. (By default, this solution is located in My Documents\Visual Studio 2005\Projects\AdvWorksSandbox.)

    The AdvWorksSandbox solution opens in Solution Explorer.

  4. Open the View menu, and click Schema View.

  5. In Schema View, expand the AdvWorksSandbox node, and then expand the Tables folder.

  6. In the Tables folder, expand the HumanResources.Employee table, and then expand the Columns folder.

  7. Right-click the EmployeeID folder, point to Refactor, and click Rename.

    The Rename dialog box appears and displays the existing column name.

  8. In New name, type EmployeeNumber.

  9. If the Preview changes check box is not selected, select it, and click OK.

    The Preview Changes - Rename dialog box appears and displays all of the items in the project that reference the column that you are renaming.

  10. Click one of the changes.

    The details of the change appear in Preview changes.

  11. After you review the changes, click Apply to apply the changes.

    Any files that are modified are checked out of version control.

You can view the pending changes in the Pending Changes window. The following files should appear in the Pending Changes window:

  • AWGenPlan.dgen

  • Employee.table.sql

  • Employee.PK_Employee_EmployeeID.sql

  • vSalesPerson.view.sql

  • uspGetManagerEmployees.proc.sql

  • vEmployee.view.sql

  • vEmployeeDepartmentHistory.view.sql

  • uspUpdateEmployeePersonalInfo.proc.sql

  • uspUpdateEmployeeHireInfo.proc.sql

  • uspUpdateEmployeeLogin.proc.sql

  • PurchaseOrderHeader.FK_PurchaseOrderHeader_Employee_EmployeeID.fkey.sql

  • Employee.FK_Employee_Employee_ManagerID.fkey.sql

  • EmployeePayHistory.FK_EmployeePayHistory_Employee_EmployeeID.fkey.sql

  • EmployeeDepartmentHistory.FK_EmploymentDepartmentHistory_Employee_EmployeeID.fkey.sql

  • JobCandidate.FK_JobCandidate_Employee_EmployeeID.fkey.sql

  • EmployeeAddress.FK_EmployeeAddress_Employee_EmployeeID.fkey.sql

  • SalesPerson.FK_SalesPerson_Employee_EmployeeID.fkey.sql

  • Employee.uEmployee.trigger.sql

Run Database Unit Tests

After you make any change and before you check it in to version control, you should run the database unit tests to verify that the application still functions correctly before you share those changes with the team.

To run database unit tests to identify any potential problems

  1. On the Test menu, point to Windows, and then click Test View.

    The Test View window lists several tests. By default, the tests named ManualTest1 and TestMethod1 are created. The tests named dbo_uspGetManagerEmployeesTest and dbo_ufpLeadingZeroTest are the ones that you created in the previous walkthrough.

  2. Select the check boxes for both dbo_uspGetManagerEmployeesTest and dbo_ufpLeadingZeroTest, right-click either, and click Run Selection.

  3. View the results in the Test Results window.

    The database project is deployed to your isolated development environment, test data is generated, and the tests run and pass.

Comparing the Database Project with Production

You can compare the schemas of the updated database project and the production database to determine how they differ. Because you are only comparing the schemas, instead of updating either one, you can specify either as the source or the target for the comparison.

To compare the database project schema with the production database

  1. On the Data menu, point to Schema Compare, and click New Schema Comparison.

    The New Schema Comparison dialog box appears.

  2. Accept the default values for Source Schema.

    The database project, AdvWorksSandbox, is specified.

  3. In Target Schema, in the Database list, click the connection to the AdventureWorks database from which you first imported the database schema, and click OK.

    The schemas are compared. The only differences should be the changes that you made during this walkthrough.

  4. Explore the differences between the schemas. When you finish, close the Schema Compare window.

    You can now share your changes with the team.

Check in Changes

After you have verified that all your changes are ready to be shared with other team members, you check them into version control. You check in any changes to the solution, which typically include the database project, your unit test project, and any associated application code and application unit tests.

To check in the changes and make them available to the team

  1. On the View menu, point to Other Windows, and click Pending Changes.

    The Pending Changes window appears.

  2. In Comment, type Renamed EmployeeID column.

  3. In the Pending Changes window, click Check In on the toolbar.

    The Check In Progress dialog box appears as the database project and the files that it contains are checked in to version control. The icons in Solution Explorer update to indicate that the files are checked into version control.

Next Steps

This iterative process will occur many times for each version of the database that your team deploys into production. Each developer makes their changes in isolation, tests them to verify that they are correct, and then shares them with the team by checking the updates into version control. The process to deploy the database to production is straightforward:

  1. A version of the files that compose the database project and its tests are labeled to mark that they are part of a version being deployed.

  2. You get the labeled sources and generate a build script.

  3. You review and modify the build script as needed, performing a test deployment to a staging server. After each deployment, you can compare the database project to the schema on the staging server.

  4. After you iterate on the build script until you are satisfied that it is ready to go to production, you deploy your modified build script into production.

  5. If you must make changes that are not reflected in the database project, you can import those updates into the database project from the updated production server.

To learn more about Team Edition for Database Professionals, you can run the more in-depth walkthroughs for each feature area.

See Also

Concepts

Terminology Overview of Team Edition for Database Professionals

Other Resources

Refactoring Databases
Running Database Unit Tests
Comparing Database Schemas