For setting a SQL Server database mirroring we need two SQL Server instance. These two SQL instances can be on the same machine or on different machine. In this article, we will learn how to get the list of all mirrored databases from our SQL Server environment.
Scan for Always On
This script would help you to scan your SQL environment and identify SQL Server databases that are part of mirroring. In this script, we would be connecting to each linked server from a Central server. Please do note that 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.
From this table, using a cursor we would be fetching each server name and then prepare the script. This script would be stored in the second table. We would then use another cursor to fetch the command and execute it on each linked server. The result set is stored in the final table.
This script fetches data from two tables. The first one is sys.databases and the other one is sys.database_mirroring.
The table sys.database_mirroring would return 21 fields for each database on the server. If the database is not a part of mirroring or if it is offline, then the output in sys.database_mirroring for that database would be NULL for all fields except the database id.
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_dbname varchar(200) ) INSERT INTO @Tcmd SELECT 'exec (''SELECT @@servername, A.name FROM sys.databases A INNER JOIN sys.database_mirroring B ON A.database_id=B.database_id WHERE a.database_id > 4 and B.mirroring_state is not null ORDER BY A.NAME '') 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_dbname ) EXEC(@cmd) FETCH next FROM c1 INTO @cmd END CLOSE c1 DEALLOCATE c1 SELECT * FROM @Tlog
This script includes server name and database name. You can easily tweak it by adding more fields from sys_database_mirroring table. You can include mirroring_state. This field gives info about the state of mirrored database and the mirroring session. If there is 0 against this field, it indicates that the mirroring session is suspended. If there is 1 against the mirroring state it indicates that the mirroring partner is disconnected from the other; 2 indicates that the database is in synchronizing state; 3 indicates that the failover is pending; 4 indicates that the database is synchronized; 5 indicates that the mirroring partners are not synchronized and there is no possibility for the failover and 6 indicates that it is highly possible for partner’s failover. If this field shows NULL, the database is not part of mirroring. Mirroring will help you to recover quickly whenever there is SQL Server corruption
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