Managing Schemas And Source Control For Databases

Visual Studio has a project type called “Database Project” that is used to manage a database schema and allows for easy integration with source control. Visual Studio Database Projects have been known by a few different names down the years such as datadude, DBPro, teamdata, TSData and Visual Studio Team System for Database Professionals.

A database project is what you can use to manage and provide source control on all aspects of a database (sp’s, views, tables). You can also manage server objects (login’s, server roles).

When using a language like C#, source control is easy as there is only one place to make changes, and that is the source-controlled code itself. But with a live database, you have two places to keep updated: the live database and the source-controlled database project. The two primary methods to keep both places updated:

In SQL Server Object Explorer (SSOX), edit objects in the database project and use a debug database (which can be a test database using LocalDB that is kept in sync with the live database with test data) to verify changes before checking them in. Then push the change to the live database in your preferred way such as Deploy (called Publish in Visual Studio 2012), Schema Compare, or SqlPackage.exe
In SQL Server Object Explorer (SSOX) or SQL Server Management Studio (SSMS), edit and verify while directly connected to your live database. Once verified, use Schema Compare to pull the changes back into your source-controlled database project. Set up the Schema Compare with the live database as Source and the source-controlled database project as Target. After updating the database project, check it in

Note that during a deployment Visual Studio will examine the target database to see what changes (if any) need to be made rather than simply attempting to create lots of objects; if all the objects already exist nothing will be done. You don’t have to determine what needs to be done to change your database schema to the desired state, Visual Studio does it for you. What this means in practice is that you only ever write CREATE … DDL statements rather than IF THEN ALTER …ELSE CREATE … statements.

You can use Visual Studio 2010 (VS2010), Visual Studio 2012 (VS2012), or SQL Server Data Tools (SSDT) to create database projects. SSDT is included with VS2012 and can be added to VS2010 as an add-in (but requires Visual Studio 2010 SP1).

Database projects in VS2010 are called “Visual Studio Database Project” and use the Visual Studio menu “Data”. SSDT calls it “SQL Server Database Project” and use the Visual Studio menu “SQL”. You can install SSDT into VS2010 so you could use both “Visual Studio Database Project” and “SQL Server Database Project”. VS2012 uses only SSDT so in VS2012 you will only see “SQL Server Database Project”. See SQL Server Data Tools in Visual Studio 2012. The schema compare in “Visual Studio Database Project” can compare SQL Server 2008 R2 and earlier databases (NOT SQL Server 2012), and “SQL Server Database Project” can compare SQL Server 2012 and earlier databases. Confusing, I know! Note that the “SQL Server Database Project” has some additional features over “Visual Studio Database Project” but is also missing some features, such as Data Compare, Dependency Viewer, Database Unit Testing, Data Generation (See How to generate test data using Visual Studio 2012?) and Schema View (See SQL Server Data Tools CTP4 vs. VS2010 Database Projects and SQL Server Data Tools: A Work in Progress). The missing features will be added to SSDT over the next few months (the SSDT December update has already added Database Unit Testing) or added to SSDT Power Tools, which has already added Schema View (update: Power Tools is now part of the SSDT December update instead of a separate install). You can use a “Visual Studio Database Project” to do a Data Compare, but be aware it does not work with SQL Server 2012. So until SSDT or Power Tools is updated to support Data Compare, there is currently no way to compare data for SQL Server 2012 databases (UPDATE: The SSDT June 2013 update now supports Data Compare).

SQL Server Data Tools (SSDT), which uses the Visual Studio 2010 shell or the Visual Studio 2012 shell, supports database projects (SQL Server 2012 and earlier). The full version of Visual Studio 2010 supports database projects for SQL Server 2010 and earlier, but not SQL Server 2012. However, if you install sp1 for Visual Studio 2010 and then install SSDT (which requires sp1), it does support database projects for SQL Server 2012, and you can in fact share database projects between Visual Studio 2010, Visual Studio 2012, and SSDT without needing any conversion. See Visual Studio 2012 New Features: Compatibility (aka Project Round-Tripping). Also check out SSDT – Installation confusion if you don’t see an option to create database projects in SSDT. The full version of Visual Studio 2012 supports database projects for SQL Server 2012 and earlier via the built-in SSDT.

In addition to source control I recommend using Continuous Integration (CI) where every time a developer checks-in a piece of code be it a stored procedure, a table definition or whatever, the entire database project is built and then deployed to a database instance. This allows a check of integration errors, for example, changing a table name but not the views that use that table name. See An Overview of Database Build and Deployment. And if using CI I highly recommend using a CI server, such as Hudson or Jenkins or Team Foundation Build, that will orchestrate your whole CI environment. It basically monitors your source control repository and when it spots a checked-in file, kicks off the CI build. It can also synchronize sources, run associated unit tests, perform code analysis, release builds on a file server, and publish build reports.

Visual Studio database projects have many nice features, such as database references, composite projects, code analysis, pre-deployment and post-deployment scripts, refactoring, generate random data, snapshots, and the ability to re-build your database from Powershell using MSBuild.

For source control, my two favorites are Team Foundation Server (TFS) and Subversion (SVN). The Windows client for SVN is TortoiseSVN. And just released is a cloud version of TFS called Team Foundation Service, which is free for a limited time. These products integrate directly into Visual Studio, making it very easy to provide source control for your database schemas.

More info:

Managing Database Change in Source Control

Implementing SQL Server solutions using Visual Studio 2010 Database Projects – a compendium of project experiences

Database Schema Compare where Visual Studio goes that extra mile

Visual Studio 2010 SQL Server Database Projects

SSDT (SQL Server Developer Tool) 2012 (video if using VS 2012/SSDT/TFS)

Visual Studio 2010 Database Development (video if using VS 2010)

TFS v. SVN: 2012 update

TFS vs. Subversion fact check

What advantages does TFS have over Tortoise SVN in this scenario?

Team Foundation Server vs. Subversion – An Objective Comparison

Videos Demystifying Team Foundation Server Builds and Developer Collaboration with Team Foundation Server 2012 and Taking Your Application Lifecycle Management to the Cloud with the Team Foundation Service and Implementing Scrum Using Team Foundation Server 2012