Walkthrough: Performing Iterative Database Development in an Isolated Development 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 the following:

  1. 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.

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

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

Prerequisites

You must have installed Visual Studio Premium or Visual Studio Ultimate and have access to the AdventureWorks2008 sample database for SQL Server 2008. 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 BusinessEntityID column in the [HumanResources].[Employee] table to be called BusinessEntityNumber. The BusinessEntityID 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 BusinessEntityID to BusinessEntityNumber

  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, expand the HumanResources schema, and then expand the Tables folder.

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

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

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

  8. In New name, type BusinessEntityNumber.

  9. Select the Update references in schema objects containing warnings or errors check box.

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

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

  11. Click one of the changes.

    The details of the change appear in Preview changes.

  12. 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:

  • Employee.table.sql

  • ufnGetContactInformation.function.sql

  • uspGetEmployeeManagers.proc.sql

  • uspGetManagerEmployees.proc.sql

  • uspUpdateEmployeeHireInfo.proc.sql

  • uspUpdateEmployeeLogin.proc.sql

  • uspUpdateEmployeePersonalInfo.proc.sql

  • FK_EmployeeDepartmentHistory_Employee_BusinessEntityID.fkey.sql

  • FK_EmployeePayHistory_Employee_BusinessEntityID.fkey.sql

  • FK_Employee_Person_BusinessEntityID.fkey.sql

  • FK_JobCandidate_Employee_BusinessEntityID.fkey.sql

  • PK_Employee_BusinessEntityID.pkey.sql

  • vEmployee.view.sql

  • vEmployeeDepartment.view.sql

  • FK_Document_Employee_Owner.fkey.sql

  • FK_PurchaseOrderHeader_EmployeeID.fkey.sql

  • FK_SalesPerson_Employee_BusinessEntityID.fkey.sql

  • vSalesPerson.view.sql

  • vSalesPersonSalesByFiscalYears.view.sql

  • AWGenPlan.dgen

Run Database Unit Tests

After you make any changes and before you check the application 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 your 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 test named dbo_ufpLeadingZeroTest is the one that you created in the previous walkthrough.

  2. Select dbo_ufpLeadingZeroTest, right-click it, 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 test runs and passes.

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 in to 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 BusinessEntityID 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 in to 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 his or her changes in isolation, tests them to verify that they are correct, and then shares them with the team by checking the updates in to version control. For more information about how to build and deploy your database, see Build and Deploy Databases to an Isolated Development Environment and Build and Deploy Databases to a Staging or Production Environment.

Note

If you deploy to the same server from which you originally imported AdventureWorks2008, an error might appear. If an error appears that indicates that you cannot create a file because it already exists, you might have missed a step in the first walkthrough. For more information, see To deploy the project to your isolated development environment.

To learn more about the database features of Visual Studio, you can run the more in-depth walkthroughs for each feature area.

See Also

Concepts

Refactor Database Code and Data

Running Database Unit Tests

Verifying Database Code by Using Unit Tests

Compare and Synchronize Database Schemas