Walkthrough: Establishing a Baseline for the Isolated Development Environment

  • Article

In this walkthrough, a database project has been created and put under version control. The database project contains a schema definition that matches that of a production server. Before development work on that database project starts, you want to establish a baseline of quality by setting up database unit tests, defining a data generation plan, generating test data, and running those tests. A test run will build and deploy the database to your isolated development environment, fill the database with generated data, and execute unit tests to report the results.

Tasks illustrated in this walkthrough include the following:

  1. How to customize deployment settings for your isolated development environment.

  2. How to build and deploy the database to your isolated development environment.

  3. How to create a data generation plan and customize it to generate more realistic test data.

  4. How to define a unit test for a stored procedure that the database project contains.

  5. How to generate test data, and build and run the unit tests.

  6. How to share the unit tests with the development team by putting them under version control as part of the solution.

Prerequisites

To define database unit tests and to create, modify, and run a data generation plan, you must have installed Visual Studio Premium or Visual Studio Ultimate and have access to the AdventureWorks2008 sample database for SQL Server 2008 (or the AdventureWorks2008R2 sample database for SQL Server 2008 R2). In addition, you must have completed the steps in Walkthrough: Creating an Isolated Database Development Environment.

To customize build settings for your isolated development environment

  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. (The default location for this solution is My Documents\Visual Studio 2010\Projects\AdvWorksSandbox.)

    The AdvWorksSandbox solution opens in Solution Explorer.

  4. In Solution Explorer, click the AdvWorksSandbox node.

  5. On the Project menu, click AdvWorksSandbox Properties.

  6. Click the Deploy tab.

  7. In the Configure deployment settings for drop-down list, select My isolated development environment.

    Note

    The deployment settings that you configure for your isolated development environment are stored in the .user file in the folder that contains your database project. The .user file is not checked in to version control. This design allows each developer to configure settings for their isolated development environment.

  8. Click Edit next to Target connection.

    The Connection Properties dialog box appears.

    Here, you can customize the connection string for your local isolated development environment. The recommended process is that each database developer updates a private copy of the database so that changes cannot adversely affect the rest of the team. When the developer has tested the changes and is ready to share them with the team, he or she shares the changes through the central version control system. In Walkthrough: Creating an Isolated Database Development Environment, the Target database name was set, but the connection was not.

  9. Type or click the name of the server that you want to use for your isolated development environment. For example, to use your local instance, type (local).

    Important

    An error occurs if you deploy your isolated development database to the same server to which you deployed in the previous walkthrough (). The error occurs because you already created the file. You can correct this by changing the FILENAME parameter in the FileStreamDocuments.sqlfile.sql file (or, for SQL Server 2008 R2, FileStreamDocuments2008R2.sqlfile.sql). Change the parameter to match the following code: FILENAME = '$(DefaultDataPath)SandboxDocuments-IDE')

  10. Click the type of authentication that you want to use to connect to the database server.

    By default, Windows Authentication is specified.

  11. Click OK.

    The Target connection displays the connection information.

  12. On the File menu, click Save Selected Items to save your changes to the database project properties.

To build and deploy the database to your isolated development environment

  1. On the Build menu, click Build Solution.

    Your database project builds, and the results appear in the Output window.

  2. In Solution Explorer, click the AdvWorksSandbox node.

  3. On the Build menu, click Deploy ProjectName. You can also right-click the AdvWorksSandbox node, and click Deploy.

    The database project builds, generates a deployment script, and then deploys that script to the isolated database development server that you specified in the database project properties.

    Next, you will generate test data for your isolated development environment.

Generate Test Data

Now that you have an isolated database against which you can develop, you need test data that can drive your unit tests. First, you create a default data generation plan, and then you customize that plan to produce more realistic data. In this example, you configure data generation for only a subset of the tables and columns in the database. You will configure data generation for the Employee table and the StateProvince table.

The original data in the StateProvince table is not sensitive. Therefore, you can use the original data to populate the isolated development database.

