Automated DB Source Control (1 of 5): Overview and Setup

Josh,

So you've run into a situation where a SQL Database is considered the container of production and/or development code.  You would like to source control all the tables, views, stored procedures, and other database objects.  However, saving the TSQL to the appropriate files/project and checking it in can be very cumbersome.  It would be nice if the developer could just focus on getting the appropriate changes in the database they are working on and let the source control be automatically updated each night.  Here is the solution you came up for this...

Overview

The solution was developed around the following technologies:

• Visual Studio Online: Source Control System
• SQL Server 2012: Database to be scripted; SQL Agent Jobs run nightly process
• SQL Server Integration Services (SSIS): Package performs the work and is run by the SQL Agent Job
• C# Custom Library: Consolidates all code around interacting with Source Control; Referenced and called from SSIS Package

The general idea is to have a nightly process to script out all the database objects to files and then check in any changes to source control.

Visual Studio Setup

Visual Studio Online provides the full Team Foundation Server functionality on a hosted environment.  At the time of this writing, it is free for up to 5 users and an unlimited number of stakeholders.  Only users can interact with the source control features, so you will need to have an account setup as at least a basic user.

So that you don't have to use your own username and password, you will want to setup alternate credentials.

If you don't already, you will want to create a project to store the source code.

After this, you will be all ready to move to the next step.