SQL Database Mirror: Implementation, Monitoring, and Troubleshooting

Database mirroring in SQL Server is a valuable tool for enhancing database availability and disaster recovery. By creating a synchronized copy of a database on a separate server instance, database mirroring ensures continuous operation even if the primary server fails 1. This article provides a comprehensive guide to implementing, monitoring, and troubleshooting SQL database mirroring.

Implementing Database Mirroring

Database mirroring involves creating a live, synchronized copy of a database on a separate SQL Server instance. This technology offers several benefits, including increased database availability, improved data protection, and enhanced availability during upgrades. In the event of a primary server failure, the mirror server can quickly take over, minimizing downtime 2.

Before implementing database mirroring, ensure the following prerequisites are met:

• All participating servers (principal, mirror, and witness, if any) must be running the same version of SQL Server 3.

• Both the principal server and mirror server must be running the same edition of SQL Server 3.

• Both servers are connected and can communicate with each other 1.

• The database to be mirrored uses the full recovery model 1.

Steps to Implement Database Mirroring

1. Prepare the Principal Database: Set the recovery model of the database to Full. This ensures that all database changes are logged, which is necessary for the mirroring process 1.

2. Backup the Principal Database: Create a full database backup and transaction log backup of the principal database. This backup serves as the base for the mirror database and allows for restoring the database to a consistent state 1.

3. Configure the Mirror Server: Ensure that the mirror server instance is ready to host the mirrored copy of the database. This includes ensuring sufficient disk space and network connectivity 1.

4. Restore the Principal Database Backup: Copy the full database backup and transaction log backup files to the mirror server and restore them using the WITH NORECOVERY option. This option keeps the mirror database in a restoring state, ready to receive and apply transaction log records from the principal database 1.

5. Set Up Database Mirroring: On the principal server, enable database mirroring and specify the mirror server. This establishes the mirroring session between the two server instances 1.

6. Start Database Mirroring: Start the mirroring session to begin synchronizing the databases. This initiates the process of transferring transaction log records from the principal to the mirror server 1.

Configuring Inbound and Outbound Connections

To ensure secure and reliable communication between the principal and mirror servers, you need to configure inbound and outbound connections. This involves creating certificates and endpoints on both servers and granting appropriate permissions 4.

Configure Outbound Connection for Principal Database

1. Create a certificate for the principal server database.

2. Create a mirroring endpoint.

3. Back up the principal server certificate and copy it to the mirror server.

Configure Outbound Connection for Mirror Database

1. Create a certificate on the mirror server.

2. Create a mirroring endpoint.

3. Back up the mirror server certificate and copy it to the principal server.

Configure Inbound Connections on Mirror Server

1. Set up a login on the mirror server for the principal server.

2. Create a user account for the principal server login.

3. Configure the certificate with the created user.

4. Grant connect permission to the created endpoint.

5. Ensure that the firewall allows inbound connections on the necessary ports.

Database SSL Configuration

For enhanced security, you can configure SSL encryption for database mirroring. This involves granting the SQL Server service account access to the certificate used for SSL encryption 5.

Operating Modes

SQL Server database mirroring offers different operating modes to suit various needs 4:

• High Safety: This mode ensures data integrity by synchronizing every transaction between the principal and mirror databases. It offers the highest level of data protection but may impact performance due to the synchronous operation. In this mode, the mirror database is in a restoring state and is not accessible to applications 4.

• High Safety with Automatic Failover: This mode requires a third server instance, called a witness, to monitor the principal and mirror servers. The witness enables automatic failover if the principal server fails. This mode combines high safety with increased availability.

• High Performance: This mode prioritizes performance by using asynchronous operation. Transactions are committed on the principal server without waiting for the mirror server to write the log to disk. This mode is suitable for applications where speed is critical, but it may involve some data loss in case of a failover. In this mode, the mirror database can be accessed with some potential data loss 4.

It's important to choose the operating mode that best suits your needs, balancing data protection with performance requirements.

Read-Only Access to Mirror Database

Database mirroring allows you to create a snapshot on the mirror database and use it for read-only purposes. This can be a valuable feature for reporting requirements or other read-only operations, as it offloads read traffic from the principal database 6.

Restrictions

While database mirroring offers significant benefits, it's essential to be aware of its limitations:

• Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb, or model databases 3.

• A mirrored database cannot be renamed during a database mirroring session 3.

• Database mirroring does not support FILESTREAM. A FILESTREAM filegroup cannot be created on the principal server, and mirroring cannot be configured for a database that contains FILESTREAM filegroups 3.

• Database mirroring is not supported with either cross-database transactions or distributed transactions 3.

Monitoring Database Mirroring

Monitoring the health and performance of a mirrored database is crucial to ensure high availability and data protection. SQL Server provides several tools and techniques for monitoring database mirroring:

Database Mirroring Monitor

Database Mirroring Monitor is a graphical user interface tool in SQL Server Management Studio (SSMS) that allows administrators to view the status of mirrored databases, including performance metrics and warning thresholds 7.

To launch the Database Mirroring Monitor:

