Walkthrough: Creating an Isolated Database Development Environment

  • Article

In this walkthrough, you set up an isolated development environment so that each database developer can make and test changes without affecting other members of the team. The isolated database development environment is based on the database project that contains the definitions for all the objects that are in the production database. You create the project, import the database schema from a production database, and configure project settings. Next, you create a data generation plan. Finally, you put the database project and all associated files under version control to share with your team.

You are playing an administrative role in this walkthrough, as you define the environment that each developer on the team will use to do his or her development work. Before each developer finishes a work item, he or she uses Data Generator to put realistic but fake data into the updated schema, and then runs database unit tests to verify that the schema changes work as expected. When work items are completed, each developer will check his or her changes into version control where the team can pick them up. By running tests before changes are checked in, each member of the team minimizes risk to the overall effort.

Tasks illustrated in this walkthrough include:

  1. How to create a database project by using the New Database Project Wizard.

  2. How to run the New Database Project Wizard to import the database schema, specify database project properties, and specify build and deployment properties.

  3. How to examine the results of the import operation.

  4. How to add the project to version control.

Prerequisites

You must have installed Visual Studio and have access to the AdventureWorks2008 sample database for SQL Server 2008. To add your project to version control, you must have installed version control software, such as Visual Studio Team Foundation Server.

For information about how to install the AdventureWorks2008 sample database, see this page on the Codeplex Web site: Microsoft SQL Server Product Samples.

You can also use SQL Server 2008 R2 and AdventureWorks2008R2 database. Differences are noted in this walkthrough where you must perform a different action for AdventureWorks2008R2.

To create a database project

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

    The New Project dialog box appears.

  2. Under Installed Templates, expand the Database node, and then click SQL Server.

    Note

    If you are using Visual Studio Professional, look under Installed Templates, expand the Database node, expand the SQL Server node, and then click Advanced.

  3. In the list of templates, click SQL Server 2008 Wizard.

  4. In Name, type AdvWorksSandbox.

  5. Accept the default Location and Solution Name.

  6. Select the Create directory for solution check box if it is not already selected.

  7. Clear the Add to Source Control check box if it is not already cleared, and click OK.

    The New Database Project Wizard appears.

    Next, you will use the wizard to configure your database project and import the initial database schema.

To configure your project and import a database schema

  1. Click Next after you read the Welcome page.

  2. Verify that A database project to manage changes to a user-defined database is selected.

  3. Under SQL Script Files, verify that By Schema is selected, and click Next.

    Important

    You cannot change a project's organization after you create it.

  4. On the Set Database Options page, in Specify the default schema to use for objects you create in your database project, type HumanResources, and click Next.

    Note

    You can change these options after the project has been created. For more information, see How to: Configure Database Properties for Database Projects.

  5. On the Import Database Schema page, select the Import existing schema check box.

  6. In the Source Database connection list, click the connection that corresponds to the server and your AdventureWorks2008 or AdventureWorks2008R2 database. If the connection does not already exist, click New Connection to create it. If you do not specify a connection, the database project will be created, but no schema will be imported.

    Note

    You can import a database schema later, if the database project does not already contain database objects. For more information, see How to: Import Database Objects and Settings.

  7. Accept the default options for importing objects and settings, and click Next.

  8. On the Configure Build and Deploy page, in Target connection, click the Edit button, and specify a connection to the database server where you want to create your development environment.

  9. In Target database name, type AdvWorksSandbox if it is not already specified.

  10. In the Default deployment collation list, click Use the collation of my project.

    This step makes the deployment engine use the collation of the database project when comparing the project to the target database.

  11. Clear the Block incremental deployment if data loss might occur check box.

    Because the isolated development environment will contain only generated test data, you can allow data to be lost.

  12. Click Finish to create the database project and import the database.

    Your database project is created. This process might take a few minutes.

  13. Review the summary results, and click Finish.

    In the background, Visual Studio is still analyzing your database schema. In the status bar, a message appears that displays the number of operations that must complete before your database schema is fully analyzed.

    Next, you will examine the database project that you created.

