Integrate source control with SQL Server to reduce database development time

Collaborating on database development introduces a series of challenges. For instance, having the development team change the tables, views, stored procedures and other objects in a single, shared database, although intuitive, can introduce severe issues down the road as valid changes can be lost or overwritten by an unsuspecting teammate. This issue might be mitigated by restoring a database backup – under the assumption a valid backup exists. Even if it does, overwriting the development database with a backup means losing all of the valid database changes that have occurred since the backup was taken; not to mention the fact that restoring a large backup can take time – during which none of the developers can work on the database being restored.

Last but not least, a key prerequisite for successful database development is to have the ability to recreate the database at any point in time. This is particularly important if the database is shipped to clients – if a bug is found in a specific database build the ability to quickly revert to the exact same conditions that caused the issue is paramount in resolving it as quickly as possible.

The best way to address all of these challenges is introducing database version control. However, simply requiring the development teams to use a SQL database source control management system isn’t the magic bullet which will prevent issues like the ones mentioned above from happening. On its own, database source control introduces additional overhead as the developers are required to save the changes they’ve made to an object in a separate SQL script, open the source control system client, check in the SQL script file using the client and then apply the changes to the live database. If this order is ignored, for instance if the database is updated and the versioned SQL script hasn’t been checked in, changes might be overwritten. Furthermore, having the object’s script checked out in the SQL version control system, won’t prevent anyone from modifying the live object, causing discrepancies between the database and the code which can be hard to detect and even harder to troubleshoot down the line.

This is where ApexSQL Source Control can help.

ApexSQL Source Control is a SQL versioning add-in, which allows you to version control SQL databases and objects directly from within SQL Server Management Studio. It integrates with all major source control systems and provides you with all object versioning features, without the need for an additional source control client. For more information visit the ApexSQL Source Control product page.

To leverage the benefits which SQL Server source control systems provide using ApexSQL Source Control perform the following steps:

  1. Start SQL Server Management Studio

  2. Right-click the database you want to version control

  3. In the context menu, navigate to the ApexSQL Source Control sub-menu

  4. Click the Link database to source control option

  5. Specify the source control system where the versioned scripts of the objects will reside, and the development model

  6. Object filtering provides possibility to exclude objects not to be linked to source control. It is possible to exclude specific object, exclude objects by owner or type:

  7. Provide the login information for the version control management system:

  8. Click the Finish button
  9. The Action center window will open and offer objects to be committed to repository as default action if repo is empty:

  10. Even if initial commit on source control repository is not performed, the objects are automatically under source control and can be accessed using Object Explorer pane in SSMS. Once the database has been put under source control all of the operations that can be executed from a source control client will be available from the Object Explorer pane, including checking out with or without lock the versioned objects, view history of that object and apply specific revision, view changes on that object that were made and place data from table to source control using the Link static data:

April 4, 2013