Publishing Database Changes To SQL Server Using Microsoft TFS

This Tutorial Explains how to Create SQL Server Database Project In TFS, and Share Project To Microsoft TFS Repository in Detail:

In the DevOps series tutorials published @ DevOps Tutorial, we have studied about Continuous Integration and Continuous Delivery automation for Java and .Net for on-premise and cloud deployments of the applications.

The tools that we have covered include Microsoft VSTS with Azure, Amazon Web Services, and Ansible. The series concentrated on source code changes and how effectively it was quickly deployed to various environments. But when we talk about any 3 tier application development, the database would also be involved and we will need to apply the same techniques to database related changes.

Microsoft Team Foundation Server 2015 And SQL Server 2014

This article will focus on areas like the use of Visual Studio 2015 for development, SQL Server Database Project creation containing database definition, creating a build definition to publish the changes to SQL Server, etc using Microsoft Team Foundation Server 2015 and SQL server 2014.

So eventually we need to manage database scripts as well and its changes effectively.

Create SQL Server Database Project In TFS

Let’s start by creating the Database Project in Visual Studio. Launch Visual Studio 2015 and create the database project as shown in the image below, using File->New ->Project. Enter a name and click on OK.

Add a new folder and name it Tables. This folder will contain all our SQL files. Right-click on the Project and select Add->New Folder.

The folder created is shown in Solution Explorer. Add a new table here and define columns as appropriate.

Right-click on the Table’s folder and select Add-> Table as shown in the below image.

Enter Name as Employee with Table option selected. Click on Add.

Add a few columns as shown in the Design view.

In case you have existing SQL files, it can be imported as well by right-clicking on the database project and selecting Import Script (*.SQL)

Similarly, Add -> Stored Procedure into a folder as shown below.

The final structure as shown in the database project will look like the below image.

So, normally for every release and as a developer, we will make changes to the tables or stored procedures and Visual Studio will ensure that the scripts are altered properly to ensure database changes are in sync with the scripts. We need to publish these changes or continuously deploy the changes in an automated way to the SQL Server database.

To do this, publish the Profile file which is an XML file, and it contains all information like database name, connection string, etc. that are needed to deploy to the SQL Server.

Once the XML file is available we will then use the Microsoft TFS build definition to publish the changes automatically to the SQL Server database either based on every check-in or on a schedule.

In the SQL Server installation, we have created an empty database, for example, Employee which we will be using for Publish.

Now create the publish profile file using Visual Studio. Right-click on the database project and select Publish.

Edit and select the connection to the Employee database in the SQL Server.

Click on the button Save Profile As to save the profile into the project. You can see a file named Employee.publish.xml is created. You can also manually deploy the changes by clicking on the Publish button but here we are going to automate the changes to be published.

Select the Cancel option for now after the profile is saved.

Open the file in an XML editor and you could see the database and connection strings. Add a key manually for Password and mention the password for the user id given. This is needed for connection to the SQL Server database.

Since the password can be accessed easily we will see how to mask the same in the build definition using the Replace Token plugin.

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>Employee</TargetDatabaseName>
    <DeployScriptFileName>Employee.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=l-0310;Persist Security Info=False;User ID=sa; Password=<AddPassword>;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

Also, ensure to select the right version of SQL Server to be used for this database project else the published data will have errors. Right-click on the database project(Employee) and select Properties. Select the appropriate version and in this case, it is the 2014 version. Save (Ctrl+S) the changes, once done.

Select SQL Server 2014 as Target Platform and save the solution.

Share Project To Microsoft TFS Repository

To use the project in a TFS Build Definition, we need to share the project with the TFS repository. Right-click on the solution and select Add Solution to Source Control. Go to the Team Explorer ->Pending Changes and Check-in the solution.

The project should now be available in TFS.

Create Build Definition To Automate Publish

We now have the database project checked in to TFS and every change that was done by the developer to the table or stored procedures will be published automatically to SQL Server without any manual intervention using Build Definition either on every check-in or based on a schedule.

First, modify the publish XML file to add a token for the Password Key as shown below. Check-in any changes that are done.

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>Employee</TargetDatabaseName>
    <DeployScriptFileName>Employee.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=l-0310;Persist Security Info=False;User ID=sa;Password=#{DBPWD}#;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

In TFS, go to the Build HUB and create an Empty Build Definition.

In the Build Definition, first, add the Replace Tokens step and configure as shown in the image below, since the password variable has to be masked. In case you need to download the plugin for Replace Tokens you can download and install the same for Microsoft TFS 2015 from GitHub-replace tokens

Go to the Variables Tab and add the variable as in the publish XML file i.e. DBPWD with a value of the password for the sa user id. Click on the lock icon and select the checkbox for Allow at Queue Time. Save the Build Definition.

Next, add a Visual Studio Build step. Select the Visual Studio solution and enter the MS Build Arguments as shown in the below image to build and publish the changes.

/t:build /t:publish /p:SqlPublishProfilePath=Employee.publish.xml

Queue or trigger the build and check for changes published in SQL Server. For the Build process, an Agent would be needed to run on the machine where SQL Server is installed.

Recommended Reading on installing and configuring agents for Microsoft TFS 2015 @ Microsoft-TFS-2015

Open SQL Server 2014 Management Studio, right-click on the database, run a query on the database and look for the changes that are published post the build which has ran.

As we can see that for the first time the tables and stored procedures are published through an automated build process in TFS. To showcase the continuous integration and automated publishing process to the database let’s do a change in the table and re-run the build.

In the table, we have added one more column.

Queue the build again and check if changes have been published successfully or not. The column Country is now added to the table. Refer to the image below.

In case you would like to look at the Database, Tables, etc. from Visual Studio itself then go to View->SQL Server Object Explorer to browse the Database which has SQL Server like hierarchy.

Conclusion

We have seen a simple but powerful solution to continuously publish database changes to SQL Server using Microsoft TFS 2015 on every check-in or based on the schedule of the build. All the tables or stored procedures or views are added or updated based on these changes and Visual Studio creates or alters the SQL scripts accordingly.

So in nutshell, managing large databases and publishing the changes have become easy with the help of the Visual Studio Database Projects and Microsoft Team Foundation Server.