A well-known high-availability concept is database mirroring, which maintains synchronization of the database on one or more servers to guarantee data redundancy and fault tolerance. This article covers the foundations of mirroring, its history, advantages, and best practices for configuration.
Table of Contents
Introduction
Database mirroring is manually replicating a database’s contents from the primary server to a mirror server in SQL Server. It provides automatic failover, which makes it easy for users to connect to the mirror server. It improves the high availability of the environment for sophisticated applications. Database mirroring is proper when data integrity and high uptime are essential for an organization.
A Glimpse into History
Database Mirroring in SQL Server was introduced in 2005 and has been enhanced. It is still used in many organizations for multiple scenarios, even though AlwaysOn Availability Groups were introduced in SQL Server 2012.
Advantages of Database Mirroring in SQL Server
A few advantages of Database Mirroring in SQL Server are given below for more clarity & better understanding:
Database mirroring in SQL Server has several benefits, especially in the DR and high availability of the environment. These are a few main advantages of database mirroring:
1. Availability
Automatic Failover: If the primary server fails, the SQL Server can automatically switch to the mirror server using synchronous mirroring (high-safety mode with witness), guaranteeing constant availability with little downtime.
Decreased Downtime: Database mirroring reduces downtime in high-availability systems by keeping a backup server ready to take over immediately in case of a breakdown.
2. Protection and Redundancy of Data
Real-Time Redundancy: Mirroring ensures data redundancy and protection against data loss by continuously transferring the Database transaction log from the principal server to the mirror server.
Minimal Data Loss: Transactions are committed simultaneously on both the principal and mirror servers in synchronous (high-safety) mode, removing the possibility of data loss during failover.
3. Increased Capability to Handle Disasters.
Geographic Redundancy: In disaster recovery scenarios when the principal server may be in a different region or location, the high-performance mode (asynchronous) enables the mirror server to be geographically far from the principal server.
Fast Recovery: In the case of a disaster, the mirror database may be brought up rapidly, enabling companies to resume operations quickly.
4. Transparency and Simplicity
Easy to Set Up: Database mirroring is relatively easy to configure & manage compared to other high-availability solutions like log shipping or Always On Availability Groups.
Transparent to Clients: SQL Server can seamlessly reroute clients to the mirror server during failover, so applications don’t need to understand the underlying mirroring configuration.
5. Enhancement of Performance (using Asynchronous Mode)
Offload Reads (with Manual Configuration): In a manual failover scenario, the mirror database can be accessed in read-only mode, which may offload some read traffic, even if mirroring by itself does not directly offer read scalability.
Low Latency Impact: The high-performance mode (asynchronous) ensures high transaction throughput with little latency impact on the principal, in which the principal server does not wait for acknowledgment from the mirror server.
6. Economical and Widely Available
No Additional Licensing Needed: Database mirroring is an affordable, high-availability, disaster recovery solution within the SQL Server ecosystem since it doesn’t require pricey third-party software or extra licensing.
Leverage existing Infrastructure: Many firms can set up mirroring using their current SQL Server infrastructure without requiring specialist hardware.
7. Adaptability
Adaptability to Various Situations: Database mirroring offers two operational modes (high-performance and high safety), enabling businesses to select the option that best suits their requirements: maximum data protection or performance.
Encourages Database mirroring can offer either manual failover for disaster recovery or automatic failover for high-availability scenarios, depending on the design.
8. Encourages Data Integrity
Transactional Consistency: Data consistency is preserved throughout failovers or in the case of server failure because transactions are applied to both the principal and mirror databases.
9. Observation and Warnings
Integrated Monitoring: SQL Server comes with integrated capabilities for Database mirroring monitoring, which enables administrators to identify and address problems early on.
Alerts & Notifications: Administrators may monitor the mirroring process by receiving alerts that let them know if the mirrored session is stopped or if failover is started. This enables prompt action.
Disadvantages of Database Mirroring in SQL Server
A few disadvantages of Database Mirroring in SQL Server are given below for more clarity & better understanding:
Although SQL Server database mirroring provides several benefits for disaster recovery and high availability, it also has certain drawbacks. The following are some of the main disadvantages:
1. Restricted to One Database
No Multi-Database Support: You can only mirror one Database at a time because Database mirroring works on a per-database basis. Since there is no built-in mechanism to coordinate failovers across many databases, this might be troublesome in applications where multiple databases must fail simultaneously.
2. Excessive Use of Resources
Increased CPU and Network Load: Mirroring can increase the CPU and network loads of both the principal and mirror servers, mainly when done in synchronous mode (high safety). The mirroring process uses a lot of bandwidth and resources to replicate transactions in real time, which could affect performance.
I/O Overhead: Compared to a solo database, synchronous mirroring may result in increased I/O overhead and poorer performance because it requires both servers to commit transactions simultaneously.
3. No Read-Only Replica Support
In contrast to Always On Availability Groups, the mirror database cannot be utilized for read-only queries. The resources on the mirror server are not being used for read operations or reporting workloads since the mirrored copy of the Database is in a recovery state and can only be accessed if a failover occurs.
4. High-Performance Manual Failover Mode
Asynchronous Mode Without Automatic Failover: In high-performance (asynchronous) mode, failover is not automated and needs to be done by hand. If the failover procedure is not adequately monitored or an administrator is not on hand to start the failover in the event of a failure, this could cause recovery delays.
5. Asynchronous Mode’s Potential for Data Loss
Data Loss Risk: Because transactions made on the primary server might not have been copied to the mirror server at the moment of failure, there is a chance that data will be lost during failover in high-performance mode (asynchronous). Because of this, it is less appropriate for situations where there must be no data loss.
6. Automatic Failover Relies on a Third Server (Witness)
Automatic Failover Requires Witness: In high-safety mode, automatic failover necessitates a third server, the witness. Automatic failover will not function if the witness server is unavailable or improperly configured, lowering the high-availability solution’s overall reliability.
7. Older SQL Server Versions Are Obsolescent
Deprecated in SQL Server 2016: According to Microsoft, database mirroring will no longer be supported in SQL Server 2016. Instead, customers are advised to switch to Always On Availability Groups, which provide excellent performance, scalability, and flexibility. Therefore, there might be better long-term options than Database mirroring for companies and those who want to stay up-to-date.
8. Complicated Configuration for Backup Situations
Needs Careful Configuration: Although basic mirroring is easy to set up, automatic failover, witness server setup, and failback scenarios can be complicated and require careful consideration. This makes mistakes and misconfigurations more likely, mainly in teams with less experience.
9. No performance scaling or load balancing
No Read or Write Load Balancing: In database mirroring, there is no inherent support for load balancing between the principal and mirror servers. The resources of the mirror server remain largely idle until a failover occurs since the principal server handles all read and write transactions.
10. Needs Comparable Hardware
Resource Matching: For best performance, the hardware of the principal server and the mirror server should be comparable. If its resources are much lower, the mirror server could become a bottleneck during failover, lowering overall performance and availability.
11. Inadequate Tools for Monitoring and Management
Less Robust Monitoring: Database mirroring has less integrated monitoring and management capabilities than more recent SQL Server high-availability options like Always On Availability Groups. This may make proactive detection and resolution of mirrored process problems more challenging.
Different Types of Database Mirroring in SQL Server
Database mirroring is a high-availability feature at the database level in SQL Servers that offers disaster recovery anytime and allows real-time database redundancy. It entails the system keeping two copies of the same database on various servers. Depending on the organization’s requirements, we need to decide the availability mode of mirroring to protect data. SQL Server provides three different database mirroring options, each with a distinct function.
In SQL Server, three types of database mirroring are available:
High-Safety Mode Without Automatic Failover
Description: This mode of database mirroring allows all database transactions on both the primary and secondary servers to be committed before transaction completion and ensures no data loss.
Use Case: It works well in settings where the system can handle the overhead of transaction confirmation on both servers and data integrity is a top priority.
Witness Server: If the primary server fails, high availability can be guaranteed by adding a witness server to enable automatic failover.
Automatic Failover: The Automatic failover feature is available in this mode, but the witness should be set up.
Automatic Failover in High-Safety Mode (Synchronous with Witness)
Description: This database mirroring mode is a variation of the high-safety mode incorporating the witness server ( a third server). If the primary mirroring server is unavailable for any reason, the witness server assists in monitoring and detecting the environment and can initiate an immediate failover to the mirror server.
Use Case: This type of database mirroring is suitable for organizations that require automatic failover without losing data and high availability. There is minimum downtime, which is one of the advantages of this mode.
Automatic Failover: If the witness is available in the setup and properly configured, then the answer is yes for automatic failover.
Asynchronous High-Performance Mode
Description: In this mode of database mirroring, all transactions are continuously sent from the principal server to the mirror server, and the principal server commits the transaction immediately without waiting for confirmation from the mirror server. It helps decrease the transaction latency and enables the principal server to operate without interruption.
Use Case: This is optimal for settings where data loss in the event of failover is tolerable and performance is paramount. Its usage commonly addresses disaster recovery scenarios involving geographically distant principal and mirror servers.
Automatic Failover: The mirror server might not have the most current transactions; therefore, data loss could occur. Automatic failover: No, failover needs to be done manually.
Important Database Mirroring Components
Principal Server: The Principal server on which the database is currently running.
Secondary Server: The Secondary server that keeps a duplicate of the primary database is known as the “mirror server.”
Witness Server: This optional third server aids in automated failover when utilized in high-safety mode with automatic failover.
Various mirroring modes provide varying degrees of protection and performance. Therefore, it is critical to select the appropriate option according to your requirements.
Conclusion
High availability can still be reliably achieved with SQL Server Mirroring, although newer solutions like AlwaysOn Availability Groups have surpassed it. Even though it is deprecated, it provides crucial data protection and simple configuration. Observing recommended practices, monitoring performance, and testing failover situations is essential for seamless operation while setting up mirroring.
FAQs
Q1. What is SQL Server database mirroring?
Ans: Database mirroring is a high-availability solution that keeps two copies of a single database on separate servers to guarantee data availability and redundancy.
Q2. Which kinds of database mirroring modes are there?
Ans: The three modes are high-performance (Asynchronous), High-Safety with Automatic Failover (Synchronous with Witness), and High-Safety (Synchronous).
Q3. Is it possible to use database mirroring for more than one database?
Ans: No, only one database at a time is supported by database mirroring.
Q4. Is automated failover supported by database mirroring?
Ans: Yes, but only when the witness server is set up in High-Safety mode.
Q5. Does database mirroring carry any danger of data loss?
Ans: If a failover happens before all transactions are replicated in the mirror, data loss may occur in high-performance mode (asynchronous).
Q6. Is it possible to run read-only queries in the mirror database?
Ans: No, until a failover occurs, the mirror database is in a recovery state and cannot be accessed for queries.
Q7. How do synchronous and asynchronous modes differ from one another?
Ans: In asynchronous mode, the principal does not wait for the mirror to commit, whereas in synchronous mode, transactions are committed on both the principal and mirror servers at the same time.
Q8. What would occur if the witness server malfunctioned?
Ans: The principal and mirror servers will continue functioning even if the witness server fails, preventing automated failover.
Q9. Does SQL Server deprecate database mirroring?
Ans: Microsoft recommends Always On Availability Groups to replace database mirroring, which is deprecated as of SQL Server 2016.
Q10. Is database mirroring between SQL Server versions feasible?
Ans: The principal and mirror servers’ SQL Server versions must match to set up database mirroring.
Q11. How can I monitor the condition with database mirroring?
Ans: To monitor the situation, you can utilize SQL Server Management Studio (SSMS) or query system views like sys.database_mirroring.
Q12. What occurs when database mirroring fails?
Ans: In failover, the former principal server transitions to a recovery state, and the mirror server assumes the role of the principal server, handling client requests.
Q13. Is geographical redundancy supported by database mirroring?
Ans: Yes, especially when using the asynchronous mode, which allows the mirror server to be situated in a separate area for disaster recovery.
Q14. What requirements must be met to set up database mirroring?
Ans: The database must be in full recovery mode, and both servers must run the same version of SQL Server.
Q15. Is SQL Server Standard Edition compatible with database mirroring?
Ans: However, only the Enterprise Edition offers automated failover (with a witness).
Review the articles below also.
Dbcc Freeproccache: A powerful command
Extended Events in SQL Server: A Deep Dive
Understand Deadlocks in SQL Server
SQL Server Pivot: Top 5 Concepts