Manage Test Data in Visual Studio Database project

One of the greatest missing of Visual Studio Database Projects, is the ability to manage data in a Database Project. One widely used technique to overcome this limitation is using PostDeployment scripts and script data inserting. This technique is used also to insert test data inside the database. When used in this way, you need some way to avoid inserting test data in Production Database, so you need to find a technique that permits you to run the inserting test data only when needed.

A first solution can be: differentiate post deployment scripts based on active configuration, basically one of the Script runs when the configuration is debug and the other one should run when the configuration is release (or other).

Actually Database Projects does not distinguish between various configurations so you need to resort to some hack to have it works. Basically you create one script for every configuration you want to use.


Figure 1: Create one script for every configuration you want to support

Then you need to unload the database project, edit the project file directly and add a pre-build step that basically copy the right script over the Script.PostDeployment.sql based on the active configuration

  <Target Name="BeforeBuild">
      DestinationFiles="Scripts\Post-Deployment\Script.PostDeployment.sql" />

This is not a so good hack, because every time you change the configuration the Script.PostDeployment.Sql will be overwritten and marked as changed. In this situation you should remember avoiding to check-in to avoid too much noise in the source control. Modifying source file with a pre-build action can also lead to TFS Build failures, because the Build agent uses standard Server workspaces and all source files are in read-only mode during the build. Moreover there is too much risk that someone mistakenly runs the debug version against production database and you will have test data inserted into production DB, so this is not my choice.

A better solution is leaving your Database Project post deployment script free from test data, use them to only insert the real configurations, but create another Database Project. This new project references the original Database Project and contains post build script to generate test data. This technique keeps your original Project clean, when it is time to deploy test data, simply deploy the other database project and thanks to the reference all the original schema will be deployed, then the post deployment script of the other project will insert Test Data.


Figure 2: Create a specific database project to deploy test data

Now the only interesting part is how to create the InsertBaseData.sql script to insert some base test data and make it stable. If you create a script with a long series of INSERT INTO, you will probably fill database with unnecessary data, or worst, the script may fail due to unique index validation.

Everything you need to know is in this interesting article from Sql Server Central called Generate MERGE Statements with Table Data. In that article the author shows you a nice stored procedure used to generate a series of MERGE statements from a database with real data.


MERGE INTO [Categories] AS Target

  ('model','Model Airplanes')
 ,('paper','Paper Airplanes')

) AS Source ([Code],[Title])
ON (Target.[Code] = Source.[Code])
 [Title] = Source.[Title]

This is really useful because you can simply fill a real database with test data and when you are happy with it, generate the script that will recreate the same sets of data.

Thanks to this simple technique you can manage Structure and production data in main Database Project and having other Database Projects to generate test data to automatically setup Test Environments.

Gian Maria.

TF Service: deploy on Azure Web Site with Database Project

The ability to automatically deploy a site on Azure Web Site from TFService is really interesting, but sadly enough there is no out-of-the-box solution to update the structure of an Azure Database with a VS2012 Database Project. In this post I’ll show how to modify the standard build template to deploy a Database Project during Azure Web Site Deployment. I’ve blogged in the past to explain how to Deploy a Database Project with TFS Build, but that post refers to the old type of Database Project (VS2010) and now I want to explain how to customize the AzureContinuousDeployment build to deploy on azure a database project of VS2012.

First of all create a copy of the standard build file in BuildProcessTemplate directory.


Figure 1: Create a copy of the original AzureContinuousDeployment.11.xaml file and check-in

This will avoid messing with the original build definition; to accomplish it simply create a copy of the file in your workspace, check-in the new file and open your local copy from Visual Studio to modify the Workflow definition. The main problem if you are not familiar with TFS Build Workflow, is to find the place where to put the new instructions. To help you locate the point where you should modify the build file, please look at Figure 2, that shows where to locate the Try Compile and Test sequence.


