Mirroring Automatic Failover?..

  • Hello,

    We are receiving the below Error message in Mirroring and principal server is failovering automatically to Mirror Server.

    Database mirroring connection error 4 'The connection was closed by the remote end, or an error occurred while receiving data: '64(The specified network name is no longer available.)''

    Database mirroring connection error 4 '10054(An existing connection was forcibly closed by the remote host.)'.

    Database mirroring connection error 2 'Connection attempt failed with error: '10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)'.'

    I have verified telnet to mirroring ports which is working from both sides.

    Any thoughts?

    thanks

    jackyjoy

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • how do you monitor your mirrored databases?

    Do you log the mirroring failover messages ?

    Check the failed login at sqlserver side. What are the details SQLServer states ( errorlog / xevents )

     

    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

  • This is how I collect the mirroring events:

     

    /*

    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 YourDBAMonitoringDB;

    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 YourDBAMonitoringDB database if it's the case
    IF EXISTS( SELECT *
    FROM sys.databases
    WHERE [name]=N'YourDBAMonitoringDB'
    AND is_broker_enabled = 0 )
    Begin
    print 'SSB enabled';
    ALTER DATABASE YourDBAMonitoringDB 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>

    DBMirrong Events Occured.

    <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 = 'wachtdienst.dba@aperam.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 YourDBAMonitoringDB.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

    go

    USE YourDBAMonitoringDB;
    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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply