skip to Main Content

Source Control for Database Applications

It is second nature for a .NET developer to store source code in a source control system such as TFS or Source Safe. Source control systems are a cornerstone of software development and are essential for tracking what changed, who changed it why it was changed, managing versions and allowing developers to work on different branches of the same system.

However, when it comes to database development, developers often neglect proper source control management. It is just easier to start SQL Server Management Studio and right-click and add a new table, column or stored procedure. They should of course generate SQL scripts for everything they do and add these to the source control system, along with all the related .NET code, but this is often done too late, or incorrectly, or not at all. Things get even worse when deploying a new version of an existing database system because a SQL script is required to update the schema (using ALTER TABLE etc.) and this script depends on which version of the system is being updated.

One of the main reasons that .NET developers find it easy to use source control is that .NET source code is just text and text can be managed easily in a source control system. In contrast database schemas tend to be created using graphical tools and not as SQL scripts.

How to Implement Source Control for Database Applications

There are third party version control offerings for SQL developers, such as SQL Source Control from Red Gate Software and Liquibase from Apache. However, there is something closer to home, in form of Data-Tier Applications and Visual Studio Database Projects that make database source control management easier.

A Data-Tier Application (or DAC) is a single entity that contains the definition of a database and all the database objects in that database that a particular application uses. Fundamentally it is a single file with the extension .dacpac that contains all the database definitions. No need for multiple script files to create databases, tables, views and stored procedures.

DAC files can be created from an existing database using SQL Server Management Studio by right-clicking on a database and selecting the All Tasks menu. Note that the examples in this article relate to SQL Server 2012, but the DAC concept was introduced in SQL Server 2008 R2.

Source Control For Database Applications 1

DAC files can be used by DBAs for deploying databases to servers. However, it is the creation of DAC files by developers using Visual Studio that is of particular interest here because of its implications for source control.

Here is an Example

Take the following simple example of creating a Database Project and a DAC using SQL Server Data Tools (aka Visual Studio 2010):

  • Start SQL Server Data Tools
  • Create a new project of type SQL Server Database Project
  • Use menu Project > Add New Item to add a new Table called Table1
  • Add a new column called data1 using the table designer  (see below)

Source Control For Database Applications 2

Note that the definition of the new table is stored in T-SQL in file Table1.sql. Also note that the project and the files in it can be added to the source control system and checked in and out like any other Visual Studio project. In this example, SQL Server Data Tools is using the source control in Team Foundation Server 2010 and the red ticks next to the files indicate they are checked out.

Source Control For Database Applications 3

To create and deploy the .dacpac file for this Data-Tier application, right-click on the project and select Publish. The .dacpac file is created in the bin > Debug folder and the database and the table are deployed to a SQL Server of your choice as shown below (in SQL Server Management Studio).

Source Control For Database Applications 4

This is not just for one off deployments however and it is able to manage the transition between versions. When publishing a DAC a database schema compare is automatically carried out and ALTER TABLE statements generated and executed to manage the change on the database server. Let’s take a very simple example of this in practice:

  • Database project and Table1.sql have been checked into TFS
  • Database1 and Table1 have already been published to SQL Server
  • Table1 is edited in Visual Studio  and a new column data2 is added

Source Control For Database Applications 5

  • When development is complete the files are checked back into TFS so that the source control system has a full record of all schema changes
  • The database project is then published to SQL Server to add the new column onto Table1 as shown below in SQL Server Management Studio

Power View Business Inteligence For Power Users 6

  • The actual SQL script used to carry out the deployment is also stored in the bin > debug folder and on inspection, it contains the required statement:

ALTER TABLE [dbo].[Table1]

ADD [data2] NCHAR (10) NULL;

The above shows how to manage a database development using Visual Studio while:

  • Still using graphical tools to design tables etc
  • Keeping track of all source changes in a source control system
  • Deploying new databases and database changes to SQL Servers in a controlled manner using a single file

In Closing

Obviously the above is a very simple example just using a single table. The same process however can be applied to however many tables you need, along with views, stored procedures, functions and everything else you need.

Post Terms: Database | source control

About the Author

As Co-Founder of Ballard Chalmers, Geoff is the company’s CTO, directing technical strategy, overseeing technical consultants, managing larger development projects and ensuring technical delivery quality standards.

Education, Membership & Awards

Geoff graduated from the University of London with a BSc (Hons) in Mathematics and computing and a Masters degree in Database Management Systems. Additionally, he is a certified SQL Server Development, Microsoft Certified IT Professional (MCITP) and Microsoft Certified Technical Specialist (MCTS).

His professional memberships include Charted Engineer (Software), Member of the British Computer Society (MBCS), Member of European Federation of Engineers (FEANI) and Fellow of the Institute of Analysts and Programmers (FIAP).


Back To Top
Contact us for a chat