Three phased approaches to getting your SQL Server database into source control

Challenge

Database development teams show an increasing interest in getting their SQL Server databases into source control. However, depending on the team’s actual needs, their development plan and the level of source control integration required, there are various ways to achieve getting a database under source control.

Solutions

We’ll address three phased approaches to source control integration from dipping your toes to jumping in with both feet, each one increasing more sophisticated and powerful, addressing both database objects and static data.

If you are not yet convinced that static data should be put under source control, the article Understanding SQL Server database static data and how it fits into Database lifecycle management can provide you with some additional details.

Periodically scripting a database into source control

The most basic form of source control implies scripting some (or all) database objects, and committing them to a local or remote repository, manually or automatically. In this way, source control is being used primarily to version the entire database (not changes) periodically in order to achieve a “snapshot” in time. The advantage is that it can be done easily, once the scripts are written and set up to be run scheduled and unattended nightly. It also requires no direct integration with source control among the developers on the team. The downside is that since the entire database is versioned, it isn’t possible to view or report on changes only, which limits the value of the repository, for reporting/informational purposes.

This can be accomplished either by using a PowerShell project and a batch file, or with a standalone 3^rd party scripting tool like ApexSQL Script. Both methods are described in the articles:

• How to export a SQL database directly to source control
• Automatically script SQL Server table data and object schema directly to source control
• How to automate batch SQL object scripting and committing to source control

Similarly, static data can be committed using ApexSQL Script, by following the steps in the article How to commit SQL Server table static data to a source control repository.

Although this approach gets the job of putting a database into a repository done, it does not differ much from simply backing up a database systematically.

Automatically keeping the repository updated with changes

A more useful implementation of getting a SQL Server database into source control, is by automatically comparing the local version of a database with the one on the repository and, if differences are detected, update the repository with only the differences. The following articles describes just that, by using ApexSQL Diff as a 3^rd party comparison and synchronization tool, along with some PowerShell and a scheduler to automate the whole process:

• How to keep a source control repository updated with the latest SQL Server database changes
• How to automatically synchronize SQL Server database schema changes to a source control repository
• Automatically comparing and synchronizing SQL Server database schema changes

Full source control integration

For bigger teams, where a number of developers are working and making changes on a certain database simultaneously, a more sophisticated source control system might be needed. The ability to track every change with details about who and when made it, check-in objects in real time, resolve conflicts among different versions of the same object, pull specific versions of certain objects and much more are all implemented in a 3^rd party tool which integrates directly into SSMS, ApexSQL Source Control.

Linking a database to a source control system and initially committing objects is the first step, which is described in the article:

• Getting a database under version control: How to link a database to source control and initially commit objects
• How to commit database objects to SQL Server database Source control

As we have already mentioned the importance of also getting SQL database static data into source control, using ApexSQL Source Control for committing and making changes to static tables is rather straightforward, and each step is explained in the respective articles:

• How to link and initially commit SQL Server database static data
• How to work with version controlled SQL Server database static data

Summary

Once you’ve made a commitment to version control, getting your SQL Server database into source control is the first step.

By following these three approaches, your team can ease into full SQL Server database source control integration gradually, in phases, with each one taking advantage of more of the advantages of source control integration, in turn.