Link to home
Start Free TrialLog in
Avatar of sunhux
sunhux

asked on

Methods for Zero downtime when doing schema changes to MS SQL 2008 DB

What are the ways I can make changes to DB schema
without incurring downtime (or at least minimal downtime)?

Q1:
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
downtime.

Q2:
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.

Q3:
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
g) Reindexing
h) Importing new data (from our company's newly
    opened branch) into the DB

Q4:
Pls list any other common DB schema changes that
can be done online or on-the-fly and those that will
require (planned) downtime

Q5:
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?
SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Good points, I misread.