However, you should make the following changes to the default generation plan for the Employee table:

  • Title should be one of the following: Developer, Sr. Developer, Tester, Sr. Tester, Project Manager, Sales Associate, or Sr. Sales Associate.

  • BirthDate must be at least 18 years ago and no earlier than January 1, 1930. This specification matches the constraint on the column in the database.

  • Marital Status must be "M" or "S".

  • Gender must be "M" or "F".

  • HireDate must be on or before today's date and on or after January 1, 2000.

  • VacationHours should have a typical distribution over the range -10 to 160.

  • ModifiedDate must be on or before today's date and on or after January 1, 2000. Most of the records will have been updated recently.

  • AdditionalContactInfo and Demographics in the Person table must come from the source AdventureWorks database.

To create a data generation plan

  1. In Solution Explorer, expand the AdvWorksSandbox node, and click the Data Generation Plans folder.

  2. On the Project menu, click Add New Item.

    The Add New Item dialog box appears.

  3. In the Categories list, click Data Generation Plan.

  4. In the Templates list, click Data Generation Plan.

  5. In Name, type AWGenPlan.dgen, and then click Add.

    A data generation plan named AWGenPlan is added to the database project and opened in the editor. The database schema is imported into the data generation plan.

    Next, you will modify AWGenPlan to generate more realistic test data.

To restrict data generation to the tables of interest

  1. Right-click in the data generation plan, and click Exclude All Tables From Data Generation.

  2. In the data generation plan, select the check boxes for the following tables: HumanResources.Employee, Person.Person, Person.CountryRegion, and Person.StateProvince.

    Next, you will define the data generation plan for the StateProvince table.

To define the data generation plan for the StateProvince table

  1. Click [Person].[StateProvince] in the data generation plan.

  2. In the Column Details pane, click StateProvinceCode, and set the Generator field to Sequential Data Bound Generator.

    This step indicates that you want to populate this column with the results of a query.

  3. On the View menu, click Properties Window.

  4. In the Properties window, in the Generator section, in the Connection Information property, click the connection that corresponds to the database from which you imported the database schema in a previous procedure.

    This step specifies the database connection that you want to use to run the query that will generate data for this column.

  5. In the Properties window, in the Generator section, in the Select Query property, set the query string to SELECT [StateProvinceCode] FROM [Person].[StateProvince].

  6. In the Column Details pane, click Modified Date.

  7. In the Properties window, in the Generator section, set the Max property to today's date because records could not have been modified in the future.

  8. In the Properties window, in the Generator section, set the Min property to 1/1/2000 12:00:00 AM. For this example, assume that the company started collecting data at the start of the year 2000.

    Next, you will specify the data generation plan for the Person table.

To define the data generation plan for the Person table

  1. Click [Person].[Person] in the data generation plan.

  2. In the Column Details pane, click PersonType, and set the Generator field to Regular Expression.

  3. In the Properties window, in the Generator section, set the Expression property to (GC|SP|EM|IN|VC|SC).

  4. In the Column Details pane, click AdditionalContactInfo, and set the Generator field to Sequential Data Bound Generator.

    This step indicates that you want to fill this column with the results of a query.

  5. On the View menu, click Properties Window.

  6. In the Properties window, in the Generator section, in the Connection Information property, click the connection that corresponds to the database from which you imported the database schema in a previous procedure.

    This step specifies the database connection that you want to use to run the query that will generate data for this column.

  7. In the Properties window, in the Generator section, in the Query Select Query property, set the query string to SELECT * FROM [Person].[Person].

  8. In the Column Details pane, in the Generator Output field for the AdditionalContactInfo, click [AdditionalContactInfo].

    This step selects the column in the result set that you want to associate with this column.

  9. In the Column Details pane, click Demographics, and set the Generator field to Sequential Data Bound Generator.

    This step indicates that you want to fill this column with the results of a query.

  10. On the View menu, click Properties Window.

  11. In the Properties window, in the Generator section, in the Connection Information property, click the connection that corresponds to the database from which you imported the database schema in a previous procedure.

    This step specifies the database connection that you want to use to run the query that will generate data for this column.

  12. In the Properties window, in the Generator section, in the Query Select Query property, set the query string to SELECT * FROM [Person].[Person].

  13. In the Column Details pane, in the Generator Output field for the Demographics, click [Demographics].

    This step selects the column in the result set that you want to associate with this column.

  14. In the Column Details pane, click Modified Date.

  15. In the Properties window, in the Generator section, set the Max property to today's date because records could not have been modified in the future.

  16. In the Properties window, in the Generator section, set the Min property to 1/1/2000 12:00:00 AM. For this example, assume that the company started collecting data at the start of the year 2000.

    Next, you will specify the data generation plan for the Employee table.

