Blog Post

SQL Server under source control with Team Foundation Services and Red-Gate Source Control

,

During the last few years I’ve been involved in database source control management and release plans tasks. These are important and (mostly) underestimated topics.

You can implement source control management in many different ways

  • Visual Studio and TFS/SVN/GIT/..
  • Direct integration with source control tools and filesystem
  • Sql Server Management Studio integrated with TFS/SVN/GIT/..
  • Database backups
  • misc..

IMHO, putting databases under source control management should be mandatory for every team.

In this post, I will try to explain how SQL Server Management Studio can cooperate with one of the third party tools that I’m currently using. This is the scenario:

Prerequisites: Red-Gate source control is available for Team Foundation Service starting from version 3.1. In addition you have to change one of the config files as described in this post.

We can manage database objects like code when using the tools described above. We can think of it as “creating a database for each team“, technically a branch for each team. This kind of approach allows us to reduce the possibility of regressions, bugs, phantom edits and so on.

Let’s try and connect a database to TFS via HTTP, using the source control plugin. We can find a new option into the right-click menu (at the database level), “Link database to source control..“:

01_linkdb_sourcecontrol
04_tfsConfig

After the link is added, a popup tries to connect to TFS services and if you are not logged in, you will have to provide your credentials in order to authenticate. When logged in you can navigate the project tree directly. We will use the “SampleProject” project with a previously created folder called “CoreDB” that will contain the source code. In this example we’re using a “Dedicated database” approach. This means that every client/developer which will connect via SSMS to the database will develop using a dedicated version of the linked database. Those versions will be available on the real database only when check-in operations from branches will occur.

Now the database is connected to source control, as we can see also from SSMS:

05_linked

Every change on that database will change the icons on each object level. Green database = database linked to source control.

Now, let’s try to create a new table with the following script:

CREATE TABLE dbo.Foo
(
        idFoo int IDENTITY(1, 1) NOT NULL
      , FooData varchar(30) NOT NULL
      , CONSTRAINT PK_dboFoo PRIMARY KEY CLUSTERED
      (
          idFoo ASC
      )
);
After a while (500 ms by default) a polling procedure under the hood will raise the “change” event and a new icon will appear on the table level:
06_newTable

That filled blue circle indicates a new object or a simple edit. If something is dropped an outlined circle will appear instead.

If another developer opens the same database he will not see any edits made by us. If we check-in some changes (the so called changeset), other developers will see them only after issuing a get latest version command. Choosing this pattern will reduce the likelihood of creating unmanageable conflicts. Every team can work without interfering with the others. However it is very important to understand how to commit the code into source control. Every project needs somebody in charge of merging the conflicts and choosing between versions, in order to reduce the possibility of regressions.

Going back to the example, it’s time to see what happens on the TFService side when changes are committed. If we open the code section of TFS we won’t see any items, because changes are pending on the developer machine. With a check-in operation we‘ll submit the changeset to the source control server.
We can try to make the example more interesting. Let’s create a backlog item and two linked tasks that summarize further operations on the table we just created:
07_SprintTask
We are on the “first” iteration, called Sprint 1. There is only a backlog item (add dbo.Foo Table) which has two children:
  • create the dbo.Foo table
  • populate the dbo.Foo table
Every task is assigned to me.
Now, let’s try to associate our changeset to the task 421 (“create the table“), using SSMS and the red-gate plugin:
08_readycheckin
Looking closely at the syntax of the check-in comment (“Table dbo.Foo created #R421?), we will spot some notable syntax bits:
The section before the # char is the real comment that will be sent to the source control server.
#R stands for resolve
but you can use also #A for associate
The number after it is the task id.
We’re using resolve, so the task will be closed as a “Done” task, and sent to TFService:
09_statustask
If we open the task (double click on web interface, but you can use Visual Studio too) we will also notice the changeset:
10_changeset
It is also possible to see the code that we sent via the commit/check-in operation. We need to navigate to the CODE section (via web interface) and open the change-set 283:
11_code

This is the DDL script stored on a .sql file. Every change in the definition of the dbo.Foo table will be stored on this file with a version history.

Now, let’s try to manage edits and data, using the same tools. First of all, we need to tell the red-gate plugin which are the tables we want to track for data changes. We can do that by right-clicking the database and selecting “Other SQL Source Control Tasks” first, and then “Link/Unlink Static Data..“:

12_staticdata
The next window allows us to select the tables we want to track for changes. In this case, we just have one table, so it won’t be a difficult choice.  We will now change the definition of the dbo.Foo table. The following script will perform both operations:
ALTER TABLE dbo.Foo ALTER COLUMN FooData varchar(100) NULL;
GO
INSERT INTO dbo.Foo ( FooData )
VALUES
'ONE' ),
  ( 'TWO' ),
  ( 'THREE' ),
  ( 'FOUR' ),
  ( 'FIVE' );
GO

If we open the source control tab on SSMS (Commit Changes) we will notice a new entry. The table dbo.Foo has a new record with the data changes:

13_datalink
We have a definition change (ALTER operation) and a Data Link changeset for the inserts. This check-in resolves the task with id 422. With TFS we can now view the differences between the two change-sets with the “Compare” command:
14_compare
Data records are stored on the source control, CODE section, under the dedicated folder:
15_datadiff

I’ve never used Visual Studio “database” editions before. Starting from Visual Studio 2010 the SQL Server projects with SQL Server Data Tools have been improved and now I think that it could be a powerful IDE to use, since it is included on the SQL Server suite. However, the plugin described in this post is integrated with SSMS, which is the best IDE for SQL Server management, more comfortable for DBAs and DBDevs, since it’s a shell integrated plugin. It also reduces the learning curve for those who are not familiar with Visual Studio.

In these examples we have seen how simple integrating the database management with source control management can be. Creating branches for every team/developer isn’t any harder either. In our example we took into account just a single table, but when  many objects like tables, views, stored procedures and functions come into play, it is important to have a centralized tool that keeps track of who changed what, and to ensure synchronization. The deployment processes will be simpler as well. Changesets based releases can be more reliable and allows us to have more control of the code that is ready to be promoted.

I strongly suggest that you put your databases under source control. Don’t underestimate the database side of your projects. There are many refactoring patterns available, however do yourself a favor and try to keep the changes with history and versions on a reliable source control server. It can be an additional amount of work initially, but you will achieve a lot of advantages later: versioning, branching, merging, less regressions, better release plan management.

Think about an AGILE development and imagine three teams working on branches, maybe on the same database: every iteration goal could involve shared objects in the database design. At a first sight this can be a simple trouble to manage with patterns (like backward compatibility) but if the number of teams/development branches grows, this will be a bottleneck while trying to resolve conflicts and to avoid regressions.

Now imagine what would happen if a particular implementation incorporates changes to an object that at least two of the three teams must change. If changes are incremental, you won’t have any problems. If they change the same information they will get conflicts, changes that overwrite previous changes, and sometimes irreversible regressions. Especially when it comes to data, which cannot be rebuilt in all cases.

That’s it. With this kind of tools and approaches we try to make easier the development organization, and related to this we try to set up a better way to follow for the application lifecycle management.

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating