Zero downtime database upgrade - SQL Bits III

Published Thursday, September 18, 2008 9:09 PM

At SQL Bits III - I presented on my experience with Zero Downtime Database Upgrades.  If you weren't at SQLBits or didn't manage to make it along to the session, I've included a prĂ©cis of the options below and the slides are available for download.  The session focussed on the methods and processes to support an upgrade project while minimising downtime.

Upgrading SQL Server 2000 (downtime <2 minutes)

It's possible to upgrade SQL Server 2000 to either SQL Server 2005 or 2008 while minimising downtime using log shipping.  This method involves manual log shipping to synchronise the new (target) server as close as possible with current production.  At time of cutover, the old database is set to READ ONLY, take a final log backup and recover the database on the target server with this log.  Downtime in this scenario is the time between setting the database to read only and enabling the database for read/ write again on the new server - typically a couple of minutes.

Upgrading SQL Server 2005 (momentary downtime)

Upgrading from SQL Server 2005 to SQL Server 2008 utilises database mirroring in synchronous mode to keep source and target servers synchronised.  It is possible to mirror from SQL Server 2005 to a SQL Server 2008 destination.  It is also supported to upgrade the mirror server from SQL Server 2005 to 2008.  In either scenario, the database can failed-over with an ALTER DATABASE statement and will be brought online at the mirror in an upgraded state.  Applications using SQL Native Access Client will automatically reconnect to the new principal server.  This upgrade method isn't possible (or practical) for every scenario - but can deliver downtime of just a few moments - completely transparent to users!

Upgrade considerations

There are a number of aspects to consider - each method has pros, cons and gotchas.  The slide deck covers some of these considerations and some further points around upgrade techniques to avoid and lessons learned from previous upgrades.

The slides I presented are available:  and here you'll also find details for Christian's session - A walk down memory lane.

Finally, big thanks to the SQL Bits organising committee who again surpassed themselves with a great venue and event that ran like clockwork, all for free - THANK YOU! 



Justin Langford.

Coeo - SQL Server Consultants | Remote DBA | Dedicated Database Engineering


No Comments