Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups

High Availability
1

Hi,
I'm planning the DR solution for one of a project and trying to configure SQL Server replication for distribution databases in SQL Server Always On. I know it is not possible having both publisher and distributor databases exist in an instance. But is it possible to have both publisher and distributor databases on the same machine but running on separate instances? I know the ideal way should be to have two VM with two Distribution instances. But from the cost point of view, we would like to save one VM and locate the 2nd replica of the distribution database back to the publisher server. Please advise.

2

Why are you building out replication? Using an availability group - configured in your DR site would already provide you with a DR solution.

What is the purpose of configuring replication?

1 Like
3

Thanks for your reply. I think you are a bit misunderstood. The purpose of replication is for the report, not DR. But it is crucial, and we would need it 24/7 available. This is why we need to configure it with AlwaysOn. Unfortunately, we only have a limit on budgeting. We would like to minimise the number of the VM. Our planning is as below.

VM1 - Publisher - primary replica
VM2 - Distribution - primary replica
VM3 - Subscriber - primary replica
VM4 - Publisher - secondary replica, Distribution - secondary replica, Subscriber - secondary replica (THREE different SQL instances)

My question is will the above design go to work? Or we MUST need separate Publisher - secondary replica, Distribution - secondary replica and Subscriber - secondary replica into THREE different VM?

4

If you have Enterprise edition you may want to look at the following:

https://www.sqlshack.com/make-the-most-of-secondary-replicas-in-sql-server-always-on-availability-groups/

5

I didn't misunderstand - you stated your goal was to create a DR solution, then brought into the discussion replication.

If the replication is for reporting, I would recommend looking at a read-only secondary instead. The only advantage replication has over using a read-only secondary would be creating indexes specific to the reporting requirements. That can be addressed by having a local database on the read-only secondary and using that database to host the data for specific reporting requirements.

In that scenario, you would run daily/weekly/monthly processes that build reporting tables in this database. This database also contains stored procedures, functions, views and other reporting tables that support all reporting requirements.

You have laid out an unnecessarily complex environment - just to support replication and then added AG functionality. That is going to be a nightmare to manage and maintain and I would not recommend it.

1 Like