Test SQL Server Exception Handling in different modes.

Image Database is a high availability solution for SQLServer. The configuration is relatively simple, but there are multiple modes. If an exception occurs in the image database, the performance in different modes varies. Here, we practice the possible effects of server exceptions in different modes. This helps us select the appropriate mode and understand the database Mirroring

Image Database is a high availability solution for SQL Server. The configuration is relatively simple, but there are multiple modes. If an exception occurs in the image database, the performance in different modes varies. Here, we practice the possible effects of server exceptions in different modes. This helps us select the appropriate mode and understand the database Mirroring

Image is a high availability solution for SQL Server. The configuration is relatively simple, but there are multiple modes. If the image encounters an exception, the performance in different modes varies. Here, we practice the possible effects of exceptions in different modes. This helps us to select the appropriate mode and understand how to maintain the database image, note.

[Database image mode]

Database images can be used in the following modes:

· High Performance Mode (asynchronous mode)

The transaction of the database in Principle can be completed directly without the confirmation of Mirror. The Principle database has better performance. However, the transaction transfer between Mirror and Principle may be delayed.

· There is no witness server in High Safety mode (that is, synchronous Mode)

The transaction of the database in Principle must be confirmed by mirror immediately. In this case, the data of Mirror and Principle is synchronized. However, the performance may be affected because all transactions require the confirmation of mirror.

· The High Satefy mode (synchronous mode) has a witness server.

If there is witness, once the Principle database is abnormal and cannot be connected, automatic switching will be performed under the witness of the witness server. The image database becomes the primary database to continue providing services.

[Exception in High Performance Mode]

· If there is a problem with the MIRROR database, the PRINCIPLE database will be in the (Principle, Disconnected) state. In this case, the Principle database can still serve normally. When the Mirror database returns to normal, the data is automatically Synchronized. After the synchronization, the PRINCIPLE server returns to the normal state (Principle, Synchronized. Although this situation will not cause problems to the service, we should restore the Mirror database as soon as possible. Otherwise, logs will accumulate on the Principle end and become larger and larger. The disk space is full.

· The Principle database is faulty and cannot be connected. applications cannot be used at this time. The Mirror database is In the (Mirror, Disconnected/In Recovery) state. At this time, we have two options: 1. Restore the Principle database operation as soon as possible. 2. Use the Forcing Service method to change the Mirror database to the primary database to continue the Service. For the Forcing Service method, note the following:

1. Stop the old PRINCIPLE database completely to avoid data updates on both sides at the same time.

2. on the mirror server, we use the following command to change the mirror database to the primary database:

ALTER DATABASE Set partner FORCE_SERVICE_ALLOW_DATA_LOSS

3. Using the Forcing Service may cause data loss. If data in the original primary database has not been transferred to the image database, this part of data will be lost. Therefore, we need to balance the use of the Forcing Service.

4. After the original primary database is up, Mirror will be in the paused state. We can restore the image and convert the original primary database to a new backup database (of course, some data will be lost permanently ). Or, you can change the image settings of the original primary database to a common database, view the records written to the original primary database but not written to the original backup database, and manually import the records to the new primary database. In this way, we need to be very familiar with the application.

[The High Satefy mode does not have witness and an exception occurs]

· If the MIRROR database is faulty and cannot be connected, the PRINCIPLE database will be in the (Principle, Disconnected) state. In this case, the PRINCIPLE database can still serve normally. When the MIRROR server returns to normal, the data is automatically Synchronized. After the synchronization, the PRINCIPLE database is in the normal state (Principle, Synchronized. Although this situation will not cause problems to the service, we should restore the MIRROR database as soon as possible. Otherwise, logs will accumulate and become larger on the PRINCIPLE database. Eventually, the disk may be full.

· If the PRINCIPLE server has a problem, the application cannot be used at this time. The MIRROR database is In the (Mirror, Disconnected/In Recovery) state. At this time, we have two options: 1. Restore the PRINCIPLE database as soon as possible. 2. Use the Forcing Service method to change the Mirror database to Principle to continue the Service.