June 22, 2010 at 10:57 pm
Hi,
We have a 3 node a/a/p cluster setup for SQL Server 2005 with SP3.
Node1 have 1 instance and node has 4 instances and node3 is passive.
Databases on Instance1 on node1 have log ship configured and the databases are in restore mode on a secondary server ServerA.
Databases on Instance2,3,4,5 on node2 have log ship configured and the databases are in restore mode on a secondary server ServerB.
Now we need to peform in-place upgrade to SQL Server 2008:
1. Do we need to first disable Logshipping on all nodes and bring the secondary databases online on ServerA & ServerB and upgrade the instances on ServerA & ServerB to SQL Server 2008?
2.Then Perform the in-place upgrade on Clustered nodes to SQL Server 2008.
3. Take the full backup on Primary instances and copy to secondary, delete the existing databases on ServerA & B.
4. Restore the databases with NORECOVERY and configure log shipping again?
I already went through the MS upgrade guide SQL2008UpgradeTechnicalReferenceGuide.docx but I'm http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gifNOT clear the steps discussed.
So please the DBA gurus who did the in-place upgrade under similar scenario, share your experience
Thanks in Advance!
June 23, 2010 at 10:01 am
I appreciate your inputs..In the following steps, Is anywhere we need to bring the secondary online?
If we did NOT bring secondary database online, then will it considered to be upgraded to SQL 2008 (I mean we will upgrade the Secondary instance and apply the t-logs generated while upgrading the Primary to secondary database but NOT bringing the secondary database online.)
From Upgrade document:
Upgrade log shipping without a role change:
If you want to retain the original log shipping configuration, the following steps are applicable. Be warned that this upgrade path will cause an outage on the primary that you need to account for. If you choose this option, consider taking a longer single outage and performing the upgrade without failover.
1.Disable the copy and restore jobs on the secondary.
2.Disable the alert job on the monitor (if it is used).
3.Upgrade the SQL Server 2005 instance containing the secondary database. As noted earlier, because the database is in a state where it is restoring transaction logs, it will not be upgraded to SQL Server 2008 yet.
4.Upgrade the monitor instance to SQL Server 2008.
5.Manually copy over and restore (WITH NORECOVERY) all transaction log backups generated during the SQL Server 2008 upgrade from the primary to the secondary.
6.On the primary, stop all incoming traffic and verify that there are no connections to ensure that, at this point, the data cannot be changed. To do this, consider using single-user mode.
7.Disable the transaction log backup job on the primary.
8.Upgrade the instance containing the primary database.
9.It is recommended that you run all the necessary health checks, including DBCC CHECKDB, at this point to ensure the well-being of the newly upgraded databases.
10.Enable the existing Log Shipping backup, copy, and restore jobs that were previously disabled during the upgrade window. Verify each job is working properly. Note that when the first transaction log from the upgraded primary database is applied to the secondary database server, it will upgrade the log shipped secondary database to SQL Server 2008.
11.Verify the database compatibility level of upgraded databases is 100.
12.Direct all users and applications to the original primary database
In above steps: Read the Step 5 & 8
5.Manually copy over and restore (WITH NORECOVERY) all transaction log backups generated during the SQL Server 2008 upgrade from the primary to the secondary
8.Upgrade the instance containing the primary database
At step 5, we already upgraded the Primary. And why step 8 is needed to upgrade the Primary instance again?
I'm very much confused with the above two steps. Please clarify me..thanks
June 23, 2010 at 11:14 am
Step 3 upgrades the secondary.
Step 5 copies the tlog backups from primary to secondary, and applies them to the secondary.
Step 8 upgrades the primary.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 23, 2010 at 11:44 am
Step 5 copies the tlog backups from primary to secondary, and applies them to the secondary.
But from step5,
5.Manually copy over and restore (WITH NORECOVERY) all transaction log backups generated during the SQL Server 2008 upgrade from the primary to the secondary
Step 5, states that manually copy the t-logs generated while upgrading to SQL Server 2008 NOT just copying the t-logs of
SQL Server 2005. Which means we are upgrading the primary at Step5 right? Then why we need to upgrade gain at step8??
Thanks
June 28, 2010 at 10:05 am
5.Manually copy over and restore (WITH NORECOVERY) all transaction log backups generated during the SQL Server 2008 upgrade from the primary to the secondary
Could you clarify me on this step?
Here are we upgrading the primary to SQL 2008? or
just applying the t-logs of SQL 2005 of primary to secondary?
June 29, 2010 at 7:22 am
Step 5 means copy the t-log generated on primary to secondary while you were upgrading the secondary to 2008, and restore it on the secodnary with no-recovery.
June 29, 2010 at 5:48 pm
Step 5 means copy the t-log generated on primary to secondary while you were upgrading the secondary to 2008, and restore it on the secondary with no-recovery.
Thanks for the clarification. From the below link
http://msdn.microsoft.com/en-us/library/cc645954.aspx
During the server upgrade, the secondary database is not upgraded to a SQL Server 2008 R2 database. It will get upgraded only if it is brought online
I have upgraded the secondary instance to SQL Server 2008 and then applied all the log backups from Primary to secondary and then brought the secondary databases online by applying the tail backup of Primary.
Now how can we make sure that the secondary databases are upgraded to SQL Server 2008? I need to prove & document to my boss by showing some out put that the secondary databases are upgraded to SQL Server 2008 from SQL Server 2005?
Is there any query to check whether the secondary databases are upgraded to SQL Server 2008 after bringing then online?
Thanks so much!
June 30, 2010 at 10:47 am
I got the answer:
select name,version from sys.sysdatabases
Version for SQL Server 2008 is 655
Version for SQL Server 2008 R2 is 661
thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply