Apply SP1 to SQL Server 2012 on active/active Windows Server 2012 cluster.

mattbowler, 2014-01-08

A while ago I blogged my about my approach to service packing a SQL Server 2008R2 active active cluster. Time to apply SP1 to SQL 2012 on top of a Windows 2012 failover cluster. The basic approach is similar but there are a couple of subtle differences.

The key to the approach with 2008 was to patch the passive node, while not allowing the SQL instances the option of failing back to the passive node. In Windows Server 2008 this was possible through the Failover Cluster Adminstration GUI tool. I haven’t seen any literature suggesting that this approach is no longer valid – although I have seen moves towards ‘cluster aware’ updating. But this is only available from SQL 2012 SP1, so to apply SP1 I’m going to stick to my tried and tested method.

1.The management of possible failover partners is no longer available via the GUI and needs to be done with cluster.exe. Cluster.exe is not required for the configuring of a server 2012 failover cluster so it may or may not be configured in your environment. You can add the Failover Cluster Command Interface feature using the server manager tool.

clus3

2. Make a note of all node names (and/or IP addresses), SQL Server virtual names along with preferred nodes. If there are more than three nodes you may need to also take note of possible owners for each SQL resource group. For my example assume that I have a cluster with node1 and node2, SQL1 normally lives on node1 and SQL2 normally lives on node2.

3. To start with a clean slate and ensure any previous updates are completed both nodes should be restarted if possible. Choose the physical node that you you want to patch second and restart that node (in my example node2).

4. Restart the node you want to patch first (node1). This will mean that both active SQL instances are now running on node2. Some restarts will be essential, but you could avoid the first two restarts if you need to keep downtime to a minimum and just fail SQL1 over to node2. The main point here is to always patch a passive node.

5. Use cluster.exe to remove node1 from the possible owners lists of SQL1 and SQL2. This means that neither SQL instance can fail over to node1 while it is being patched. The relevant commands are:

  • Check the possible owners: cluster.exe resource “SQL1″ /listowners
  • Remove passive node from possible owner list of all instances: cluster.exe resource “SQL1″ /RemoveOwner:node1
  • Confirm: cluster.exe resource “SQL1″ /listowners

(repeat for SQL2)

6. Run the service pack executable on node1 and apply SP1 to both SQL instances.

7. Restart node1.

8. Add node1 back into the possible owners lists of SQL1 and SQL2 using:

  • cluster.exe resource “SQL1″ /AddOwner:node1

(repeat for SQL2)

9.Fail both instances over to node1. You could do this by restarting node2 or using powershell to suspend node2 with the drain parameter. Once the instances have successfully moved and you have removed the possible owners  – resume the node to patch.

10. Repeat steps 5 – 8 on node2.

11. Add node2 back into the possible owners lists of SQL1 and SQL2 and fail both instances over to node2. Check that the build level is correct and review the SQL Server error logs.

12. Fail SQL1 over to node1. Check build levels and SQL Server error logs.

I want acknowledge Sean Gallardy who helped clarify my thinking by responding to my msdn forum question.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads