Upgrade Clustered SQL Server 2005 having log shipping configured

  • Mani-584606

    SSChampion

    Points: 11788

    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!

  • Mani-584606

    SSChampion

    Points: 11788

    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

  • WayneS

    SSC Guru

    Points: 95341

    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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Mani-584606

    SSChampion

    Points: 11788

    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

  • Mani-584606

    SSChampion

    Points: 11788

    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?

  • peterhe

    SSChampion

    Points: 11362

    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.

  • Mani-584606

    SSChampion

    Points: 11788

    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!

  • Mani-584606

    SSChampion

    Points: 11788

    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 8 (of 8 total)

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