Setting up and Monitoring of Database Mirroring as DR Strategy

Quick Step by Step Procedure involved:

• Perform a full backup of the database on the Principal server.
• Perform a Transaction Log backup on the Principal server.
• Copy the backup file to the Mirror.
• Do a restore of the full backup into a new step, but before doing the restore, go to Options, then ensure you check the No Recovery option
• Perform another restore of the Transaction Log, also with the No Recovery option.
• Go to the Database Properties on individual databases and choose the Mirroring tab.
• Click “Configure Security”
• Click Next on the wizard
• Choose not to have a Witness server since Asynchronous Database Mirroring is used.
• Click Next
• In the Principal Server Instance stage, leave everything as its default using a Particular Port for DB Mirroring.
• In the Mirror Server Instance stage, choose your Mirror server from the dropdown and click Connect to provide the credentials. Configure Port for DB Mirroring.
• Click Next.
• In the next dialog provide the Service Accounts used.
• Click Next and Finish.
• Setup of DB Mirroring monitoring
• Perform a manual failover to test it.
The Mirror is always in a permanent Restoring state to prevent users accessing the database, but will be receiving synchronization data from the Principal Database. In DR Scenario, the mirror database is brought online by Force Service with Command below:
USE
master
GO
ALTER DATABASE [dbName] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO


Steps involved in Monitoring of Database Mirroring
Three effective ways to monitor Database Mirroring:
1. Create a job which will monitor sys.database_mirroring Catalog view like query below and send daily email report every three hours:

SELECT DB_NAME(database_id) AS 'DatabaseName', database_id, mirroring_role, mirroring_role_desc, mirroring_state FROM sys.database_mirroring WHERE database_id = DB_ID('SomeDatabaseName')

Or Setup Email like

DECLARE @DBMirroringState VARCHAR(30),@DB_ID INT,
@ErrorMessageToSend VARCHAR(100)
DECLARE @MirroredDatabases TABLE (DatabaseID INT,
mirroring_state_desc VARCHAR(30))

-- get status for mirrored databases
INSERT INTO @MirroredDatabases(DatabaseID,mirroring_state_desc)
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')

WHILE EXISTS (SELECT TOP 1 DatabaseID FROM @MirroredDatabases WHERE mirroring_state_desc IS NOT NULL)
BEGIN
SELECT TOP 1
@DB_ID = DatabaseID,
@DBMirroringState = mirroring_state_desc
FROM @MirroredDatabases

SET @ErrorMessageToSend = 'DBMirroring Error on DB:'+CAST(DB_NAME(@DB_ID) AS VARCHAR)+
',DBState='+@DBMirroringState

-- Send Email
EXEC MSDB.dbo.Sp_Send_Dbmail
@Profile_Name = 'DBA',
@Recipients = 'Someone@gmail.com',
@body = @ErrorMessageToSend,@subject = @ErrorMessageToSend,
@Attach_Query_Result_As_File = 0

DELETE FROM @MirroredDatabases WHERE DatabaseID = @DB_ID
END

2. We can fetch SQL performance counters related to DB Mirroring from DMV called “sys.dm_os_performance_counters”

Will need to fetch following counters as email:

– Redo Queue KB : The number of bytes of transaction log that remains to be
applied to the mirror to roll it forwards.

– Log Send Queue KB: The number of bytes in the log that has not been sent to
the mirror

SELECT [counter_name] as CounterName, [instance_name] as InstanceName, [cntr_value] as CounterValue FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ('%Database Mirroring%')
AND [counter_name] IN ('Log Send Queue KB','Redo Queue KB')
AND 'TestDB'

Or Setup email like

declare @err int
set @err = (SELECT count(*) FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ('%Database Mirroring%')
AND [counter_name] IN ('Log Send Queue KB','Redo Queue KB')
AND Instance_Name='testmirror'
and cntr_value<>0)
if @err > 0
begin
EXEC MSDB.dbo.Sp_Send_Dbmail
@Profile_Name = 'DBA',
@Recipients = 'Someone@gmail.com',
@Query = 'set nocount on
declare @tab table(CounterName varchar(20), inst_name varchar(20),cntr_val int)

insert into @tab(CounterName, inst_name, cntr_val)
SELECT [counter_name] as CounterName, [instance_name] as InstanceName, [cntr_value] as CounterValue FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE (''%Database Mirroring%'')
AND [counter_name] IN (''Log Send Queue KB'',''Redo Queue KB'')
AND Instance_Name=''testmirror''

select * from @tab',
@Attach_Query_Result_As_File = 0
end

3. We can setup the thresholds in the “Database Mirroring Monitor” and setup alerts using:
• In SSMS, connect to the principal or mirror server instance for which you want to define an alert.
• Expand the SQL Server Agent folder in Object Explorer.
• Right-click Alerts and select New Alert.
• In the New Alert dialog box, type a name you can use to identify the alert, such as “DB Mirroring: Unsent log warning.”
• Select Error number and type the number from Table below.
• In Select a page, click Response to specify the actions to take when the event occurs, such as executing a job or notifying an operator.
• In Select a page, click Options to specify how often to send a notification on this event, the format of the message sent to operators, and other options.
Event IDs for Performance Warnings :

Warning	EventID
Warn if the age of the oldest unsent transaction exceeds the threshold	32040
Warn if the unsent log exceeds the threshold	32042
Warn if the unrestored log exceeds the threshold	32043
Warn if the mirror commit overhead exceeds the threshold	32044

The first two warnings in table above should be defined in the Principal Server,
the rest in the Mirror Server.
When we define such alerts, do not specify the database, choose “All Databases”,