For some time I wanted to document the possibilities of the SQL Server Database Project and how developers and DBA’s can benefit from it.

Problem Statement

Many companies I’ve worked for struggle with their database lifecycle management:

  • Production databases are copied to Test, Staging, UAT… environments by the DBA’s with production (sensitive) data
  • DBA’s spend too much time doing the copy operations
  • Backup chains are broken because of the lack of use of the WITH COPY_ONLY option
  • Changes are made by the DBA executing individual scripts, sometimes breaking because the development database and production database were out of sync.
  • Bugs are “Emergency” fixed in the production database so the schemas of the development and production database are out of sync

Possible solution

In general there is not much to learn from a developer (just kidding!) but normally they have some software lifecycle management and typically use a Source Control Provider to keep version information and the production version of their software. With SQL Server Data Tools and using a Database Project we should be able to setup a similar lifecycle management for our databases. In my next series of posts I will explore the possibilities of the Database Project and share them with you.

Getting started – Choosing a Source Control Provider

Again as a DBA I did not know what Source Control Provider to choose, I worked with Visual Source Safe a decade ago and had to do a setup of Team Foundation Server once and didn’t want to go through this pain again. A quick poll on twitter on which Source Control Provider to use was depressing. Probably because our American friends were sleeping, the Belgian Developers were still hitting the snooze button and Christiaan was walking his dog. Lucky for me Dave Dustin (twitter) from the other side of the world gave me some possibilities. In the next example I will use AnkhSVN, Subversion with Support for Visual Studio. You can download the setup from here.

The setup

Launch the installer, accept the license agreement and hit Install:

Off course you have to allow the program to install software:

Et voila, the setup is finished:

When you open SQL Server Data Tools and open the Options pane in the Tools menu you should see that AnkhSVN – Subversion Support for Visual Studio is selected as the Current Source Control plug in, if not you can select it from the list:

Now that we have a Source Control provider we can get started with our database project but this will be the subject of my next post.