SQL Mirroring Best Practices – Things to keep in mind

The following article discusses some of the best practices that can be followed while dealing with mirroring SQL.

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.

  1. Setting up mirroring
  2. Changing failover options
  3. Failing over
  4. Breaking up mirroring
  5. Changing mirroring port

Validations around Mirroring Setup

Setting up a new mirroring relationship generally involves knowing the SQL server that would act as a Primary instance, the SQL server that would act as a Secondary instance and the database itself, which needs to be mirrored. Setting up a new mirroring relationship involves various steps, including ensuring that the db to be mirrored on the Primary is in FULL recovery mode, restoring the db on secondary in NO-RECOVERY mode etc. In addition to these validations, we can add a few more validations to ensure that mirroring setup happens as desired.

Before you setup mirroring here is a list of validations that you can perform:

  1. Ensure that the Primary and Secondary SQL servers are not the same
  2. Ensure that the Primary SQL server is reachable
  3. Ensure that the Secondary SQL server is reachable
  4. Ensure that a mirroring endpoint exists on the Primary and Secondary SQL server
  5. Ensure that the mirroring endpoint on Primary and Secondary has the expected name. For example, you can make sure that on all the Primary and Secondary SQL servers serving your needs, the mirroring endpoint has a common name. Ex: “Service_Mirroring”.
  6. Ensure that the mirroring endpoint on Primary and Secondary is listening on an expected port number. For example, you can make sure that on all the Primary and Secondary SQL servers serving your needs, the mirroring endpoint is bound to a common TCP port. Ex: 6023
  7. Ensure that the mirroring endpoint on Primary and Secondary is using the expected transport mode. For example, you can make sure that on all the Primary and Secondary SQL servers serving your needs, the mirroring endpoint is using TCP.
  8. Ensure that the mirroring endpoint on the SQL server serving as the “Primary” and “Secondary”, the role is “Partner”
  9. Ensure that the database to be mirrored is present on the Primary
  10. Ensure that the database to be mirrored is not already mirrored between the same Primary and Secondary SQL server pair: It is not in a “Principal” role on the Primary SQL server and not in a “Mirror” role on the Secondary SQL server
  11. Ensure that the database to be mirrored is not already mirrored between different Primary or Secondary SQL server pair: It is not in a “Principal” role on the Primary and “Mirror” role on a different Secondary or vice versa
  12. Ensure that there is enough disk space available on the Primary (for backup) and on the Secondary (for restore)

Only if all the above conditions succeed, proceed with mirroring setup.The following flowchart enumerates a few of such validations that might prove helpful.

Flowchart 1: Validations - Setup Mirroring

Validations around changing “Failover” mode

Once mirroring has been setup successfully, it is important to set the correct failover mode based on the business need. Changing the failover mode might involve any of the following two scenarios:

  • 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: You can change the failover mode from “manual” to “automatic” ONLY on the Primary SQL server.

Here are a few validations that you can perform before changing the failover mode:

  1. Ensure that the Primary SQL server is reachable
  2. Ensure that the Witness SQL server is reachable
  3. Ensure that the mirroring endpoint on the Witness has the expected name. For example, you can make sure that on all the SQL servers serving your needs, the mirroring endpoint has a common name. Ex: “Service_Mirroring”.
  4. Ensure that the mirroring endpoint on the Witness listening on an expected port number. For example, you can make sure that on all the Witness SQL servers serving your needs, the mirroring endpoint is bound to a common TCP port. Ex: 6024
  5. Ensure that the mirroring endpoint on the Witness is using the expected transport mode. For example, you can make sure that on all the Witness SQL servers serving your needs, the mirroring endpoint is using TCP.
  6. Ensure that the mirroring endpoint on the SQL server serving as the Witness, the role is “Witness”
  7. Ensure that the database exists on the Primary
  8. Ensure that the database is mirrored on the Primary
  9. Ensure that the Primary and Witness SQL servers are not the same
  10. Ensure that the Secondary and Witness SQL servers are not the same
  11. Ensure that the failover mode is not already “automatic” for the existing mirroring relationship

Flowchart 2: Validations - Change Failover options – “Manual” to “Automatic”

  • 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”

  1. Ensure that the Primary or Secondary SQL server is reachable
  2. Ensure that the database exists on the Primary or Secondary
  3. Ensure that the database is mirrored on the Primary/Secondary
  4. Ensure that the failover mode is not already “manual”