Figure 2: Try Compile and Test is the part of the workflow you need to modify.

Now you should expand Try Compile and Test, scroll down until you find a sequence called Deploy on Test Success, expand it and you will find a Publish Output sequence where you can find a call to a MSDeploy action that actually is deploying the web site.


Figure 3: The point of the workflow where the site is deployed

The right place where to insert additional operations to deploy some stuff is right after the MSDeploy.

First of all you should add a couple of Arguments to the Workflow to make it more generic and make it reusable; with arguments the user will be able to specify if he want to deploy a database (DeployDatabase argument) and the Database Output File Name (DatabaseProjectOutputFile) directly from the build editor. This will permit you to keep only one xaml file with the build workflow, and have multiple build, based on that workflow, and choose for each one what database to deploy. You should also configure the Metadata property to specify Name, description and other properties of your custom arguments. You can have a look at TFS2010 Create a Personalized Build post for details about Metadata and Workflow Arguments.


Figure 4: Add a couple of arguments to parametrize the workflow.

The whole customization is represented in Figure 5 and you can see that it is really easy. You start adding a condition to verify if the argument DeployDatabase is true, name this condition If Should Deploy Database, and then add a standard Sequence inside it in the then area. If you exclude WriteBuildMessage actions, that are merely logging, the whole operation is done by only two Actions.


Figure 5: The sequence added to the standard workflow to deploy a database project.

The ConvertWorkspaceItem is used to convert a path expressed in TFS source control (starts with dollar sign) to local path of the build server. This is needed because to deploy a database project the easiest path is using SqlPublish.exe program, (installed locally in your developing machines when you install Sql Server Data Tools). I’ve simply located them in my HD, then copied inside a folder of the source control to have it included along with the source code of my project. This technique is really common to achieve Continuous Deployment, if is it possible, including all the tools needed to build and deploy your solution should be included in the source code of the project.


Figure 6: Insert all SqlPackage.exe distribution in your source code and check-in, this will make the tool available to the Build Agent

This is the easiest way to have an executable available for build agents. When the agents starts the build it does a get latest before compiling the source and this will automatically get tools to deploy database along with the source code. This technique is really useful for TF Service because you have no access to the elastic Build installation, and you have no way to install SqlPackage.exe in the Build Server. (Note: If you have your Build Agent on premise, you can simply install SqlPackage.exe, then find the location on the hard disk and simply hardcode  (or add another variable) installation path in the build definition and get rid of the ConvertWorkspaceItem action )

The ConvertWorkspaceItem action is needed because I know the path on the source control Es: $/myproject/trunk/tools/Deploy but I do not know where the Build Server is creating the workspace to build the source. The ConvertWorkspaceItem is the action that permits me to convert a path on source control to a real path on disk. Configuration is represented in Figure 6


Figure 7: Configuration of the ConvertWorkspaceItem.

This is the configuration of the ConvertWorkspaceItem action and the Input path is composed by Workspace.Folders.First().ServerItem variable, that represents the first mapped server folder on the workspace used to build the solution. This is a convenient way to make the build file reusable, as long as you remember that the first folder to map should be the top level folder of your project (trunk, or specific branch), and it should contains a folder called /Tools/Deploy/SqlDac that should contains the SqlPackage.exe distribution. The result property point to a Variable of the workflow (I have previously declared), that will contain the physical location of the SqlPackage.exe tool after the execution of the action.

The other interesting action is the InvokeProcess used to invoke the SqlPackage.exe tool to deploy the database. You can customize the build with custom action, or msbuild scripts (I’ve talked a lot about it in the past) but the easiest way is to include an executable in source control Tools directory and have it invoked from InvokeProcess. This solution does not require deploying custom assemblies, and is really simple to setup. If you expand it you can find that this action have a couple of slots to place an activity that will be invoked with the standard output and the standard error, usually you have them setup as in Figure 8. The WriteBuildError activity is really useful because it write error message on the build log, and it makes also the build as Partially Failed, so if the database deploy operation fails, I have automatically my build marked as partially failed without the need of any other further customization work.


