My usual day to day work has been hectic since my first blog but one of the tasks I have implemented over this period was a rolling update of SQL Servers within an AlwaysOn Availability Group. The 2014 SP2 cumulative update 5  was required urgently due to a new API release that was going to take advantage of the read-only secondary replica instance.

The specific hotfix being: VSTS bug number 9278809; KB article no. 4013236

Description: The fix Bad query plan created on secondary replicas after statistics updated via FULLSCAN option on primary replica.

Due to the urgency of this requirement there wasn’t much time to prepare and luckily I still had a run book from a previous AlwaysOn SQL update at the end of last year (albeit when there was only two instances in the AlwaysOn automatic failover synchronous commit setup).

When I’ve researched into best practices/gotchas/step by step guides in the past you can easily end up with an overload of information that could make the task even more daunting when you know the environment you are supporting is critical to the overall running of the business.

Therefore, one of the points of my post is that database changes, however large are small, should always be tested where possible and a clear, easy to follow run-book needs to be created. Taking the military adage of the 7-Ps…

Whenever there are database system changes everyone is aware and all eyes are on the DBA team. Thus, you are questioned first (rightly so) when any small deviations from the normal day to day running expectations of your systems are encountered by the end-users.  Having a run-book gives me, the DBA, room to respond to questions that may come your way mid release. Even better if you have another DBA as your wing man as he can fend off the questions, see where you are in the process and investigate accordingly when required.

With regard to information overload you can find. My run-book is written based around Transact-SQL commands only where possible.  I have found that there are many articles that are very well written with many different variations of how to do the task – i.e. SQL Server Management Studio wizards; PowerShell commands; Transact-SQL statements. For my benefit, I like to KISS!

Disclaimer – Some of the steps I have taken deviate from what you may expect to be part of the best practices. This is due to good working knowledge of the environment and expectations on RTO/RPO and a risk/reward call on performance during and after the implementation.

AlwaysOn Availability Group Configuration

RUN BOOK

I also like to highlight each step within the document when I have completed it

• ON BOTH PRIMARY/SYNCRONOUS SECONDARY REPLICA INSTANCES SR1 & SR2

exec msdb.dbo.sp_update_job @job_name = 'DBA: DatabaseMaintenance - UPDATE_STATISTICS', @enabled = 0;

exec msdb.dbo.sp_update_job @job_name = 'DBA: DatabaseBackup - USER_DATABASES_FULL', @enabled = 0;

GO

• PRIMARY REPLICA SR1

BACKUP system databases

DIFFERENTIAL BACKUPS of user databases

Run for all Databases

EXECUTE dbo.DatabaseBackup @Databases = ‘AlwaysOnDatabaseNames’,

            @URL = 'https://BLOBNAME/diffbackups',

            @Credential = 'BackupCredential',

            @BackupType = 'DIFF',

            @Compress = 'Y',

            @Encrypt = 'Y',

            @EncryptionAlgorithm = 'AES_256',

            @ServerCertificate = 'BackupCertificate',

            @LogToTable = 'Y';

GO

EXECUTE dbo.DatabaseBackup @Databases = 'SYSTEM_DATABASES',

            @URL = 'https://BLOBNAME/diffbackups',

            @Credential = 'BackupCredential',

            @BackupType = 'FULL',

            @Compress = 'Y',

            @Encrypt = 'Y',

            @EncryptionAlgorithm = 'AES_256',

            @ServerCertificate = 'BackupCertificate',

            @LogToTable = 'Y'

• Check availability group is healthy – See query at end of doc

• 1) PRIMARY REPLICA SR1

Remove automatic failover on synchronous-commit replicas after double checking is failover ready on the primary

ALTER AVAILABILITY GROUP AlwaysOnAG MODIFY REPLICA ON SR1

   WITH (FAILOVER_MODE = MANUAL);

GO

ALTER AVAILABILITY GROUP AlwaysOnAG MODIFY REPLICA ON SR2

   WITH (FAILOVER_MODE = MANUAL);

GO

• 2) ASYNCHRONOUS SECONDARY REPLICA ASR3

RDP on to ASR3 Host:

RESTART ASR3

Run CU5 update from \Temp\SP2_CU5

RESTART ASR3

Check ErrorLog and Availability Group is still healthy (query at end of doc)

• 3) SYNCHRONOUS SECONDARY REPLICA SR2

RDP on to SR2 Host:

Manually failover to Secondary Replica

ALTER AVAILABILITY GROUP AlwaysOnAG FAILOVER;

Check Errorlog and Availability Group is still healthy (query at end of doc)

Run update statistics with full scan on SR2 for database/transaction table

UPDATE STATISTICS Database.dbo.Table WITH FULLSCAN

• 4) ORIGINAL SYNCHRONOUS PRIMARY REPLICA SR1

RDP on to SR1 Host

RESTART SR1 Host

Run CU5 update from \Temp\SP2_CU5

RESTART SR1

        RDP again on to SR1

CHECK AG health (query and SSMS GUI)

• 5) NEW SYNCHRONOUS PRIMARY REPLICA SR2

Configure automatic failover partners again on new Primary

USE master

GO

ALTER AVAILABILITY GROUP AlwaysOnAG MODIFY REPLICA ON SR2

   WITH (FAILOVER_MODE = AUTOMATIC);

GO

ALTER AVAILABILITY GROUP AlwaysOnAG MODIFY REPLICA ON SR1

   WITH (FAILOVER_MODE = AUTOMATIC);

GO

• ON BOTH PRIMARY/SYNCRONOUS SECONDARY REPLICA INSTANCES SR1 & SR2

exec msdb.dbo.sp_update_job @job_name = 'DBA: DatabaseMaintenance - UPDATE_STATISTICS', @enabled = 1;

exec msdb.dbo.sp_update_job @job_name = 'DBA: DatabaseBackup - USER_DATABASES_FULL', @enabled = 1;

GO

Run above jobs if schedule was due while upgrading instances

New AlwaysOn Availability Group Configuration

Check health via tsql

select

dg.primary_replica

,dg.primary_recovery_health

,dg.primary_recovery_health_desc

, dg.synchronization_health

,dg.synchronization_health_desc

,dg.secondary_recovery_health

,dg.secondary_recovery_health_desc

, da.replica_server_name

, da.join_state

, da.join_state_desc

, dr.database_name

, dr.is_failover_ready

, dr.is_pending_secondary_suspend

, dr.is_database_joined

, dr.recovery_lsn

, dr.truncation_lsn

from sys.dm_hadr_database_replica_cluster_states dr

inner join sys.dm_hadr_availability_replica_cluster_states da

on dr.replica_id = da.replica_id

inner join sys.dm_hadr_availability_group_states dg on da.group_id = dg.group_id