Clustering

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!

How to Add a New Disk/Drive to SQL Server Failover Cluster?

Hey Folks! I know…..It’s been a long time since I wrote something technically related to SQL Server(well, am playing with my new DSLR Camera 😀 a lot now a days in leisure times) and so I’m here back with a very interesting and confusing topic for many DBA’s. Recently I had a discussion with one of my buddies on issues she had with a newly added drive to Windows Server 2008R2 Failover cluster and I was able(at least I think I was :D) to explain over our phone conversation! Later thought about coming up with a write up which might help even others in a similar situation. So, In this blog post let’s see how to add a new drive to your existing SQL Server Failover Cluster(Just an FYI : Win Server 2008/SQL 2008 in my case).

Before going any further, I’ll be wearing 3 hats in this Blogpost(a SAN Admin hat, Windows Admin hat and obviously a SQL DBA hat). Let’s start with wearing a SQL DBA hat!

Below are the screenshots of my current SQL Server Failover Cluster which I’m going to add a new Drive.

As you can see I’ve 6 SAN Drives dedicated to this cluster. Let’s see how to add a new drive with a name aaaaahhhh……say “SQLBacks2” to our SQL Server.  First thing is your SAN Admin should create/present a new Drive for you to be able to add to our cluster.

Okay, let me wear my SAN hat. Now am a SAN Admin and I’m going to create a new SAN Drive for my Windows/SQL team.

Creating a SAN Drive:

Note: If you didn’t followed my earlier Clustering Series, I use Starwind for all my SAN Stuff. Just refer to my previous posts in clustering series to understand this tool more in depth.

step1: Add a new Target.

Step 2:

Step3:

Step 4: placing and sizing the Drive( I chose just 1 GB for this example)

Step 5: Making it a ISCSI aware disk(Mandatory for Clusters)

Okay..now Am done with SAN guy role and now I’m wearing  a Windows Admin Hat to initialize the new disk which my SAN Admin just created for me from my Nodes.

Wearing a Windows Admin hat:

Went to ISCSI Initiator and all I have to do is Initiate this new drive as shown below. you can see our new drive as Inactive as of now in the below screenshot.

Once you click ok, you will be seeing this drive as connected in your ISCSI Initiator, but still this is not available for Windows. Now we’ve to go to Server manager->Storage and Bring it Online->Initialize->Create new Simple Volume(Format the Drive) so that you can see it physically in your My Computer. (See below Screenshot, once I did created the drive, I selected K$ as my volume)

Once, this drive is logged on both the Nodes  basically now we’ve to add this drive to our Windows Cluster using Fail Over Cluster manager(am on Node1) as shown below.

It will search for all the disks which are suitable for clustering for a while…After few seconds, You’ll see below.

Once added, you can see this Drive has been added to our Cluster as Available Storage(Note: At this Point SQL Server Service is not yet ready using this Drive).

Now…Right Click on your SQL Server and select “Add Storage” as shown below.

So…Are we done yet? Nopee…….Here comes the most Important Part and the piece which I’ve seen people missing a lot while adding a new Drive to existing SQL Server Clusters. So what are we missing here? The Answer is SQL Server Dependency. Didn’t quite understood what I’m saying? Well, see the below Screenshot where Drive 7(new drive which we just added) is floating in air with no relation to SQL Server Service.

How to verify that the Drive has not yet added successfully 100% to our SQL Server Instance from SSMS? It’s very simple, Just try to access this Drive from your SSMS. In this example I’m just trying to take a backup of one of my Databases and looking for $K to place the backup, which I can’t see(But I can see it from My Computer) unless I add the dependency Manually as you can see below.

So…What to do now?? Just make an “AND” Dependency for your New Drive. But how??

Wearing SQL Admin hat now! Well, this could be even a Windows Admin depending on your company!

Navigate to your SQL Server and Right Click and select properties and go to dependencies tab as shown below.

As you can see, there is no Drive 7(DriveK) as dependency. Now you have to add the new drive as “AND” dependent as shown below and you are all set to go 🙂

Now see below Dependency report, where Drive 7 is no longer floating in the air 😀

and of course a happy SSMS as you would expect to locate the Drive for your Instance!

Now you are all set guys…Just try to move the Service to other node(s) and double check that everything is working as expected from all the nodes. Hope this helps! Cheers…