Should I use Asynchronous Mirroring?

by Yugz   Last Updated December 12, 2017 22:06 PM

I plan on setting up mirroring on a SQL 2012 database which is currently in production. My concern though is how much it will affect the performance of the principal database.

I am using mirroring for disaster recovery and nobody will require access to the mirrored database except in the event of a problem.

I am planning on doing asynchronous mirroring. Is this a good option?

Answers 2

Database mirroring is more of a high availability solution, not a disaster recovery one. What makes you think synchronous commit will lead to high latency in your environment? Are the principal and mirror server geographically separate?

Asynchronous commit will lead to potential data loss in the event that you need to do a forced service failover, therefore you would need to ensure that your SLA guidelines are congruent with that. BOL reference on Force Service in a Database Mirroring Session:

Forcing service may involve some data loss. Therefore, force service only if you are willing to risk losing some data in order to restore service to the database immediately.

And equally important, database mirroring is marked for deprecation:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead.

Is there a reason why you don't want to utilize AlwaysOn AG? See BOL for an overview of AlwaysOn Availability Groups.

AlwaysOn AG has all of the capabilities of database mirroring with many more advancements.

Thomas Stringer
Thomas Stringer
February 13, 2013 11:28 AM

You said in a comment:

Cant use AlwaysOn because we are using SQL Server Standard edition.... lokks like i'm going to have to use mirroring

This means you're not able to use asynchronous mirroring, as Standard Edition supports synchronous mirroring only:

SQL Server 2017 edition comparison chart

(since the original question was asked in 2013, this already was the same in SQL Server 2012)

And "Full safety only" (on the screenshot) means "synchronous mirroring only".

In the last link, under "Asynchronous Database Mirroring (High-Performance Mode)", there's a blue box which says:

Most editions of SQL Server 2017 support only synchronous database mirroring ("Safety Full Only").

Christian Specht
Christian Specht
December 12, 2017 21:40 PM

Related Questions