1. Connect to the principal server instance in SSMS.

2. Expand Databases and select the database to be monitored.

3. Right-click the database, select Tasks, and then click Launch Database Mirroring Monitor.

SQL Diagnostic Manager

SQL Diagnostic Manager is another tool that can be used to monitor database mirroring. It provides a 'Databases' table that displays various information about mirrored databases, including:

• Database Name

• Server Instance

• Current Role (principal or mirror)

• Partner Instance

• Mirroring State

• Witness Connection

• Operational State

• Operating Mode

• Performance metrics (e.g., unsent log, send rate, unrestored log) 8

System Stored Procedures

The sp_dbmmonitor system stored procedures provide a programmatic way to monitor database mirroring. These procedures allow administrators to:

• Create a job that periodically updates the status information for every mirrored database on the server instance. (sp_dbmmonitoraddmonitoring) 9

• Change the value of a database mirroring monitoring parameter. (sp_dbmmonitorchangemonitoring) 9

• Return status rows for a monitored database. (sp_dbmmonitorresults) 9

• Stop and delete the mirroring monitor job. (sp_dbmmonitordropmonitoring) 9

Database Mirroring Monitor Job

A database mirroring monitoring job, Database Mirroring Monitor Job, operates in the background to update mirroring status. SQL Server Agent calls this job at regular intervals (the default is once a minute) to collect and update status information 9.

Key Metrics to Monitor

When monitoring database mirroring, pay attention to the following key metrics:

Monitoring these metrics can help you identify potential issues, such as network latency, performance bottlenecks, or data loss, and take corrective action. It's also important to understand how much data was lost when the principal server becomes unavailable during high-performance mode, which can be determined by monitoring the amount of unsent transaction log 9.

Warning Thresholds

Database Mirroring Monitor allows you to configure warning thresholds for key performance metrics. If a metric exceeds its threshold, an informational event is sent to the Windows event log, alerting administrators to potential issues 10.

Troubleshooting Database Mirroring

Troubleshooting database mirroring involves identifying and resolving issues that may arise during setup, configuration, or operation. Here are some common issues and troubleshooting steps:

Types of Failures

Failures in a database mirroring session can be categorized into two types 11:

• Hard Errors: These are errors reported by underlying components, such as network failures, hardware failures, or operating system issues.

• Soft Errors: These are errors detected by the database mirroring time-out mechanism, such as network timeouts or resource issues.

Common Issues and Troubleshooting Steps

• Error Message 1418: This error indicates that the server network address cannot be reached or does not exist. Verify the network address name and ensure network connectivity 12.

• Account Issues: Ensure that the accounts under which SQL Server is running have the correct permissions. If the accounts are in different domains, create a login for one account on the other server and grant CONNECT permissions on the endpoint 12.

• Endpoint Issues: Verify that the endpoints are correctly configured on all participating servers. Check the port numbers, ensure the endpoints are started (STATE=STARTED), and verify that the ROLE is correct. Also, ensure that the login for the service account from the other server instance has CONNECT permission on the endpoint 12.

• Network Access: Ensure that each server instance can access the ports of the other server instances over TCP. Use the netstat command-line utility to display all the ports on which a server is listening and identify any connectivity issues 13.

• Mirror Database Preparation: Ensure that the mirror database is restored with the WITH NORECOVERY option and that all log backups have been applied 12.

• Failed Create-File Operation: If a create-file operation fails, remove the mirroring session, restore a full backup of the affected filegroup, and manually restore the log backup on the mirror database using the WITH NORECOVERY and WITH MOVE options 12.

• Starting Mirroring with Transact-SQL: When using Transact-SQL to start mirroring, ensure that the ALTER DATABASE database_name SET PARTNER ='partner_server' statements are issued in the correct order. The first statement must be run on the mirror server 12.

• Cross-Database Transactions: Database mirroring does not support cross-database transactions. An automatic failover could lead to incorrect resolution of in-doubt transactions 12.

• Performance Issues with Synchronous Mirroring: Synchronous mirroring can introduce performance delays due to the two-phase commit process. Consider using asynchronous mirroring (high-performance mode) if performance is critical 14.

• Database Corruption: If the principal database becomes corrupted, database mirroring can automatically replace corrupt pages with healthy copies from the mirror database. However, if this doesn't resolve the issue, restore the database from a backup or use a database repair tool 15.

Error Messages Indicating Corruption

The following error messages may indicate database corruption in a mirroring environment 15:

• Msg 8978, Level 16, State 1, Line 1

• Msg 823, Level 24, State 2, Line 1

• Msg 824, Level 24, State 2, Line 1

• Msg 829, Level 16, State 1, Line 1

If you encounter these errors, investigate the cause of the corruption and take appropriate action to resolve the issue.

Best Practices for Database Mirroring

To ensure optimal performance and reliability of database mirroring, consider the following best practices:

• Use a Dedicated Network Adapter: For best performance, use a dedicated network adapter for mirroring traffic. This helps to minimize network latency and improve data transfer speeds 3.

