Sql-server – Mirroring issue with mirror server

high-availabilitymirroringsql serversql server 2014tcpip

I am running two Windows Servers 2012 R2 with SQL Server 2014 Standard Edition. I have strange issue on mirroring.

I configured mirroring on for 15 databases, no Witness server. The Principal server show all database is Principal, Synchronized and the Mirror is Mirror, Synchronized/Restoring.

When I open Database Mirror Monitor I see Role Principal is in Mirroring State Synchronized but the Mirror's Role is not connected. I have fail over in both directions and the database came up on the mirror server normally. data is replicating for troubleshooting purpose I have done below steps.

  1. I have seen in error log in principle nothing is related to mirroring traced I can see most of the errors in principle are Error: 18456, Severity: 14, State: 38.

  2. I have seen errors in mirroring server for all the databases are Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://server.company.com:5022'. Error: 1474, Severity: 16, State:

  3. I have tried to resolve by stop and start the endpoint its was not resolved.

  4. I have tried pause and resume it is also not resolved.

  5. I have verified TCP/IP it is enabled on both the servers.

  6. I have tried to configure alias it is also not resolve.

  7. I have telnet from each other, it seems have no communication issues

  8. I have verified connect rights to domain level same service account for both principle and mirror.

  9. Earlier it is SQL server 2014 sp2 ,today I have updated with latest service sp3 even though still I can see same in mirror monitor Not connected to mirror.

Please help on this issue, how to resolve this?

Best Answer

SQL 2014 std does not support AlWays on Availability Group , You need Enterprise Edition, But SQL 2016 std edition supports basic availability group features

you can check the state of the mirror by using the query below.

this will give you a starting point for further investigations.

--==============================================================================
-- query that shows the current state of each database in the mirroring
--==============================================================================
  SELECT   db_name(sd.[database_id])           AS [Database Name]
          ,sd.mirroring_state                  AS [Mirror State]
          ,sd.mirroring_state_desc             AS [Mirror State] 
          ,sd.mirroring_partner_name           AS [Partner Name]
          ,sd.mirroring_role_desc              AS [Mirror Role]  
          ,sd.mirroring_safety_level_desc      AS [Safety Level]
          ,sd.mirroring_witness_name           AS [Witness]
          ,sd.mirroring_connection_timeout AS [Timeout(sec)]
    FROM sys.database_mirroring AS sd
    WHERE mirroring_guid IS NOT null
    ORDER BY [Database Name];

make sure the sql server service account has all the relevant permissions on both servers.

I am not sure of your environment, but depending on the query below, and what I could find in the logs, on a convenient time, plan a restarting of the sql server service (which will cause a failover) and back.

Another thing that has worked for me in the past, is scripting the whole thing, then dropping it, and then setting it up again through the script, one database a time.

that way you can try to narrow down the problem.

there is a very similar question: Database Mirroring error, but everything looks OK

the answer recommends looking at the permissions. Do that first.

another thing is to do a failover following the example below: (the original had many databases, I left only one DB per server, but you can get the idea)

--============================================================================
--instructions:
--for each server,
--1) get the list of the alter database set safety full
--2) paste that script below
--3) run it
--4) get the list of the alter database set partner failover
--5) run the first line make sure it goes through
--6) run the other lines - failover all the databases
--version 20140624 Anne and Marcello

--============================================================================




--============================================================================
-- before failing over
-- the safety mode should be changed to FULL (SYNCHRONOUS)
--============================================================================

select 'ALTER DATABASE '+quotename(db_name(database_id))+
' SET SAFETY FULL'
from sys.database_mirroring
where mirroring_role_desc='PRINCIPAL'
and (substring(db_name(database_id),1,2) IN ('US')
 or db_name(database_id) LIKE '%ROW')
order by db_name(database_id)


*
-- sqlweblon3
ALTER DATABASE [US14AUTPProduct] SET SAFETY FULL


*
--sqlweblon4
ALTER DATABASE [USAccount] SET SAFETY FULL



--============================================================================
-- generate the failover scripts
--============================================================================
select 'ALTER DATABASE '+quotename(db_name(database_id))+' SET PARTNER FAILOVER'
from sys.database_mirroring
where mirroring_role_desc='PRINCIPAL'
and (substring(db_name(database_id),1,2) IN ('US')
 or db_name(database_id) LIKE '%ROW')
order by db_name(database_id)

*
--sqlweblon3 (principal)
ALTER DATABASE [US14AUTPProduct] SET PARTNER FAILOVER


*
--sqlWeblon4 (principal)
ALTER DATABASE [ProductOfferROW] SET PARTNER FAILOVER
ALTER DATABASE [USAccount] SET PARTNER FAILOVER
ALTER DATABASE [USProductOffer] SET PARTNER FAILOVER
ALTER DATABASE [USStock] SET PARTNER FAILOVER



ALTER DATABASE [ProductOfferROW] SET PARTNER FAILOVER


/** IF YOU ARE FAILING FROM DR TO LIVE CHECK THE LITESPEED 
ACTIVITY SERVICE IS RUNNING ON THE SERVER. IF IT IS NOT LITESPEED 
SQL AGENT JOBS WILL FAIL **/



--============================================================================
-- after the failover change the safety mode back to ASYNCHRONOUS   
-- this is to be done on what is now the principal
--============================================================================

-- DR-sqlweblon3
ALTER DATABASE [US14AUTPProduct] SET SAFETY OFF
Related Question