To define the data generation plan for the Employee table

  1. Click [HumanResources].[Employee] in the data generation plan.

  2. In the Column Details pane, click JobTitle, and set the Generator field to RegularExpression.

  3. In the Properties window, in the Generator section, set the Expression property to (Developer|Sr\. Developer|Tester|Sr\. Tester|Project Manager|Sales Associate|Sr\. Sales Associate).

  4. In the Column Details pane, click BirthDate.

    By default, the Generator field is set to DateTime2.

  5. In the Properties window, in the Generator section, set the Max property to 18 years before today's date (for example, 10/30/1987 11:59:59 PM). Also set the Min property to 1/1/1930 12:00:00 AM.

  6. In the Column Details pane, click MaritalStatus, and set the Generator field to RegularExpression.

  7. In the Properties window, in the Generator section, set the Expression property to (M|S).

  8. In the Column Details pane, click Gender, and set the Generator field to RegularExpression.

  9. In the Properties window, in the Generator section, set the Expression property to (M|F).

  10. In the Column Details pane, click HireDate.

    By default, the Generator field is set to DateTime2.

  11. In the Properties window, in the Generator section, set the Max property to today's date (for example, 10/30/2006 11:59:59 PM). Also set the Min property to 1/1/2000 12:00:00 AM.

  12. In the Column Details pane, click VacationHours.

    By default, the Generator field is set to SmallInt.

  13. In the Properties window, in the Generator section, set the Distribution property to Normal.

  14. In the Properties window, in the Generator section, set the Max property to 160 and the Min property to -10.

  15. In the Column Details pane, click ModifiedDate.

    By default, the Generator field is set to DateTime2.

  16. In the Properties window, in the Generator section, set the Distribution property to ExponentialInverse. Also set the Max property to today's date (for example, 10/30/2006 11:59:59 PM) and the Min property to 1/1/2000 12:00:00 AM.

To adjust the number of rows being generated

  1. Click [Person].[CountryRegion] in the data generation plan.

  2. In the Rows to Insert field, type 10.

  3. Click [HumanResources].[Employee] in the data generation plan.

  4. In the Rows to Insert field, type 25.

  5. Click [Person].[StateProvince] in the data generation plan.

  6. In the Related Table field, click [Person].[CountryRegion].

  7. In the Ratio to Related Table field, type 2:1.

    This step specifies that you want to generate two times as many rows for the [Person].[StateProvince] table as for the [Person].[CountryRegion] table.

  8. On the File menu, click Save AWGenPlan.dgen to save your changes to the data generation plan.

To Preview the Data Generation and Generate Test Data

The HumanResources.Employee table contains triggers that might interfere with data generation if you run data generation multiple times. To perform data generation as part of an automated test run, you must disable (and later enable) those triggers as part of your unit tests.

To run data generation multiple times as part of this procedure, you must follow the procedure To disable and enable the triggers in the Employee table around data generation. To run data generation only one time, follow the procedure To verify the data generation plan results.

To preview the data generation and generate test data

  1. On the Data menu, point to Data Generator, and click Preview Data Generation.

    The Data Generation Preview window appears and shows a sample of the data that will be generated. If the data meets the criteria that you specified, continue to the next step. If you have to adjust the data generation plan, make the appropriate changes, and then repeat this step.

  2. On the Data menu, point to Data Generator, and click Generate Data.

    The Generate Data for Target Database dialog box appears.

  3. In Target Database, create a connection to the AdvWorksSandbox database, and click OK.

  4. When you are asked "Do you want to delete existing data from tables before you generate new data?", click Yes.

    Data is generated according to your specifications. When generation has completed, a green check mark appears in each row to indicate that all data was generated successfully. If data was not generated, errors appear in the Error List window.

  5. Skip to the procedure named "To verify the data generation plan results".

