Making Complex Table Changes

Tables are a problem for anyone trying to grow and modify software. Views, stored procedures, functions, all of these objects are easy to modify, but when we start to deal with actual data, where we need to maintain state, we start to have issues with concurrency, performance, and more. These challenges can really stress the developers and administrators that work on large tables.

As an evangelist for Redgate Software, I talk with a lot of customers and potential customers about their database work. Almost all of the problems and difficulties they experience revolve around tables, especially as we seek to deploy changes with little to no downtime. In fact, one of the hardest problems Redgate has been trying to solve is how to make these changes easier for DBAs and developers. It’s hard, especially because the impact becomes greater as the table gets larger.

I ran across a post from Michael J Swart that looked at ways to alter a large table while keeping the data available for querying. It’s the start of a nice series that examines a technique, looking at the pros and cons. This seems complex, but there isn’t any magic to altering tables and ensuring the system continues to run. There really are a limited number of ways to perform alterations to tables.

These are hard changes, and need to take place across time, meaning you won’t get this all done in an hour, sitting at your desktop. Some of these changes might take hours, or even take place across days. Really complex changes that must synchronize with an application (or many), might actually sit in some limbo state where triggers keep data in sync for months.

There are different ways to make these changes, but how do you test which one works best? How do you ensure the correct steps run in the right order? You want an automated process that can be repeated. If I need to restore and retry my idea, I won’t want to depend on humans to execute the correct steps in the correct order. That’s a recipe for disaster. Especially when repeat the deployment to production.

As the changes you make to your systems become more complex, with multiple steps, and the repercussions for problems grow, I think it’s important that you have an automated process. A human might need to kick off parts, but really an entire batch of items should run automatically, in a reliable, repeatable fashion, without requiring a human to execute each step. This doesn’t mean some things are too difficult to completely automate, or that human’s aren’t involved (maybe for smoke test) but people should be involved as little as possible.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Mevio .

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged . Bookmark the permalink.