Figure 8: Intercept standard output and error and have them dumped as build message

This is the complete configuration of the InvokeProcess action.


Figure 9: Configuration of the InvokeProcess activity

Actually the interesting part is the FileName property that use the SqlPackageLocation variable (populated by ConvertWorkspaceItem) to locate where the agent downloaded SqlPackage.exe and the Arguments that contains all the info for the deploy. Here is its full value.


Figure 10: Configuration of the Arguments property, it contains the full command line arguments for SqlPackage.exe tool

The cool part is the ability to use the variable azureWebSiteProfile.SqlServerDBConnectionString that contains the database connection string extracted from the publish file, it was parsed for you by AzureContinuousDeployment build. The outputDirectory variable is the directory where the build copied all the output of the various project after the build, and is the location of the .dacpac file of your database project. Finally you can check-in modified workflow back to TFS.

Now you can let azure create a standard Build for you, with standard configuration from Azure Management portal, this will create a build in your Team Project. Once the build is created, you can simply edit to use new Build Workflow. As first step you need to change the workspace in Source Settings, because you need to map the entire trunk (main) directory, because you want Tools to be downloaded with source.


Figure 11: Change configuration of the workflow, map the entire trunk of the project to include the Tools directory

Finally you can go to the Process section so you can change the workflow, from the standard AzureContinuousDeployment to the one you customized with previous steps.


Figure 12: Choose the new Build workflow for the build

Now you should see in all variables added in Figure 4, if you correctly filled metadata for them, you should see description for them. I also created a new area of configuration called DeployCustom to separate all standard build properties by new ones introduced by the customization.


Figure 13: Specify values for your custom variables

Now I can proceed to set Enable Database Deploy to true and specify the name of the dacpac file I want to deploy. Name of dacpac file 99% is the name of the database project followed by the .dacpac extension.

Now at each Build the Azure database connected to the Web Site would be updated to the new structure of the database project.

If you prefer You can download the modified build definition file from Here but I strongly suggest you to modify your file. First of all because the original publish file can be updated from the time this post was published, but it is also important that you try to being familiar with build customization, so you will be able to further modify it if you need to deploy other stuff.

Gian Maria.

Migrate MsTest of database edition to VS2010

I have a VS2008 project that is being converted to VS2010. This solution has a MsTest test project used to run database test against a Database project. After conversion you need to target the 4.0 framework for the test project, you run the test and you get

Microsoft.Build.BuildEngine.InvalidProjectFileException: Microsoft.Build.BuildEngine.InvalidProjectFileException: The expression "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\9.0\VSTSDB@VSTSDBDirectory" cannot be evaluated. . Aborting test execution

This is due to the fact that the test project probably references old version of the Microsoft.Data.Schema and Microsoft.Data.Schema.UnitTesting dll, just verify that you are using the correct version, simply remove the above two dll from references and add them again using the right version.


Figure 1: reference rigth database testing dll for MSTest

this is not enough, because you need also to go to app.config and change the version of the database test related configuration section

   1: <configSections>

   2:     <section name="DatabaseUnitTesting" 

   3:         type="Microsoft.Data.Schema.UnitTesting.Configuration.DatabaseUnitTestingSection, Microsoft.Data.Schema.UnitTesting, 

   4:             Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>


As you can see the Version should be changed to because you should have (the one related to VS2008 database testing tools)

Now all of your test should run fine again.


Pay attention to file length in database project after a synchronization

One of the coolest feature of Database Projects in VS2008 is the ability to synchronize the database project with a physical instance of SQL server.

This is useful if not all people from the team have the ability to run a database project (this is solved in vs2010 where database project are available even in the professional edition), or if some DBA makes modification to a database with management studio.

After a full synchronization occurs double check the file structure, because the synchronization procedure store size of the files into the definition

