July 22, 2014 at 10:58 am
Hi Experts,
How to upgrade an Active & Active sql cluster?
Is there any difference in upgrade for sql 2005 Active-Active cluster and sql 2008 R2 Active - Active cluster?
Thanks in advance.
July 23, 2014 at 7:39 am
Do you want to upgrade the SQL 2005 clustered instance to SQL 2008 R2? I did this a year ago and then I followed this procedure:
1) Create new cluster group and install a new clustered instance with new name.
2) Restore all db's you want to migrate by restore with norecovery.
3) Run sp_help_revlogin and export the users and logins. Import these users and logins on the new servers.
4) Copy all the jobs you want on SQL 2005 on SQL 2008 and disable them.
5) When you are ready to shift from old to new instance - set user databases in read_only - run a diff backup on SQL 2005 and restore them on new SQL 2008 instance.
6) Run SQL 2008 installer and change the Clustered Server name to the same as SQL 2005. You might need to manually delete some DNS entries or uninstall the SQL 2005 clustered server first.
7) Change the port which SQL 2005 run on and use this port on SQL 2008.
I have never tried to run inplace upgrade and I don't know if you can do it.
July 23, 2014 at 1:16 pm
Hi runaldo,
Thank you very much for the help. Good points.
July 24, 2014 at 5:15 am
runaldo (7/23/2014)
Do you want to upgrade the SQL 2005 clustered instance to SQL 2008 R2? I did this a year ago and then I followed this procedure:1) Create new cluster group and install a new clustered instance with new name.
2) Restore all db's you want to migrate by restore with norecovery.
3) Run sp_help_revlogin and export the users and logins. Import these users and logins on the new servers.
4) Copy all the jobs you want on SQL 2005 on SQL 2008 and disable them.
5) When you are ready to shift from old to new instance - set user databases in read_only - run a diff backup on SQL 2005 and restore them on new SQL 2008 instance.
6) Run SQL 2008 installer and change the Clustered Server name to the same as SQL 2005. You might need to manually delete some DNS entries or uninstall the SQL 2005 clustered server first.
7) Change the port which SQL 2005 run on and use this port on SQL 2008.
I have never tried to run inplace upgrade and I don't know if you can do it.
Step 8 you may want to run DBCC CHECKDB on all upgraded databases. And update the stats.
qh
July 24, 2014 at 6:21 am
I'd put a caveat on step 5 as it depends on your backup routines. If your doing daily full's then doing a diff wont help if you have waited more than a day to upgrade as the diff is only what's changed since the last full so that method will not work. But should you do the upgrade in the period before the next full backup then yes this step will still be valid.
How ever if your doing regular transaction log backups, these are not dependent on the full or diff so these can be replayed providing you have a full log chain from the full backup you restored initially back into the new clusters databases. Again set to read only and perform a final transaction log backup to ensure you have captured all of the transactions up to the point you take the database to read only mode.
Alternatively you could setup log shipping to the new cluster then you only have the last few logs to restore.
Also a point to not is that its a massive piece of work to downgrade back to 2005, so as always backup, backup, backup your 2005 last version and store them somewhere just on the off chance you need to downgrade, then you can restore your 2005 version and do a data differential using a Data Compare tool like Red-Gates, or by creating linked servers and manually querying the two sides to see whats needed to reimport back into the 2005 version. Or you go down the route of you re-create the schema and reimport the data straight from 2008.
July 24, 2014 at 6:37 am
There's a lot of if's here so you can dig as you like. I moved 3tb in 200 databases in this case (Sharepoint databases). We used Hallengren's solution so I assume that if you don't have the large amounts that I have then you can always uninstall SQL 2005 cluster and install SQL 2008 and attach all the databases and import the users. But in this case it took it's time.
I did the checkdb and updatestats and prioritised checkdb first. The updatestats and reindex (included both in the Hallengren version we had deployed) the following night.
We tested different things as well. Remove the old clustername and just add a new computername to the clustergroup. Add a new CName record (or Alias record its called now) in DNS. For those of you who like clusters (I do) it is fun to test out these things.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply