Mirroring Automatic Failover?..

Question

Post reply

Mirroring Automatic Failover?..

alexsunny123

SSC-Addicted

Points: 427
More actions
November 21, 2020 at 11:41 am

#3812302

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

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

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