Planning and Implementation of Maintenance of Servers with Least Amount of Downtime with SQL Server 2012 AlwaysON Availability Groups

There was a request where we had to perform maintenance on servers which were hosting Primary and Secondary AlwaysON Availability Group Databases. Initially, when the Servers were setup, the hardware was not planned for best performance, so it was decided that we will replace the servers with complete new servers with new and better Hardware. Below is the environment setup.

Servers Server01 and Server02 are running Windows Server 2012 Operating System with One Default Standalone Instance on each server installed with SQL Server 2012 Enterprise Edition. AlwaysON Availability Group with Listener name as “PROD_LSTNR” has been setup between Server01 as Primary AlwaysON replica and Server02 as Secondary replica and this AG has couple of user databases. There is another AlwaysON Availability Group with Listener name as “PROD_LSTNR_RPT” has been setup between Server02 as Primary AlwaysON replica and Server01 as Secondary replica and this AG has ReportServer and ReportServerTempDB databases.

Now, we had to replace both the servers with brand new servers with new and better hardware. This needs to be accomplished with minimum downtime for end users and least amount of manual work to be performed by DBA or SysAdmins.

Below are the High Level steps which we identified and later successfully implemented with minimum downtime and least amount of manual work.

Prepare for Maintenance of AlwaysON Availability Group Secondary Replica
– Let the AlwaysON Availability Group “PROD_LSTNR” run on the Primary Replica Server01.
– Make a list of Report Server Subscription jobs which are currently enabled on Server02. Save it for future reference. If there are any other user created jobs related to Report Server, script and create them on Server01 and keep them disabled for now.
– Backup ReportServer and ReportServerTempDB databases and system databases on secondary replica Server02
– Backup ReportServer Encryption Keys on Server02.
– Backup ReportServer Encryption Keys on Server01.
– Synchronize all logins and resolve any orphan users on Primary Server Server01
– Failover AlwaysON Availability Group “PROD_LSTNR_RPT” to Replica Server01. This causes very short downtime for reporting services.
– Restore the Encryption Keys on Server01 with the backup taken from Server02.
– Remove Server02 from Scale-Out deployment tab using Report Service Configuration Manager on Server01.
– Stop Reporting Services on new secondary server Server02.
– Restart SQL Server Agent on Server01. This will create or enable Report Server Subscription jobs on Server01 automatically. Enable any user created report server related jobs on Server01.
– Test the reports to make sure they are working. If you have configured reports data sources to use AlwaysON Secondary databases read_Only databases, then make sure that AlwaysON Availability Group settings allow Primary replica to allow both Read/Write connections.
– Turn off SQL Services on Secondary replica Server02.

Steps for System Administrators:
– This is critical step and to be done by System Administrators. Take down Secondary replica Server02 and copy the consistent server snapshot of Secondary server Server02 in to new server with better hardware.
– Rename Server02 to Server02_Old and rename new server to Server02 and change the IP addresses same way.
– Add new secondary server Server02 with powerful hardware back to the network and domain.
– Now, you should be able to see that AlwaysON Availability Groups start synchronizing and after some time will get fully synchronized.
– Now that the secondary replica maintenance is complete, next we need to prepare the primary server for the maintenance to replace the server with new server with powerful hardware.

Prepare for Maintenance of AlwaysON Availability Group Primary Replica
– Backup all user and system databases on Primary replica Server01.
– Backup Encryption Keys on primary and secondary server Server01 and Server02
– Make a list of all application or user created jobs which are enabled on primary replica Server01 and disable the jobs on primary server Server01.
– Failover AlwaysON Availability Group “PROD_LSTNR” to Secondary Server Server02 and now this will become new primary server.
– Enable all application or user created jobs as we noted earlier.
– Test the Application.
– Failover AlwaysON Availability Group “PROD_LSTNR_RPT” to Secondary Server Server02 and now this will become new primary server.
– Stop the reporting Services on new secondary server Server01.
– Restart SQL Server Agent Service on new primary server server02. This will create or enable report server subscription jobs on new primary replica Server02.
– Restore the Encryption keys on Server02 using the backup taken from Server01.
– From Report Server Configuration Manager, remove Server01 from Scale-Out Deployment.
– Test the reports to make sure they are working. If you have configured reports data sources to use AlwaysON Secondary databases read_Only databases, then make sure that AlwaysON Availability Group settings allow Primary replica to allow both Read/Write connections.

Steps for System Administrators:
– This is critical step and to be done by System Administrators. Take down new Secondary replica Server01 and copy the consistent server snapshot of Secondary server Server01 in to new server with better hardware.
– Rename Server01 to Server01_Old and rename new server to Server01 and change the IP addresses same way.
– Add new secondary server Server01 with powerful hardware back to the network and domain.
– Now, you should be able to see that AlwaysON Availability Groups start synchronizing and after some time will get fully synchronized.

– Now that the new secondary replica Server01 maintenance is complete, next we can failover the AlwaysON Availability Group “PROD_LSTNR” to Server01 where it was running as primary before, enable all the user created jobs on Server01 and disable them on Server02. Let the AlwaysON Availability Group “PROD_LSTNR_RPT” run on Server02 and use the read_only copy and offload the reporting from primary server.
– Test the application.

We have successfully followed these steps in our environment, but you should test this solution thoroughly on your lab or test environment before proceeding with the production changes. This post is provided “AS IS” with no warranties or guarantees.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings, SQL Server Logshipping on Databases which are part of AlwaysON Availability Groups (AG)

 

Leave a Reply

Your email address will not be published. Required fields are marked *