• Start with Asynchronous Mode: When deploying database mirroring in a production environment, start with asynchronous, high-performance mode. This mode is less sensitive to network latency and allows you to assess the performance impact of mirroring before enabling synchronous operation 3.

• Monitor for Network Errors: Throughout testing and operation, monitor your mirroring sessions for network errors that can cause failures. Use monitoring tools and techniques to identify and address network issues promptly 3.

• Enable Synchronous Mode Gradually: Once you are confident that asynchronous operation meets your needs, consider enabling synchronous operation (high-safety mode) to improve data protection. Start with high-safety mode without automatic failover to assess the performance impact before enabling automatic failover 3.

• Leverage Rolling Upgrades: Database mirroring can improve the availability of the production database during upgrades by allowing for rolling upgrades. This involves sequentially upgrading the server instances hosting the failover partners, minimizing downtime 16.

• Maximum Number of Mirrored Databases: Be aware that the maximum number of databases that can be mirrored is limited on 32-bit systems but not on 64-bit systems. Plan your mirroring configuration accordingly, considering the limitations of your environment 17.

Conclusion

Database mirroring is a valuable feature in SQL Server that can significantly enhance database availability and disaster recovery capabilities. By implementing mirroring, you can ensure continuous operation of your critical databases even in the event of server failures. This article provided a comprehensive guide to implementing, monitoring, and troubleshooting SQL database mirroring, covering key aspects such as prerequisites, operating modes, monitoring tools and metrics, troubleshooting steps, and best practices. By understanding and following these guidelines, you can effectively leverage database mirroring to protect your valuable data and maintain high availability of your SQL Server databases.

Works cited

1. A Comprehensive Guide to Implementing Database Mirroring in SQL Server - Medium, accessed January 24, 2025, https://medium.com/@rakesh.mr.0341/a-comprehensive-guide-to-implementing-database-mirroring-in-sql-server-9636a98dd0ac

2. How to Configure Database Mirroring for SQL Server - TatvaSoft Blog, accessed January 24, 2025, https://www.tatvasoft.com/blog/how-to-configure-database-mirroring-for-sql-server/

3. Database Mirroring: Prerequisites, restrictions, & recommendations - SQL Server Database Mirroring | Microsoft Learn, accessed January 24, 2025, https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/prerequisites-restrictions-and-recommendations-for-database-mirroring?view=sql-server-ver16

4. How to setup MSSQL database mirroring? - AccuWeb Hosting, accessed January 24, 2025, https://manage.accuwebhosting.com/knowledgebase/3759/How-to-setup-MSSQL-database-mirroring.html

5. SQL Server Mirroring - Delinea, accessed January 24, 2025, https://docs.delinea.com/online-help/secret-server/backup-and-disaster-recovery/disaster-recovery/sql-server-mirroring/index.htm

6. Database Mirroring Best Practices and Performance Considerations - Download Center, accessed January 24, 2025, https://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/dbm_best_pract.doc

7. Start Database Mirroring Monitor (SQL Server Management Studio) - Microsoft Learn, accessed January 24, 2025, https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/start-database-mirroring-monitor-sql-server-management-studio?view=sql-server-ver16

8. Monitor database mirroring - IDERA wiki, accessed January 24, 2025, https://wiki.idera.com/display/SQLDM/Monitor+database+mirroring

9. Monitoring Database Mirroring (SQL Server) - Microsoft Learn, accessed January 24, 2025, https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/monitoring-database-mirroring-sql-server?view=sql-server-ver16

10. Use Warning Thresholds and Alerts on Mirroring Performance Metrics (SQL Server), accessed January 24, 2025, https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/use-warning-thresholds-and-alerts-on-mirroring-performance-metrics-sql-server?view=sql-server-ver16

11. Possible Failures During Database Mirroring - SQL - Microsoft Learn, accessed January 24, 2025, https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/possible-failures-during-database-mirroring?view=sql-server-ver16

12. Troubleshoot Database Mirroring Configuration (SQL Server) - Microsoft Learn, accessed January 24, 2025, https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/troubleshoot-database-mirroring-configuration-sql-server?view=sql-server-ver16

13. Troubleshooting SQL Server Database Mirroring Problems with Best Practices: Requirements, and the 64-bit Benefit - SQLServerCentral, accessed January 24, 2025, https://www.sqlservercentral.com/blogs/troubleshooting-sql-server-database-mirroring-problems-with-best-practices-requirements-and-the-64-bit-benefit

14. Performance issue due to database mirroring? – SQLServerCentral Forums, accessed January 24, 2025, https://www.sqlservercentral.com/forums/topic/performance-issue-due-to-database-mirroring

15. How to Diagnose and Fix Database Corruption in SQL Server Mirroring Environments?, accessed January 24, 2025, https://www.stellarinfo.com/article/diagnose-and-fix-database-corruption-in-sql-server-mirroring-environments

16. Database Mirroring (SQL Server) - Microsoft Learn, accessed January 24, 2025, https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-ver16

17. Prerequisites for Database Mirroring, accessed January 24, 2025, https://www.sqldbahub.com/p/prerequisites-for-database-mirroring.html