Upgrade a SQL Servr 2005 Cluster under Windows 2003 to SQL Server 2008 R2 Cluster under Windows 2008

Question

Upgrade a SQL Servr 2005 Cluster under Windows 2003 to SQL Server 2008 R2 Cluster under Windows 2008

Schadenfreude-Mei

SSChampion

Points: 14083
More actions
July 26, 2011 at 7:53 am

#240576

Hey all,
Firstly apologize if this has already been posed before, i did a search and didn't find a preexisting thread with this exact requirement.
We have two sperate a/p SQL 2005 clusters. Both are Enterprise x64 editions running SP3 flat. Each has a sperate DR node (none clustered) where databases are mirrored to (no witness server).
We had a requirement to upgrade to SQL 2008 R2 Enterprise x64 and had a plan to proceed (we were initially told NOT to consider Windows 2008 (currently on Windows 2003 Enterprise x64 SP2)); however coming out of a meeting today it seems that we now have to blow away the existing clusters, rebuild as Windows 2008 clusters and reinstall SQL (the end result being it ends up as a 2008 R2 a/p cluster) and DR needs doing and the mirrors need to be reinstated.
Now before anyone says 'thats a lot for 1 day', let me say that i know! Its not my idea. Luckily though we have 6 virtual dev environments where we can perfect this.
My question is this: Taking that we have to blow away the existing cluster (Windows and SQL), which of the following is our best bet? Or is there a better solution?
1. Upgrade existing SQL 2005 cluster to 2008. Backup databases, SP's, Jobs etc. Blow away, install Windows 2008, SQL 2008 cluster and restore.
2. Backup databases, SP's, Jobs etc in 2005. Blow away server, install Windows 2008, SQL 2008 cluster and restore.
3. Backup databases, SP's, Jobs etc in 2005. Blow away server, install Windows 2008, SQL 2005 cluster, restore and then upgrade to 2008 cluster.
And then as an added treat, should the DR servers (which aren't clustered) be upgrade before or after (obviously with mirrors broken). I know you can mirror from 2005 to 2008 (as long as you don't failover) but you cant mirror 2008 to 2005 can you? So either we can have a one-way DR or no during!
Please let me know your thoughts. We haven't started anything yet, so just fact finding at the moment.
Thanks in advance.
Adam Zacks-------------------------------------------Be Nice, Or Leave

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Colleen M. Morrow SSCrazy Points: 2457 More actions · Answer 1

You're correct in that you can't mirror a 2008 database to a 2005 instance.

My $.02, I'd go with option 2 (just seems cleaner to me), using your mirroring to minimize application downtime. I'm not sure what you'd gain by options 1 or 3.

http://msdn.microsoft.com/en-us/library/bb677181.aspx

Colleen M. Morrow
Cleveland DBA

JeremyE SSCoach Points: 16198 More actions · Answer 2

I would vote for option 2 as well.

You could upgrade your mirror server to Windows/SQL 2008R2.

Rebuild mirroring from the 2005 cluster to the 2008R2 machine.

Failover and run on the standalone machine which will upgrade the database to 2008R2.

Rebuild the cluster to Windows/SQL 2008R2.

Rebuild mirroring from the standalone to the cluster and fail back to the cluster.

This would minimize the downtime needed.

ricer Right there with Babe Points: 722 More actions · Answer 3

Number two should be the simplest way to a clean install.

I don't see any issue with restoring 2005 sql backup on sql 2008 r2.

We are expecting to do the same upgrade but we will have all new hardware so we will be doing a side by side install in the near future.

-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 4

Please let us know how it goes. Good luck with the migration.

My blog

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 5

Thanks all for the suggestions.

Seems like option 2 is a clear favorite and pretty much what i was thinking.

If/when 2k5 database backups are restored to 2k8 does that change the compatibility level or do anything other then upgrade the database to 2k8 (have very little 2008 experience and want to expect gotchas rather then be surprised).

As far as the DR is concerned, if we ran on DR while cluster update was going, we'd have to switch the apps to DR mode which has its own complications. I think on balance DR should be done the day before and the business will have to except the risk (after all we will still have the cluster).

That said i am open to opinions and suggestions! Keep'em coming 🙂

Adam Zacks-------------------------------------------Be Nice, Or Leave

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 6

Schadenfreude-Mei (7/28/2011)
If/when 2k5 database backups are restored to 2k8 does that change the compatibility level or do anything other then upgrade the database to 2k8 (have very little 2008 experience and want to expect gotchas rather then be surprised).

When you restore the SQL 2005 backups on the SQL 2008 Server, the databases will be on compability level 90.

My blog

adb2303 SSCertifiable Points: 7588 More actions · Answer 7

Hi,

I'm working with Adam on this upgrade.

When it comes to Windows Server Roles (we'll have Win2K8 R2 EE), what role should be picked for SQL Server? No Analysis Services or Reporting services required.

Thanks,

Andrew