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 🙂

Advertisements

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…

Cluster Logs in Windows Server 2008??

In this Blog post, let’s focus on where/how we can review Failover Cluster Logs on Windows Server 2008 and above. As most of us know on Windows Server 2003 Cluster, we used to have  “cluster.log” file on each node participating in cluster, which contains debug information. FYI, One can locate these files in “%systemroot% \ cluster” Folder. But how about cluster log files in Windows Server 2008/2008R2?? Uhuhh…It’s not something which you can review directly by navigating to systemroot folder. Below is the screenshot of that folder in my cluster.

You can see a folder called “REPORTS” in the above screenshot where all the cluster Validation Reports will be stored by default. attaching below Screenshot Just to prove, that cluster.log file can’t be located in the “reports” folder as well 🙂

Starting Windows Server 2008, cluster logs are managed by something called as “Windows Event Tracing“. Just an FYI, If you are interested, You can pull all the current  running traces by opening “perfmon” and navigating to Data Collector Sets. (Shown Below in the Screenshot)

So, as any other logs, cluster logs are stored in “C:\Windows\System32\winevt\Logs” folder with “etl” extension as you can see below.

Well, so How to read those .ETL files??

For that, we have to use “cluster.exe” command with “/gen” switch. Basically this will generate a human readable text file in your “Reports” folder.

Syntax: Cluster log /gen

Output:

 

As you can see in the above Screenshot, it will communicate with all the nodes in your cluster. In my scenario, Node2 is offline(Powered down).  BTW, even though Node2 is down, it will create “Cluster.txt” file in your Reports Folder with related information.

So, how to generate Logs related to a specific Node?

You have to use “/NODE” switch with your cluster log syntax. Please see below Screenshot.

As you can see, this time, we had no RPC Errors.

So, there is lot to explore/learn in 2008 Failover Clustering, if you are using 2003 since long time, things got changed drastically. There are lot of other options/switches available with cluster.exe. Even you can limit the size if you are interested. BTW, everything which I’ve shown here can be achieved via Powershell Cmdlets as well!

Hope this is informative….

Quorum Drive in Failover Cluster(Windows Server 2008R2)?

Let me share one of the interesting conversations I had couple of days ago with one of my colleagues.  This was regarding Quorum Drive in Failover Cluster(This was Windows Server 2008R2).

Let me tell you the story in short. ” She had a Question regarding Quorum(Q Drive typically) disappearing from Failover Cluster Manager. She also verified all the 3 Nodes(Remember this was a 3 Node Cluster) in the cluster and was not able to locate the Q Drive.

So, is this Normal in Failover Cluster?? The Answer is Absolutely Yes, this is perfectly normal for any Failover Cluster(Starting Win Server 2008) with Odd Number of Nodes. Quorum Drive is not mandatory anymore starting Win Server 2008. This might be really confusing for folks who are coming from Windows Server 2003! Yes, you heard it right..There is no need of having a dedicated Drive to act as Quorum. In this case our Cluster Quorum was configured as “Node Majority“. In Other Words, all the nodes will be participating in forming a Quorum and will be voting. If one Node dies, we still have 2 nodes Up and Running(so, we still have Majority of votes) and hence our cluster will be running without any issues. If 2 Nodes dies, majority of Nodes are down, at this point our cluster goes down. The basic idea is to avoid single point of failure!

Modes of Quorum starting Windows Server 2008:

Node Majority: Each node that is available can vote. The cluster functions only with majority of the votes. – For Odd Number of Nodes.

Node and Disk Majority: Each node + a Dedicated Drive(Typically Q) will be voting. – For Even Number of Nodes.

Node and File Share Majority: Each node + File Share Witness will be voting. ( Personally, I wouldn’t prefer this for any number of nodes)

No Majority(aka Disk only): This is what we used to have till Windows Server 2003, where a dedicated Disk will be acting as a Quorum.

