SQL Mirroring best practices
Few years ago when I was working on web services, I was part of a team that built a tool to achieve SQL Mirroring in a reliable manner. Here are few best practices that I took away from that experience.
Mirroring is one of the best ways in which you can achieve fault tolerance in a distributed data store environment. There are various operations around mirroring that are critical and need to be dealt with cautiously. The following few sections discuss some of the validations that a user can perform around these mirroring operations.
a. Setting up mirroring
b. Changing failover options
c. Failing over
d. Breaking up mirroring
e. Changing mirroring port
If you want to look at the entire paper, it is attached to this blog.
Validations around Mirroring setup
Validations around changing "Failover" mode - Changing the failover mode from "Manual" to "Automatic"
“Manual” to “Automatic” failover mode change requires the setting up of a new SQL server as Witness. You can read in detail about setting up a Witness at: https://technet.microsoft.com/en-us/library/ms365603.aspx . You can change the failover mode from “manual” to “automatic” ONLY on the Primary SQL server
Validations around changing "Failover" mode - Changing the failover mode from “Automatic” to “Manual”
Changing the failover mode from “Automatic” to “Manual” simply involves removing the Witness server from the mirroring relationship. You can remove the Witness server either from the Primary or the Secondary. Here are a few validations that you can perform before you change the failover mode from “automatic” to “manual”
Validations around "Failover"
Validations around breaking up mirroring relationship - "normal break up"
This is the normal breakup scenario where you might want to breakup mirroring when the Primary and Secondary SQL servers are connected. Here is a list of validations that you can perform before performing a breakup.
Validations around breaking up mirroring relationship - "force break up"
There could be situations where the Secondary is in disconnected state. This might happen when the Primary goes down in a “manual” failover mode and “Primary” and “Witness” go down in an “automatic” failover mode. If your Primary and Witness are not in recoverable state, you might want to breakup mirroring and bring the existing Secondary server to process all connections in a standalone mode. Note that this might lead to a data loss [https://msdn.microsoft.com/en-us/library/ms189977.aspx].
Validations around modifying the mirroring port
Let us say that you want to change the port related to the mirroring endpoint on the Primary or Secondary SQL server which you have already created. Now you need to ensure the following:
a. For databases that are not mirrored on your Primary/Secondary, there is nothing much to worry about
b. For databases that are mirrored, once you change the mirroring endpoint port, the database goes into a disconnected mode since the TCP connection is broken. Now you need to make sure that you re-establish mirroring partnership between the Primary and Secondary for these databases so that they get back into connected state
