Database Mirroring Mode - Synchronous or Asynchronous or Asyncfullpage

[Atticus]

Can anyone please explain the terms synchronous and asynchronous in the database mirroring solution and what are their roles in it? Just trying to gain knowledge about database mirroring...

[Cannon]

Hello and welcome back to bodHOST forum, Atticus!

The Synchronous, Asynchronous are the database mirroring modes – methods of transmission synchronization.

By default, the synchronous mode is enabled in a server. The main function of these models is to control the transactions that are recorded on the mirror server. The choosing of a synchronization method completely depends upon two things – recovery speed an state of the data. You need to decide which is more important when failover occurs, the recovery speed or the state of the data and depending on this you can choose the synchronization mode.

[Atticus]

Hey Cannon, thanks for the explanation. According to you, which transmission synchronization method or mode would be the best in terms of high availability?

[Alonso Z.]

Here is some more information on these synchronization modes in more detail.

Since the synchronous mode is enabled by default, it guarantees the committed transactions to be recorded accurately on the mirror (slave) server. When the Primary (master) Server fails or encounter any problem, the mirror server replaces the master server and serves the user and application requests without any loss of committed transactions. The synchronous mode ensures the transaction safety as the functioning servers are in a synchronized state.

If you speak about the asynchronous mode, it doesn’t guarantee the recording of the committed transaction on the mirror (slave) server. In this mode, when a failure occurs on the primary (master) server, you may lose some committed transactions when the mirror server replaces the primary server.

I think the best synchronization method would be the Asynchronous, as it is much faster than synchronous mode.

[Cannon]

Though the Asynchronous method seems to be faster than the synchronous method, but I personally believe that it is not much reliable as the failover from the master server to the slave server (mirror) is not automatic, and the slave server may not have all the transactions that were on the primary server.

Usually, most of the web hosting companies use a “witness server” to instruct the secondary server to replace the primary server when failover occurs. However, here at bodHOST a monitoring script is used through which all the execution requests are redirected to the Slave server and the slave server starts functioning as a Master server until the Master server comes online again.

Check out the bodHOST’s Server Mirroring page for more detailed information.