Clustering

Upgrading Windows Failover Cluster 2012R2 to 2016

In this blog post, let’s see how to upgrade a Windows Server 2012R2 Failover cluster to Windows Server 2016. My current LAB setup is a 3 node cluster, all running 2012R2 and I decided to upgrade them to 2016 to learn new features/enhancements of windows server 2016. Thought of coming up with a short blog post on how to perform this if anyone of you are on the same boat as me.

Anyways….Here’s my current lab setup.

1_current_clust_config

Before moving on…what options do I have for upgrading my cluster?
Option 1: Install Windows Server 2016 on a completely new machine and introduce to the existing cluster and move roles and remove the old 2012R2 node….Work on the next node and perform the same….so on…till all your nodes in your cluster are 2016 machines. As the last step Raise the Cluster functionality level to 2016.

Option 2: Select a node which you want to upgrade, drain roles->evict node->Perform in-place upgrade from 2012R2 to 2016->Introduce the upgraded node back to cluster. Perform the same till all your nodes in your cluster are 2016 machines. As the last step Raise the Cluster functionality level to 2016.

NOTE: Yes, you can have windows server 2016 and windows server 2012R2 nodes participating in the same cluster. It’s called mixed mode, which is a new compatibility feature/enhancement introduced to transition from server 2012 R2 to 2016 without downtime. However you can’t leave the cluster in that state for ever. You’ve 4 weeks time to be in supported state. As soon as all the nodes in the cluster are upgraded to server 2016 the cluster functional level should be upgraded. Once this is done, we can’t revert/rollback, we can’t add 2012 R2 nodes to this cluster anymore.

I’ve chosen to perform inplace upgrades(Method 2 as mentioned above). Would I do this in Production? Probably not.

Now…I’ve drained the node which I would like to upgrade to 2016 and evicted from cluster.

2

3

It’s gone! My current cluster state at this point is shown below.

4_node_evicted

Now…I inserted 2016 media on the server which I just evicted and performed upgrade.

5_install

6

7_upgrade_drive

I had no free space on C$, so I ended up adding a new drive(U$) to facilitate upgrade process. Well, It failed…I had to expand Drive C$ and restart the process!!

8

After struggling for around 90 minutes or so, I am all set.

9_upgrade_done

Now, I am reintroducing the node back into the cluster.

10

12

13

14

Alrighttttttttttttttty…..Here it is!

15

As you can see below, Current cluster functional level is set to 8. Once all the nodes have been upgraded to 2016 in this cluster, we should upgrade functional level as I already mentioned and the below command would return 9 as the output.

16

There you go guys, Hope this helps. Happy weekend!

Update: After spending few more hours, I was able to upgrade all my nodes to 2016 and upgraded cluster functionality level. It’s no longer in Mixed Mode(in other words, I won’t be able to add a windows 2012 R2 node to my cluster). See below…As I mentioned above, the cluster functional level is set to 9 after I issued “update-clusterfunctionallevel”

last

 

Lab setup – AlwaysOn AGs in a Multi Subnet Cluster – Part 2

In part1, I’ve shown how to create a windows cluster in a multi subnet setup.  In this post let’s see how to create a AG and corresponding listener.

In my lab, I will be creating two AGs and two corresponding listeners.

Details:
I’ve two databases – > sales and customers.
Two AGs – > Sales_AG and Customers_AG.Two Listners – > sqllst_Sales and sqllst_Cust.
For sales AG, I’ve disabled “Database level health detection”, a new feature introduced in SQL 2016.

AG1

ag2

Now, for Listener two IPs from both subnets have been provided.

ag3

ag5

Same process has been followed for creating customers AG and listener as well(But this time I’ve enabled Database level health detection).

ag4

Since this is a multi-subnet setup two entries(one from each subnet) will be created in DNS for each listener name as shown below.

ag_final

That’s about it folks.

Lab setup – AlwaysOn AGs in a Multi Subnet Cluster – Part 1

Let’s see how to setup an AG(SQL 2016) in a multi subnet cluster(Geo cluster) in a lab environment.

Below is my lab setup:

Two Replicas sitting in my Production Data center.(Subnet 192.168.1.x) – Sync Mode Automatic Failover.
Third(Far) Replica sitting in my DR Data Center.(Subnet 192.168.2.x) – Asynch Mode Manual Failover.

So, What do we need to be able to setup multiple subnets and routing in a lab environment? Answer is “Routing and Remote Access“. Have that installed by going to Add Roles/Features on your AD/DNS server.

Pre-req Step: Created 2 NICs on my SANDC machine with IPs 192.168.1.100 and 192.168.2.100

Open Routing and remote access config tool; right click on the root node and select “Enable and Configure Routing and remote access”.

Now…under IPv4, under General right click and select new routing protocol and select “RIP Version 2 for Internet Protocol”.

1

Now right click on RIP and select new interface and select your NIC1 and hit okay and next repeat the same step and select NIC2 this time and click okay. You are done with routing…That’s all you need for routing to work(As long as you got all the IPs and DNS details right).

2

4

3

5

Now, I’ve setup 3 nodes(two nodes(Prd) in 1.x and one(DR) in 2.x) and installed Failover Cluster feature on all the nodes and disabled all firewalls.

My PRD nodes: STLSQLAG1 and STLSQLAG2.
My DR Node:AZSQLAG3

Do find all my NIC settings from all my nodes at the very end of this post.

