Rolling upgrade to SQL Server 2008
So if you have been following me on Twitter or ran across my thread on MSDN, you know I have been trying to get rolling upgrades to SQL Server 2008 working. I got it to work this morning. A SQL Server 2008 upgrade with a couple of seconds of downtime. Booyah.
The idea is to setup database mirroring between two SQL Server 2005 instances. Run the SQL Server 2008 upgrade on the mirror instance and then failover the principal.
After failover you get these messages:
2008-04-07 11:44:11.83 spid25s Converting database 'test01' from version 611 to the current version 643.
2008-04-07 11:44:11.83 spid25s Database 'test01' running the upgrade step from version 611 to version 621.
<snip>
2008-04-07 11:44:12.42 spid25s Database 'test01' running the upgrade step from version 641 to version 642.
and, of course, mirroring cannot resync with SQL Server 2005.
"'TCP://JMASSI-00033004.drtn.corp:5022', the remote mirroring partner for database 'test01', encountered error 948, status 2, severity 20. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance."
There are other considerations like updating stats, functionality testing, external objects like jobs that you might want to take the down time for but sometimes up time is more important.