To disable and enable triggers in the Employee table around data generation

  1. Before you generate data, open the Data menu, point to Transact-SQL Editor, and click New Query Connection.

  2. If a connection exists for the database in your isolated development environment, go to step 7.

  3. Click New Connection.

    The Connection Properties dialog box appears.

  4. In Server name, type or click the name of the database server to which you deployed the database, such as (local)\SQLExpress.

  5. In Log on to the Server, click Use Windows Authentication.

  6. In Connect to a database, click Select or enter a database name, type AdvWorksSandbox, and click OK.

    The connection is created and added to the list in the Connect to Database dialog box.

  7. In the Connect to Database dialog box, click the connection string that corresponds to the database in your isolated development environment, and click OK.

    The Transact-SQL editor appears, with an active connection to the AdvWorksSandbox database.

  8. Click OK.

    The Transact-SQL editor appears.

  9. In the Transact-SQL editor, type the following Transact-SQL:

    DISABLE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee]
GO

  10. On the Transact-SQL editor toolbar, click Execute SQL.

    On the Messages tab, the following appears:

    The command(s) completed successfully.

  11. Click the window that contains the data generation plan.

  12. On the Data menu, point to Data Generator, and click Preview Data Generation.

    The Data Generation Preview window appears and shows a sample of the data that will be generated. If the data meets the criteria that you specified, continue to the next step. If you have to adjust the data generation plan, make the appropriate changes, and then repeat this step.

  13. On the Data menu, point to Data Generator, and click Generate Data.

    The Generate Data for Target Database dialog box appears.

  14. In Target Database, click the connection to the AdvWorksSandbox database, and click OK.

  15. When you are asked "Do you want to clear the contents of the selected tables before inserting new rows?", click Yes.

    Data is generated according to your specifications. When generation has completed, a green check mark appears in each row to indicate that all data was generated successfully. If data was not generated, errors appear in the Error List window.

  16. Click the Transact-SQL editor window.

  17. Typically, you would re-enable your triggers here. However, to simplify the database unit tests so they do not have to disable and re-enable triggers as part of the unit test, you leave the trigger disabled.

To verify the data generation plan results

  1. On the Data menu, point to Transact-SQL Editor, and click New Query.

    The Connect to Database dialog box appears.

  2. If a connection exists for the database in your isolated development environment, go to step 8.

  3. Click New Connection.

    The Connection Properties dialog box appears.

  4. In Server name, type or click the name of the database server to which you deployed the database, such as (local)\SQLExpress.

  5. In Log on to the Server, click Use Windows Authentication.

  6. In Connect to a database, click Select or enter a database name, type AdvWorksSandbox, and click OK.

    The connection is created and added to the list in the Connect to Database dialog box.

  7. In the Connect to Database dialog box, click the connection string that corresponds to the database in your isolated development environment, and click OK.

    The Transact-SQL editor appears with an active connection to the AdvWorksSandbox database.

  8. In the Transact-SQL editor, type the following:

    SELECT * from HumanResources.Employee;

  9. Press F5 to run the query.

    The results pane appears and displays the results of your data generation.

  10. On the File menu, click Close to close the Transact-SQL editor.

    Next, you define database unit tests that verify the existing database code.

Define Unit Tests

Typically, you would define unit tests for all stored procedures, functions, and triggers. In this walkthrough, you define only one unit test as an example. You define a test for the ufnLeadingZeros function and the dbo.uspGetManagerEmployees stored procedure.

The ufnLeadingZeros function takes an integer and returns a VARCHAR(8) string representation of that integer padded with leading zeros. To test this function, you can pass in a value and test that you get the result that you expect.

The uspGetManagerEmployees stored procedure is passed an employee identifier and returns all the employees who report through the manager whose identifier that you specified (the full hierarchy). Because the data generator will produce the same test data if given the same seed value, you can know how many rows should be returned. This test is very simple. In a real project, you would want to create more-detailed tests to verify that the correct employees were returned.

