2

When we set up web servers in AWS, we have a load balancer which allows highly scalable performance improvements per instance provided.

I would like something similar for MS Sql server. I have been informed that Transactional Replication would provide the read performance improvements I am seeking while also providing multi-instance resilience for uptime.

How does Transactional Replication relate to Availability Groups? Does Availability Groups use Transactional Replication under the hood or are they separate features? Can I use Transactional Replication without Availability Groups?

AGs use WSFC which usually requires an AD network. We just use AWS with no AD, so how do people using AWS normally do read load balancing on an AWS network without worrying about setting up AD?

Improve this question
0

1 Answer 1

Reset to default
1

How does Transactional Replication relate to Availability Groups? Does Availability Groups use Transactional Replication under the hood or are they separate features? Can I use Transactional Replication without Availability Groups?

Transactional replication doesn't relate to Availability Groups, other than the fact that they're both SQL Server technologies. Transaction Replication is a solution for replicating data to another location, usually for reporting solutions or data integrations with other systems. Availability Groups are a High Availability/Disaster Recovery technology to provide transactionally-consistent failover capabilities.

In SQL Server, Replication is not an HA/DR solution and shouldn't be used as such. Replication might provide scale-out read capabilities, but there is no 'multi-instance resilience' built-in to replication that you can rely on for HA/DR.

AGs use WSFC which usually requires an AD network. We just use AWS with no AD, so how do people using AWS normally do read load balancing on an AWS network without worrying about setting up AD?

You haven't stated which SQL Server version you're using, but from SQL Server 2016 onwards, SQL Server supports Availability Groups on domain-less WSFC clusters (or Linux clusters). Depending on your edition, Standard Edition will provide either basic DR functionality only (no read on the secondary node), or Enterprise Edition allows for scale-out read workloads on the secondary node(s).

More Info:

SQL 2016 Supported Features

Basic Availability Groups

Transactional Replication

Improve this answer
6
  • why is Replication not seen as High Availability/Disaster Recovery? it seems to do exactly the same thing without all the blah blah about primaries/secondaries etc.
    – Luke
    Commented Nov 26, 2021 at 1:45
  • Because it is not designed to provide high availability or disaster recovery. Transactional replication can filter rows, columns or tables, it doesn't feature any kind of failover control, there is no functionality for redirecting requests from the old server to the new one and no capability for failing back to the publisher after a DR event. Replication and AAGs handle latency differently as well - high latency can be a non-issue for replication depending on the configuration, but for AAGs this is considered a serious issue and alerts are generated because of the risk to resilience.
    – HandyD
    Commented Nov 26, 2021 at 1:51
  • Superficially, they do the same thing, they both copy data, but the mechanisms by which this is achieved, the controls around data consistency and HA/DR functionality are very different. Replication is not HA/DR.
    – HandyD
    Commented Nov 26, 2021 at 1:51
  • While I think this is a good answer, I disagree on saying Replication is not a HA/DR solution. I believe there's some subjectivity on what characterizes a technology as HA/DR and while AlwaysOn Availability Groups are more robust in that category than Replication is, I do think under certain circumstances (RTO / RPO dependent) Replication can be used in a HA/DR capacity.
    – J.D.
    Commented Nov 26, 2021 at 2:23
  • 1
    @J.D. While agree there is some subjectivity around what is and isn't HA/DR, Microsoft themselves don't list SQL Server replication under the Business Continuity section of the documentation. I think for most common scenarios, there are better options available, given the complexity of replication. Treating it as "not HA/DR" and instead using it only when the features and limitations make it the best option is a safer and simpler approach, but, of course, this is just my opinion.
    – HandyD
    Commented Dec 13, 2021 at 2:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.