Now, Let’s create Windows Cluster:

Please refer to my earlier posts on how to create a cluster under “Clustering category” for detailed steps. Below are the steps at a high level.

clu1

clu2

clu3

Now…My cluster is ready, but it’s missing Quorum which is very critical for a cluster to be healthy. For that I’ve created a File share witness as Quorum.

clu4

clu5

FYI, NIC settings from Cluster manager are shown below.

clu6

clu7

NIC settings on all of my nodes:

STLSQLAG1: (1 NIC Card)

nic1

STLSQLAG2: ( 1 NIC Card)

nic2

AZSQLAG3(DR Server): 1 NIC Card

nic_AZ_DR

AD/DNS Server: ( 2 NICs one for 1.x and other for 2.x)

nic3_sandc

nic4_sandc

In this post we’ve seen how to setup a geo cluster in a lab environment. So, this completes the prep work needed from Windows stand point…Let’s see how to create AGs and Listeners in our Multi Subnet environment in next part of this series.

SQL Server 2016 Cluster setup – What’s changed?

In this short blog post let’s take a screenshots tour of SQL Server 2016 Failover cluster installation and see if Microsoft made any significant changes to the setup process.

1

2

3

4

In the above two screenshots, I’ve provided my VNN and Instance name for SQL.

5

Add your Disks as needed.

6

provide your IP address.

7

Enter your Service accounts.

8

Select your Database directories accordingly and go to TempDB tab to check SQL installation wizard creating tempdb data files based on the number of CPUs you have got.

9

10

11

This is the first node of my cluster setup, hence you can see PRDSQLTREKA as the only node listed in the above screenshot. Next Next Done.

Now on the second node:

Select Add node to SQL Server Failover cluster and Next-Next-Next, provide service account and done.

12

13

Basically nothing has been changed specific to Cluster setup. If you are comfortable with earlier cluster setups, then you should be golden. Cheers!

 

Setup a SQL server Cluster Lab in your own laptop!

In this post, am just providing links to my previous Blog posts(in a sequential manner) where i explained how to setup a Cluster from scratch.  Received multiple complaints that old links are broken and few found it difficult to navigate through the series. So…i just wanted to make sure you guys have it handy in the right place.

You can start from Part 2 in my series…

https://sqlbuzz.wordpress.com/2011/08/02/installing-sql-server-20082008r2-failover-cluster-part2/

Part 3:

https://sqlbuzz.wordpress.com/2011/08/03/installing-sql-server-20082008r2-failover-cluster-part3/

Part 4:

https://sqlbuzz.wordpress.com/2011/08/03/installing-sql-server-20082008r2-failover-cluster-part4/

Part 5:

https://sqlbuzz.wordpress.com/2011/08/09/installing-sql-server-20082008r2-failover-cluster-part5/

Part 6:

https://sqlbuzz.wordpress.com/2011/08/11/installing-sql-server-20082008r2-failover-cluster-part6/

Part 7:

https://sqlbuzz.wordpress.com/2011/08/11/installing-sql-server-20082008r2-failover-cluster-part7adding-passive-node-to-sql-cluster/

Part 8:

https://sqlbuzz.wordpress.com/2011/08/12/how-to-patch-sql-server-2008-failover-cluster/

Hope this helps…Have fun with your cluster 🙂

The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group.

Let me share with you guys, an Error Message what I’ve encountered yesterday. Well, this is not a very detailed post but I hope this will help what to check for when you encounter this Error/Warning Message.

Okay, I was validating a brand new SQL Server Clustered Instance(two node) and I tried to failover the Instance to the other node. But when I try to initiate a failover, I got this Message saying “the operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group“.

What’s the Issue: The 2nd Node was not added into this Clustered Instance.(Add Node to Cluster piece was missed by whoever Installed this SQL Server Cluster)

How to Confirm?

Go to the 2nd node and look for SQL Server Binaries for respective Instance. Also you should be seeing SQL Server Instance Being Installed but in Offline Status(assuming this is your Secondary Node at this particular point of time) from your SQL Server Config Manager Oooor you can simply Open Failover Cluster Manager and check for Possible Owners(Not preferred Owners) for this Instance.

Once he added the missing node to the SQL Cluster, everything was back in business as expected.

I know it’s a Quick and a Dirty Post, But I hope this helps!

How to Failover the Cluster Group in Windows Server 2008/2008R2

In this short Blog post let us see how to move(Failover) the Cluster Group from one node to other in a Windows Server 2008 Failover Cluster. See here for more details on locating your Cluster Group for your Cluster.

Let’s get into the content! As you can see in the below Screenshot, I’ve my SQL Server running on Node2.

But How about my Cluster Group? I would simply issue “Cluster Group” from my cmd prompt for getting that info. As you can see below my cluster group is running on Node1.

Now, How to Failover the Cluster Group from Node1 to Node2 without any SQL Services Interruptions?

Sol 1 – Using CMD: Simply Issue the below command from your cmd prompt.

Sol2 – Using PS: Simply use Move-ClusterGroup “Cluster Group”. You might get the below Error if you are using Powershell for your Cluster for the First time.

This is because you haven’t imported Cluster Module yet for your PowerShell. You should be good to go once you import Failover Cluster Module. For More Information on Cluster CmdLets please see here.

Btw, There is no way you can do Cluster Group Failover from your Failover Cluster Manager GUI unlike Windows Server 2003!!

Hope this helps!