Troubleshooting Database Mirroring


The two areas where you get errors with mirroring are during setup and at runtime. We'll discuss each of these separately.

Troubleshooting Setup Errors

You may get the following error while setting up database mirroring:

 2006-05-27 17:53:16.630 spid15s      Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MyMirrorServer:4040'. 2006-05-27 17:55:13.710 spid15s Error: 1443, Severity: 16, State: 2. 

It may be possible that the firewall on mirror or principal server is blocking the connection on the port specified. Make sure that the firewall is not blocking communication on that port.

You may get the following error while setting up database mirroring on the principal:

 Msg 1412, Level 16, State 0, Line 1 The remote copy of database "TestMirroring" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. 

This indicates that the database on mirror is not rolled forward enough to establish the mirroring session. You may have backed up some transaction logs while backing up and restoring the database, so you have to restore these logs to the mirror server with the NORECOVERY option to sync the mirror with the principal. See the RestoreLogOnMirror.sql script for an example.

Make sure that the SQL Server Windows service accounts on each server are trusted and that the user account under which the SQL Server instance is running has the necessary connect permissions. If the servers are on nontrusted domains, make sure the certificates are correct. You can refer to the "Database Mirroring Endpoint" section for details regarding how to configure the certificates.

Make sure the endpoint status is started on all partners and the witness. You can use the Mirroring CatalogView.sql script, which we have provided in the code for this chapter. Look for the EndPointStatus column and make sure that the value is STARTED.

Make sure that you are not using a port used by other process. The port number must be unique per server, not per SQL Server instance. You can use any port number between 1024 and 32767. You can use the MirroringCatalogView.sql script to view the port information under the PortUsed column.

Make sure that the encryption settings for the endpoints are compatible on the principal, mirror, and witness. You can check the encryption setting using MirroringCatalogView.sql. Look for the script that selects from the catalog view sys.database_mirroring_endpoints, and check for the IS_ENCRYPTION_ENABLED column. This column has a value of either 0 or 1. A value of 0 means encryption is DISABLED for the endpoint, and a value of 1 means encryption is either REQUIRED or SUPPORTED.

Make sure that you have identified the correct fully qualified names of the partners and witness (if any) in the ALTER DATABASE command.

Troubleshooting Runtime Errors

If your database mirroring setup is done correctly and you get errors after that, the first thing you want to look at is the sys.database_mirroring catalog view. Check the status of the mirroring state. If it is SUSPENDED, check the SQL ErrorLog for more details. You may have added a data file or log file on the principal, and you do not have the exact same path on the mirror, which will cause a Redo error to occur on the mirror, and the database session will be suspended. You will see an error similar to the following in the mirror server error log:

 Error: 5123, Severity: 16, State: 1. CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'D:\t\TestMirroring_1.ndf'. 

If that is the case, create the folder on the mirror and then resume the mirroring session using the following command:

 ALTER DATABASE TestMirroring SET PARTNER RESUME 

If you have added the file and you do not have that drive on the mirror, you have to delete that file from the principal. You may have to empty the file before you delete it, if pages are allocated on the file. Then resume the mirroring session again.

If you cannot connect to the principal database even though your server is online, it is most likely because safety is set to FULL, and the principal server cannot form a quorum because both the witness and mirror are lost. This can happen, for example, if your system is in high-safety mode with witness, and the mirror has become disconnected from the old principal, followed by the witness. You force the mirror server to recover, using the following command on the mirror:

 ALTER DATABASE TestMirroring SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS 

After that, since again both the old principal and the witness are not available, the new principal cannot serve the database, so turn the SAFETY to OFF using the ALTER DATABASE <db_name> SET PARTNER SAFETY OFF command.

Check to make sure there is sufficient disk space on the mirror for both redo (free space on the data drives) and log hardening (free space on the log drive).



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net