Sql – Versioning SQL Server

gitmercurialsql-server-2005svnversion control

My development group uses Visual Source Safe for version control; this choice was originally made due to cost and its tight integration with Visual Studio.

As our repository has grown Source Safe has really started to show its limitations and we are considering moving to another solution. Up for discussion are Team Foundation Server, Subversion, Git, and Mercurial.

We are largely a data shop, so another major factor for us is being able to easily version SQL Server 2005/2008 projects. This is one of the benefits of using Source Safe, and also of Team Foundation Server – the integration with Microsoft SQL Server Management Studio.

I'm wondering if anyone has had experience versioning SQL Server with Subversion, Git, or Mercurial and can provide some solid pros/cons for each of these systems, as well as how you went about implementing them.

Best Answer

My honest answer is don't do any integration with your database tooling and SCM if you can avoid it. Use the filesystem where possible. It's another layer of integration which is going to be a pain. Small separate tools are better than a behemoth.

We use Subversion and SQL 2005 together in the following manor:

We use TortoiseSVN only. No VS/SSMS integration at all.
We have a principle of "automate everything", so we never rely on GUI tools to do work.
We keep all scripts inside SVN along with the code. The code, schema and scripts are versioned together.
Schema changes are numbered in order of application i.e. 000-create-table-users.sql. We write down the maximum script number deployed in each environment. Each script performs a migration to the next database r number. When we deploy, we check out the source and run all scripts from the last version number to the highest number.
Any non-schema scripts (sprocs/views) are idempotent (can be executed any number of times with the same result). They are applied via a nant plugin we wrote. These are replaced every time we deploy. Don't forget to refresh your views!
We avoid any scripts where possible anyway as we use NHibernate so there are less problems with script versioning anyway.

From this structure, we can re-create the environment and database at any point in time on any machine which is important.

We do NOT use it for unit testing however - we rely on the NHibernate schema generation to do this on top of an SQLite database.

The only negative point we've encountered has been making sure that developers adhere to the process. Herding cats is a very appropriate description.

Database Upgrade Scripts

A sequence database upgrade scripts that contain the DDL necessary to move the schema from version N to N+1. (These go in your version control system.) A _version_history_ table, something like

create table VersionHistory (
    Version int primary key,
    UpgradeStart datetime not null,
    UpgradeEnd datetime
    );

gets a new entry every time an upgrade script runs which corresponds to the new version.

This ensures that it's easy to see what version of the database schema exists and that database upgrade scripts are run only once. Again, these are not database dumps. Rather, each script represents the changes necessary to move from one version to the next. They're the script that you apply to your production database to "upgrade" it.

Developer Sandbox Synchronization

A script to backup, sanitize, and shrink a production database. Run this after each upgrade to the production DB.
A script to restore (and tweak, if necessary) the backup on a developer's workstation. Each developer runs this script after each upgrade to the production DB.

A caveat: My automated tests run against a schema-correct but empty database, so this advice will not perfectly suit your needs.

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.