To examine the resulting project

  1. In Solution Explorer, expand the AdvWorksSandbox node, and expand the Schema Objects child node.

  2. Explore the subnodes that are underneath the Schema Objects node in the hierarchy.

    Solution Explorer contains the files that define the objects that are in the schema of the database from which you imported them.

  3. On the View menu, click Database Schema View.

  4. In Schema View, expand the AdvWorksSandbox node.

  5. Explore the subnodes that are underneath the AdvWorksSandbox node in the hierarchy.

    Schema View contains the objects that are defined in the files that appear in Solution Explorer.

  6. In Solution Explorer, click the AdvWorksSandbox node.

  7. On the Project menu, click AdvWorksSandbox Properties.

  8. On the Properties page, click the Deploy tab.

    Note

    Any deployment settings that you change when you configure My project settings are saved to the project file (.dbproj) and are checked into source control. In a subsequent walkthrough, you will configure settings for a specific developer's environment.

  9. In Target database name, type AdvWorksSandbox if it is not already specified.

  10. On the File menu, click Save Selected Items.

    Each developer will specify the connection string to the database server where he or she wants to host his or her copy of the database.

To deploy the project to your isolated development environment

  1. In Solution Explorer, click the AdvWorksSandbox node.

  2. If you will deploy this project to the same database server that is running the original AdventureWorks2008 or AdventureWorks2008R2 database, you must change a file name to avoid conflicts. If you instead deploy to a server that does not contain the original AdventureWorks2008 or AdventureWorks2008R2 database, you can go to step 7.

  3. Expand the Schema Objects folder, expand the Database Level Objects folder, expand the Storage folder, and expand the Files folder.

    Three files appear: AdventureWorks2008_Data.sqlfile.sql, AdventureWorks2008_Log.sqlfile.sql, and FileStreamDocuments.sqlfile.sql. If you are using SQL Server 2008 R2, these files appear: AdventureWorks2008R2_Data.sqlfile.sql, AdventureWorks2008R2_Log.sqlfile.sql, and FileStreamDocuments2008R2.sqlfile.sql.

    Note

    Older versions of AdventureWorks did not use FILESTREAM. If you import an older version of AdventureWorks, you can skip to step 7.

  4. Double-click FileStreamDocuments.sqlfile.sql.

    The file opens in the Transact-SQL editor.

  5. Modify the definition of the file to match the following:

    ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [FileStreamDocuments], FILENAME = '$(DefaultDataPath)SandboxDocuments') TO FILEGROUP [DocumentFileStreamGroup];

    If you are using AdventureWorks2008R2, you should instead use the following definition:

    ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [FileStreamDocuments2008R2], FILENAME = '$(DefaultDataPath)SandboxDocuments') TO FILEGROUP [Document2008R2FileStreamGroup];

  6. On the File menu, click Save All.

  7. On the Project menu, click AdvWorksSandbox Properties.

    The Project Properties window appears.

  8. Click the Deploy tab.

  9. In the Deploy action list, click Create a deployment script (.sql) and deploy to the database.

    Important

    If you do not change the deploy action, you will only generate the deploy script; nothing will be deployed to the target database.

  10. On the Build menu, click Deploy AdvWorksSandbox.

    A build script is generated for your database project and then deployed to the database server where you chose to host your isolated development environment. When deployment is completed, Deployment Succeeded appears in the Output window.

    You have defined a database project, and imported the schema from a production database. You can now put the solution under version control to make it available to the team.

Make the Project Available to the Team

To add your project to version control

  1. In Solution Explorer, click the AdvWorksSandbox node.

  2. On the File menu, point to Source Control, and click Add Solution to Source Control.

    At this point, you interact with the installed version control software. This walkthrough provides steps to add your project to Team Foundation Server. If you want to use different version control software, substitute the equivalent steps. If you are using Team Foundation Server, the Connect to Team Foundation Server dialog box appears.

  3. In Connect to a Team Foundation Server, click the server where the team project to which you want to add your solution is found.

    Note

    If you do not have a team project to which you can add the database project, see Planning and Tracking Projects.

  4. In Team projects, click the team project to which you want to add the database project, and click OK.

    The Add Solution AdvWorksSandbox to Source Control dialog box appears.

  5. Click OK to accept the default values.

    Your database project and the files that it contains are put under version control. Initially, they are still checked out. You must check them in before other team members can access them.

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

    The Pending Changes window appears.

  7. In the Comment field, type Initial database project creation, schema import, and data generation.

  8. 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. The icons in Solution Explorer update to show that the files are checked in to version control.

Next Steps

With the database project checked in to version control, the next step is to establish some quality assurance measures before the team starts to make changes. In the next introductory walkthrough, Walkthrough: Establishing a Baseline for the Isolated Development Environment, you create unit tests and build, deploy, and run them in your isolated database development.

See Also

Tasks

How to: Create Database and Server Projects

Concepts

Starting Team Database Development

An Overview of Database Build and Deployment

An Overview of Database Project Settings

Change History

Date

History

Reason

Added support for SQL Server 2008 R2 and AdventureWorks2008R2.

SP1 feature change.