Menu
Having to upgrade a database schema makes installing a new release of software a lot trickier. What are the best practices for doing this?
I'm looking for a checklist or timeline of action items, such as
etc, showing how to minimize risk and downtime. Issues such as
are especially of interest.
934
To change the schema of a table by using SQL Server Management Studio, in Object Explorer, right-click on the table and then click Design. Press F4 to open the Properties window. In the Schema box, select a new schema. ALTER SCHEMA uses a schema level lock.
What Does Schema Change Mean? A schema change is an alteration made to a collection of logical structures (or schema objects) in a database. Schema changes are generally made using structured query language (SQL) and are typically implemented during maintenance windows.
Database schema changes are not popular among DBAs, not when you are operating production databases and cannot afford to switch off the service during a maintenance window. These are unfortunately frequent and necessary, especially when introducing new features to existing applications.
I have a lot of experience with this. My application is highly iterative, and schema changes happen frequently. I do a production release roughly every 2 to 3 weeks, with 50-100 items cleared from my FogBugz list for each one. Every release we've done over the last few years has required schema changes to support new features.
The key to this is to practice the changes several times in a test environment before actually making them on the live servers.
I keep a deployment checklist file that is copied from a template and then heavily edited for each release with anything that is out of the ordinary.
I have two scripts that I run on the database, one for schema changes, one for programmability (procedures, views, etc). The changes script is coded by hand, and the one with the procs is scripted via Powershell. The change script is run when everything is turned off (you have to pick a time that annoys the least amount of users for this), and it is run command by command, manually, just in case anything goes weird. The most common problem I have run into is adding a unique constraint that fails due to duplicate rows.
When preparing for an integration testing cycle, I go through my checklist on a test server, as if that server was production. Then, in addition to that, I go get an actual copy of the production database (this is a good time to swap out your offsite backups), and I run the scripts on a restored local version (which is also good because it proves my latest backup is sound). I'm killing a lot of birds with one stone here.
So that's 4 databases total:
You really, really need to get it right when you do it on production. Backing out schema changes is hard.
As far as hotfixes, I will only ever hotfix procedures, never schema, unless it's a very isolated change and crucial for the business.
91
I guess you have considered the reads of Scott Ambler? http://www.agiledata.org/essays/databaseRefactoring.html
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