Note: Leave the Quorum Config as is, chosen by windows by default when configuring the cluster.  Windows is smart enough to choose appropriate Quorum configuration for your cluster. Change only if you are sure about what you are doing. Also, please note that Quorum configuration can be changed any time even after creating a cluster.

In My lab, I’ve a 2 Node Single Instance Cluster. So I’ve Node and Disk Majority as my Quorum. Please see below screenshots.

See Quorum Configuration as Node and Disk Majority. ( Just Ignore the warning above :D, my Node2 is turned off as of now, hence that warning!)

How to change the Quorum Configuration?

Right click on your Windows Cluster and choose More Actions as shown below.

Now, you can click Next and choose your Quorum Configuration type and proceed further.

Just want to remind again, Don’t change anything unless you understand completely what you are doing!!…

CNOs/VCOs(Computer Objects) and few ways to protect them…!

If you already have experience working on Clustered Environments, you might already know about CNO(Cluster Name Object) and VCO(Virtual Computer Object). For Newbies, let me explain what CNO and VCO are in a line or two…

CNO: This is the Core piece of your Windows Cluster and acts as an identity of your Windows Cluster. This is a computer Object which will be created in your AD under Computer Node(under your Domain or OU, if you have any).  It will be same name as your Cluster.

VCO: Again, these are the Objects being created in AD under Computer Node depending on the Services and Applications which you are creating inside your Cluster. Yes, CNO is responsible for creating those VCO’s.  CNO’s should not be deleted or not even touched in terms of security by any means and by any person.  Services won’t come Online if CNO permissions are modified or CNO gets dropped accidentally, which is a potential threat for your cluster.

In order to Recover from deleted CNO situation, your Domain Admin should be involved and he/she needs to restore your Active Directory Objects which is not a simple task, especially in larger enterprises. Good News is Starting Windows Server 2008R2, we’ve something called Active Directory RecycleBin which is an awesome way to recover AD Objects. Hold on Guys….there’s a GOTCHA though!

Gotcha: AD Recycle Bin is not Enabled by Default. It has to be enabled within your Domain by your Domain Admin!

What if we communicate with our Domain/OU/Server Admins to enable some settings which basically prevents any accidental deletion of Computer Objects? It would be really nice if we could prevent that deletion action in first place right, instead of recovering after a disaster. So what can be done here? Windows Server 2008/2008R2 offers a really simple way to prevent these accidental operations(mostly Human Mistakes). There’s a small checkBox which we should enable to make this happen. Once enabled, It won’t let anyone to delete that Object.

Demo:

I’m on my Domain Controller and I’ve opened AD Users and Computers from Administrative Tools. You can see WINCLUST is my CNO.

Very Imp: Now You’ve to go to View and select Advanced Features, to be able to see/perform all the available options/operations we’ve. You can see below

Now, I’m trying to protect my CNO from accidental deletion. All I’ve to do is Right click on CNO and select properties and navigate to “Object” tab and check that tiny box as shown below:)

That’s it! Is it really hard? Nope. You can check with your Domain Admins to make sure that this is checked on all your CNO’s and VCO’s.

Note:

All the New OUs are automatically set to be protected.

Any New Users/Groups are not set to be automatically protected.

Any New Computers are not set to be automatically protected.

With the protection being enabled, now let’s see what happens if we try to delete that CNO manually from my AD.

Note: Don’t even think about doing this in your Company(In the First Place, we will not be having those level of privileges). I bet, you’ll be fired the very next moment!

I got this warning Message saying, Are you really Sure about what you are trying to do here?? See below Screenshot.

Let’s say…I’m one Stupid Guy and went ahead and clicked on Yes. The below is the screenshot of what I got.

Remember I logged onto this as a Domain Administrator, even then..Windows is saying, “Uhuhhhhh….No Idiot! I’m not letting you to perform this operation unless you uncheck that tiny box which we checked earlier” 😀

Isn’t it Something Awesome Guys? I really really encourage you to check with your Server Admins on this Option being enabled if you are responsible/accountable for some Mission critical Production SQL Server Clusters.

Hope this is useful info you learnt something new! Cheers!…