Below is the first version of a query to gather information on a SQL Server instance (If its clustered) and listing if a database has Replication, Mirroring, Log Shipping, Always On.

 

/*

Query - HADR 
Author Stephen Bennett

List all HA DR occurances on the Instance
checks for 
	Clustered
	Log Shipping  (Primary / Secondary)
	Replication  Trans Publisher /  Trans Distrubtion / Trans Secondary / Merge Replication )
	Mirroring (Principal Performance Mode / Principal Safty Mode / Mirror database (perf/safty) )

*/

IF OBJECT_ID('tempdb..#hadr') IS NOT NULL
    DROP TABLE #hadr;

CREATE TABLE #hadr
    (
      [Database] VARCHAR(100) ,
      [Clustered] SQL_VARIANT DEFAULT '' ,
	  [TransactionalReplication] VARCHAR(15) DEFAULT '' ,
      [LogShipping] VARCHAR(15) DEFAULT '' ,
      [LogShippingDetails] VARCHAR(255) DEFAULT '' ,
	  [Mirroring] VARCHAR(33) DEFAULT '' ,
      [MirroringDetails] VARCHAR(255) DEFAULT '',
      [AlwaysOn] VARCHAR(1) DEFAULT '',
	  [AlwaysOnDetails] Varchar(255) DEFAULT ''

    );



INSERT  INTO #hadr
        ( [Database]
        )
        SELECT  name
        FROM    sys.databases
        WHERE   database_id > 4
        ORDER BY name;

-- clustered
UPDATE  #hadr
SET     [Clustered] = ( SELECT  SERVERPROPERTY('IsClustered')
                      )

-- Always On
-- Always On
DECLARE @v INT
SET @v = CONVERT(INT, LEFT(CONVERT(VARCHAR(MAX), SERVERPROPERTY('ProductVersion')),
                           CONVERT(INT, CHARINDEX('.',
                                                  CONVERT(VARCHAR(MAX), SERVERPROPERTY('ProductVersion'))))
                           - 1))
		

IF ( @v >= 11 )
    BEGIN
        UPDATE  #hadr
        SET     #hadr.[AlwaysOn] = '1' ,
                #hadr.[AlwaysOnDetails] = AG.name
        FROM    master.sys.availability_groups AS AG
                LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states
                AS agstates ON AG.group_id = agstates.group_id
                INNER JOIN master.sys.availability_replicas AS AR ON AG.group_id = AR.group_id
                INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id
                                                              AND arstates.is_local = 1
                INNER JOIN master.sys.dm_hadr_database_replica_cluster_states
                AS dbcs ON arstates.replica_id = dbcs.replica_id
                JOIN #hadr ON #hadr.[Database] = dbcs.database_name
    END



-- log shipping primary
UPDATE  #hadr
SET     #hadr.LogShipping = 'Primary' ,
        #hadr.[LogShippingDetails] = s.secondary_server
FROM    #hadr
        JOIN msdb..log_shipping_primary_databases AS d ON #hadr.[Database] = d.primary_database
        JOIN msdb..log_shipping_primary_secondaries AS s ON s.primary_id = d.primary_id


-- log shipping secondary
UPDATE  #hadr
SET     #hadr.LogShipping = 'Secondary' ,
        #hadr.[LogShippingDetails] = s.primary_server
FROM    #hadr
        JOIN msdb..log_shipping_secondary_databases AS d ON #hadr.[Database] = d.secondary_database
        JOIN msdb..log_shipping_secondary AS s ON s.secondary_id = d.secondary_id


-- Transaction Replication Publisher
UPDATE  #hadr
SET     [TransactionalReplication] = 'Publisher'
WHERE   [Database] IN ( SELECT  name
                        FROM    sys.databases
                        WHERE   is_published = 1 );

-- Transaction Replication distrubtor
UPDATE  #hadr
SET     [TransactionalReplication] = 'Distrubtor'
WHERE   [Database] IN ( SELECT  name
                        FROM    sys.databases
                        WHERE   is_distributor = 1 );

-- Transaction Replication Subscriber
UPDATE  #hadr
SET     [TransactionalReplication] = 'Subscriber'
WHERE   [Database] IN ( SELECT  name
                        FROM    sys.databases
                        WHERE   is_subscribed = 1 );



-- mirroring
CREATE TABLE #mirrorTemp
    (
      db VARCHAR(200) ,
      mode VARCHAR(100) ,
      princ VARCHAR(100) ,
      [state] AS mode + princ ,
      info VARCHAR(200)
    )
INSERT  INTO #mirrorTemp
        ( db ,
          mode ,
          princ ,
          info
        )
        SELECT  DB_NAME(database_id) ,
                CASE mirroring_safety_level
                  WHEN 1 THEN 'High Performance Model '
                  WHEN 2 THEN 'High Safety Mode '
                END ,
                CASE mirroring_role
                  WHEN 1 THEN 'Principle'
                  WHEN 2 THEN 'Mirrored'
                END ,
                mirroring_partner_instance
        FROM    sys.database_mirroring
        WHERE   mirroring_safety_level IS NOT NULL

UPDATE  #hadr
SET     #hadr.Mirroring = #mirrorTemp.state ,
        #hadr.[MirroringDetails] = #mirrorTemp.info
FROM    #hadr
        INNER JOIN #mirrorTemp ON #hadr.[Database] = #mirrorTemp.db

DROP TABLE #mirrorTemp

/*
DELETE  #hadr
WHERE   LogShipping = ''
        AND TransactionalReplication = ''
        AND Mirroring = ''
		AND [AlwaysOn] = ''
*/
-- View Results
SELECT  *
FROM    #hadr

-- Drop table	
DROP TABLE #hadr

 
 

Advertisements