Question

kimdav111 on Fri, 24 Jul 2015 21:41:48


we are going to do some test between asynchronous mirroring versus synchronous mirroring.

with asynchronous mirroring we will not use a witness however for synchronous mirroring we will use a witness.

my understanding is that the iwitness is used for automatic failover.  why not use asynchronous with a witness server-- this will enable automatic failover.

Is there any other considerations when switching between mirroring types other than reconfiguring the mirror?

Just trying to avoid any issues.


dsk

Replies

SQLGru on Fri, 24 Jul 2015 22:19:46


MS actually does not recommend using a witness configuration in High Performance Mode ( asynchronous)

Link is below:

https://msdn.microsoft.com/en-us/library/ms175191.aspx?f=255&MSPPError=-2147217396

While using a witness in High Safety Mode ( synchronous) is totally fine be carefull on configuring it for servers with high CPU load, if your primary server has consistant CPU load over 50% or above it can potentially cause an unwanted failovers. which of course would a be a problem.

Also in High Safety you are actually waiting for a double commit as it has to harden the transactions on the partner instance before commiting them on the principal. If your prinical instances are highly transactional and you  dont have a good enough network connection that could potentially introduce an unwanted latency on your application. Also even with a good network you would still be taking a slight hit on total duration of any transaction since it still needs to commit it to the partner first. Hope that helps 

 

Lydia Zhang on Mon, 27 Jul 2015 03:21:33


Is there any other considerations when switching between mirroring types other than reconfiguring the mirror?

Just trying to avoid any issues.


dsk


Hi dsk,

In addition to other post, the database owner can change the transaction safety level at any time. However, please note that SQL Server Database Mirroring asynchronous mode is only supported in the Enterprise edition of SQL Server.

You can use SQL Server Management Studio and T-SQL to change SQL Server database mirroring operating mode as described in the following blog.

Change operating modes for SQL Server Database Mirroring
https://www.mssqltips.com/sqlservertip/3668/change-operating-modes-for-sql-server-database-mirroring/
                 

Thanks,
Lydia Zhang

kimdav111 on Mon, 27 Jul 2015 13:47:54


why is it recommended that asynchronous mirror be configured without a witness while synchronous mirror be configured with a witness?   And in establishing synchronous mirroring the mirror database has to be close in transaction logs to the principal so would this same apply to asynchronous mirroring.  Does the principal database and mirror database in asynchronous need to be close in transaction logs to establish mirroring.

dsk



SQLGru on Mon, 27 Jul 2015 14:57:30


It's actually explained in the link i posted above why MS does not recommend the witness config for asynch mirroring.

As far as establishing the mirroring session, it does not matter if you are doing a asynch or synch mirroring the process is the same, both prinicipal and partner database need to be close in transactions for mirroring session to succseed. 

Let me know if it helps

kimdav111 on Mon, 27 Jul 2015 15:04:02


I asked why asynchronous mirroring does not require a witness server? (this is the first sentence).

SQLGru on Mon, 27 Jul 2015 15:09:54


Witness is actually not required for either synchronous or asynchrouness. The only thing witness server provides is automatic failover feature for mirroring session. Mirroring without wintess configuration would need to be failed over manually either through GUI or T-SQL.

kimdav111 on Mon, 27 Jul 2015 15:12:15


I know about the manual failover. I am asking why witness can be configured for asynchronous failover.

is it because there is a lag in transaction logs which would not allow a automatic failover?

SQLGru on Mon, 27 Jul 2015 15:23:23


Exactly, you can still configure it, but it doesnt provide HA for you...

From MS Article:

If you use Transact-SQL to configure high-performance mode, whenever the SAFETY property is set to OFF, we strongly recommend that the WITNESS property also be set to OFF. A witness can coexist with high-performance mode, but the witness provides no benefit and introduces risk.

If the witness is disconnected from the session when either partner goes down, the database becomes unavailable. This is because, even though high-performance mode does not require a witness, if one is set, the session requires a quorum consisting of two or more server instances. If the session losses quorum, it cannot serve the database.

When a witness is set in a high-performance mode session, the enforcement of quorum means that:

  • If the mirror server is lost, the principal server must be connected to the witness. Otherwise, the principal server takes its database offline until either the witness or mirror server rejoins the session.

  • If the principal server is lost, forcing service to the mirror server requires that the mirror server be connected to the witness.

kimdav111 on Mon, 27 Jul 2015 15:32:38


So are you saying that synchronous because it uses a witness for automatic failover is more risky than asynchronous which does not require a witness?

SQLGru on Mon, 27 Jul 2015 15:38:34


No, that is not what i am saying, Witness in synchronous mode is safe and provides automatic failover for your mirroring session ( there could be issues with it if your servers are under CPU stress, but if its not the case its totally safe).

Witness in asynchronous mode is basically useless as it does not provide automatic failover and also introduces additional risk as discribed by the article above.