Need a better way to manage database schema changes

Question

Currently, we manage database (SQL Server) changes with a series of numbered scripts. One folder holds scripts that can only be run once (table alters, data initialization, etc). Another holds all scripts that can be run multiple times without fear of them destroying anything (stored procedures, functions, etc).

Typically, when we need to make a change to the database, we'll create a script for the change, number it, stick it in the appropriate folder, and commit it. When we do pushes, I keep notes on the last number that was run, and run all of the subsequent scripts. I know there's a better way to do this, but I wanted to get your opinion on methods that work for you. I'm OK with a commercial solution, but an open source or free one would be preferred.

My motivation for this is that I don't want to have to think about what's changed, and I want to cut as much human error out of the process as possible.

EDIT: Just to clarify, this is an ASP.NET app, with SQL Server. We currently do version control with Subversion.

Answer 1

You could store the "script number" in the database. Subsequent runs could automatically execute anything numbered higher than that (in increasing order)

Answer 2

I base my answer a bit on what I know, ie the ruby on rails migrations.

Keep track of the database version in a table somewhere and like that you can only execute the scripts that interest you. You should take a look at what they are doing with rails, it might help you get your head around the concept and write something on your own.

http://railscasts.com/episodes/107-migrations-in-rails-2-1 http://guides.rubyonrails.org/migrations.html

I don't know of anything already doing that for SQL Server, but I'm not a big SQL Server user, so try looking that up.

Answer 3

You can also use a tool to compare two databases and generate change scripts.

1. SQL Compare
2. SQL Delta

In a previous job about a week before a release we would run a compare tool between development and production. We'd back up the development and production database, then restore the production db to development. Then we'd run the change script in development to make sure everything worked. If all was good, we just ran that script on production.

It actually looks like there are number of these tools, I can vouch for the two I have listed, but if you are interested in others, see this http://en.wikipedia.org/wiki/Microsoft_SQL_Server_Compare_Tools

Answer 4

SVN? - You can get the latest versions via command line. You can set up a process that gets the latest from a Production trunk (or QA, TEST, etc) then runs each of the scripts (in the order you choose). This also allows you to safely and properly manage your source. You can label your trunks by database version or whatever value you see fit.

Answer 5

Do you have access to any kind of version control software? Even our old frient Microsoft SourceSafe? They make it easy to automatically keep track of stuff like this; plus a number of other benefits I would expect you'll appreciate if you use one (like seeing differences between versions, etc.)

If minimally it would be less work than you're doing now.

Answer 6

You can look into using migrations to help remove the manual parts of the process. Migration utilities are very similar to how you are doing it now, the programs just provide you with some infrastructure to make it an easier process. Depending on the program you choose, you can either write the migrations in code or in sql script. If you choose one that uses sql script for database updates, it sounds like it could be a pretty seamless transfer from your home-grown system.

I use Migrator.Net but there are also a few other options available. Migrator.net provides command line access or nant/msbuild tasks. Migrations are well supported in ruby on rails, so that may work if rails fits better in your tool set. This blog has a run-down of some .net options to give you a feel for what's available.

Answer 7

Check out Microsoft Visual Studio Team System Database edition. It pulls a local copy of the database and treats it like a regular project file. It can compile, deploy, version control, perform schema and data comparisons, as well as sync both.

Plus many, many more.

Answer 8

There is no easy way to accomplish this through tools alone. Tools are good but unless you have your DB clamped down so that nobody other than you can make changes there are always other paths into the DB from which new objects can mysteriously appear.

Consider that scripts that create objects are essentially code. In the same way source code control allows you traceability to any particular state/version your app source is in, it can help you with your scripts. But because of the multiple paths beyond scripts that can result in new or altered objects, source code management only solves part of the problem.

