SQL: Always On Availability Group - ServerProperty
Author: jason
Date: 2014-05-08 14:00:08
Category: Technical

Here is small script using SERVERPROPERTY to check the Always On Availability Group settings with tSQL

SERVERPROPERTY: http://technet.microsoft.com/en-us/library/ms174396.aspx
Monitor Availability Groups: http://technet.microsoft.com/en-us/library/ff878305.aspx





Code

--Does the SQL 2012 server have AOAG enabled?
SELECT SERVERPROPERTY('MachineName') ServerName
, LEFT(@@VERSION, CHARINDEX(CHAR(10), @@VERSION, 1)) SQLVersion
, SERVERPROPERTY('Edition') SQLEdition
, RIGHT(@@version, LEN(@@version)- 3 -charindex (' ON ', @@VERSION)) WindowsVersion
, (CASE WHEN SERVERPROPERTY('IsHadrEnabled') = 0 THEN 'The AlwaysOn Availability Group(s) is Off'
WHEN SERVERPROPERTY('IsHadrEnabled') = 1 THEN 'The AlwaysOn Availability Group(s) is On'
ELSE SERVERPROPERTY('IsHadrEnabled') END) IsHADREnabled
, (CASE WHEN SERVERPROPERTY('HadrManagerStatus') = 0 THEN 'Not started, pending communication'
WHEN SERVERPROPERTY('HadrManagerStatus') = 1 THEN 'Started and running'
WHEN SERVERPROPERTY('HadrManagerStatus') = 2 THEN ' Not started and failed'
ELSE SERVERPROPERTY('HadrManagerStatus') END) HADRManagerStatus
, (CASE WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'Clustered'
WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'Not Clustered'
ELSE SERVERPROPERTY('IsClustered') END) [Clustered]








jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009