Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to upgrade an Active & Active sql cluster? Expand / Collapse
Author
Message
Posted Tuesday, July 22, 2014 10:58 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:13 PM
Points: 774, Visits: 1,734
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.

Post #1595188
Posted Wednesday, July 23, 2014 7:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:23 AM
Points: 83, Visits: 513
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.





Post #1595483
Posted Wednesday, July 23, 2014 1:16 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:13 PM
Points: 774, Visits: 1,734
Hi runaldo,

Thank you very much for the help. Good points.
Post #1595679
Posted Thursday, July 24, 2014 5:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 2,405, Visits: 996
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


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1595854
Posted Thursday, July 24, 2014 6:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1595868
Posted Thursday, July 24, 2014 6:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:23 AM
Points: 83, Visits: 513
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.



Post #1595873
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse