How to choose between Azure SQL Always On Availability Groups vs SQL Server Replication

Question

Background:

Our production SQL Server database is hosted on Azure VM. We have a nightly process to create a data warehouse environment that is used by our power users to query a day-old data and run reports. We would like to create a (or more in the future) read-only replica of the production database for read-only workloads such as Power/BI and SSRS reporting as well as ad-hoc querying.  Latency in minutes if needed is acceptable (not hours). Failover capability is not required at the moment.

The business reason for creating read-only replicas is to improve the read-write performance of the production database, in addition to being able to query near-real-time production data without negatively impacting the read-write/transactional performance of the production database by all our external users.

While there may be two ways to approach this: a) SQL Server Replication, and b) Always-on Availability Groups, we would like to know how people decide between the two approaches.

1. Are both methods a viable option given the intended business purpose?
2. Are there any downsides to consider for Always-on AGs?
3. What happens during large nightly/periodic batch processing? What is the effect on the read-only replicas when such large processes run on the read-write primary (during off-business hours)?
4. Do you have any recommendations or best practices in building and testing AG framework?
5. Do you have any recommendations or best practices in building and testing SQL Server Replication?

Answer 1

@Somasekar Srinivasan Thank you for reaching out.

When deciding between SQL Server Replication and Always on Availability Groups (AGs) for creating read-only replicas, it’s essential to consider your specific business requirements.

Are both methods a viable option given the intended business purpose?

Both SQL Server Replication and Always on Availability Groups (AGs) are viable options for achieving your intended business purpose of creating read-only replicas. Always On Availability Groups can be used for High-Availability, Disaster Recovery, and to create Readable Replicas of a production database.

Are there any downsides to consider for Always-on AGs?

Before you deploy Always On availability groups, we strongly recommend that you read every section of this topic. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availability?view=sql-server-ver16.

Always On availability groups supports two availability modes-asynchronous-commit mode and synchronous-commit mode. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible. For the Synchronous-commit mode, committed transactions are fully protected. This protection comes at the cost of increased transaction latency. Optionally, SQL Server 2017 introduced a required synchronized secondaries feature to further increase safety at the cost of latency when desired. Availability Modes

What happens during large nightly/periodic batch processing? What is the effect on the read-only replicas when such large processes run on the read-write primary (during off-business hours)?

When large nightly or periodic batch processes run on the read-write primary, it can impact the read-only replicas. There could be a sync delay.

Do you have any recommendations or best practices in building and testing Availability Groups framework?

Recommendations and Best Practices When Deploying SQL Server AlwaysOn Availability Groups in Microsoft Azure (IaaS)

Prerequisites, restrictions, and recommendations for Always On availability groups

Best Practices for Replication Administration

Use the Azure portal to configure a multiple-subnet availability group for SQL Server on Azure VMs

Do you have any recommendations or best practices in building and testing SQL Server Replication?

Best Practices for Replication Administration

Replication Security Best Practices

Tutorial: Prepare SQL Server for replication (publisher, distributor, subscriber)

Please let me know if you need additional clarification.

Regards,

Oury

Answer 2

Thanks Oury for your detailed response. At this time I don't have any questions.