Best practice for SQL Server 2008 schema change

Question

I am looking for information concerning the following:

What are the best practices for updating the schema of my dev DB to my production DB, or even more succinctly making DB schema changes in general.

The production database is the back-end for two distinct ASP.NET websites.

Our schema change process is fairly robust with each "migration" actually being a .cs file containing the schema changes. We will then use ADO.NET to apply the schema changes against the db.

My question is more about the connectivity of the database.

Should I stop the two websites that are accessing the db. I assume I should. Should I put the DB into single user mode. It looks like I should but I am not entirely confident on that.

What could I be missing? What are things that have bitten you in the hand before concerning DB schema changes.

Answer 1

If the updates change things like column names, stored proc parameters, etc then always take the apps offline prior to doing a schema update.

If the updates are only for things that do not impact the normal processing of the data then you might be able to do so "hot". This category is when you are adding things like indexes, tables, etc.

If someone is using the app while a schema update is processing you might very well find yourself in a situation where data consistency is impaired.

If this update requires a corresponding update to your web application files then take the site(s) offline prior to performing the update. You don't know who might be viewing a page and about to click submit only to get an error...

Typically maintenance of this sort is done during your off peak hours. You will want to notify the users ahead of time as to when the site will be down and for how long.

Also, we use tools like Redgate's SQL Compare to script our db updates. This is practiced on a staging server that had been refreshed from production data prior to the actual push in order to ensure that there are no surprises and it can be done very quickly.

Regarding Single User Mode, you typically use this to limit access to a database to a single management studio instance. It's not something we normally do as part of our deployments.