Maintaining high availability and data integrity in a SQL Server environment is one of the main priorities as a SQL DBA. One issue we may encounter is Mirrored databases being left in a Disconnected or In Recovery state. In this article, we’ll share the different methods on how to resolve issues with database Mirroring in SQL Server, guide you through the troubleshooting process.
MSSQL Database Mirroring Issues
Imagine a scenario where a secondary instance of SQL Server is abruptly terminated due to high CPU usage, and upon restart, all Mirrored Databases are found to be in a Disconnected or In Recovery state. You may find error messages in the SQL Server log that further compounds the issue.
This issue can also happen on a Mirrored SQL Server at random for other reasons such as an unstable period on the network, or an interruption to the SQL Service on the Secondary. When we have issues with Mirroring you might see latency between the Principal Mirror and Secondary continuing to grow. Below in this post we’ll share methods of how to resolve Database Mirroring issues, first by restarting the SQL Server Database Mirroring Endpoints, then proceeding to final measures for resolving.
Resolving Mirroring Issues in SQL Server
Method 1: Pause/Resume Database Mirroring Endpoints
The SQL commands below might be particularly useful if there’s only a few disconnected databases, not all. These commands pause & resume the Mirroring session for a database:
-- Suspend Mirorring on Database ALTER DATABASE <database-name> SET PARTNER SUSPEND; -- Resume Mirroring on Database ALTER DATABASE <Database Name> SET PARTNER RESUME
Check out Microsoft Docs on Pausing & Resuming a Database Mirroring Session for more info on this part.
Method 2: Restart Database Mirroring Endpoints
Recycling the Database Mirroring Endpoints in SQL Server would likely be the next thing would I try run to attempt to resolve issues with Database Mirroring.
The first command below retrieves information on the SQL Server Mirroring Endpoint, which is required for a Mirroring setup. With this we can check the state of the endpoint, if the Mirroring_State_Desc column below is showing as STARTED it’s at least displaying as healthy.
-- Get SQL Endpoint Info
-- Includes mirroring_endpoint state & port
SELECT
e.name AS Endpoint_Name,
e.state_desc AS Endpoint_State,
e.type AS Endpoint_Type,
e.type_desc AS Endpoint_Type_Description,
d.state AS Mirroring_State,
d.state_desc AS Mirroring_State_Desc,
tcp.port AS Port_Number,
d.is_encryption_enabled AS Encryption_Enabled,
d.encryption_algorithm_desc AS Encryption_Algorithm_Desc,
d.certificate_id AS Certificate_Id
FROM sys.endpoints e
JOIN sys.database_mirroring_endpoints d ON e.endpoint_id = d.endpoint_id
JOIN sys.tcp_endpoints tcp ON e.endpoint_id = tcp.endpoint_id;
-- Stop the Database Mirroring Endpoint
ALTER ENDPOINT <Endpoint Name> STATE=STOPPED
-- Start the Database Mirroring Endpoint
ALTER ENDPOINT <Endpoint Name> STATE=STARTED
If communication between the endpoints doesn’t restart, we could try test connectivity to and from the Principal Mirror server and Secondary server. We can test network connectivity quickly using PowerShell, using the Mirroring Port Number returned in the query above, as well as testing SQL Server (1433 default port).
If Mirroring is still not working after Stopping/Restarting the Mirroring Endpoint, lets continue on to the next steps in this post.
Method 3: Restart SQL Services on Secondary
We can try restart the SQL Services on the Secondary Mirror server, which will not affect the Live Service. Alternatively, you can also try reboot the Secondary Mirror host.
Method 4: Reconfigure Mirroring
If you are still with me, here till the end, we’ll need to reconfiguring Database Mirroring for each of the problem databases. Re-add each database to Mirroring, which is commonly done with a Full Backup / Transaction Log Database Restore during setup. I wish you luck and hope your databases are very big! Sometimes this might take a day to resolve.
Still Having Issues?
1. If you’re unable to re-configure Mirroring and get errors while Adding a Database to Mirroring, that’s probably narrowing down the issue. Check networking is good and review error messages thoroughly.
2. If this is a frequent issue within your environment and you’re having to resolve Mirroring issues, you should really take action and start a documented investigation. Submit a case with Microsoft, ensure you’re on the latest patch level for OS & SQL Server, and review all log files.
Leave a Reply