Still use SQL Server Database Mirroring - List all DBs and status for alerting

List all Mirrored Databases and status on a SQL Server Instance:

To list all databases in a SQL Server instance with a little information or even a lot of information for all databases configured for mirroring use the following queries:
Reminder 
The mirroring_state numbers represent the following:
0 = Suspended
1 = Disconnected from the other partner
2 = Synchronizing 
3 = Pending Failover
4 = Synchronized
5 = The partners are not synchronized. Failover is not possible now

Below is a comparison and methodology for stepping through example until you refine what you want to see. 
Enjoy!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

USE master
GO

SELECT d.name AS Database_Name, CASE
                                     WHEN dm.mirroring_state is NULL THEN 'Not Mirrored'
                                     ELSE 'Mirrored' END AS Mirroring_Status
  FROM sys.databases d
  JOIN sys.database_mirroring dm ON d.database_id = dm.database_id
 ORDER BY d.name;
GO

--Query for Database Name, Mirroring status and state for all databases on an instance:
--DISCONNECTED, SYNCHRONIZED, SYNCHRONIZING, PENDING_FAILOVER, SUSPENDED, UNSYNCHRONIZED.

SELECT d.name AS Database_Name, CASE
                                     WHEN dm.mirroring_state is NULL THEN 'Not Mirrored'
                                     ELSE 'Mirrored' END AS Mirroring_Status
      ,dm.mirroring_state_desc as Mirroring_State
  FROM sys.databases d
  JOIN sys.database_mirroring dm ON d.database_id = dm.database_id
 ORDER BY d.name;
GO

-- Get more refinded to remove databases not participating in mirroring
SELECT d.name AS Database_Name, dm.mirroring_state AS Mirroring_Status
      ,dm.mirroring_state_desc as Mirroring_State
  FROM sys.databases d
  JOIN sys.database_mirroring dm ON d.database_id = dm.database_id
  WHERE dm.mirroring_state_desc IS NOT null
GO

-- Returns the same thing as the temp table and cursor below…
SELECT TOP 1 dm.mirroring_state AS Mirroring_Status
  FROM sys.databases d
  JOIN sys.database_mirroring dm ON d.database_id = dm.database_id
  WHERE dm.mirroring_state_desc IS NOT NULL
  ORDER BY dm.mirroring_state asc
GO
-- Pick which is easier and better for your use case
-- This was created for use with RedGate SQL monitor database mirroring alert 
USE [msdb];
GO
-- Drop #Tmp_tblDatabaseInfo table if it exists
IF OBJECT_ID( 'tempdb..#Tmp_tblDatabaseName' ) IS NOT NULL
    DROP TABLE #Tmp_tblDatabaseName;

-- Create #Tmp_tblDatabaseInfo table for population
CREATE TABLE #Tmp_tblDatabaseName ([DBName] [nvarchar](254) NULL) ON [PRIMARY];

-- Populate data info from sys.master_files and sys.databases
INSERT INTO #Tmp_tblDatabaseName (DBName)
SELECT d.name AS Database_Name
FROM sys.databases d JOIN sys.database_mirroring dm
ON d.database_id=dm.database_id
WHERE dm.mirroring_state is NOT NULL
ORDER BY d.name
--SELECT * FROM #Tmp_tblDatabaseName

-- Declare variables
declare @databaseName nvarchar(254);
-- local means the cursor name is private to this code
-- fast_forward enables some speed optimizations
declare MirroringDBs cursor local fast_forward for
    select DBName FROM #Tmp_tblDatabaseName
         order BY DBName asc
        
DECLARE       @MonitorResults AS TABLE (database_name VARCHAR(255),role INT,mirror_state TINYINT,witness_status TINYINT,log_generat_rate INT,unsent_log INT,sent_rate INT,
unrestored_log INT,recovery_rate INT,transaction_delay INT,transaction_per_sec INT,average_delay INT,time_recorded DATETIME,time_behind DATETIME,local_time DATETIME);

open MirroringDBs;

while 1 = 1
    BEGIN
        fetch next from MirroringDBs
         into @databaseName;

        if @@fetch_status <> 0 begin
break;
end;
              INSERT INTO @MonitorResults EXEC sp_dbmmonitorresults @database_name = @databaseName, @mode = 0, @update_table = 0;
    END;
---- Return one result if other than 4 then this will mean Redgate should alert otherwise a 4 is all good
SELECT TOP 1 mirror_state FROM @MonitorResults ORDER BY mirror_state asc;
---- Uncomment the below line if you want to see more/all data points
--SELECT * FROM @MonitorResults ORDER BY mirror_state asc;
close MirroringDBs;
deallocate MirroringDBs;
GO

Comments

Post a Comment

Popular posts from this blog

How to mass update all or some sql server agent job step commands

How to mass update all or some sql server agent job step and commands using TSQL.   This will use the built-in Replace function to search text in SQL agent jobs  SQL Agent Job information is stored in MSDB and can reviewed using some of the following queries: SELECT * FROM msdb.dbo.SysJobs SELECT * FROM msdb.dbo.SysJobSteps -- OR SELECT a.name as job_name, b.step_name, b.subsystem, b.command, b.database_name, a.enabled       ,a.description, a.date_created, b.job_id, b.step_id   FROM [dbo].[sysjobsteps] as b   INNER JOIN sysjobs as a ON a.job_id = b.job_id Since I will be focusing on the Commands column I will look at using this query to extract data into a temp table so that I can test any updates with the replace step before doing an update to MSDB. This would be your opportunity to archive or save any commands as needed.  Some people may prefer to generate all the syntax necessary to use EXEC sp_update_jobstep so you a...
Read more

SQL Monitoring for Blocking and Locking Capture and Log to table with sp_WhoIsActive or sp_BlitzWho

Image
You can use  sp_WhoIsActive or sp_BlitzWho and log the capture data to a table.   In my example I will use sp_whoisactive as I am more familiar with its use and data collection, however I will look at creating a future post using BlitzWho.  I will provide a few examples that were used to determine that the paid SQL monitoring products can be fancy tools but can be useless if you don't understand the basic concepts or even an understanding of how we capture similar data or in some cases more finely tuned to a specific scenario or use case.  Sometimes these paid solutions are incorrect or things can simply go wrong (Services Fail to start or the data is aggregated and misses something like the query commands and SQL Fragments) and it's always good to plan ahead and be prepared.   Sp_WhoIsActive is a procedure written by Adam Machanic. It can be found here:  sp_whoisactive downloads Sp_BlitzWho is a procedure written by the Brent Ozar team....
Read more

SQL Server General Index Naming Convention

SQL Server General Index Naming Convention  easy to follow:  PK_ for primary keys PK_  Primary Keys, which are logical constructs and not necessarily physical constructs, get tagged with a PK_ prefix. In most systems that I design the PK_ isn’t a clustered index.  IX_ for non clustered non unique indexes IX_  standard single or even multi-column indexes used for seeks or intersection just get prefixed with an IX_ (for  I nde X ). In cases where I need a composite index (i.e., multiple columns) but still explicitly ‘tolerate’ key/bookmark lookups, I’ll still call my seek/intersection/composite indexes IXes. UK_ for unique keys UK_  for unique key index UX_ for unique indexes UX_  for unique index. CLIX_ for Clustered Index (non PK) CLIX_  (non-PK) Clustered Index name is prefixed with CLIX (for  CL ustered  I nde X ) Additionally, I like to code all Create Indexes in SQL 2012 and above with an if not ...
Read more