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

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

Posted September 24, 2014 by Robert Smit [MVP] in Cluster-Aware Updating

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Twitter

  • %d bloggers like this: