Script to check if the databases are configured for Mirroring

Couple of days back one of my team members wanted to get the database list which were configured for Database Mirroring on a given instance. Since I did not have a script readily available, I started putting together one for him.

The easiest approach is to query the sys.database_mirroring DMV. The output of this DMV has a column named mirroring_state, which has different values for the Mirroring State. This column will have a value of NULL if a database is not mirrored or is inaccessible. Based on this logic, I wrote the following script to check the mirroring status of all the databases on a given instance.

SELECT
A.name,
CASE
	WHEN B.mirroring_state is NULL THEN 'Mirroring not configured'
	ELSE 'Mirroring configured'
END as MirroringState
FROM
sys.databases A
INNER JOIN sys.database_mirroring B
ON A.database_id=B.database_id
WHERE a.database_id > 4
ORDER BY A.NAME

The output of this script is as follows.

Do you have a better approach to this requirement? Please feel free to share.

21 thoughts on “Script to check if the databases are configured for Mirroring

  1. venkat

    hi pradeep,

    i need ur help regarding how to configure the database mirroring through script. can you pls help me on this issue.

    Reply
  2. bharath

    Hi pradeep i am putting together a website dont mind i use your code to promote the knoweledge? seems very simple yet very useful for everyday DBA’s..tks btw

    Reply
    1. bharath

      just a observation for beginners, if you are getting the following error in the messages window after running the script

      Msg 4104, Level 16, State 1, Line 1
      The multi-part identifier “a.database_id” could not be bound.

      Change this line to this

      WHERE a.database_id > 4
      to this
      WHERE A.database_id > 4

      Reply
        1. Valid

          Instance specific :-

          select mirroring_role_desc from sys.database_mirroring where database_id > 4 and mirroring_state is NOT NULL

          Database specific :-

          select mirroring_role_desc from sys.database_mirroring where database_id =(select database_id from sys.databases where name=”)

          Reply
  3. venkat

    Hi Pradeep,

    i have one issue on SQL Server IO related error, every day i am getting this issue. below the issue.
    “SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [I:\MSSQL\Data\XXXX_log.ldf] in database [BIPLUS_DR] (21). The OS file handle is 0x0000000000000C1C. The offset of the latest long I/O is: 0x000000001b2000”

    as per micro soft suggestion i verified some Perfmon counter like Logical disk counter under %Ideal time%, Disk Sec/Read, Disk Sec/Write, some other counters also verified. everything is fine disk level. can you pls. help me. how to fix this issue and give me some suggestion on this issue.

    thanks

    Regards
    Venkat

    Reply
    1. PradeepAdiga Post author

      Hi Venkat,
      Usually 833 error involves issues with the disk subsystem. Please involve your strorage admins to check the drives for any issues.

      Reply
      1. venkat

        storage team sayings, it is everything fine and there are no issues. actually this server is window 2008 server. is there any thing from will check SQL prospectives. can you please help me, is there any SQL Server side instructions.

        Reply
  4. JASMA

    Hi Pradeep,

    My secondary database which is in restoring state…is returned as mirroring not configured.

    But it is configured for mirroring. Can you please help me to fix that? It should be returned as mirroring configured.

    Reply
  5. venkat

    Hi Pradeep,

    still i am full confusion to configured DB mirroring through script. which is your given suggestion. can you please help me to the details steps for this script.

    Reply
  6. Matthew Hallam Holloway

    Thank you for your post, while it did not achieve what I was searching for it pointed me to where I could start, saving me several hours of work!
    What I was trying to do?
    Audit all the databases in the environment (multiple servers over multiple instances) and include information about Mirroring if present.
    The following script can be run per instance or from a Central Management Server to quickly create a list of all your mirrored DB’s.
    As far as I can tell it only lists this data from the point of view of the Primary DB and only those that are on line.
    I hope this saves someone else as much time as your post saved me.

    /*Gets the DB names and mirroring details for a server (or servers if run from a central management server) */

    SELECT
    A.Name,
    CASE
    WHEN B.mirroring_state is NULL THEN ‘Mirroring not configured’
    else (Select mirroring_partner_instance)
    END as Partner_Instance,
    CASE
    WHEN B.mirroring_state is NULL THEN ‘Mirroring not configured’
    else (Select mirroring_partner_name)
    END as Partner_Name,
    CASE
    WHEN (B.mirroring_witness_name is NULL or b.mirroring_witness_name = ”) THEN ‘Witness not configured’
    else (Select mirroring_witness_name)
    END as Witness_Name
    FROM
    sys.databases A
    INNER JOIN sys.database_mirroring B
    ON A.database_id=B.database_id
    WHERE a.database_id > 4
    ORDER BY a.Name

    Reply
    1. Matthew Hallam Holloway

      Correction, I have found that on subsequent runs it includes the DB from both sides.

      To make it clearer I have amended to:

      /*Gets the DB names and mirroring details for a server (or servers if run from a central management server) */

      SELECT
      A.Name,
      CASE
      WHEN B.mirroring_state is NULL THEN ‘N/A’
      else (Select mirroring_partner_instance)
      END as Partner_Instance,
      CASE
      WHEN B.mirroring_state is NULL THEN ‘N/A’
      else (Select mirroring_partner_name)
      END as Partner_Name,
      CASE
      WHEN (B.mirroring_witness_name is NULL or b.mirroring_witness_name = ”) THEN ‘N/A’
      else (Select mirroring_witness_name)
      END as Witness_Name,
      CASE
      WHEN (B.mirroring_role is NULL or b.mirroring_role = 1) THEN ‘PRINCIPAL’
      else ‘MIRROR’
      END as ‘Role’
      FROM
      sys.databases A
      INNER JOIN sys.database_mirroring B
      ON A.database_id=B.database_id
      WHERE a.database_id > 4
      ORDER BY a.Name

      Reply
  7. Nalla Shyam Priya Bharat

    i am having 100 servers. how can I find out whether which server is having replication configured through using a script.Could anyone please help me regarding replication issue.

    Reply

Leave a Reply