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:
- How many nodes are there in the system?
- Are these SQL Server instances Clustered currently?
- Which is the current active node the instance is currently running?
- Who are the active or possible owners available?
- Which are the shared disks we are accessing?
- 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
4. Which nodes instance is running
SELECT NodeName, Status, status_description, is_current_owner
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
SqlDumperDumpTimeOut, FailureConditionLevel, HealthCheckTimeout
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.Share this article
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.