SQL Server 2008 Failover Clustering

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….

How to Patch SQL Server 2008 Failover Cluster??…

This would be the last part of this Cluster Installation Series! In the Previous part, I’ve shown you how to Configure an Active-Passive SQL Server 20008 Failover Cluster. So at this moment our SQL Version will be at RTM, let’s apply Service pack 2 over our RTM.

Let’s begin! FYI I’ve downloaded SQL Server 2008 Service Pack 2 from microsoft and copied the exe files onto both the Nodes of our Cluster.

Procedure:

To be very simple and precise, We’ve to Install SP2 on our Passive Node(I’ve chosen Node2 as current Passive) first. Once completed, we’ve to manually failover SQL Services to Node2(Currently SQL is running on Node1) and apply patch on Node1.

This model has been introduced starting SQL 2008. Earlier SQL patching was cluster aware where it used to automatically patch all the nodes in your Cluster. But this leads to more down time depending on number of Nodes involved in your Cluster. Let us say…we’ve 8 Nodes, in this case SQL Server will not be available until all the Nodes are successfully being patched. Starting 2008 MSFT avoided this, basically making High Availability Solution more highly available 😉

Same logic applies to Active-Active Cluster as well. You’ve to Manually failover your SQL Services to other node(so at this point 2 instances of SQL will be running on single node) and you’ve to apply patch to idle node. Once patching is completed successfully on your idle node, you’ve to manually failover both the Instances to this node and patch the other node. Hope am not confusing!!

Let’s begin! I’ve chosen to patch my second node first. So I’ll make sure that SQL and all other dependencies are running on Node1. You can see below in the screenshot that Node1 is owning every SQL resource.

Very Important: You should remove Node 2 as a possible owner through out the patching process of Node2, making sure that SQL is not going(allowed) to failover to Node2 for any reason throughout this process.

Also,Just to double check open your SQL Server Configuration manager on your Node2 and you should be seeing SQL Server and SQL Server Agent as Offline as shown below. you might be seeing Browser and FTS services as running since they are not clustered (SQL Cluster Patch installation will take care of those services as needed).

Last thing to do is open your SSMS and note your Current build and Version just by issuing @@Version. For me the current Output is

“Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1) (VM) “

Close your SSMS(if any) on Node2 and double click on the SQL Server 2008 SP2 executable, you should be welcomed with the below screen

Next ->Accept the License Terms ->Next and you’ll get the below screen where you’ve to choose the features which you want to apply patch to.

Next and It’ll perform a basic Check for any Files in Use which might prevent successful installation of SP2 as shown below.

Click Next -> Update as shown below…That’s it:)

It will take few minutes to complete and you’ll get below screen.

Click Next->Close. Once Done, go to your Failover cluster Manager and add your Node2 back as a possible owner and Failover SQL Services to Node2(At this Point If you open your SQL Server Configuration manager on your Node1, SQL Server Service and SQL Agent Service should be Offline) and repeat the same procedure what we did for Node2, on Node1 to patch Node1 as well.

Note: Don’t forget to remove Node1 as possible owner now through out the process, making sure that SQL is not going(allowed) to failover to Node1 for any reason through out this process.

Once you are done with patching both the nodes successfully, add node1 back as possible Owner and now open your SSMS and issue @@Version and let’s compare with what we had prior to Installation.

As you see in the above screenshot we’ve upgraded our SQL from “Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34″ to “Microsoft SQL Server 2008 (SP2) – 10.0.4000.0 (Intel X86) Sep 16 2010 20:09:22″.

Perfect…That’s all for the day!

Basically we’ve covered everything right from scratch(Installing Virtual machines, Installing Windows Server 2008 Enterprise OS, Configure your DNS and Domain controller, Adding new Physical Computers to Domain, Creating your own ISCSI SAN, configuring NIC’s…Clustering Windows and creating SQL Server Cluster and finally how to Patch your SQL Servers which are configured as clusters).

Hope this series will be useful to someone who is new to SQL Failover Cluster setups and also hoping you enjoyed this entire series of SQL 2008 Cluster Installation as i did:)

Installing SQL Server 2008/2008R2 Failover Cluster- Part7(Adding Passive Node to SQL Cluster)

In the Part-6 of this Series, I’ve shown you how to Install a SQL Server Failover Cluster. In this Part Let’s add a Passive Node(Node2 in our case) to our Failover cluster. I assume you’ve followed all the parts in this series upto this point as shown without any issues.

Now, your very first step would be..You’ve to go to your passive node(node2) and insert your SQL Server installation media. At this Point your MSDTC, SQL Sever Service, Quorum Disk…literally all your resources are running on your node1. In other words, Node1 is hosting every application and service on your cluster.

Open you setup.exe and again annoyingly on my Node2 as well it asked me to install (KB942288) which i did just by clicking OK and it required a restart of your machine:( Well, once rebooted i opened my setup.exe and now I’m welcomed with typical SQL Server Installation Screen. This time You’ve to choose “Add Node to a SQL Server Failover Cluster” in the below screenshot.

It does a basic Rule check, once finished with no errors/warnings click OK. Choose your SQL Edition(in my case Eval Enterprise) and accept license terms and Next -> Install. It’ll do some Windows Installer actions/extracts all the required setup files. Once finished, you’ll be welcomed with the below screen where it does a thorough Rule check for your cluster settings.

Once everything is Green, you can proceed further by clicking Next and now at this point, SQL is smart enough to automatically identify your SQL Network name and SQL Instance name(Since we’ve only one Instance at this point, if you’ve multiple Instances you’ve to select from the drop down menu) and all you need is just click NEXT blindly as shown below:)

Now enter your Service account(s) password(s) and Click Next as shown below.

Next ->Next(choose Error Reporting to be sent to MSFT if you need). It will now do a validation on Add Node Rules as shown below.

Click Next and INSTALL. That’s all you need to do..So basically It’s nothing but a blind game where you have to click couple of NEXT, OKAY,INSTALL buttons to add a node to your existing SQL Cluster 😉

Hurrayyyyyyyyyyyyyy! We are done Guys…Now let’s failover our SQL Server back and forth and actually let’s see from SSMS.

I’ve opened my SSMS on NODE2 and connected to SQL Server and issued

select serverproperty(‘computernamephysicalnetbios’) to verify the current physical node under which SQL is running under as shown below.

Now, let’s failover SQL to node2 and let’s issue the same command once again(please note I’m not changing any connection strings….That’s the magic of Clustering).

Now, as you can see in the below Screenshot you can verify from T-SQL under which node SQL is currently running under(it says Node2 as expected).

Prestooooooooooo!! We did it…See how Simple it is to create a Cluster in your LAB Environment. Please note, I’ve violated all the enterprise rules in this world by disabling my Firewall and Not checking my Partition Alignments on my drives…but, as i mentioned this demo is just to create your Lab environment. If you’ve enough patience dealing with all those Firewall rules, Please explore those as well:)

BTW, in your Failover Cluster Manager there’s my favorite feature something called as dependency report…which gives awesome pictorial representation of all your services dependencies along with brief verbiage. You can pull that report as shown below.

As you can see SQL Agent is dependent on SQL Server, SQL is dependent on Physical Disks and Network name, Network name is dependent on Network IP =>Agent is actually dependent on all and SQL Server is dependent on above shown disk drives and Network name, but not on Agent and SQL Network name is dependent on IP Address….Everything is self explanatory in this Awesome report which MSFT is offering us starting Windows Server 2008. Kudos to Microsft:)

Let’s see how to apply Patches to your SQL Server cluster in next part of this Series!