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.
Always 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
This 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
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.
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