Find information about SQL Server 2012 AlwaysOn FCI September 4th, 2013

Vinod Kumar

When we were writing our AlwaysOn book, there were so much more to write that we couldn’t add a lot of content which we wished. Having said that, it still made to a 500+ pages which was something we didn’t realize. As a DBA, you need to administer several SQL Server 2012 instances at a given point in time. Before you can start your administration of SQL Server instances, I highly recommend we be clear on certain things:

  1. How many nodes are there in the system?
  2. Are these SQL Server instances Clustered currently?
  3. Which is the current active node the instance is currently running?
  4. Who are the active or possible owners available?
  5. Which are the shared disks we are accessing?
  6. The various settings for SQL Server Cluster resource? etc

Even though these look simple, ask your DBA and you will be surprised with their expression :) – trust me. In this blog post, let me call out some of the important information we need to collect ahead of time. This is not exhaustive, but I am sure this gives you a good start.

First connect to SQL Server Management Studio, connect to Server and open a Query window. Make sure you document these in each of your environments.

1. Version and Edition


SELECT SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,

SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,

SERVERPROPERTY(‘Edition’) AS Edition

2. Find SQL Server Instance is Clustered


We will get a value of 0 if the instance is not clustered, else we will get 1.

3. Which instance are we actively running

SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetbios’)

4. Which nodes instance is running

SELECT NodeName, Status, status_description, is_current_owner

FROM sys.dm_os_cluster_nodes;

5. How to identify Shared Disks the instance can access

SELECT DriveName FROM sys.dm_io_cluster_shared_drives;

6. Current SQL Server Cluster resource settings

SELECT VerboseLogging,

SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout

FROM sys.dm_os_cluster_properties;

Some of these properties are used to set the values that affect failure detection, failure response times etc. Knowing our environment is important and as a professional DBA, it is critical for us to have the datapoints handy.

Inspired from Book content : “SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle) is now available.

Tags: , , , , , , , ,

This entry was posted on Wednesday, September 4th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Find information about SQL Server 2012 AlwaysOn FCI”

  1. Vinod,

    Great post!! This has inspired me to write another version of my script on this very topic, especially for SQL Server 2005 onwards.

    When I wrote this script, it was to capture the similar kind of details (except disk resources) but this script was mainly meant for the SQL Server 2000 (yes, still, many servers in production were using SQL 2000) which were recently migrated to SQL 2008.

    Thank you!!! One more time, your post inspired me to write one more script that could help me and other DBAs.


Leave a Reply