I recently gave two webcasts on database mirroring – Mirror Mirror on the Server, Who is the Principal of Us All? and Mirror Mirror on the Server, Who is the Mirror of Us All? – which generated a lot of great discussions!


There are a few topics that have come up over and over again, so here are my answers to your database mirroring frequently asked questions!

  • What versions of SQL Server is database mirroring available in? 
    • SQL Server 2005, 2008, 2008R2, and 2012
  • What editions of SQL Server is high safety (synchronous) available in?
Synchronous mirroring by SQL Server edition

Synchronous mirroring by SQL Server edition

  • What editions of SQL Server is high performance (asynchronous) available in?

Asynchronous mirroring by SQL Server edition

  • Do the principal and the mirror need to be on the same version?
    • You can have the principal be one version – say 2008R2 – and the mirror another – say 2012. This is how an upgrade with minimal downtime can be accomplished using mirroring! However, once you fail over to the mirror, you can no longer fail back.
  • Do the principal and the mirror need to be on the same edition?
    • To be fully supported by Microsoft, yes.
  • Can multiple databases on the same instance be in mirroring sessions?
  • How do I mirror the system database (master, model, msdb, tempdb)?
    • You can’t! This isn’t supported.
  • If I set up mirroring, do I still have to take full, differential, or transaction log backups?
    • Yes! Mirroring increases the availability of your databases. It is not a substitute for regular backups, however.
  • How does index maintenance (rebuilds) affect the mirror?
    • Transactions that fill up the log can affect the performance of mirroring. When the amount of information in the log increases, the amount of information that needs to be sent to and committed to the mirror increases also. If performance is crucial, you may want to do index maintenance more frequently, so it takes less time. If large transactions such as batch inserts are affecting performance, break those into smaller transactions.

What other questions do you have?

Jes Schultz Borland
↑ Back to top
  1. Hi Jes,

    Thanks for the demo you gave on mirroring. It was very helpful. I wanted to know how do we handle mirroring encrypted databases. We have a Sql server 2005 database that is encrypted. Our application connects to the database and retrieves the data by using decryption keys. How do we make sure that in the event of a failover the application can connect to the mirror database just the way it does to the principal database?

    • Thanks for watching! Database mirroring is compatible with encryption – it requires having the master key on both the principal server and mirror server. Here’s a good link from Microsoft to start:

      • Its not the master key specifically, although one does need to exist on each instance. Its the unique certificate that the encryption uses which must be applied to each server for the encryption to be fully operational

  2. Is mirroring is possible principal and Mirror DB have diffrent domain accounts ?

  3. Is there anything in particular with using AlwaysOn Availability groups, and Index maintenance–performance degradation between the groups, transactions latency, etc? I’m just wondering what ramifications the maintenance will have in 2012 using AlwaysOn vs. (asynchronous (in particular)) mirroring.

    • Seamus – sure, there’s a lot of moving parts there. The more work you do on the primary, the more you’ll need to push down to the secondaries. Factors affecting your performance include the network throughput, the drive latency, number of databases you’re replicating, etc.

    • ^^ To clarify further I guess, I would like to know if there are large differences between performance, etc. between 2012 Mirroring/AlwaysOn and (say) Asynchronous mirroring in 2008R2. I didn’t see anything jumping out at me while trying to research this, but I’m thinking I could have maybe overlooked something as well.

  4. Hi
    In mirroring High protection,High availability,High peformance modes which Editions are availadble

  5. I heard that SQL 2005 SP1 and later version only support DB Mirroring is that true?

  6. How can we configure mirroring between servers which are in different domains

  7. Hi
    mirroring database went to suspended .how to reslove it

  8. Hi, is it possible to have the witness on a 2008 or 2008R2 while the mirrored databases are on 2012? Thanks

    • Why worry about that, when you could spin up a quick (free!) SQL Server 2012 Express box for the witness?

  9. when configuring mirroring getting error message port5023 not found.
    How to resolve it?

  10. Hi
    In mirroring unsentlog,unrestore keep growing.How to reslove the issue.

  11. Hi
    In mirroring unsentlog,unrestorelog keep growing.How to reslove the issue.

  12. Can my application esp reporting connect to the mirrored database?. I am planning to implement the High-Performance mode of db mirroring.

  13. What about performance impact by using database mirroring? How does the number of DBs in the SQL server 2012 Standard affect performance? SQL Server 2008 R2 was “single thread synchronous only”.
    Is it possible to use more threads than one in SQL Server 2012 Standard?
    Or would you better use the SQL Server 2012 Enterprise because of performance reasons?
    In comparison with SQL Server 2012 availability groups I cannot see a clear advantage. Also the operational overhead is greater.

  14. Hi
    How many databases mirroring configuration is possible in single instance (Any recommendation on Microsoft)

    • Please read the link under the above bullet point, “Can multiple databases on the same instance be in mirroring sessions?”

  15. when mirroring fails can we recover the lost data

  16. Hi Experts i have some doubt related to mirroring.

    Please clear it and please clear this doubt with your expertise do not paste any MSDN link.
    1) Can we use SQL SERVER 2008 express edition if our Principal and mirror belong to SQL Server 2012 Enterprise edition if no pls let me know the answer why??

    2 )Why we can’t setup mirriro DB in read only mode in the same scenario log shipping work with both things.Please reply

  17. I have sql server 2008 R2 I have set the database mirroring. But i am unable to start the database mirroring I receive 1416 error. Mirroring Configured Without Domain – The server network address TCP://SQLServerName:5023 can not be reached or does not exist

    Do I need to stop the fire wall?
    everything TCP end point have been started?

  18. Can you set up mirroring to two secondary servers? (like 1:2)

    • No, mirroring is a 1:1 technology. If you need more than one secondary, you’d have to look into something like Log Shipping or AlwaysOn Availability Groups.

  19. Mirror database logfile is full in mirror server…How to resolve it….

    • Sounds like transactions aren’t being written fast enough. What does your storage on the mirror look like? What are your wait types on the mirror server?

  20. Hi
    I am going to set up Mirroring and Replication together.Since he DB is going to be in Full recovery model,inorder to managethe transaction log growth we will be taking frequent log backups on Principal.
    Network connectivity between primary and DR location is gone, what happens to the logs and transactions that are on principal?Until the network connectivity comes back, we have to manually failover to the mirror location.
    We are using Asynchoronous mode for mirroring.

    • If you are using async mirroring, and the link between your primary and mirror databases fails, the log on the primary will continue to grow until the connection is re-established.

  21. Pingback: What is SQL Server database mirroring? - SQL Shack - articles about database auditing, server performance, data recovery, and more

  22. As Standard editions have limited capabilities in rebuild/re-organizing indexes. Does mirroring be of help if the mirror can be paused and if maintenance is run on secondary server? What are the down sides of it?

    • Raj – no, only one server can be writable at any given time, so that’s not an option.

    • connection_timeout only occurs in high availability operating mode or it may occur in any other operating mode also ?
      i have a 3 node cluster setup which has a connection timeout of 60 seconds default value because of IS Alive checks having a default connection timeout of 10 seconds for a database which is mirrored on the same instance where db mirroring has been configured would it be causing problems because of cluster ?

  23. HI,

    I it possible to build the mirroring between sql server 2005 and sql 2012 or 2014. Both are in same editions. If possible please let me know how to do that.

  24. For High Availability option, is it possible to run multiple SQL2008 Std edition instances where the primary instance runs in your own shop and the secondary instance in the cloud?

  25. Pingback: Refreshing A SQL Mirrored Database Using Powershell - SQL Server - SQL Server - Toad World

  26. do we use encryption during database replication…?

  27. hi,

    i have two node cluster for high availability, DR sight configured DB mirroring with async( High Performance ) , now i want apply service packs , can you please me where i need to start and end?

  28. Good Article on Mirroring; Can we have different databases from different SQL instances be mirrored in one mirror server, as mirroring is at database level; sorry if that is a dumb query…

    say Database A from Server A; Database B from Server B … on to single mirror server C ; mirrored DB as A and B

  29. can we add new table or Alter table in principle DB and syn to mirror DB without stopping mirroring?

  30. We are currently using single VM to solve the purpose of witness for various SQL DB Mirroring servers. Do you know how many databases a SQL witness can server up to?

  31. Hi, I’m currently in the planning stage of moving a primary server (A) to a new server (C) with the secondary server staying put (B)

    Is it possible to switch from A–>B to C–> B without having to reconfigure mirroring. Server C is currently being updated by log shipping from server A or should server C get it’s logs from server B as per


  32. Awesome thread with lots of excellent information.

    Is this configuration possible:

    Datacentre1 Datacentre2
    ========= =========
    Server1 Server2
    DB1 (principal) DB1 (mirror)
    DB2 (mirror) DB2 (principal)


  33. Can I have multiple witnesses for a database mirroring set up. In other words, can we set up high avaiability for the Witness. what if the witness and the Primary go down at the same time?

  34. Dear Brent

    Quick question. I have a two node SQL cluster (nodes A and B) used by an application in production. I want to establish database mirroring between these two nodes for different application which is in testing phase. Can database mirroring work in this scenario without breaking/affecting the existing SQL cluster / windows server failover cluster?


    • Fab – generally, I try to do my testing in an environment that mirrors what production will look like. If production is a cluster + mirroring to a separate standalone server, then that’s what I want to test with.

      I don’t know if that setup would affect anything because I’ve never done mirroring between nodes of the same cluster. (That doesn’t seem very likely to get used in a production environment, right?)

  35. Hi Kendra,
    Hi Brent,

    hope you’re doing well. Regarding the discussion and the upgrade limitations in……
    I wonder whether it is possible (or even supported by ms) to build the mirroring between sql server 2008 (fci) and sql 2014 (fci) in an upgrade scenario. Both same Editions, of course. What do you think ?

    • Obix1 – just like any production upgrade scenario, your best bet is to try it out for yourself in a lab. I would totally answer if I had done that, but I’ve just never tried it. (It’s a pretty obscure scenario, jumping that many versions.)

    • Great question. The article says that SQL Server 2014 supports upgrades from SQL Server 2008 SP3 or later, so if you’re on that service pack I would expect a rolling upgrade to work using database mirroring.

      In your case the mirroring is almost a red herring. When you fail the database over to the mirror, the database will be upgraded, but you’re not really upgrading the cluster, if that makes sense.

      Of course, always best to create a small test database using the exact same setup and verify that everything works like expected first, but as long as you’ve got SP3, it looks supported for rolling upgrade to me.

  36. Hi Everyone,

    I have this problem which I’ve been trying to resolve for almost a week now. I’ve done my research and posted to quite a number of forums already. To make this reply short and I hope you don’t mind, here’s the URL of one of my online question.

    • Rommel – connectivity errors are really tough for remote folks to troubleshoot. I don’t think you’re going to be successful asking that one online. You need someone to work with you together on the server. I’d probably call Microsoft for support – it’s $500 and they’ll work the case with you until it’s done.

  37. Is it possible to rebuild index on mirror with SQL standard 2012 without downtime and data loss? Like I break mirror between two sql server but principal sql is attached to witness, i bring sql mirror down by removing it from mirror group then build index, after that i put mirror back to group and make it principal then bring Sql1 down and perform same action..
    Is it possible?

    • Afaque – I’ll answer that with a question: have you tried doing it? I’m guessing you have, since you’re breaking the mirror when you rebuild indexes – so why did you decide to start breaking the mirror whenever you do an index rebuild?

      It’s not a rhetorical question – I’m trying to get at the pain points you were having with index rebuilds that made you decide to start breaking the mirror first.

  38. if i have 2 instances installed on one server can both instances db1 and db2 participate in mirroring another server with 2 instances installed as well db3 and db4 ?

    • Deedee – yep! As long as you don’t try to mirror to the same server.

      • Ok thanks this would explain the the 1418 error i’m getting trying to mirror the second db2 instance to the db4 instance but i fiddled so much trying to get it to work now the first instance db1 which was successfully able to mirror to db3 isn’t working. and is giving the same 1418 error . What would you recommend uninstalling the second instance and attempting the mirroring again

        • ok so solved the problem without uninstalling here’s how

          1. i identified the endpoints using

          select * from sys.endpoints

          then is dropped all the endpoints on all 4 instances using
          IF EXISTS ( SELECT * FROM sys.tcp_endpoints WHERE name = ‘nameofendpoint’)
          DROP ENDPOINT nameofendpoint;

          where nameofendpoint is the name of the endpoint you are trying to drop

          3. Check to see if the endpoint has been dropped successfully

          select * from sys.endpoints

          Then reconfigured mirroring for the instance that was originally working

  39. Good Moring,
    Please i really need tour help :'(
    I have 3machine A.B.C (windw server 2012) sql server 2012. With different ip dress.
    i need to configure mirroring .i start mirroring in the first machine A after uploading the 2 files .back ans .trn . and Downing thé first step but when i need to choose thé miror instance, it didn’t appear .i have the error::http//SQLServerName:5022 can not be reached or does not exist
    I stop the firewall but the dame error :'(
    It ils my first time working with SQL server..please help me i an blocked since 4days :'(

  40. I dont fins my post 🙁

  41. Can you have a SQL Witness server in a mirror setup which has a higher SQL version than the mirror? So the mirror has SQL 2012 Standard and the Witness has SQL 2014 Express.

  42. I wanted to know if it would be an issue to mirror a database from a SQL 2008 R2 Enterprise server to a SQL 2008 R2 standard server?

  43. Hi Brent

    Is there any query that I can run in Witness server that identifies all Principals and Mirrors.

    One of my Clients use one witness server for multiple Principal Servers (Instances). I am trying to find a way to get the name(s) of all Principal server names and the corresponding Mirror(s) by running a query against Witness Server.

    It should be possible as Witness monitors all Principals based on some metadata. Where is it stored?



    I already tried all mirroring related views like sys.database_mirroring, sys.dm_db_mirroring_connections and sys.database_mirroring_witness etc but they all return Null.

    • Ram

      the table sys.database_mirroring_witnesses should show you the princical and the mirrors – but you’ll need to run it on the witness serer itself.

      If it’s coming back as NULL on the witness then I would double check that it’s acutally a witness server.

      (sorry – also posted this as a comment to the post – which can be deleted if needs be – can’t do it myself 🙂 )

  44. Ram

    the table sys.database_mirroring_witnesses should show you the princical and the mirrors – but you’ll need to run it on the witness serer itself.

    If it’s coming back as NULL on the witness then I would double check that it’s acutally a witness server.


  45. Hi Lee

    I checked and verified:

    Here is the details from Principal:

    database_id mirroring_role mirroring_partner_name mirroring_witness_name

    35 PRINCIPAL XXXUWDB008 xxxtwdb027

    select count(*) from sys.database_mirroring_witnesses
    select @@servername

    (No column name)

    (No column name)

    Hence, I am not sure where else to look to get these details.



    • Hi Ram

      And what happens if you look at the mirroring properties of that server from the GUI on the principal?

      Does ‘xxxTWDB027’ show up in the witness box? Could it be that the config has got in a mess somehow with the meta data being partially removed ?

      If you’re trying to find all the principals and mirrors from all your servers you might be better served running a query from either a CMS or multi server query from within registered servers – if you have all those details ?


      • Hi Lee

        Yes – it does appear in the witness address, when I launch the monitor from Principal.

        I thought it is not possible to mess up with Metadata anymore as SQL tightened up security.

        There has to be somewhere else that it keeps that info. I will keep digging around.



  46. Hi Lee and others

    I ran the witness server queries from witness server:

    select count(*) from sys.database_mirroring_witnesses
    select @@servername



  47. Regarding the below point.

    •You can have the principal be one version – say 2008R2 – and the mirror another – say 2012. This is how an upgrade with minimal downtime can be accomplished using mirroring! However, once you fail over to the mirror, you can no longer fail back.

    Can you give us more information on this sentence “However, once you fail over to the mirror, you can no longer fail back.”

  48. Can the Mirror have different indexes than the Principal? We are building BI on top of a mirrored AX-DB. I assume we can’t alter indexes on the actual AX-DB. But we do need columnstore indexes on the mirror to have decent performance in the ETL.

Leave a Reply

Your email address will not be published. Required fields are marked *