SQL Database Mirroring –Introduction to Operating Modes

In the last part of SQL database mirroring, you have learnt about database mirroring and why it’s called as 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 as below, depending on their requirements –

High-Performance Mode –

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

SQL_1

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

Solutions if the principle database goes down –

1. Don’t panic or take up some step. 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 can be forced on the mirror database, 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 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 a synchronous data writing and committing on the principle and mirror databases. The activity of database application can be continued only after committing on both databases.

SQL_2

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

Solutions if the principle 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 can be forced on the mirror database, 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 synchronous operation. Here data is written and must be committed synchronously on the principle as well as mirror databases. The application will continue running only after committing on both databases.

SQL_3

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

Solution if the principle server goes down –

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

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

This entry was posted in Web Hosting. Bookmark the permalink.