locked
Mirroring not connected in mirror monitor but it seems mirroing working fine RRS feed

  • Question

  • Hi Team,

    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. Ihave 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 were 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?


    Wednesday, January 2, 2019 8:35 AM

All replies

  • Hi Nasar babu,

     

    According to your description, my understanding is that you could successfully fail over, but when you checked the Database Mirror Monitor, you found that the principal database was in sync but the mirror database was not connected. If anything is misunderstood, please tell me.

     

    Could you please use the below T_SQL command to query the status of the principal database and mirror database respectively. If it is convenient, please share us the results for the query. Please run the following statements on both principal server and mirror server.

     

    SELECT
    DB_NAME(database_id) AS Database_Name, mirroring_state_desc, FROM
    sys.database_mirroring WHERE mirroring_state IS NOT NULL;

     

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

     

    Besides, I will appreciate that you can tell us whether both your principal server and mirror server service accounts are domain accounts. If you use Windows Authentication to login and the server instances run in different accounts, please verify the service accounts for SQL Server. These service accounts all need to be domain accounts (in the same or trusted domains).

     

    Best Regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, January 3, 2019 6:47 AM
  • Hi Dedmon Dai,

    Thanks for your reply yes you are right the principal database was in sync but the mirror database was not connected and both principal server and mirror server service accounts are domain accounts (in the same or trusted domains) and below is the out put for given command.

    Principle output:

    Database_Name                mirroring_state_desc
    MMD_COMMON                           SYNCHRONIZED
    mmd.saas.deployment                 SYNCHRONIZED
    V8_MASTER                                 SYNCHRONIZED
    PatientPortal                                  SYNCHRONIZED
    Medispan                                         SYNCHRONIZED
    STGRT                                              SYNCHRONIZED
    openapi                                               SYNCHRONIZED
    CFIOITDB.MobileSmith                         SYNCHRONIZED
    SZZ03_122                                          SYNCHRONIZED
    CERT2015                                            SYNCHRONIZED
    SBZ03_129                                           SYNCHRONIZED
    SZZ03_114                                          SYNCHRONIZED
    test                                                      SYNCHRONIZED
    MMD_SR                                                SYNCHRONIZED

    Mirror output:

    Database_Name                                           mirroring_state_desc
    CERT2015                                                      SYNCHRONIZED
    Medispan                                                        SYNCHRONIZED
    mmd.saas.deployment                                     SYNCHRONIZED
    MMD_COMMON                                                SYNCHRONIZED
    PatientPortal                                                     SYNCHRONIZED
    STGRT                                                             SYNCHRONIZED
    V8_MASTER                                                           SYNCHRONIZED
    test                                                                       SYNCHRONIZED
    SZZ03_122                                                           SYNCHRONIZED
    SZZ03_114                                                           SYNCHRONIZED
    SBZ03_129                                                             SYNCHRONIZED
    openapi                                                                   SYNCHRONIZED
    CFIOITDB.MobileSmith                                              SYNCHRONIZED
    MMD_SR                                                                       SYNCHRONIZED

    Tuesday, January 8, 2019 12:02 PM
  • Hi Nasar babu,

    It can be seen from your output that the status of both the principal database and the mirror database is normal.Would you please tell me what version of SSMS are you using.Please try SSMS 17.9.1 to see the status of the principal and mirror databases.

    Here is the download link:

    https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

    Best Regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, January 9, 2019 1:49 AM