Walkthrough: Performing Iterative Database Development in an Isolated Development Environment
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 Visual Studio Team System Database Edition 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
In Team Explorer, expand the Team Project node that contains the database project.
Open Source Control Explorer by double-clicking the Source Control node.
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
If the AdvWorksSandbox solution is open in Solution Explorer, go to step 4.
On the File menu, point to Open, and click Project/Solution.
The Open Project dialog box appears.
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.
Open the View menu, and click Schema View.
In Schema View, expand the AdvWorksSandbox node, expand the HumanResources schema, and then expand the Tables folder.
In the Tables folder, expand the Employee table, and then expand the Columns folder.
Right-click the BusinessEntityID folder, point to Refactor, and click Rename.
The Rename dialog box appears and displays the existing column name.
In New name, type BusinessEntityNumber.
Select the Update references in schema objects containing warnings or errors check box.
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.
Click one of the changes.
The details of the change appear in Preview changes.
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
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.
Select dbo_ufpLeadingZeroTest, right-click it, and click Run Selection.
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
On the Data menu, point to Schema Compare, and click New Schema Comparison.
The New Schema Comparison dialog box appears.
Accept the default values for Source Schema.
The database project, AdvWorksSandbox, is specified.
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.
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
On the View menu, point to Other Windows, and click Pending Changes.
The Pending Changes window appears.
In Comment, type Renamed BusinessEntityID column.
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.
The process to deploy the database to production is straightforward:
A version of the files that compose the database project and its tests is labeled to mark that the files are part of a version being deployed.
You get the labeled sources and generate a build script.
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.
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.
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 Database Edition, you can run the more in-depth walkthroughs for each feature area.
See Also
Concepts
Refactor Database Code and Data
Verifying Existing Database Code with Unit Tests