SQL Database Mirroring – Introduction to Operating Modes

In the last part of SQL database mirroring, you have learned about database mirroring and why it’s called SQL database mirroring. Now, let’s take a look at the operating modes of SQL database mirroring. SQL database mirroring can prove beneficial in terms of high availability and disaster recovery by setting it up to different operating modes. A DBA can choose among the three modes below, depending on their requirements –

High-Performance Mode –

The high-performance mode operates asynchronously when the transaction safety turned OFF. In this mode, only the principal server and mirror server come into the picture where data is written and committed on the principle server. Later, the data sent and committed to the mirror server. There’s no chance of automatic failure and also no user of the witness server –

 

 

 

 

Remember – You will get the high-performance mode only in the Enterprise edition of the SQL server.

Solutions if the principal database goes down –

1. Don’t panic or take up some steps. Instead, wait for the principle server to become available again. Though the SQL server is unavailable, mirroring will continue from the point where it stopped.
2. SQL server forced on the mirror database, which means the mirror database becomes the principle server.
3. You can update the server manually by taking the end of the log backup if the server allows you to do so, remove mirroring and then restore the end of the log on the prior mirrored database.
   
   Note: Asynchronous communication between databases leads to greater possibilities for data loss.

High Safety Mode –

This is a synchronous operation mode including a witness server with complete safety. There is synchronous data writing and committing on the principle and mirror databases. The activity of the database application continued only after committing to both databases.

 

 

 

 

Remember – This operating mode might lead to delay and slowness since transactions committed on both databases.

Solutions if the principal database goes down –

1. Be patient and wait for the principle server to become accessible again. During this period, the SQL server instance will be non-accessible but mirroring will continue from where it stopped.
2. SQL server forced on the mirror database, which means the mirror database becomes the principle server.
   
   Note: There is a possibility of data loss because of committed transactions on the original principle database which aren’t yet committed to the mirror database currently acting as the principle.

High safety with automatic failover –

Three servers are essential for this mode as there is a synchronous operation. Here data written and must commit synchronously to the principle as well as mirror databases. The application will continue running only after committing to both databases.

 

 

 

 

Remember – Since it is essential to commit transactions on both databases, it might lead to latency and slower operations.

Solution if the principal server goes down –

1. Allow the automatic failover process to complete. The mirrored database will become the principal database.

So, these are the three operating modes of SQL database mirroring.