Managing Database Change in Source Control

Managing Database Change in Source Control

Rate This
  • Comments 5

This post demonstrates two methods for managing database changes in source control with SQL Server Data Tools.

The standard integration that you would find with any project type in Visual Studio is all there for SSDT Database Projects, but C# developers have it easy – there is only one place to make changes, and that is the source-controlled code itself. But you, database developer, have a live database to deal with! How do you achieve a rapid edit and verify loop with a live database and a source-controlled database project?

There are two primary methods you can use in SSDT, depending on your preference. Let’s say you want to create or change a Stored Procedure, verify it works as expected, and then check it in.

Method 1: Start with the Project

Edit objects in the database project and use the debug database to verify changes before checking in. This is the safest method since the testing is isolated from any production environment.

  1. Specify a debug database for the project in Project Properties -> Debug tab -> Target Connection. By default, this sets up LocalDB for you, but you can use any other database connection. For example, use a test database that is kept in sync with the production database with test data. Find more details on using LocalDB here:
  2. In SQL Server Object Explorer (SSOX), go to the debug database and View Code for the Stored Procedure you want to change, or Add New Stored Procedure. This opens the project source code for that procedure. Make your edits.
  3. There are a few options to verify your changes before checking in the Stored Procedure.

Option 1: Execute directly. Right-click in the Editor and you can execute in place against the debug database. For example, highlight portions of T-SQL and Execute as you build up a query.

Option 2: Hit CTRL+F5 from the project to deploy changes to the debug database. Through SSOX, right-click the Stored Procedure and Execute. You can set a breakpoint within the Stored Procedure to debug.

Option 3: In the project, add a new Script (Not in Build) that executes the Stored Procedure. In Project Properties -> Debug tab -> Startup Action, set the new script as the Startup script. Hit F5. This will deploy and run the Startup script with the debugger, allowing you to verify the Stored Procedure.

       4.  Once verified, check in the Stored Procedure file in your project.

At this point, you have the right code in source control, and can push the change to production in your preferred way (Publish, Schema Compare, SqlPackage.exe).

Method 2: Start with the Database

If you prefer to edit and verify directly connected to your target database, you can still easily get the changes checked in to source control.

  1. In SSOX, View Code on the Stored Procedure you’d like to change or Add New Stored Procedure. Make your edits. Click Update in the object editor to push the changes to the live database.
  2. Right-click the object in SSOX, and choose Execute Procedure or Debug Procedure to verify.
  3. Once verified, use Schema Compare to pull the changes back into your source-controlled database project. Set up the Schema Compare with the database as Source and the database project as Target. You can save the Schema Comparison (.scmp) to your project to make this step even faster for subsequent iterations. After updating the project with the new Stored Procedure, check it in!

At this point, you have the right code in your database and in source control.

Note: If you use SSDT stand-alone, without a full Visual Studio 2010 SKU, you can still integrate with source control providers!
Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post
  • Thanks for this detailed post. I didn't watch the videos, but just read the post, and learned a few things I didn't know - have only played around a little bit with SSDT in 'down times' (like there are any :D) in my current project. We currently use RedGate tools to manage our DB but I'm looking at switching to SSDT once I figure out how to include it in the nightly integration build. Currently we re-create the DB using the RedGate source control command line, and run integration tests against that DB. Do you have any tips on getting that use case going with SSDT (or other suggestions)?

    Until then I'll keep digging around :B

    Got to this post from [ ].

  • Good post, but the videos on youtube are not working.

  • Videos are not working (Error says "This Video is Private"

    I'm somewhat confused because on my Debug Properties tab there is no "Target Connection".  In VS 2012 there is no Database Project type.  I would very much like to learn how to do source control with SSDT as I'm comparing this tool with RedGate Source Control.

  • Videos are still marked Private

    "This Video is Private"

  • I think ApexSQL and Redgate are very good and mature for managing database changes in source control... but they are quite expensive when you want to collaborate in a multi developer environment. i.e. 10 users.

    I have just launched gitSQL. -

    gitSQL is a *free front end GUI for scripting out SQL objects into Flat File for use with Source Control such as GIT.

    *free version limited to 4 objects per type. Types are stored procedures, tables, functions and views.

    The licensed version is cheaper than ApexSQL and RedGate.

    * Redgate = $399

    * ApexSQL = $299

    * gitSQL = $40

    What you get for $40 is unlimited objects Export/Import between SQL Server and Flat file (Source Control).

    Here is an article which describes how to source control SQL Server.

Page 1 of 1 (5 items)