Cluster-Aware Updating Windows 2012R2 with SQL Server 2014 AlwaysOn availability groups #Cau #winserv #SQL

Cluster Aware Updating or CAU is a great tool for patching your cluster but there are some situations that you need to be carefully when using CAU.

Patching your SQL Clusters is no problem unless you have a Windows Server 2012 cluster with SQL Server AlwaysOn availability groups.

then you can’t use CAU the #NNFW (next next finish way) For Cluster.

Windows Server 2012 cluster with SQL Server AlwaysOn availability groups

I’ll not showing the Setup for this you can jump to my blog https://robertsmit.wordpress.com/?s=cau

but what I do show you is the basic steps for updating the Windows Server 2012 cluster with SQL Server AlwaysOn availability groups

image

Here is my SQL Cluster and it is a two node cluster ready for patching but most of the time the advanced options are skipped.

who cares about a pre and after setup I just want to install the updates.   let me show you why this is important !

image 

imageimage

My cluster is hitting the download and after this one node is set on Pause.

image

Eh pause but what about my SQL Server AlwaysOn availability group yes this is no longer working. eh the DBA guy is calling you right now Winking smile

How CAU Affects SQL Server AlwaysOn Availability Groups

Disclaimer: Microsoft does not support the use of CAU to update Windows Server 2012 clusters with SQL Server AlwaysOn availability groups. CAU is currently not aware of AlwaysOn availability groups. When CAU brings a node into maintenance mode to update the node, AlwaysOn availability groups are affected by the following known issues. Potential mitigations for these issues are provided to be transparent about our findings, and are meant for your non-production test environment only. These mitigations are not guaranteed to solve all issues. 

http://msdn.microsoft.com/en-us/library/jj907291.aspx

review the document, please download the Patching SQL Server Failover Cluster Instances with Cluster-Aware Updating (CAU)

Main reason why you must be careful :

Do not use the Failover Cluster Manager to manipulate availability groups:

 

  • Do not change any availability group properties, such as the possible owners.

  • Do not use the Failover Cluster Manager to fail over availability groups. You must use Transact-SQL or SQL Server Management Studio.

 

But what Can you do ?

first connecting to all the SQL boxes and clicking on the SQL Server AlwaysOn availability group that is a lot of work.

Connect to the server instance that hosts the replica whose database you want to suspend.

  1. Suspend the database by using the following ALTER DATABASE statement:

    ALTER DATABASE database_name SET HADR SUSPEND

Well a TSQL Script still connecting to my SQL box manually

Using PowerShell

To suspend a database

  1. Change directory to the server instance that hosts the replica whose database you want to suspend.

  2. Use the Suspend-SqlAvailabilityDatabase cmdlet to suspend the availability group.

  3. For example, the following command suspends data synchronization for the availability database MVPDB in the availability group MVPSQLAG01 

    Suspend-SqlAvailabilityDatabase -Path SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MVPSQLAG01\Databases\MVPDB

To resume a secondary database

  1. Change directory to the server instance that hosts the replica whose database you want to resume. 

  2. Use the Resume-SqlAvailabilityDatabase cmdlet to resume the availability group.

  3. For example, the following command resumes data synchronization for the availability database MVPDB in the availability group MVPSQLAG01

    Resume-SqlAvailabilityDatabase -Path SQLSERVER:\Sql\Computer\Instance\AvailabilityGroups\MVPSQLAG01\Databases\MVPDB

    Note

So I placed the scripts on the CSV from my Cluster 

image

 

image

 

 

Disclaimer: Microsoft does not support the use of CAU to update Windows Server 2012 clusters with SQL Server AlwaysOn availability groups. CAU is currently not aware of AlwaysOn availability groups. When CAU brings a node into maintenance mode to update the node, AlwaysOn availability groups are affected by the following known issues. Potential mitigations for these issues are provided to be transparent about our findings, and are meant for your non-production test environment only. These mitigations are not guaranteed to solve all issues. 

Greetings,

Robert Smit

https://robertsmit.wordpress.com/?s=cau

Author: Robert Smit [MVP]

Robert Smit is Senior Technical Evangelist and is a current Microsoft MVP in Clustering as of 2009. Robert has over 20 years experience in IT with experience in the educational, health-care and finance industries. Robert’s past IT experience in the trenches of IT gives him the knowledge and insight that allows him to communicate effectively with IT professionals who are trying to address real concerns around business continuity, disaster recovery and regulatory compliance issues. Robert holds the following certifications: MCT - Microsoft Certified Trainer, MCTS - Windows Server Virtualization, MCSE, MCSA and MCPS. He is an active participant in the Microsoft newsgroup community and is currently focused on Hyper-V, Failover Clustering, SQL Server, Azure and all things related to Cloud Computing and Infrastructure Optimalization. Follow Robert on Twitter @ClusterMVP Or follow his blog https://robertsmit.wordpress.com Linkedin Profile Http://nl.linkedin.com/in/robertsmit Robert is also capable of transferring his knowledge to others which is a rare feature in the field of IT. He makes a point of not only solving issues but also of giving on the job training of his colleagues. A customer says " Robert has been a big influence on our technical staff and I have to come to know him as a brilliant specialist concerning Microsoft Products. He was Capable with his in-depth knowledge of Microsoft products to troubleshoot problems and develop our infrastructure to a higher level. I would certainly hire him again in the future. " Details of the Recommendation: "I have been coordinating with Robert implementing a very complex system. Although he was primarily a Microsoft infrastructure specialist; he was able to understand and debug .Net based complext Windows applications and websites. His input to improve performance of applications proved very helpful for the success of our project

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.