Friday, July 11, 2014

What is your Database Mirror status?

If your databases are Mirrored, this one is for you.  As you know, SQL Server generates events if the status changes in your mirroring session.  But what if you want to get in and just check on things yourself?  How do we check our database mirroring state?  We have the Database Mirroring Monitor, which can be launched to return mirroring status details.  Personally, I am not a big fan of GUI tools.  They really are memory pigs!  

To avoid the GUI, I like to check my mirror status with the script below.  You can see I am using sys.database_mirroring, which contains details regarding the state of the mirrored databases.  You can run this by hand to check mirror status, or you could even wrap it into a job that would notify you if the mirrored state changes, or becomes suspect.  I will try to put that together, and post a sample job soon.

     /*
What is the state of your mirrored databases?    */

-- declarations
DECLARE  
@DatabaseID INT, 
@State VARCHAR(55), 
@IsMirrored INT,
@String VARCHAR(155) 

DECLARE @databases TABLE (
DatabaseID INT, MirroringState VARCHAR(55)
)

-- query status for mirrored databases 
INSERT @Databases (DatabaseID,MirroringState)
SELECT database_id, mirroring_state_desc 
FROM sys.database_mirroring 
WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR'
AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING'

-- walk through all mirrored databases 
WHILE EXISTS (
SELECT TOP 1 DatabaseID 
FROM @Databases 
WHERE MirroringState IS NOT NULL


BEGIN
SELECT TOP 1 @DatabaseID = DatabaseID, @State = MirroringState
FROM @Databases 

SET @String = 'Host: '+@@SERVERNAME+'.'+CAST(DB_NAME(@DatabaseID) AS VARCHAR)+ ' - DB Mirroring is '+@State +'.  Please review.'
EXEC msdb.dbo.sp_send_dbmail 'YOUR MAIL PROFILE', 'yourEmailAddress', @body = @string, @subject = @string 
DELETE FROM @Databases 
         WHERE DatabaseID = @DatabaseID
END

-- You can also check to see if there is no mirroring, when there should be
SELECT @IsMirrored = COUNT(*) 
FROM sys.database_mirroring 
WHERE mirroring_state IS NOT NULL 

IF @IsMirrored =
BEGIN
SET @string = 'Host: '+@@SERVERNAME+' - No databases are mirrored.  Please review.'
EXEC msdb.dbo.sp_send_dbmail 'YOUR MAIL PROFILE', 'YourEmailAddress', @body = 
     @string,  @subject = @string 
END



Take a look, let me know what you think.  You should also take a look at both of these for additional information regarding the catalog view and the Database Mirroring Monitor tool:

    Database Mirroring Monitor -
              http://msdn.microsoft.com/en-us/library/ms365786.aspx
    sys.database_mirroring catalog -
             http://technet.microsoft.com/en-us/library/ms178655(v=sql.110).aspx

No comments:

Post a Comment