How to Scan and Get Always On Enabled Servers in SQL Server Environment

Maintaining a SQL Server inventory is very important. Before performing a migration project, the inventory will help a database administrator to get more detail about that server. In this article, we will learn how to scan the entire SQL environment and identify servers on which Always on has been enabled.

The Best

Always On Availability GroupsAlways on Availability Groups is considered as the best high availability, disaster recovery feature in Microsoft SQL Server. It is the highly-advanced version of database mirroring.  Though mirroring has been used for increasing the SQL Server database’s availability, it works based on a single database and requires the databases to be in a FULL recovery model. If your application is governed my multiple databases, mirroring is not the best option.

If you are using database mirroring and have not tried always on, it is the right time you should switch to Always on Availability Groups.

Scan for Always On

SQL Server Always OnThis script would help you to scan your SQL environment and identify SQL Server instances that are Always On Enabled. In this script, we would be connecting to each linked server from a Central server. Please do note, this script will not create any linked server. Before executing this script, please make sure that you have created linked servers. As a first step, we would be loading name of all linked servers in a table.

How it works

This script uses serverproperty. The property IsHadrEnabled is of int data type and would return 0 if the always on availability group feature is disabled on the SQL Server. 1 would be returned by it if the always on availability group feature is enabled on the SQL Server. Using this script as a template, you can modify it and use it to collect information about replicas, databases, and replicas participating in the always on availability groups. You can also remotely identify if an availability group is in a healthy state.

DECLARE @Tserver TABLE 
     ( 
         cserver VARCHAR(200) 
     )

INSERT INTO @Tserver 
VALUES ('ECPLL027\NI2014')

DECLARE @Tcmd TABLE 
     ( 
         ccmd VARCHAR(2000) 
     ) 
DECLARE @Tlog TABLE 
     ( 
         p_server VARCHAR(200), 
         p_value sql_variant 
     )

INSERT INTO @Tcmd 
SELECT 'exec (''SELECT @@servername, SERVERPROPERTY (''''IsHadrEnabled''''); 
'') at [' 
            + cserver + ']' 
FROM @Tserver

DECLARE c1 CURSOR FOR 
     SELECT * 
     FROM @Tcmd 
DECLARE @cmd VARCHAR(2000)

OPEN c1

FETCH next FROM c1 INTO @cmd

WHILE @@FETCH_STATUS = 0 
     BEGIN 
         PRINT @cmd

         INSERT INTO @Tlog 
                     (p_server,p_value 
                     ) 
         EXEC(@cmd)

         FETCH next FROM c1 INTO @cmd 
     END

CLOSE c1

DEALLOCATE c1

SELECT * 
FROM @Tlog

Secondary backup

SQL Server’s always on allows the database administrators to configure the backup task on secondary replicas. For the backup to be useful, we must ensure that secondary replicas should always be in synchronized or synchronizing state. These backups will help you to recover from corrupt MDF files. Please note, these backups occurring on secondary replicas will not share any information to the MSDB database on the primary. So do not look for backup history on Primary replica if you have configured database backup on the Secondary replica.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook repair and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.