Awhile, back I answered a LinkedIn question on version control for database objects.  After continuing to use Visual Studio for Database projects for an additional four years since the original question was asked, my answer remains the same.

I’ve updated the response slightly to use some of the newer product nomenclature. The original link as well as some of the other responses can be found on LinkedIn here.

Question: What is your choice of CM tool for Microsoft SQL Server?

What is your choice of configuration management (aka change or version management) tool for SQL Server? I’m looking for one which tightly couples with SQL Server and is able to deploy changed DDL scripts as a package. It would help that the tool would be integrated well with Microsoft Visual Studio as well.

Please tell me your experiences, good or bad in as much detail as possible.

Thanks!

Answer:

I’m a big fan of Visual Studio Database Projects (aka Team Database Edition, aka VSTDB).  Visual Studio Database projects were designed specifically for integration with SQL Server.  It works exactly as you’ve requested: It creates a single SQL script from source control which allows you to deploy all changed objects between source control and the target database.  Also a word of warning that much of the standard advice regarding source control applies well to application code and *possibly* stored procedures but will require very strict processes/training and regular review to work well with tables.

Regardless of what tool you use here are a few of the standard things you would probably like to be able to do with your DB under source control:

1. Compare different versions of an object. (“When and who dropped column x from table a?”)
2. Create a specific version of the database from source control (“We need a new test instance of the production database but the production DB is too big to backup and restore in a timely manner” or “We need to recreate an older version of our database to troubleshoot an issue a customer is having with an older version of our product.) 3. Deploy all database code, tables and procs, using a single process quickly and efficiently.

With these goals in mind the following questions need to be answered:

1. Does the development team check in “create” scripts or “alter” scripts or a combination depending on the situation? (In DB pro you always check in a “create” and the engine converts it to an alter when it generates the deploy script).
2. How do I control the order scripts are deployed? For example, if I am creating parent and child tables with a foreign key between them, how do I force the tables to be created before the foreign keys? (With DB Pro every object, from index and foreign key to stored proc is checked in as an individual file, the build engine then generates a single deploy script with the DDL automatically ordered correctly).

All of these questions and goals can be handled by using a combination of other tools, such as subversion + change director, but the result will likely be a fairly manual process which can be tricky to troubleshoot. A Visual Studio Database Project addresses these issues out of the box.  As with any tool it is not without its downsides, you more than likely will need to modify your existing development processes to fit the way the tool works and cost is of course a factor. Overall though, it’s an excellent tool. In our case it allows our team to perform relatively painless weekly builds with a database containing 400 tables and 1,800 procs. Hope that helps!

Follow Up Question: Team makeup

I am curious to know the structure of the team which implemented the solution and also who manages/maintains this on a day to day basis? Is it a one person show?

Follow Up Answer:

I’ve used two different team structures with Visual Studio Database Projects:

1. Team of software developers with a single database developer who was the sole user of VSTDB. The database develoepr wrote all DB code, checked in all DB code and performed the builds and deploys. On the rare occasion that another developer wrote DB code it was sent to the database developer for review and check-in.
2. Team of software developers writing database code, with three DBAs with VSTDB. Software develoeprs would check DB code into TFS source control DBAs would add new files to the VSTDB project (mods to existing files were picked up automatically by doing a get latest). DBAs perform build and deploy and coordinate with software developers to resolve any issues. This is actually the model for our current team.

 

Looking for more information on Visual Studio’s Database specific features?  The following link provides a pretty decent launching point if you are not familiar with the product: Getting Started with Database Features in Visual Studio