Versioning SQL Server database

Question

I want to get my databases under version control. Does anyone have any advice or recommended articles to get me started?

I'll always want to have at least some data in there (as alumb mentions: user types and administrators). I'll also often want a large collection of generated test data for performance measurements.

Answer 1

+1 for everyone who's recommended the RedGate tools, with an additional recommendation and a caveat.

SqlCompare also has a decently documented API: so you can, for instance, write a console app which syncs your source controlled scripts folder with a CI integration testing database on checkin, so that when someone checks in a change to the schema from their scripts folder it's automatically deployed along with the matching application code change. This helps close the gap with developers who are forgetful about propagating changes in their local db up to a shared development DB (about half of us, I think :) ).

A caveat is that with a scripted solution or otherwise, the RedGate tools are sufficiently smooth that it's easy to forget about SQL realities underlying the abstraction. If you rename all the columns in a table, SqlCompare has no way to map the old columns to the new columns and will drop all the data in the table. It will generate warnings but I've seen people click past that. There's a general point here worth making, I think, that you can only automate DB versioning and upgrade so far - the abstractions are very leaky.

Answer 2

We don't store the database schema, we store the changes to the database. What we do is store the schema changes so that we build a change script for any version of the database and apply it to our customer's databases. I wrote an database utility app that gets distributed with our main application that can read that script and know which updates need to be applied. It also has enough smarts to refresh views and stored procedures as needed.

Answer 3

We had the need to version our SQL database after we migrated to an x64 platform and our old version broke with the migration. We wrote a C# application which used SQLDMO to map out all of the SQL objects to a folder:

                Root
                    ServerName
                       DatabaseName
                          Schema Objects
                             Database Triggers*
                                .ddltrigger.sql
                             Functions
                                ..function.sql
                             Security
                                Roles
                                   Application Roles
                                      .approle.sql
                                   Database Roles
                                      .role.sql
                                Schemas*
                                   .schema.sql
                                Users
                                   .user.sql
                             Storage
                                Full Text Catalogs*
                                   .fulltext.sql
                             Stored Procedures
                                ..proc.sql
                             Synonyms*
                                .synonym.sql
                             Tables
                                ..table.sql
                                Constraints
                                   ...chkconst.sql
                                   ...defconst.sql
                                Indexes
                                   ...index.sql
                                Keys
                                   ...fkey.sql
                                   ...pkey.sql
                                   ...ukey.sql
                                Triggers
                                   ...trigger.sql
                             Types
                                User-defined Data Types
                                   ..uddt.sql
                                XML Schema Collections*
                                   ..xmlschema.sql
                             Views
                                ..view.sql
                                Indexes
                                   ...index.sql
                                Triggers
                                   ...trigger.sql

The application would then compare the newly written version to the version stored in SVN and if there were differences it would update SVN. We determined that running the process once a night was sufficient since we do not make that many changes to SQL. It allows us to track changes to all the objects we care about plus it allows us to rebuild our full schema in the event of a serious problem.

Answer 4

It is a good approach to save database scripts into version control with change scripts so that you can upgrade any one database you have. Also you might want to save schemas for different versions so that you can create a full database without having to apply all the change scripts. Handling the scripts should be automated so that you don't have to do manual work.

I think its important to have a separate database for every developer and not use a shared database. That way the developers can create test cases and development phases independently from other developers.

The automating tool should have means for handling database metadata, which tells what databases are in what state of development and which tables contain version controllable data and so on.

Answer 5

A while ago I found a VB bas module that used DMO and VSS objects to get an entire db scripted off and into VSS. I turned it into a VB Script and posted it here. You could easily take out the VSS calls and use the DMO stuff to generate all the scripts, and then call SVN from the same batch file that calls the VBScript to check them in?

Dave J

Answer 6

Here at Red Gate we offer a tool, SQL Source Control, which uses SQL Compare technology to link your database with a TFS or SVN repository. This tool integrates into SSMS and lets you work as you would normally, except it now lets you commit the objects.

For a migrations-based approach (more suited for automated deployments), we offer ReadyRoll, which creates and manages a set of incremental scripts as a Visual Studio project.

In SQL Source Control it is possible to specify static data tables. These are stored in source control as INSERT statements.

If you're talking about test data, we'd recommend that you either generate test data with a tool or via a post-deployment script you define, or you simply restore a production backup to the dev environment.