How do you deal with configuration management of Database Tables?

Question

How do you deal with source control management and automated deployment (configuration management) of database tables. I work in a SQL Server environment and it's pretty easy to script out drop and create files for stored procedures/triggers/functions even jobs. It's also easy to handle scripting out the creation of a new db table. However, if at a later point you want to modify that table, you can't necessarily just drop it and recreate it with the new field for fear of losing data. Is there an automated way to deal with this problem? Do you script out a temp table and backfill after updating the new changed table? (could be rough if there is a lot of data)

Any suggestions would be greatly appreciated.

Answer 1

It varies, depending on how you want to treat existing data and how extensive the schema changes are, but even in Management Studio, before you commit changes, you can generate a script of all the changes.

For a lot of data or where there are constraints or foreign keys, even simple ALTER operations can take a significant amount of time.

Answer 2

There are tools available that help you develop your schema, develop changes, version those changes and will help you compare the differences between versions and even generate the SQL to make the DDL changes.

For example, check out Embarcadero Change Manager and other products offered by Embarcardero.

Answer 3

You can automatically create the initial creation script, but ALTER scripts really need to be hand-coded on a case-by-case basis, because in practice you need to do custom stuff in them.

In any case, you'll need some way of creating apply and rollback scripts for each change, and have an installer script which runs them (and a rollback which rolls them back of course). Such an installer should probably remember what version the schema is in, and run all the necessary migrations, in the right order.

See my article here:

http://marksverbiage.blogspot.com/2008/07/versioning-your-schema.html

Answer 4

Tools like Red-gate's SQL Compare are invaluable in making sure you have a complete script. You still may need to manually adjust it to make sure the objects are scripted in the correct order. Make sure to script triggers and constraints, etc as well as tables.In general you will want to use alter commands instead of drop and create especially if the table is at all large.

All our tables and functions and stored procs are required to be under source control as well, so we can return to old versions if need be. Also our dbas periodically delte anything they find not in Source COntrol, so that keeps developers from forgetting to do it.

Of course all development scripts being promoted to production should be run on a QA or staging server first to ensure the script will run properly (and with no changes required) before it is run on prod. Also the timing of running on prod needs to be considered, you don't want to lock out users especially during busy periods and time has shown that loading scripts to production late on Friday afternoon is usually a bad idea.

Answer 5

Ohh forgot to say, make sure you have a good set of database backups before loading schema changes to production. Better safe than sorry.