Update SQL 2012/2014 Failover cluster with Cluster Aware Updating

My today  task is to update specific SQL 2012 SP1 failover instance using Cluster Aware Updating  with minimal downtime to CU9 . Procedure is the same for SQL 2014 even I didn’t test it myself. First lets see my node schema. I have next configuration witch is probably usual for all SQL cluster beginners. Cluster is in Active/Pasive mode, and one side of this cluster schema is always offline

image

I need to update Instance 2 on all nodes. If you have time to read you can read all details in this Microsoft document  Patching SQL Server Failover Cluster Instances with Cluster-Aware Updating (CAU).  If not, follow my blog post for this scenario

CAU provides two plug-ins: Microsoft.HotfixPlugin and Microsoft.WindowsUpdatePlugin. For this task we choose Microsoft.HotfixPlugin in Remote-Updating-Mode because it support my scenario:

  • Updating a specific SQL Server 2012 cluster instance across all nodes using either Remote-Updating-Mode, or Self-Updating-Mode

The disadvantages of Microsoft.HotfixPlugin are:

  • It does not support applying multiple updates that should be installed in sequence.
  • It does not support a detection logic mechanism to determine update applicability. Hence, with every Updating Run, the updates are offered and executed even if the update is already installed

To use the Microsoft.HotfixPlugin, the following steps are required.

  1. Publish the update to a CAU directory structure
  2. Create a custom hotfix configuration file
  3. Execute the Updating Run by using Microsoft.HotfixPlugin

Publish the update to a CAU directory structure

The steps in this section explain how to create the CAU directory structure that is required to apply updates to specific SQL Server 2012 cluster instances.

  1. Review (if you want) How CAU Plug-ins Works , which explains how the CAU hotfix directory structure can be used for different needs.
  2. Create a network share with name hotfixroot for the directory structure (for example, \\<networkshare>\hotfixroot). This can be a standard network share, or a Server Message Block (SMB)-enabled file share. Also create folder CAUHotfix_All.  We will use this folder for holding updates we need to run for all nodes without any exceptions.

For my scenario I created \\servername\hotfixroot\ share with two folders with names Node1 and Node2 under which I created CU9 folder with CU9.exe file. Names of the folder should be names of the cluster node computers. Folder name CU9 is important because we need to tell in configuration script where is our update.  If you have more then two nodes and you want to run update only on two nodes out of six, this is only way you can do it. This is how directory path look like:

\\servername\hotfixroot

\Node1

\CU9\<SQLServer2012SP1CU9Package>.exe

\Node2

\CU9\<SQLServer2012SP1CU9Package>.exe

Create a Custom Hotfix Configuration File

In this step copy the configuration file DefaultHotfixConfig.xml  to the “hotfixroot” folder on the share. You can find config file under C:\Windows\System32\WindowsPowerShell\v1.0\Modules\ClusterAwareUpdating on any of the cluster nodes, or on the orchestrator machine (in my case  Win 8.1 client machine with RSAT for Failover cluster installed)

Change name of DefaultHotfixConfig.xml file to SQLinstancenameConfig.xml. Add folder rules part under Default rules in  configuration xml. file. I found 2 examples there. I deleted it. Use XML notepad or Notepad++ if you are not  certain about xml syntax.

<root>

<DefaultRules>

</DefaultRules>

<FolderRules>
<Folder name=”CU9“>
<Template path=”$update$” parameters
=”/ACTION=PATCH /INSTANCENAME=BALBOA1\SAPPIP /QUIET /IAcceptSQLServerLicenseTerms”>
<ExitConditions>
<Success>
<ExitCode code=”0″/>
</Success>
<Success_RebootRequired>
<ExitCode code=”3010″/>
</Success_RebootRequired>
<NotApplicable>
<ExitCode code=”-2068578302″/>        <!– ERROR_PATCH_TARGET_NOT_FOUND –>
</NotApplicable>
<AlreadyInstalled>
<ExitCode code=”-2068643838″/>        <!– ERROR_PATCH_ALREADY_APPLIED –>
</AlreadyInstalled >
</ExitConditions>
</Folder>
</FolderRules>

</root>

Running update

