If you have database mirroring setup in SQL Server 2005 or 2008 environment, you should monitor the lag time between principal and mirror database. You can setup warning threshold using Database Mirroring Monitor tool.
SQL Server mirroring is resilient enough to recover after network glitches and it continues sending
transactions after network connection reestablishes. However if the network interruption is long then
SQL Server will put mirroring into Supended state if it can't tranfer transaction in several retries.
You can setup a scheduled job to reset all Suspended or Disconnected mirroring setups. This job can run at an interval of your choice.
Another good practice is to setup an alert to monitor for event 32040, which gets triggered when mirror lags behind principal by a give interval. You can also execute the endpoint reset job as a response to this alert.
In the following example, I setup an alert to fire if the delay between principal and mirror is greater
than 1800 seconds. It "executes Mirroring Endpoint Reset" job to reset all endpoints which are Suspended, Disconnected or not Started.
-- Script to create a job
USE [msdb]
GO
/****** Object: Job [Mirroring Endpoint Reset] Script Date: 02/27/2012 16:27:41 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 02/27/2012 16:27:41 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @'JOB', @'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Mirroring Endpoint Reset',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job checks if mirrored endpoint status is not started, then resets the endpoint.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Reset endpoint status] Script Date: 02/27/2012 16:27:41 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Reset endpoint status',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare
@mirror_status varchar(50),
@end_point varchar(50),
@mirror_state varchar(50),
@sql varchar(500)
select @mirror_status = state_desc, @end_point = name from sys.database_mirroring_endpoints
select @mirror_state = mirroring_state_desc from sys.database_mirroring
where mirroring_guid is not NULL and mirroring_state_desc = ''DISCONNECTED'' OR mirroring_state_desc = ''SUSPENDED''
if @mirror_status <> ''STARTED'' OR @mirror_state = ''DISCONNECTED'' OR @mirror_state = ''SUSPENDED''
set @sql = ''ALTER ENDPOINT '' + @end_point + '' STATE = STARTED''
exec(@sql)
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
-- Script to create an alert
USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Database Mirroring',
@message_id=32040,
@severity=0,
@enabled=1,
@delay_between_responses=1800,
@include_event_description_in=0,
@database_name=N'',
@notification_message=N'',
@event_description_keyword=N'',
@performance_condition=N'',
@wmi_namespace=N'',
@wmi_query=N'',
@job_name=N'Mirroring Endpoint Reset'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'Database Mirroring', @operator_name=N'dbalert', @notification_method = 1
GO
SQL Server mirroring is resilient enough to recover after network glitches and it continues sending
transactions after network connection reestablishes. However if the network interruption is long then
SQL Server will put mirroring into Supended state if it can't tranfer transaction in several retries.
You can setup a scheduled job to reset all Suspended or Disconnected mirroring setups. This job can run at an interval of your choice.
Another good practice is to setup an alert to monitor for event 32040, which gets triggered when mirror lags behind principal by a give interval. You can also execute the endpoint reset job as a response to this alert.
In the following example, I setup an alert to fire if the delay between principal and mirror is greater
than 1800 seconds. It "executes Mirroring Endpoint Reset" job to reset all endpoints which are Suspended, Disconnected or not Started.
-- Script to create a job
USE [msdb]
GO
/****** Object: Job [Mirroring Endpoint Reset] Script Date: 02/27/2012 16:27:41 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 02/27/2012 16:27:41 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @'JOB', @'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Mirroring Endpoint Reset',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job checks if mirrored endpoint status is not started, then resets the endpoint.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Reset endpoint status] Script Date: 02/27/2012 16:27:41 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Reset endpoint status',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare
@mirror_status varchar(50),
@end_point varchar(50),
@mirror_state varchar(50),
@sql varchar(500)
select @mirror_status = state_desc, @end_point = name from sys.database_mirroring_endpoints
select @mirror_state = mirroring_state_desc from sys.database_mirroring
where mirroring_guid is not NULL and mirroring_state_desc = ''DISCONNECTED'' OR mirroring_state_desc = ''SUSPENDED''
if @mirror_status <> ''STARTED'' OR @mirror_state = ''DISCONNECTED'' OR @mirror_state = ''SUSPENDED''
set @sql = ''ALTER ENDPOINT '' + @end_point + '' STATE = STARTED''
exec(@sql)
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
-- Script to create an alert
USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Database Mirroring',
@message_id=32040,
@severity=0,
@enabled=1,
@delay_between_responses=1800,
@include_event_description_in=0,
@database_name=N'',
@notification_message=N'',
@event_description_keyword=N'',
@performance_condition=N'',
@wmi_namespace=N'',
@wmi_query=N'',
@job_name=N'Mirroring Endpoint Reset'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'Database Mirroring', @operator_name=N'dbalert', @notification_method = 1
GO