Methods for Zero downtime when doing schema changes to MS SQL 2008 DB
Posted on 2012-08-20
What are the ways I can make changes to DB schema
without incurring downtime (or at least minimal downtime)?
My current Prod MS SQL 2008 replicate to a remote DR
MS SQL 2008 DB & I have a UAT MS SQL 2008 DB too.
Anyway to leverage on these 2 environments to achieve
0 downtime? Management doesn't like to give scheduled
We're running MS SQL 2008 Enterprise Server R2 (x64)
on Win 2008 Enterprise server R2 (x64). I read that
in Win 2008 there's a VSS feature that allows us to
backup an MS SQL DB without shutting down the DB.
Can we leverage on this feature to get minimal or
0 downtime? Heard Win2008 can even take a bare
metal recovery online.
Some of the schema changes are:
a) adding an extra column field to the DB (eg: previously
we don't have "Home Tel# of employee" & now we
want to add this info.
b) extending the length of a field/column, eg: if the
office tel# used to be 7-digit, we now needs to
implement 8-digit telephone number by prefixing
with a digit "6"
c) Deleting an old unused field/column
d) Creating a new combination key by linking keys
from 2 different SQL tables
e) Applying an MS SQL patch
f) Making a column as secondary key
h) Importing new data (from our company's newly
opened branch) into the DB
Pls list any other common DB schema changes that
can be done online or on-the-fly and those that will
require (planned) downtime
Perhaps make an online copy of DB that's constantly
sync'ing data to the backup copy, make DB changes
to the backup copy of DB & after the changes are
done, run a script to
1. shut down current running DB
2. rename the current DB to "old DB"
3. rename the backup DB to current DB
4. start up MS SQL?
which hopefully takes less than 1 minute?