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 corner stone 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 scripts 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.
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 makes database source control management easier.
A Data-Tier Application (or DAC) is 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.
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.
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)
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.
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).
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
· 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
· 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
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.
