locked
Database state after mirroring RRS feed

  • Question

  • Hi all,

    I have set up mirroring between two SQL 2012 servers.
    After the mirroring was established, the state of the databases on both servers is Mirror, Synchronized / Restoring...
    I expected this to be Mirror / Synchronised

    Does someone has an idea how this happened and how I can resolve this?

    Regards, Martin

    Thursday, June 23, 2016 1:08 PM

Answers

  • Hi Lin,

    On the server where the principal database is hosted I ran your statement, and found that the partner name is the same as the server name of the principal database.

    As you might have guessed I was connected to the partner database in SSMS on both servers.

    Connecting to the principal database showed me a perfectly healthy situation :-)

    Thanks for the SQL script, it helped me in finding what I did wrong.

    Cheers, Martin

    Friday, June 24, 2016 11:31 AM

All replies

  • That's an odd situation... What does the SQL Errorlog say?
    Thursday, June 23, 2016 4:47 PM
  • Hi Martin van der Kroef,

    Firstly, the mirror database should be in ‘Mirror, Synchronized / Restoring...’ after database mirroring has established, and the principal database should be in ‘Principal, Synchronized’. Please run the following statement on each instance to check the current mirroring role in database 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];

    Secondly, could you please refresh your SQL Server Management Studio by right clicking on your instance in object explorer and clicking on refresh as it could be some display error in SSMS. If the issue persists, it would be better if you post a screenshot of your object explorer so we can have a better understanding.

    If you have any other questions, please let me know.

    Regards,
    Lin

    Friday, June 24, 2016 9:56 AM
  • Hi Lin,

    On the server where the principal database is hosted I ran your statement, and found that the partner name is the same as the server name of the principal database.

    As you might have guessed I was connected to the partner database in SSMS on both servers.

    Connecting to the principal database showed me a perfectly healthy situation :-)

    Thanks for the SQL script, it helped me in finding what I did wrong.

    Cheers, Martin

    Friday, June 24, 2016 11:31 AM