About Me

My photo
I am an MCSE in Data Management and Analytics, specializing in MS SQL Server, and an MCP in Azure. With over 19+ years of experience in the IT industry, I bring expertise in data management, Azure Cloud, Data Center Migration, Infrastructure Architecture planning, as well as Virtualization and automation. I have a deep passion for driving innovation through infrastructure automation, particularly using Terraform for efficient provisioning. If you're looking for guidance on automating your infrastructure or have questions about Azure, SQL Server, or cloud migration, feel free to reach out. I often write to capture my own experiences and insights for future reference, but I hope that sharing these experiences through my blog will help others on their journey as well. Thank you for reading!

Minimize Downtime for Mirrored Databases When Upgrading Server Instances from SQL server 2008 to SQL server 2012/2014/2016.

Various ways to Migrate SQL Server from one version to another.

1.  Traditional ways
     1. Take backup from old server and restore into new server – Hardware cost involved.
     2. Detach database from old Server and attach into new server – Long downtime

   2. Rolling upgrade
                                      1.   In-place update of SQL server from SQL server 2008 or 2008 R2 to SQL Server 2012/ 2014 – Risk involved as after PONR, in place upgrade fails   we cannot revert back to previous
                                       2.  Minimize Downtime for Mirrored Databases When Upgrading Server Instances.

Here we will discuss how to Minimize Downtime for Mirrored Databases When Upgrading Server Instances.

                   When upgrading server instances to SQL Server 2014, you can reduce downtime for each mirrored database to only a single manual failover by performing a sequential upgrade, known as a rolling upgrade. A rolling upgrade is a multi-stage process that in its simplest form involves upgrading the server instance that is currently acting as the mirror server in a mirroring session, then manually failing over the mirrored database, upgrading the former principal server, and resuming mirroring. In practice, the exact process will depend on the operating mode and the number and layout of mirroring session running on the server instances that you are upgrading.     

Suppose we have 2 SQL Server Instance

    Principal Server in SQL Server 2008 / SQL server 2008 R2        -- Says Server-A.
    Mirror Server in SQL Server 2008 /SQL Server 2008 R2 -- Says Server-B.

    High Performance/High Safety Mirroring is configured between Server A and Server B.


 

        
          
The End Task is to upgrade Server-A and Server -B from SQL Server 2008 to SQL Server 2014, with Minimum downtime.
How we will achieve this.

As this is a multi-Stage process, hence it all depends on operating mode of mirroring...
  
   If operating mode is High Performance means, Asynchronous mirroring...
          
  1. Switch Mirroring Session to high safety without automatic failover and suspend Mirroring
  2. Now Perform rolling upgrade in each session by:-
-          Upgrade Mirror Server – Server-B. Hence now Mirror Server Server-B will be in SQL server 2014.
-          Now manually failover to upgraded Mirror Server. – Server –B.
-          Now Connect  Frontend Application with Server – B
-          You will observe Mirroring Working perfectly between Server – A (SQL server 2008) to Server –B(SQL Server 2014)
   

  1. Run DBCC CHECKDB on every principal database.                     
  2. Resume Mirroring
  3. Return session to high performance mode.
  4. Upgrade the new mirror Server instance.  (Server –A), you will observe Mirroring working perfectly.
  5. Now manually failover to upgraded Mirror Server- Server-A from Server -B
  6. Change Application connection back to Server-A.

 





The following illustration is a flowchart that shows the basic stages of a rolling upgrade for each operating mode. The corresponding procedures are described after the illustration.
 

Labels: