How to Version Your Database Schema

How do you version your database schema?

See

Is there a version control system for database structure changes?

How do I version my MS SQL database in SVN?

and Jeff's article

Get Your Database Under Version Control

I feel your pain, and I wish there were a better answer. This might be closer to what you were looking for.

Mechanisms for tracking DB schema changes

Generally, I feel there is no adequate, accepted solution to this, and I roll my own in this area.

Best Practices for storing a database schema version in SQL Server?

I'm the product manager for SQL Source Control and SQL Compare at Red Gate. We had to solve this very same problem as our tool needs to know which version the databases were at in order to select the appropriate migration scripts to build the full deployment script.

We considered a version table, which is the most commonly devised home-grown solution. However, from our research we learnt that users wanted to keep the set of database objects 'unpolluted' so we opted for the database level extended property. We append this to the scripts as follows:

IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))
  EXEC sp_dropextendedproperty N'SQLSourceControl Database Revision', NULL, NULL, NULL, NULL, NULL, NULL
EXEC sp_addextendedproperty N'SQLSourceControl Database Revision', @RG_SC_VERSION, NULL, NULL, NULL, NULL, NULL, NULL

When the database is loaded into SQL Compare, it performs a check to ensure that the version that it claims to be corresponds to the version as stored in source control.

Hope this helps!

How can I do version control of Database Schema?

Here is a nice article by Jeff Atwood on database version control

You can use Team edition for database professionals for this purpose

Here is a list of tools that you can purchase which can be used too:

Red Gate SQL Compare from $295.

DB Ghost from $195

SQL Change Manager $995 per instance.

SQL Effects Clarity standard ed. from $139

SQLSourceSafe from $129.

sqlXpress Diff contact for price. :-(

Embarcadero Change Manager contact for price. :-(

Apex SQL Diff from $399

SQL Source Control 2003 from $199

SASSI v2.0 professional from $180

Evorex Source # shareware or $299+ (conflicting reports!)

Edit Just found this post which explains version control through svn: Versioning SQL Server database

How to do version control for SQL Server database?

Martin Fowler wrote my favorite article on the subject, http://martinfowler.com/articles/evodb.html. I choose not to put schema dumps in under version control as alumb and others suggest because I want an easy way to upgrade my production database.

For a web application where I'll have a single production database instance, I use two techniques:

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

1. A script to backup, sanitize, and shrink a production database. Run this after each upgrade to the production DB.
2. 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 Server Database schema versioning and update

I use database extended properties, see Version Control and your Database:

SELECT [value] 
    from ::fn_listextendedproperty (
        'MyApplication DB Version', 
        default, default, default, default, default, default);

...

EXEC sp_updateextendedproperty 
    @name = N'MyApplication DB Version', @value = '1.2';
GO

How to version control a record in a database

Let's say you have a FOO table that admins and users can update. Most of the time you can write queries against the FOO table. Happy days.

Then, I would create a FOO_HISTORY table. This has all the columns of the FOO table. The primary key is the same as FOO plus a RevisionNumber column. There is a foreign key from FOO_HISTORY to FOO. You might also add columns related to the revision such as the UserId and RevisionDate. Populate the RevisionNumbers in an ever-increasing fashion across all the *_HISTORY tables (i.e. from an Oracle sequence or equivalent). Do not rely on there only being one change in a second (i.e. do not put RevisionDate into the primary key).

Now, every time you update FOO, just before you do the update you insert the old values into FOO_HISTORY. You do this at some fundamental level in your design so that programmers can't accidentally miss this step.

If you want to delete a row from FOO you have some choices. Either cascade and delete all the history, or perform a logical delete by flagging FOO as deleted.

This solution is good when you are largely interested in the current values and only occasionally in the history. If you always need the history then you can put effective start and end dates and keep all the records in FOO itself. Every query then needs to check those dates.

Keeping a database Schema upto date

I can highly recommend Liquibase. It really does work - I've used it and was very impressed.

Essentially, it keeps its own log of statements run on a database and runs them only if not already run/needed. It is XML driven and allows you to use optional pre- and post-execution statements and conditions. You check your XML files into your source control and invoke it from your build tool. It's even suitable for driving production releases.

It's magic.

Update SQL Server database schema using MVC .net with versioning

Your web app can be recycled for multiple reasons that are beyond your control. So placing code within Application_Start to run scripts that bring your database up to date is not optimal for the reasons you just mentioned.

Since you're already using a Dev Ops pipeline, there's a built in way to handle this as part of your deployment. Check out the Database Project type in Visual Studio. This will allow you to script all of your tables, stored procedures, views, etc in text form and keep it under source control. Then, as part of your Dev Ops pipeline, you can use the Visual Studio Build task to build your Database project and create a DACPAC as output. Then, use the Azure SQL Database Deployment Task to pick up the DACPAC output and deploy it to your Azure SQL instance.

This way, you KNOW that your database is already upgraded before you finish your production deployment.

---

Related Topics