SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to upgrade an Active & Active sql cluster?


How to upgrade an Active & Active sql cluster?

Author
Message
Oracle_91
Oracle_91
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1162 Visits: 1742
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.
runaldo
runaldo
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 566
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.



Oracle_91
Oracle_91
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1162 Visits: 1742
Hi runaldo,

Thank you very much for the help. Good points.
quackhandle1975
quackhandle1975
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3239 Visits: 1240
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

Who looks outside, dreams; who looks inside, awakes. – Carl Jung.
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10098 Visits: 6336
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
When a question, really isn't a question - Jeff Smith
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


runaldo
runaldo
SSC-Enthusiastic
SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)SSC-Enthusiastic (154 reputation)

Group: General Forum Members
Points: 154 Visits: 566
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search