Month: January 2012

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

Advertisements

SQL 2012RC0 Database(s) Properties from 2008R2 SSMS

This is one interesting thing which I’ve Observed recently connecting to SQL Server 2012 via SSMS 2008R2.

SSMS 2008R2 is not displaying Compatibility Levels of the 2012 databases! See below Screenshot for what I’m trying to explain.

See below screenshot for the same database properties from SSMS 2012.

As you can see, It is displaying Containment properties, additional File Stream properties…etc!  I think this is not an Issue with 2008R2 SSMS, but just something to be aware of!

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

sp_configure and sys.Configurations

This would be a very small blogpost, Well, actually an informational Post, no Issues and no troubleshooting shown in this post!

We all, being a DBA will be running “sp_configure” every now and then on our SQL Instances to see what are the configured settings on the Instances. okay, okay….If you don’t have this Practice, make this a habit from right now. Document your Instance settings in your Run Book and compare with them each time you run this and you can analyze what/why/when/who changed accordingly.

Okay, By default if we run “sp_configure” on our Instance, we’ll be getting only 17 Rows(as of SQL Server 2012 RC0).

So are those the only configurable Settings we’ve in SQL Server? Nope!

Actually, you can get all the configurable options from querying sys.configurations, where it returns 69 Rows as shown below.

Is_Dynamic = 1 implies, no need of running RECONFIGURE Statement after changing the setting.

Is_Advanced = 1 implies, we should run RECONFIGURE Statement after changing a setting.

Now, for sp_configure to return all the 69 Options(rows) all we’ve to do is enable “show advanced options” and run “RECONFIGURE” as shown below.

Once, done issuing “SP_Configure” will return all the options available as you can see below.

Now, you can see 69 rows being returned. Cheers!

Windows Networking Tools for SQL DBA’s – PATHPING/TRACERT/PING

Hey Folks! Hope you all had fabulous holiday season 🙂

In this Post let’s see Three fabulous tools which Windows OS offers for testing your Network between your Servers. A typical example for DBA’s would be testing Network Latency between your Prod and DR Server while initial testing of your Mirroring configuration or even looking for latency/Response times when you are copying a huge Backup File from one Server to another Server etc.

Tool 1: PING

This is one of the most used command by any IT professional, irrespective of what he/she does. PING is a very Basic test to verify IP connectivity from one machine to another.

Eg: PING MachineName ; PING IPAddress; PING -T MachineName/IPAddress(T for Continuous ping).
For More Options and Info please visit http://technet.microsoft.com/en-us/library/bb490968.aspx

Tool2: TRACERT

This is another useful tool which traces the entire route(Includes all the Routers/NIC’s involved in your Path) from Source Machine to Destination machine.  Pretty useful tool.

Eg: TRACERT Machinename
For info on how to interpret those results, please visit http://technet.microsoft.com/en-us/library/cc940128.aspx

Tool3: PATHPING ( Awesome Tool to have in your ToolKit)

This is one of my all time Favorite tool which basically mixes both PING and TRACERT Results and will provide you with a very detailed/useful information.

Eg: PATHPING MachineName. In this Example I’m doing a Pathping from my Domain Controller to one of the server in my domain. See below on how it looks for the output.

Typically, you would see many Network Interfaces in between(Of Course right…!). Try “PATHPING GOOGLE.COM” from your machine and you can see what I mean. Or Just try PATHPING on one of your Servers in your company, you’ll see what I’m trying to say.

Tip: I typically make use of “>”  appended to my command, so that I can save the Output as a report in a text file and I can review later any time or you can simply forward that text file to your Server/Network Team to look into. Please see below for what I’m trying to explain(Basically, it’s something like “SPOOLING”  in Oracle if you are familiar with ORACLE SQLPLUS).

Now it created a Text file called “Output.txt” under my C Drive and the contents are as shown below..

Trust me Guys, PATHPING is one of the most Useful tools we have for free, which many of DBA’s are not aware of, or I should say most ignored Tool by many DBA’s!

Technet has fabulous info on how to interpret results and options available etc here: http://technet.microsoft.com/en-us/library/cc958876.aspx

Hope this Info helps!…