SQL Canada (SQLCAN)

Database Mirroring ensures availability of SQL Server database in scenarios of data disasters. It is implemented in SQL databases that use Full Recovery model. It maintains two copies of a particular database, which reside in different locations. When the primary server is unavailable due to any reason, the secondary server comes into action and proceeds with the functioning.

In this blog, we will illustrate some of the measures to troubleshoot database mirroring in SQL Server. Below is the list of some factors that need to be considered whilst troubleshooting:

Accounts

• It is to be noted that the accounts, which are facilitating the SQL server to run, should be correctly configured. In case the accounts run on the same domain accounts, there are less chances of misconfiguration.
• However, in case the accounts are running on different domains, the login of one of the accounts should be created on the other computer in master. In addition to this, the login should be granted CONNECT permission in the endpoint.
• In case SQL Server is running as SaaS and is using the local system account, certificates must be used for authentication purposes.

Endpoints

Following key points should be considered to make sure that the endpoints are configured correctly:

• Make sure that all the server instances- principal, mirror and witness server should possess database mirroring endpoint. Database Mirroring Endpoint can be created either by using Windows Authentication or by using certificates.
• Make sure that all the port numbers are correct. In order to identify the port number associated with database mirroring endpoint use the following catalog views:
  • sys.database_mirroring_endpoints
  • sys.tcp_endpoints
• Check whether the status of the endpoints is STARTED. For each server instance, make use of the following Transact-SQL command:

SELECT state_desc
  FROM sys.database_mirroring_endpoints

• For starting an endpoint use the following command:

ALTER ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = <port_number>)
FOR DATABASE_MIRRORING (ROLE = ALL);

• In order to check whether the ROLE is correct or not, use the following command:

SELECT role
  FROM sys.database_mirroring_endpoints;

System Address

For naming the server instance in the database mirroring, any name, which identifies the system can be used. If the systems reside in the same domain, the system name can be used as the server name. In addition to this, a static IP address or a fully qualified domain name can also be used as the server names.

Network Access

If the server instances are in different domains, it is necessary for every server instance to access the ports of all the server instances or the instances over the TCP. This puts a stop to the communication between the instances of the server.

Mirror Database Preparation

The most important thing that is to be made sure is that the database is prepared for mirroring, no matter if the mirroring is done again or for the first time. It is to be noted that whilst creating a mirror database on the mirror server, restoration of principal database backup with the same name of the database is to be done. Moreover, all the log backups created prior to backup creation should also be applied. Both the things should be done WITH NORECOVERY.

It is recommended to store the mirror database at the same location as of the principal database. However, if the file paths are different, MOVE option should be included with RESTORE statement.

In case the mirroring was stopped earlier and is being restarted, all the backups taken afterwards should be applied to the mirror database.

Fixing Failed Create-File Process

In order to add a file without having any impact on the mirroring session, the path of the file should exist on both the servers. Due this very fact, if the database files are moved while mirror database creation, the add-file operation might fail and the mirroring can be suspended.

In order to fix the issue, follow the below-mentioned steps:

1. The complete mirroring session should be removed and the full backup of the file group containing the added file must be restored.
2. The log containing the add-file operation should be then backed up. Moreover, the log backup should be restored manually by using WITH NORECOVERY and WITH MOVE. This will create a file path on the mirror server and the file will be restored to a new location.

Conclusion

Care should be taken while troubleshooting database mirroring in SQL server irrespective of the fact that the mirroring has been previously done or not. With the help of the above-mentioned measures, the users can easily fix the issues while creating database mirroring.