Let's consider the DB in a known state if all the objects in it map to a script of a specific version in an SCM tool. That DB is said to be conformant to a specific DB version. Conversly, if all the objects defined in SCM exist in the DB, then the DB state of the DB = the DB version. If either of these are not true then the DB is in an unknown state, fixable by either a) dropping objects that have no script in SCM, b) creating scripts for such objects and checking into SCM, or c) leaving it in a confomant but unequal state. The last one refers to the case when all objects required for an SCM version exist but then other objects also exist but are considered benign (eg: joes_author_table).

There's a lot of work involved in detecting specific versions of particular objects, so writing the code to do it is not trivial. There are likely some tools that can do it for you, like some of the ones mentioned on other posts. The point is that the manner in which you use the tools, in conjunction with the SCM tool, to be able to determine the state of your DB in terms of its objects will dictate whether you are positioned to meet your objective of knowning what is changed (on demand) and cutting down on human error.

One last note: avoid having all your scripts in anything but an SCM tool. This is because your DB comes with one or more apps whose code is in an SCM. Storing the scripts with the code in the SCM has many advantages.

Answer 9

There's a tool for SQL Version control, maybe you'll find it better than what you are using now. As people already said here, you can use one of the Data compare tools, Red Gate's, or NH Data compare tool.

Answer 10

Keeping databases in sync and up to date is hard - luckily, there are quite a few tools to help.

As many folks have mentioned, there's a slew of good "SQL Diff" tools that allow you to take a difference snapshot between two versions of your database.

Also, Red-Gate has a "SQL Packager" tool which allows you to package up the difference between version A and version A+1 of your database into a stand-alone EXE, or a C# project or a SQL script - whichever works for you. With this, you could easily package up a "upgrade my database from v1 to v2" EXE file in your install and run it to handle the updates.

A fairly more complete, holistic approach to the whole story of DB update management is the DB Ghost product line by Innovartis in the UK. It's really more of a mindset and tools to support it. I like the approach - each dev works on his own local DB copy, checks in any changes to the database into a source control system.

When a release to TEST or PROD is due, a release master can grab the latest "SQL source" from the source control system and generate a stand-alone EXE to create that database from scratch, or upgrade an existing one to that new version.

It looks all very slick and convincing (at least to me), but you'll have to sell your boss on the whole idea - seems like a "either believe in it or not" kinda story. But definitely worth checking out!

Marc

Answer 11

There have been several people here mention SQL diff tools. These can be a lifesaver if you're trying to unpick the difference between two databases in an unknown state, but their usefulness for managing database migrations is very limited and in some cases unreliable. I wrote a blog entry giving a critique of this approach some time ago.

It sounds like your existing approach, of database migration scripts, is a fairly good one. My recommendation is to build some kind of script runner to apply them all to a blank (or reference) database as part of your unit test suite: then if anyone checks in a broken script, it breaks the build. Another thing you need to do is keep a record in the database itself of which change scripts have been run.

I'd recommend that you check out this series of posts by K Scott Allen:

1. Three rules for database work
2. The Baseline
3. Change Scripts
4. Views, Stored Procedures and the Like
5. Branching and Merging

(via Jeff Atwood, Get Your Database Under Version Control)

I'd also recommend the book "Refactoring Databases" by Scott Ambler and Pramod J Sadalage (Addison-Wesley Signature Series) for a more in-depth treatment of the whole database lifecycle management process.

Answer 12

The problem with Diff tools is that you assume you are going to apply all of the changes. What if after a few commits Joe now says he isn't ready to release his changes so hold his stuff back? You are left picking through changes you don't want to release.

We create a branch in Subversion and all of the database changes for the next release are scripted out and checked in. All scripts are repeatable so you can run them multiple times without error - like someone else mentioned in another answer.

We also link the change scripts to issue items or bug ids so we can hold back a change set if needed. We then have an automated build process that looks at the issue items we are releasing and pulls the change scripts from Subversion and creates a single SQL script file with all of the changes sorted appropriately.

This single file is then used to promote the changes to the Test, QA and Production environments. The automated build process also creates database entries documenting the version (date, branch and build id.)

When it is time to collapse the branch back to trunk, we don't include any changes that were not released. (So trunk always reflects what is in the Production environment.)

More details on how we do this can be found HERE