how to manage sql structure updates

Question

How do people recommend managing database structure updates?

e.g. new features are added that require existing tables being altered, new ones added etc.

Is there a good piece of software or is it a case of writing ALTER type statements.

Answer 1

While there is software that can analyze the differences between two schemas (RedGate SQL Compare) and generate the necessary change scripts, I prefer to write my own database change statements. That way, I have full control over what is being changed -- nothing more, nothing less. Roll the changes into an Install.sql script, or something like that, for each database version so you can just run that script and update the database.

This makes it easy to move changes from dev to test to production.

See: Deploying SQL Server Databases from Test to Live

Answer 2

If you don't want to hand write all the alter statements, SQL Server Management Studio has a GUI to deal with all of this type of stuff. You can use the GUI and then look at the script it generates and go from there if you want some kind of quick, hybrid approach.

Answer 3

I'm going to add one thing. It is not enough to write an alter table statement to change a table structure. If you are changing a table structure, you had better be sure before you run it that you know exactly what other views,functions, tables, stored procs, triggers, SSIS(DTS) packages (for SQL Server) and dynamic code from the applications will be affected by the change. If you are not completely sure what other objects may be impacted, you are not ready to alter the table. I've seen way too many business critical functions break because someone hapahazardly changed a table structure without considering what else used that structure. If you are considering making database structural changes, I suggest you read up on database refactoring before you do so.

Here isa good book to start with: http://www.amazon.com/Refactoring-Databases-Evolutionary-Addison-Wesley-Signature/dp/0321293533

Answer 4

You don't mention the scope of your application or the number of developers, etc., so it is a little hard to make any recommendations. However, if your development consists of multiple concurrent projects and multiple developers and you are copying from a Development to Production I would recommend something like the following:

• implement 3 "areas": dev, qa, production.
• develop all changes in dev, create all changes in scripts, use something like cvs to track changes on all objects
• when changes are ready and tested, run your scripts in qa, this will validate your scripts and install procedure
• when ready run your scripts and install procedure on production

note: qa is almost identical to production, except applied changes waiting for their final production install. dev contains any work in progress changes, extra debug junk, etc. You can periodically restore a production backup onto qa and dev to resync them (just make sure all developers are aware of this and plan accordingly), because (depending on the number of developers) they (production vs. qa vs. dev) will start to incur more differences over time.