Go to Cluster Aware updating console and connect to failover cluster you want to update. Click on apply updates to this cluster wizard. On this first page choose Microsoft.Hotfix.Plugin like on the picture below and click Next

image

On this page enter you hotfixroot share name and select “Disable check for administrator access to the hotfix root folder and conf file”. This is not neede if you configure hotfixfolder in that way that user under which you run this process has only read and execute rights. If you don’t want to spend some more time configuring it check this box. Click Previous not Next

image

Now under box Microsoft.hotfix.plugin you will see all arguments you configured. We need to change one and it is HotfixconfigFilename. Enter your SQLinstancenameConfig.xml name instead of DefaultHotfixConfig.xml. Now click Next two times and you will get to confirmation page. You will se here all your settings and Powershell command that you will run when you click Update. In short you can just write this command in Powershell and run it for the same task. You can find details for Powershell in Patching SQL Server Failover Cluster Instances with Cluster-Aware Updating (CAU) document.

image

Click update and close. You will see the process of updating under Log of updates in Progress. If your update failed you can find CAU logs on  any of nodes under “%windir%\cluster\reports\” . For SQL update in this case you can fin logs for SQL update under “%programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log” for SQL 2012

image

My errors and troubleshooting

I had one error I found in CAU log and that is “The share for the hotfix root folder does not enable data integrity. You must configure the SMB share for data integrity to access the hotfixes from the specified folder, by enabling either SMB Signing or SMB Encryption on this SMB share. Cluster Node rocky1. Program path: $update$. “

What I needed to do is enable encryption on share folder. Because my share was on 2008 R2 system I moved it to W2012 computer. There you have Powershell option to encrypt share data with next command. It is much easier then configure it on 2008R2 system.

You can find more about it on How CAU Plug-ins Works.

Set-SmbShare &amp;lt;ShareName&amp;gt; -EncryptData $true

Best regards to everybody and hope I helped you not to read all of the documents I did

Advertisements

Adding new SQL failover cluster instance

I had this task today, so why not to document it. If you already have SQL Failover cluster created you will probably have special requirements for some exotic application that require special settings for database instance you can not fulfill on existing SQL failover cluster . That is why you need to create new SQL failover instance.  Most usual are SQL collation settings or max DOP settings to mention some.

Lets document the procedure. Requirements are that you have prepared: 

-disks that you will use for SQL data and log files. If you didn’t format disks that you already added to cluster storage resources turn on maintenance mode, because you can not format it otherwise.

– ip address for new sql cluster instance

– domain/windows  sql accounts that will be used for running sql services

1. Start installation and choose New SQL Server Failover installation

image

2. On Setup support rules page click Next if everything is fine

3. On product updates you can check Next or look for updates online if you need it.

4. On Setup support rules page click Next if everything is fine

5. License Terms confirm I accept

6. SQL setup role choose SQL Server Feature installation

7. On Feature Selection chose Database Engine Services.  In a failover cluster installation, the features SQL Server Replication, Full Text and Semantic Extractions for Search and Data Quality Services are mandatory and you cannot deselect them.

image

8. On Feature rules click Next if everything is fine

9. Give SQL server Network name and named instance name on instance configuration page

image

10. On Disk Space requirement page check if you have enough space and click OK

11. On cluster Resource Group click Next

12. On select disk for cluster failover group choose disk that you have available and want to use. Click Next

image

13. Add IP address that will be used for your cluster, and click Next

image

14. Add SQL Service account for Agent and Engine Service

image

15. If you need specific Collation choose it

image

16. Enter SQL admin users and chose Authentication mode you need

image

17. Choose you DATA directories

image

18. On Error reporting page click Next

19. On Cluster installation riles click Next if all is green

20. On Ready to Install page check selection. If you are satisfied click Install. You can find Configuration.ini file later with all settings under configuration file path.

image

Error like this can happen. “The cluster resource “SQL Server(INSTANCENAME)” could not be brought online ……

image

If you go to Failover Cluster you will see this error.

image

It means that you need to prestage cluster computer object in AD, you can found detail instructions on: http://technet.microsoft.com/en-us/library/dn466519.aspx 

When everything is done you will get this success message.

image

That is for today, and tomorrow I need to update SQL Failover Cluster to SQL 2012 SP1 CU9. Expect new post tomorrow. See you