Configure Replication or AlwaysOn in SQL 2019 Standard

Hi experts,
I have limited experience with AlwaysOn in older versions. I will need to replicate 1 database from a SQL Server on Azure VM to another SQL Server on Azure VM. Both will be 2019 Standard. I need a fairly simple solution without having to add more than these 2 servers.

Transaction-replication would work but it's high maintenance because when table structures change you have to modify the replication accordingly.

Because it's only 1 database, would AlwaysOn BAG be a good option? Is 2019 Standard still limited to using Basic Avail Groups? How many servers/instances are required for the most basic setup? Oh, the secondary will be for reporting so it needs to be a readable database.
Thanks.

Basic Availability Groups don't allow readable replicas, they're strictly for failover. They function exactly like Database Mirroring, except for the Windows clustering requirement. You only get readable AG replicas with Enterprise Edition.

If you don't want to deal with replication (or Azure Data Sync), you can look at Change Data Capture or Change Tracking. Both have overhead, but it's less than replication. Both also require custom synchronization routines, and you need to schedule something to sync regularly.

1 Like

You could also consider log shipping - which would allow for pausing the application of the logs and switching to standby mode for read only access if needed.

If the goal is for HA - the a BAG would probably be the better option.

1 Like

Thanks. The goal on this effort is to have a reporting database, always current data.

If you need always current data - then your options are replication or Azure Data Sync (there are quite a few requirements for this).

The only other option for near real-time access to a read-only copy would be upgrading to Enterprise Edition and setting up an AG.

2 Likes

Thanks, Jeff. To clarify, this is 2019 Standard.
I have 2 requirements.
One instance for HA failover. You said earlier that AlwaysON BAG is an option with Standard I believe. So the options for HA will be BAG, T-replication or Azure Data Sync?

One instance for read-only Reporting: The options would be Transactional replication or Azure Data Sync, correct? I don't have experience with CDC Change Data Capture. so I'm leaving that option off the table.

You're correct on all items.

1 Like