ALTER DATABASE [$(DatabaseName)]

    ADD FILE (NAME = [xxxxx], FILENAME = '$(VirtualDiskDataPath)\xxxxxx.ndf', 

SIZE = 6586816 KB,



the SIZE parameter is really huge, and this can slow down deploy process during unit testing. Keep this in mind and always take a look to the file definition after you sync a database project with a live database.

You can always avoiding to update file definition (because they rarely change) to avoid this problem




Deploy a database project with tfs build 2010

If you want to deploy a database project into a target sql server instance during a tfs 2010 build, you can use with success the basic MsBuildTask, similar to tfs2008.

I decided to deploy the database, only if the tests are ok and the build is ok, so I place a condition activity under the test phase and I set the condition to pass only if the test and build status are different from Failed


Now I need to know where the dbproj will be located in the disk during the build, and since I know the repository path, I can use a specific TFS build action to convert the source control path into physical path. The source control path is “$/Experiments/NorthwindTest/NorthwindTest.Database/NorthwindTest.Database.dbproj” so I can drop a “ConvertWorkspaceItem” activity to convert this path to the physical one. First of all I need to declare a variable where to put the result.


I called this variable “dbProject” (String type). Now I can configure my ConvertWorkspaceItem activity I dropped in the “then” part of my condition activity.


This activity needs three variables, the first is the source control path you want to translate, the second is the name of the variable that will contain the result and the third is the Workspace to use (is stored into the global variable Workspace).

Following this activity I dropped a WriteBuildMessage activity, used to write something into the build log (I wrote the exact path of the database project that is to be deployed)


This is only informational, but it is useful to check during the build process.

The most important activity is the MsBuildActivity that will do the real work. This activity invokes Msbuild.exe that in turns will target the database project to deploy. The most important property is the CommandLineArgument where you must specify all details of the deploy

"/p:TargetDatabase=NorthwindTest" +
" /p:""DefaultDataPath=C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA""" +
" /p:""TargetConnectionString=Data Source=\SQL2008,49400%3Buser=sa%3Bpwd=Pa$$w0rd""" +
" /p:DeployToDatabase=true"

These properties are used to specify the name of the database, the datapath, the connectionstring where to deploy the database, and they override the standard one defined on the project. As you can see in connection string I used the sa password, that is strongly discouraged for security reasons in production environment. You can use integrated security if the user used to run the team agent has the right to create a database in the target instance.

Finally these are all the properties of the msbuild activity.


LogFile is really important, because you want a msbuild log file of what is happened during the deploy. You can check this file in the logs\ subfolder of the drop location


If you open deploydatabase.log, you can find all the information you need on what is happened, here is a little extract.

Build started 1/4/2010 7:08:38 AM.
Project "C:\Builds\1\Experiments\DeployDatabase\Sources\NorthwindTest\NorthwindTest.Database\NorthwindTest.Database.dbproj" on node 1 (Deploy target(s)).
  Deployment script generated to:
  Creating NorthwindTest…
  Creating [dbo].[Categories]…
  Creating PK_Categories…
  Creating [dbo].[Categories].[CategoryName]…
  Creating [dbo].[CustomerCustomerDemo]…
  Creating PK_CustomerCustomerDemo…
  Creating [dbo].[CustomerDemographics]…
  Creating PK_CustomerDemographics…
  Creating [dbo].[Customers]…
  Creating PK_Customers…

A really important property is the Target, that is set to New String() {“Deploy”}. A database project supports some different targets, as an example the build, deploy, dataGeneration and StaticCodeAnalysis, each of them will perform a different task.

The project to deploy is specified in the Project property, and as you can see I use the variable dbProject that was populated with the ConvertWorkspaceItem.

If you check build details you can find all the information you need about the build process


From here you can verify project directory, and all the options that are passed to the msbuild.exe. You can now schedule the build and verify that the database is deployed on target server.