Flowchart 3: Validations - Change Failover option – “Automatic” to “Manual”

Validations around Failover

Failover is one of the most important operations in mirroring and you need to be extra cautious when you failover from one server to another. There can be situations where if you failover the wrong way around, you might end up in a situation where you can potentially lose data.To perform a failover, it is important to know the Primary and Secondary SQL servers. Here is a list of validations that you can use:

  1. Ensure that Primary and Secondary SQL servers are not the same
  2. Ensure that the Primary is reachable
  3. Ensure that the Secondary is reachable
  4. Ensure that the database to be failed over exists on Primary
  5. Ensure that the database to be failed over exists on Secondary
  6. Ensure that the database to be failed over is mirrored on the Primary
  7. Ensure that the database to be failed over is in a “Principal” role on the Primary
  8. Ensure that the database to be failed over is in a “Mirror” role on the Secondary
  9. Ensure that the partner for the database to be failed is Secondary on Primary
  10. Ensure that the partner for the database to be failed is Primary on Secondary
  11. Ensure that the failover mode is not “automatic”
  12. Ensure that the database is in “synchronized” state

Only if all the above conditions succeed, continue to failover the database.

Please refer to the below flow chart (Flowchart 4) for a few validations that you make before you perform a failover.

Flowchart 4: Validations - Failover from Primary to Secondary

Validations around breaking up mirroring relationship

There could be two situations where you would want to breakup mirroring:

  • Breakup mirroring: 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.
  1. Ensure that the Primary is reachable
  2. Ensure that the database for which we need to breakup mirroring exists on the Primary
  3. Ensure that the database for which we need to breakup mirroring is mirrored
  4. Ensure that the database for which we need to breakup mirroring is in a “Principal” role on the Primary

Proceed to breakup mirroring once all the above validations have succeeded.

  • Force breakup mirroring: 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 [ Here is a list of validations that you can perform:
  1. Ensure that the Secondary SQL server is reachable
  2. Ensure that the database for which we need to breakup mirroring exists on the Secondary
  3. Ensure that the database for which we need to breakup mirroring is mirrored
  4. Ensure that the database for which we need to breakup mirroring is in a “Mirror” role on the Secondary
  5. Ensure that the database on Secondary is in “disconnected” state

Proceed to force-breakup mirroring once all the above validations have succeeded. The following flow charts describe each one of these validations in detail.

Flowchart 5: Validations - Breakup Mirroring

Flowchart 6: Validations - Force Breakup Mirroring

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:

  • For databases that are not mirrored on your Primary/Secondary, there is nothing much to worry about
  • 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

Here is a list of validations that you can perform before you change the TCP port on a mirroring endpoint.

  1. Ensure that Primary and Secondary do not refer to the same SQL server
  2. Ensure that the Primary SQL server is reachable
  3. Ensure that the Secondary SQL server is reachable
  4. If a database is not mirrored, there is no validation required
  5. For a database that is mirrored on the Primary, ensure that the partner is the expected Secondary
  6. For a database that is mirrored on the Secondary, ensure that the partner is the expected Primary
  7. Ensure that the database is in “synchronized” state
  8. Ensure that the failover mode is not “automatic”

If all of the above validations succeed, proceed to modifying the mirroring TCP port

The following flow chart discusses in detail about few of the validations that you can perform before you go about changing the mirroring endpoint port.

Flowchart 7: Validations – Modifying Mirroring Endpoint Port

Conclusion

Having SQL mirroring setup for your database back end is very important and performing all the relevant mirroring operations on time is equally critical and important. Having validations around these operations ensures that you perform the operation when the system is in the desired state so that you don’t run into situations which can potentially lead to data loss. This article tries to educate you of a minimum set of validations that you can ensure, but please refer MSDN for more detailed information.

References

  • Overview of Database Mirroring:
  • Setting Up Database Mirroring:
  • Role Switching During a Database Mirroring Session:
  • Removing Database Mirroring:
  • Database Mirroring in SQL Server 2005:
  • White paper: Using database mirroring (Office SharePoint Server):

SQL Mirroring Best Practices – Things to keep in mind1