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

Posted on 2012-08-20
Last Modified: 2012-09-08
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?
Question by:sunhux
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    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.
    LVL 75

    Accepted Solution

    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.
    LVL 68

    Assisted Solution

    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
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Good points, I misread.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    SQL Server Fine Tuning 7 38
    Format to Percentage in SQL Server Query 8 20
    Which one to download? 8 28
    query question 4 19
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now