SQL Server Mirroring
Concept:
- SQL Server Mirroring is a high-availability solution that involves maintaining a mirror (exact copy) of a database on a different server.
- It continuously sends transaction log records from the principal server to the mirror server.
Key Features:
- Roles: Involves two roles, the principal server (primary) and the mirror server (secondary).
- Synchronous/Asynchronous: Can be configured in synchronous (high-safety) mode or asynchronous (high-performance) mode.
- Automatic Failover: This can achieve automatic failover if configured with a witness server (a third server that acts as an arbitrator).
- Database Level: Works at the database level, meaning only one database is mirrored at a time.
- Limited Read Access: The mirror database is not readable unless using database snapshots, which are read-only.
Limitations:
- Limited scalability as it is designed for a single database at a time.
- Deprecated: As of SQL Server 2016, database mirroring is a deprecated feature and Microsoft recommends using Always On Availability Groups instead.
Always On Availability Groups
Concept:
- Always On Availability Groups is an enterprise-level high-availability and disaster recovery solution.
- It allows for multiple databases to be grouped for failover purposes.
- Provides read-scale and failover capabilities for a group of databases rather than just a single database.
Key Features:
- Availability Groups: Consist of a primary replica and one or more secondary replicas (up to 8 secondary replicas in SQL Server 2016+).
- Synchronous/Asynchronous: Supports both synchronous-commit and asynchronous-commit modes.
- Automatic Failover: Supports automatic failover with synchronous-commit mode.
- Read-Only Secondaries: Secondary replicas can be configured for read-only access, which allows for offloading read operations from the primary.
- Database-Level Failover: Supports failover of a group of databases as a single unit.
- Integrated with Windows Server Failover Clustering (WSFC): Leverages WSFC for failover coordination.
Advantages:
- Multi-Database Support: Can manage failover for multiple databases simultaneously.
- Scalability: More flexible and scalable compared to database mirroring.
- Read-Only Secondaries: Enhances read scalability by allowing read-only access to secondary replicas.
- Enhanced Monitoring and Management: Provides more robust tools and options for monitoring and managing high availability.
Use Cases:
- Ideal for applications requiring high availability and disaster recovery across multiple databases.
- Suitable for environments where read scale-out is needed.
- Supports complex scenarios with multiple replicas and automatic failover capabilities.
Summary
- SQL Server Mirroring: Best suited for simpler scenarios where only a single database requires high availability, but it is now a deprecated feature.
- Always On Availability Groups: Offers a more advanced, scalable, and flexible solution for high availability and disaster recovery, supporting multiple databases and read-only secondary replicas.
For new implementations, Microsoft recommends using Always On Availability Groups due to its advanced features and future support.