• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1070
  • Last Modified:

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)?

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
g) Reindexing
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?
  • 2
3 Solutions
Aneesh RetnakaranDatabase AdministratorCommented:
ALTER TABLE TableName Add ColumnName INT NULL
You need to make sure that the column has either a default value or it's been set to NULL.  
Another thing to note is that the publication property 'Subscription Options' is enabled

You dont really need to shutdown the db to take a backup, (if you are processing transactions during the backup process, there is chance that the full backup may not contain those data, that's why we take the differential, transaction log backup.
Anthony PerkinsCommented:
a) No downtime (provided you don't use the SSMS Designer and instead use ALTER TABLE tablename ADD...)
b) No downtime (provided you don't use the SSMS Designer and instead use ALTER TABLE tablename ALTER ...)
c) No downtime.
d) Not following you.
e) Required downtime
f) Not following you.
g) It depends on your Edition and other circumstances.
h) No downtime.
Scott PletcherSenior DBACommented:
I have a few quibbles with some of those:

Q3: [nd=no downtime]
a) nd UNLESS you need to provide a specific default value
b) nd UNLESS/UNTIL you add the leading '6' to the column -- that UPDATE will cause some locking
f) nd for creating a nonclustered index(es)
h) depends on the volume of data and the table definition
Anthony PerkinsCommented:
Good points, I misread.

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now