Can I use SQL AG to create readable secondary when primary is enterprise and secondary is standard edition?

All we need is an easy explanation of the problem, so here it is.

1 have 2 sql servers.

Svr1 is enterprise edition and Svr2 is standard edition.

Both have their own independant FCI.

Svr1 has got 60 production dbs. Svr2 has got 10 reporting dbs and multiple etls.

Presently the etls running on Svr2 fetch data from 5 dbs from Svr1 and insert data into one/more reporting dbs on Svr2.

I want to sync 5 dbs from Svr1 into Svr2 so that reporting tools and etl on Svr2 has access to copy of those dbs on Svr2 itself.

The sync need not be real time and a delay of 15 minutes is acceptable.

I am exploring SQL AG asynchronous with readable secondary for this.

Link: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver16#limitations

It says: Basic AG – No read access on secondary replica. But in my case the primary is enterprise which I assume means that the AG that I create is not basic but full fledged AG. I’m not able to get documentation regarding this that- what a AG is basic and when it is not basic.

This link says about the readable secondary that I am talking about: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-scale-availability-groups?view=sql-server-ver16

But there is no information about license on the secondary server.

Questions:

  1. Does enterprise edition primary and standard edition secondary support readable secondary?

  2. Have I understood correctly that I can’t create a group of 5 AG, instead I need to create 5 separate AGs because my secondary is having standard license?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Does enterprise edition primary and standard edition secondary support readable secondary

No. Standard Edition can only use Basic AGs, and EE can’t use Basic AGs. So you can’t have any sort of AG between the two servers.

Can I use SQL AG to create readable secondary when primary is enterprise and secondary is standard edition?

Editions and supported features of SQL Server 2019

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply