AlwaysOn | Database Mirroring | |
Reporting | Readable replicas allow real time reporting | The mirror database is always in a state of recovery, which essentially means it can’t be used while mirroring is active. You can create a snapshot of the mirror database to use for reporting, but the snapshot is a point-in-time copy making it unsuitable for real time reporting purposes |
Read-only routing | Supports the re-routing of read-intent connection requests to a readable secondary replica. | This feature in not available in Database Mirroring. |
Performance Considerations | Performing highly CPU, I/O-intensive read-only workloads on the secondary databases, it will improve the database performance of Source database. | Synchronous mirroring requires a 2-phase commit to take place which could introduce delays on the principal. |
Additional H/W | Integrated with Windows Server Failover Clustering (WSFC) | Database mirroring doesn’t require WSFC but if you want to use it in High Safety mode for automatic failover, then you need to use a third SQL Server system as a witness. |
Feature support | Only available with SQL Server Enterprise edition | Available with both SQL Server Enterprise and Standard edition. Synchronous mirroring is available only on Enterprise edition. |
Risks | All active workloads must be licensed and requires additional cost of SQL server license. | Database Mirroring is available in SQL2012 and SQL2014 but will be deprecated in future SQL Server releases. |
Schema or data changes | You cannot make schema or data changes on the secondary databases which limits creating additional indexes on secondary databases | The mirror database is always in a state of recovery, which essentially means it can’t be used while mirroring is active |
Benefits | Provides High availability for SQL Server databases. Capacity of replicate databases to facilitate read only access which makes it possible for them to be used for multiple purposes at the same time. You can use a secondary database for reporting purposes. | Provides a high availability of the database and Do not need any singular hardware. |
No comments:
Post a Comment