April 20, 2011 at 2:32 am
Hi,
I want to know when the mirroring failover occured between two server. Is there any table where these informations get logged.
In SQL server Logs we can find the details of the failover. Since there are so many logs, it is difficult to check all the logs.
Kindly suggest.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 20, 2011 at 4:11 am
This is the script I use ( sql2005 for the moment )
/*
* DB Mirroring : followup
*
*/
/* show mirrored databases and their mirror-state */
Select db_name(database_id) as dbName
, *
from sys.database_mirroring
Where mirroring_guid is not null -- show only mirrored databases
order by dbName;
/* the "simple" proc */
exec msdb..sp_dbmmonitorresults 'DMKoudwals'
SELECT principal_server_name
, mirror_server_name
, database_name
, safety_level_desc
FROM sys.database_mirroring_witnesses
/*
* to be run at the WITNESS
*/
Select *
from sys.database_mirroring_witnesses
order by database_name, principal_server_name;
/*
* Make mirror the principal
*/
-- Alter database [TheDb] set partner failover;
/*
* Make mirror the principal with potential dataloss (if unsynced !)
*/
-- Alter database [TheDb] set partner FORCE_SERVICE_ALLOW_DATA_LOSS;
-- db will be in state (Principal, Suspended) !!
-- You need to reactivate mirroring !! using: ALTER DATABASE [TheDb] SET PARTNER RESUME;
/*
* Suspendig / resuming
*/
-- at the CURRENT Principal server
-- ALTER DATABASE [TheDb] SET PARTNER SUSPEND;
-- ALTER DATABASE [TheDb] SET PARTNER RESUME;
/*
* Ending DbMirroring
*/
-- at the CURRENT Principal server
-- ALTER DATABASE [TheDb] SET PARTNER OFF;
-- at the CURRENT Mirror server the db stays in "recovering" mode.
-- If you want to activate it, you can:
-- Restore database [TheDb] with recovery:
/*
* follow up queues
*/
-- SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + cast(serverproperty('instanceName') as sysname);
/* endpoint overview */
select e.*
, t.*
FROM sys.database_mirroring_endpoints e
INNER JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id
order by e.name;
/* DBM connections */
select *
from sys.dm_db_mirroring_connections
select *
from sys.event_notifications
select *
from sys.dm_os_wait_stats
where wait_type like '%MIRROR%'
order by wait_type
-- did someone configure tresholds
SELECT [database_id]
, db_name(database_id) as DbName
,[retention_period]
,[time_behind]
,[enable_time_behind]
,[send_queue]
,[enable_send_queue]
,[redo_queue]
,[enable_redo_queue]
,[average_delay]
,[enable_average_delay]
FROM [msdb].[dbo].[dbm_monitor_alerts]
order by [database_id]
SELECT [database_id], db_name(database_id) as DbName
,[role]
,[status]
,[witness_status]
,[log_flush_rate]
,[send_queue_size]
,[send_rate]
,[redo_queue_size]
,[redo_rate]
,[transaction_delay]
,[transactions_per_sec]
,[time]
,[end_of_log_lsn]
,[failover_lsn]
,[local_time]
FROM [msdb].[dbo].[dbm_monitor_data]
order by [database_id], [local_time] desc
/*
kan ook opgevraagd worden via http://msdn.microsoft.com/en-us/library/ms366320%28SQL.90%29.aspx
exec msdb.dbo.sp_dbmmonitorresults
'DMKoudwals'
, 9 -- rows_to_return
, 0 --update_status
database_name
Specifies the database for which to return mirroring status.
rows_to_return
Specifies the quantity of rows returned:
0 = Last row
1 = Rows last two hours
2 = Rows last four hours
3 = Rows last eight hours
4 = Rows last day
5 = Rows last two days
6 = Last 100 rows
7 = Last 500 rows
8 = Last 1,000 rows
9 = Last 1,000,000 rows
update_status
Specifies that before returning results the procedure:
0 = Does not update the status for the database. The results are computed using just the last two rows, the age of which depends on when the status table was refreshed.
1 = Updates the status for the database by calling sp_dbmmonitorupdate before computing the results. However, if the status table has been updated within the previous 15 seconds, or the user is not a member of the sysadmin fixed server role, sp_dbmmonitorresults runs without updating the status.
*/
select *
from sys.dm_os_performance_counters
Where object_name = 'SQLServer:Database Mirroring'
-- and instance_name = 'DMKoudwals'
order by case when instance_name like '[_]%' then 999 else 0 end , instance_name, counter_name ;
/*
select *
from sys.messages
where text like '%mirror%'
and language_id = 1033
order by message_id
*/
/* Monitor db state changes using WMI alerts */
--USE [msdb]
--GO
---EXEC msdb.dbo.sp_add_operator @name=N'DBA',
--@enabled=1,
--@pager_days=0,
--@email_address=N'mymail'
--GO
/*
Below is a list of the different state changes that can be monitored.
Additional information can be found here Database Mirroring State Change Event Class.
0 = Null Notification
1 = Synchronized Principal with Witness
2 = Synchronized Principal without Witness
3 = Synchronized Mirror with Witness
4 = Synchronized Mirror without Witness
5 = Connection with Principal Lost
6 = Connection with Mirror Lost
7 = Manual Failover
8 = Automatic Failover
9 = Mirroring Suspended
10 = No Quorum
11 = Synchronizing Mirror
12 = Principal Running Exposed
*/
---EXEC msdb.dbo.sp_add_alert @name=N'DB_Mirroring_Check_WMI',
--@enabled=1,
--@delay_between_responses=0,
--@include_event_description_in=1,
--@notification_message=N'DB_Mirroring_Check_WMI alert !',
--@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\SQL2005DE',
--@wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 OR State = 8 OR Stare = 10 '
--GO
---EXEC msdb.dbo.sp_add_notification @alert_name=N'DB_Mirroring_Check_WMI', @operator_name=N'DBA', @notification_method = 1
--GO
/* you may want to link this alert to a job to perform additional check / failover preparation / ... */
/*
If there are potential issues with network load or some other reason that may be causing
a delay in communicating with all three servers one solution is to change
the PARTNER TIMEOUT.
By default this value is set to 10 seconds, so if a "ping" is not received
in this 10 second period a failover may occur.
To make this change to a longer value, such as 20 seconds,
the following command would be issued on the Principal server for the database
that is mirrored.
ALTER DATABASE dbName SET PARTNER TIMEOUT 20
Be careful on the value that use for this option.
If this value is set to high and a failure really does occur, the automatic failover
will take longer based on the value you set.
In addition, the lowest this value can be set to is 5 seconds based
on information found in SQL Server Books Online.
*/
-- http://msdn.microsoft.com/en-us/library/ms403828%28SQL.90%29.aspx
-- Returns the current update period. (in minutes)
EXEC msdb.dbo.sp_dbmmonitorhelpmonitoring;
/* TOOLS for dbmirroring
-- FREQUENCY of monitoring http://msdn.microsoft.com/en-us/library/ms365375%28SQL.90%29.aspx
Alter monitoring frequency period the range of 1 to 120 that specifies a new update period in minutes.
The following example changes the update period to 5 minutes.
-- EXEC sp_dbmmonitorchangemonitoring 1, 5 ;
-- DROP monitoring http://msdn.microsoft.com/en-us/library/ms365810%28SQL.90%29.aspx
The following example drops database mirroring monitoring on all of the mirrored databases on the server instance.
-- EXEC sp_dbmmonitordropmonitoring ;
-- CREATE monitor job http://msdn.microsoft.com/en-us/library/ms403582%28SQL.90%29.aspx
Creates a database mirroring monitor job that periodically updates the mirroring status for every mirrored database on the server instance.
-- exec msdb.dbo.sp_dbmmonitoraddmonitoring [ update_period ] -- in minutes, default = 1
-- update monitor data http://msdn.microsoft.com/en-us/library/ms403827%28SQL.90%29.aspx
Updates the database mirroring monitor status table by inserting a new table row for each mirrored database,
and truncates rows older than the current retention period.
The default retention period is 7 days (168 hours).
When updating the table, sp_dbmmonitorupdate evaluates the performance metrics.
-- exec msdb.dbo.sp_dbmmonitorupdate [ database_name ]
-- Monitoring Mirroring Status http://msdn.microsoft.com/en-us/library/ms365781%28SQL.90%29.aspx
*/
/*
To cleanup monitor data manually you can use this ... (snipped from sp_dbmmonitorupdate)
declare @oldest_date datetime
declare @database_id int
Select @database_id = db_id('DMKoudwals')
, @oldest_date = dateadd(dd, -7, dateadd(dd, datediff(dd,0,getutcdate()),0))
Select db_name(@database_id) as DbName
, @oldest_date as oldest_date
select *
from msdb.dbo.dbm_monitor_data
where time < @oldest_date
and database_id = @database_id
--delete from msdb.dbo.dbm_monitor_data where time < @oldest_date and database_id = @database_id
*/
To see if and when mirroring failover occured I use:
/*
DBA_DBMirroring_Events: follow up state changes of mirrored databases
-- This implementation uses SQLServer Service Brocker with Event Notifications
*/
set QUOTED_IDENTIFIER on;
go
USE DDBA;
if object_id('dbo.T_DBA_DBMirroring_Events') is null
begin
print 'Table [T_DBA_DBMirroring_Events] Created';
CREATE TABLE [dbo].[T_DBA_DBMirroring_Events](
[tsStartTime] datetime NOT NULL,
[DatabaseID] int NOT NULL,
[DatabaseName] sysname NOT NULL,
[ServerName] sysname NOT NULL,
[Event_Text] [varchar](2000) NOT NULL,
[New_State] [int] NOT NULL,
[tsRegistration] datetime NOT NULL default getdate()
) ;
Create clustered index clX_DBA_DBMirroring_Events on [dbo].[T_DBA_DBMirroring_Events] ([tsRegistration]);
end
-- Enable Service Broker for DDBA database if it's the case
IF EXISTS( SELECT *
FROM sys.databases
WHERE [name]=N'DDBA'
AND is_broker_enabled = 0 )
Begin
print 'SSB enabled';
ALTER DATABASE DDBA SET ENABLE_BROKER;
END
-- Create a queue
CREATE QUEUE Q_DBM_Events_Queue;
-- Create a service
CREATE SERVICE S_DBM_Events_Service
ON QUEUE Q_DBM_Events_Queue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
-- Create a route
CREATE ROUTE R_DBM_Events_Route
WITH SERVICE_NAME = N'S_DBM_Events_Service'
, ADDRESS = N'LOCAL';
go
/* current user get ownership of EVENT NOTIFICATION, so switch to 'sa' */
EXEC AS LOGIN = 'sa';
go
-- Create the event notification at the server level for the AUDIT_LOGIN event
CREATE EVENT NOTIFICATION N_DBMirroring_Event_Notification
ON SERVER FOR DATABASE_MIRRORING_STATE_CHANGE
TO SERVICE 'S_DBM_Events_Service', 'current database';
go
/* Switch back to original user */
REVERT;
GO
-- Create the stored procedure that will handle the events
-- First set the options required to work with the XML data type
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE [dbo].[spc_DBA_DBMirroring_Events]
AS
BEGIN
SET NOCOUNT ON ;
-- Use an endless loop to receive messages (loop uitgeschakeld 20100212)
WHILE ( 1 = 1 )
BEGIN
DECLARE @messageBody VARBINARY(MAX) ;
DECLARE @messageTypeName NVARCHAR(256) ;
WAITFOR (
RECEIVE TOP ( 1 )
@messageTypeName = message_type_name,
@messageBody = message_body
FROM Q_DBM_Events_Queue
), TIMEOUT 500
-- If there is no message, exit
IF @@ROWCOUNT = 0
BEGIN
BREAK ;
END ;
-- If the message type is EventNotification do the actual work
IF ( @messageTypeName = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification' )
BEGIN
DECLARE @XML XML
, @tsStartTime datetime
, @DatabaseID int
, @DatabaseName sysname
, @ServerName sysname
, @TextData varchar(max)
, @NewState int
, @EventType varchar(128) ;
SELECT @XML = CONVERT(XML, @messageBody)
, @tsStartTime = null
, @DatabaseID = -1
, @DatabaseName = ''
, @ServerName = ''
, @TextData = ''
, @NewState = -1
, @EventType = '' ;
-- Get the payload
SELECT @tsStartTime = @XML.value('(/EVENT_INSTANCE/StartTime)[1]', 'NVARCHAR(128)')
, @DatabaseID = @XML.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'NVARCHAR(128)')
, @DatabaseName = @XML.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)')
, @ServerName = @XML.value('(/EVENT_INSTANCE/ServerName)[1]', 'NVARCHAR(128)')
, @TextData = @XML.value('(/EVENT_INSTANCE/TextData)[1]', 'NVARCHAR(2000)')
, @NewState = @XML.value('(/EVENT_INSTANCE/State)[1]', 'NVARCHAR(128)')
, @EventType = @XML.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(128)') ;
if @EventType = 'DATABASE_MIRRORING_STATE_CHANGE'
BEGIN
begin tran
INSERT INTO dbo.T_DBA_DBMirroring_Events
( tsStartTime, DatabaseID, DatabaseName, ServerName, Event_Text, New_State )
VALUES ( @tsStartTime, @DatabaseID, @DatabaseName, @ServerName, @TextData, @NewState )
commit tran
--sp_senddbmail
DECLARE @body NVARCHAR(MAX)
Declare @subject NVARCHAR(128)
--process emails
SELECT @body = '<html><H2>DBMirrong Events Occured.</H2><body><table style="font-family: arial; font-size:10pt; border = 0"><tr bgcolor="#AAAAAA"><th>tsStartTime</th><th>DatabaseID</th><th>DatabaseName</th><th>ServerName</th><th>Event_Text</th><th>New_State</th><th>New_State_Descryption</th><th>tsRegistration</th></tr>'
+ '<td>' + convert(varchar(23), @tsStartTime, 121)
+ '</td><td>' + convert(varchar(15), @DatabaseID)
+ '</td><td>' + @DatabaseName
+ '</td><td>' + @ServerName
+ '</td><td>' + @TextData
+ '</td><td>' + convert(varchar(15), @NewState)
+ '</td><td>'
-- BOL nov 2008 - New_State_Descryption
+ case @NewState
when 0 then 'Null Notification'
when 1 then 'Synchronized Principal with Witness'
when 2 then 'Synchronized Principal without Witness'
when 3 then 'Synchronized Mirror with Witness'
when 4 then 'Synchronized Mirror without Witness'
when 5 then 'Connection with Principal Lost'
when 6 then 'Connection with Mirror Lost'
when 7 then 'Manual Failover'
when 8 then 'Automatic Failover'
when 9 then 'Mirroring Suspended'
when 10 then 'No Quorum'
when 11 then 'Synchronizing Mirror'
when 12 then 'Principal Running Exposed'
when 13 then 'Synchronizing Principal'
else 'DBA - UNDOCUMENTED STATE'
end
+ '</td><td>' + convert(varchar(23), getdate(), 121)
+ '</td>'
+ '</table></body></html>'
Select @subject = 'DBAMessage - DBMirrong Events Occured at [' + @@servername +']'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dba@Myco.com'
, @body = @body
, @body_format = 'HTML'
, @subject = @subject
/*
Select *
-- BOL nov 2008
, case New_State when 0 then 'Null Notification'
when 1 then 'Synchronized Principal with Witness'
when 2 then 'Synchronized Principal without Witness'
when 3 then 'Synchronized Mirror with Witness'
when 4 then 'Synchronized Mirror without Witness'
when 5 then 'Connection with Principal Lost'
when 6 then 'Connection with Mirror Lost'
when 7 then 'Manual Failover'
when 8 then 'Automatic Failover'
when 9 then 'Mirroring Suspended'
when 10 then 'No Quorum'
when 11 then 'Synchronizing Mirror'
when 12 then 'Principal Running Exposed'
when 13 then 'Synchronizing Principal'
else 'DBA - UNDOCUMENTED STATE'
end as New_State_Descryption
from DDBA.dbo.T_DBA_DBMirroring_Events with (nolock)
order by tsStartTime, [tsRegistration] desc
*/
END ;
END ;
END ;
END ;
go
if object_id('dbo.spc_DBA_DBMirroring_Events') is not null
begin
-- Link the stored procedure to the Q_DBM_Events_Queue
ALTER QUEUE Q_DBM_Events_Queue
WITH STATUS=ON
, ACTIVATION ( STATUS=ON
, PROCEDURE_NAME = dbo.spc_DBA_DBMirroring_Events
, MAX_QUEUE_READERS = 4
, EXECUTE AS SELF) ;
end
ELSE
BEGIN
RAISERROR ('DBA Message: SSB Queue Q_DBM_Events_Queue NOT Activated !!! ', 1,1 ) WITH log
END
/*
The information posted to the S_DBM_Events_Service, about the DATABASE_MIRRORING_STATE_CHANGE event, has the following structure:
<EVENT_INSTANCE>
<EventType>DATABASE_MIRRORING_STATE_CHANGE</EventType>
<PostTime>2010-03-22T10:21:43.080</PostTime>
<SPID>22</SPID>
<TextData>DBM: Synchronized Principal with Witness -> DBM: Manual Failover</TextData>
<DatabaseID>8</DatabaseID>
<TransactionID />
<StartTime>2010-03-22T10:21:43.080</StartTime>
<IntegerData>1</IntegerData>
<ServerName>UABE0SU312</ServerName>
<State>7</State>
<DatabaseName>DMKoudwals</DatabaseName>
<LoginSid>AQ==</LoginSid>
<RequestID>0</RequestID>
<EventSequence>1286456</EventSequence>
<IsSystem>1</IsSystem>
<SessionLoginName />
</EVENT_INSTANCE>
This event type or event group can be specified when you create Event Notifications to monitor
and respond to activity in the database or server instance.
Event notifications can be created directly in the SQL Server Database Engine or by using the WMI Provider for Server Events.
Some events can also be used when you create DDL triggers.
For more information, see Designing DDL Triggers.
Parent: TRC_DATABASE
Properties
Inherited from Parents
Name CIM Type
LoginSid Uint8[]
PostTime DateTime
SQLInstance String
IsSystem Sint32
DatabaseID Sint32
DatabaseName String
ComputerName String
IntegerDataSint32
SessionLoginName String
SPID Sint32
StartTime DateTime
EventSequence Sint32
Event Properties
Name CIM Type
RequestID Sint32
State Sint32
TextData String
TransactionID Sint64
*/
/* Event Sequences ( SQL2005 SP3 )
-- manual failover by dba from SU311 to SU312
tsStartTime DatabaseIDDatabaseName ServerNameEvent_TextNew_StatetsRegistrationNew_State_Descryption
2010-03-22 10:54:34.4808DMKoudwals UABE0SU312DBM: Null Notification -> DBM: Synchronizing Principal132010-03-22 10:54:34.560Synchronizing Principal
2010-03-22 10:54:34.6438DMKoudwals UABE0SU312DBM: Synchronizing Principal -> DBM: Connection with Mirror Lost62010-03-22 10:54:34.647Connection with Mirror Lost
2010-03-22 10:54:34.6478DMKoudwals UABE0SU312DBM: Connection with Mirror Lost -> DBM: Principal Running Exposed122010-03-22 10:54:34.650Principal Running Exposed
2010-03-22 10:54:34.6508DMKoudwals UABE0SU312DBM: Principal Running Exposed -> DBM: Synchronizing Principal132010-03-22 10:54:34.650Synchronizing Principal
2010-03-22 10:54:54.6608DMKoudwals UABE0SU312DBM: Synchronizing Principal -> DBM: Connection with Mirror Lost62010-03-22 10:54:54.663Connection with Mirror Lost
2010-03-22 10:54:54.6608DMKoudwals UABE0SU312DBM: Connection with Mirror Lost -> DBM: Principal Running Exposed122010-03-22 10:54:54.663Principal Running Exposed
2010-03-22 10:54:55.1608DMKoudwals UABE0SU312DBM: Principal Running Exposed -> DBM: Synchronizing Principal132010-03-22 10:54:55.163Synchronizing Principal
2010-03-22 10:54:55.1878DMKoudwals UABE0SU312DBM: Synchronizing Principal -> DBM: Synchronized Principal with Witness12010-03-22 10:54:55.190Synchronized Principal with Witness
-- manual failover by dba from SU312 to SU311
tsStartTime DatabaseIDDatabaseName ServerNameEvent_TextNew_StatetsRegistrationNew_State_Descryption
2010-03-22 11:14:45.2708DMKoudwals UABE0SU312DBM: Synchronized Principal with Witness -> DBM: Manual Failover72010-03-22 11:16:22.373Manual Failover
2010-03-22 11:14:45.8308DMKoudwals UABE0SU312DBM: Null Notification -> DBM: Synchronizing Mirror112010-03-22 11:16:22.377Synchronizing Mirror
2010-03-22 11:14:45.9008DMKoudwals UABE0SU312DBM: Synchronizing Mirror -> DBM: Synchronized Mirror with Witness32010-03-22 11:16:22.377Synchronized Mirror with Witness
*/
USE DDBA;
GO
-- clean up only if needed
/* In case of Problems first drop the EVENT NOTIFICATION subscription */
--DROP EVENT NOTIFICATION N_DBMirroring_Event_Notification ON SERVER
--ALTER QUEUE Q_DBM_Events_Queue WITH STATUS=OFF
--DROP EVENT NOTIFICATION N_DBMirroring_Event_Notification ON SERVER
--DROP ROUTE R_DBM_Events_Route
--DROP SERVICE S_DBM_Events_Service
--DROP QUEUE Q_DBM_Events_Queue;
--DROP PROCEDURE dbo.spc_DBA_DBMirroring_Events
--
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 20, 2011 at 11:17 am
I have WMI based alerts configured to notify me.
SELECT @instanceName = CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceName'));
SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName;
SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 1';
SELECT @alertName = N'VWDBA DBM State: Synchronized Principal with Witness';
EXEC msdb.dbo.sp_add_alert
@name=@alertName,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=@namespace,
@wmi_query=@wquery;
EXECUTE msdb.dbo.sp_add_notification
@alert_name=@alertName, @operator_name = N'[YOUR OP HERE]', @notification_method = 1
I have these setup for WHERE State = 1 thru 13 with a corresponding alert name
More info here http://technet.microsoft.com/en-us/library/cc966392.aspx
April 20, 2011 at 11:20 am
Similar thread here
http://www.sqlservercentral.com/Forums/Topic1059299-391-1.aspx#bm1087746
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply