Blog

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!

mirroring

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?
async

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?

↑ 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?

  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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php