November 10, 2021

Comparison: AlwaysOn Vs Database Mirroring



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:

Creating DataFrames from CSV in Apache Spark

 from pyspark.sql import SparkSession spark = SparkSession.builder.appName("CSV Example").getOrCreate() sc = spark.sparkContext Sp...