To create unit tests

  1. On the View menu, click Schema View.

    Schema View appears if it was not already displayed.

  2. In Schema View, open the Schemas folder, open the dbo folder, and then open the Programmability folder.

  3. Right-click the Functions folder, and click Create Unit Tests.

    The Create Unit Tests dialog box appears.

  4. Expand the Current selection tree, and select the check box for the function dbo.ufnLeadingZeros. Clear the check boxes for any other stored procedures or functions that are selected.

    Note

    To establish a real baseline for your project, you would create unit tests for all functions, stored procedures, and triggers in your database. This walkthrough is focusing on one function to demonstrate the process.

  5. In Project, click Create a new Visual C# test project….

  6. In New project name, type AWSandboxTestProject, and click OK.

    The unit test project is created, and the Project Configuration dialog box for unit tests appears.

  7. In Database connections, click the connection for AdvWorksSandbox.

  8. In Deployment, clear the Automatically deploy database project before running tests check box.

  9. In Database state, select the Generate test data prior to running unit tests check box.

  10. In the Database state list, click AWGenPlan.dgen.

  11. Select the Clear database prior to generating test data check box if it is not already selected, and click OK.

    The database unit test project is created and added to the solution. The Database Unit Test Designer opens so that you can modify test conditions and author your tests.

    Note

    If you must modify the test configuration, open the Test menu and click Database Test Configuration. The Project Configuration dialog box will appear, and you can change the configurations.

    Next, you will specify test conditions and write the test.

To define a unit test for the ufnLeadingZeros function

  1. In the Database Unit Test Designer, in the list of tests, click dbo.ufnLeadingZeros.

  2. Verify that the second list displays "Test".

  3. Replace the Transact-SQL statements in the top pane of the designer with the following:

    -- db unit test for dbo.ufnLeadingZeros
DECLARE @RC VARCHAR (8),
@Value INT

SELECT @RC = NULL,
@Value = 27

SELECT @RC = [dbo].[ufnLeadingZeros]( @Value)

IF @RC <> '00000027' 
RAISERROR(N'ufnLeadingZero: expected "00000027" but got %s.', 16, 1, @RC)

SELECT RC=@RC

  4. In the Test Conditions pane, click the test condition whose type is Inconclusive, and delete it by clicking Delete Test Condition (x).

  5. Add a row count test condition by clicking Row Count in the list in the Test Conditions pane, and then click Add Test Condition (+).

  6. In the Properties window, set the Row Count property to 1.

    If you want to force the test to fail, change @Value from 27 to another value.

    You are now ready to run your unit tests.

Build and Run Unit Tests

Now that you have defined unit tests, you can build and run them.

To build and run the unit tests

  1. In Solution Explorer, right-click the solution node, and click Rebuild Solution.

    This step forces a rebuild of the database project and the unit test project to guarantee that everything is current.

  2. On the Test menu, point to Windows, and then click Test List Editor.

    The Test View window lists several tests. The tests named ManualTest1 and TestMethod1 are created by default. The test named dbo_ufpLeadingZeroTest is the one that you created in this walkthrough.

  3. Select the check box for dbo_ufpLeadingZeroTest, right-click it, and click Run Checked Tests.

  4. View the results in the Test Results window.

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

Check In Pending Changes

Now that you have defined unit tests to detect whether changes break your database, you can check in your tests and share the solution with your team.

To share the solution with the team

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

    The Pending Changes window appears.

  2. In Comment, type Defined data generation plan and baseline unit tests.

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

Next Steps

Now that you have checked the solution in to version control, each member of the team can work on his or her assigned tasks. Each person can work in an isolated database development environment until any changes are ready to be shared.

In Walkthrough: Performing Iterative Database Development in an Isolated Development Environment, you use refactoring to rename database objects in your isolated development environment. You build, deploy, and test the changes before you check them in, and then other members of the team can synchronize your changes with their own isolated development environments.

See Also

Tasks

Walkthrough: Performing Iterative Database Development in an Isolated Development Environment

Concepts

Generating Test Data for Databases by Using Data Generators

Verifying Database Code by Using Unit Tests

Build and Deploy Databases to an Isolated Development Environment

An Overview of Database Project Settings

Change History

Date

History

Reason

Added support for SQL Server 2008 R2 and AdventureWorks2008R2. Also corrected some data generation and unit testing issues pointed out in customer feedback.

SP1 feature change.