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:
- Red-Gate SQL Source Control as a source control tool (SSMS plugin)
- Team Foundation Service as a source control server (cloud TFS service)
- A simple SQL Server database
- SQL Server Management Studio as a database management IDE
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..“:
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:
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 ) );
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.
- create the dbo.Foo table
- populate the dbo.Foo table
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..“:
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:
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.
