SQLTeam.com | Weblogs | Forums

Adding High Availability/Always On to existing topology


The more I read about this topic the more questions I have. Ultimately I think I want to mix my current peer to peer topology with an Always On cluster.

The existing peer to peer replication only occurs on certain high traffic tables on servers A, B, C. Other tables in those databases have relatively static data (maybe updated a couple times a month). We have a SQL Server (#1) who's job it is to handle updates to this 'static' data. It uses transactional replication out to the servers that participate in the peer to peer mesh and everything is fine. Here is a picture that probably describes what I have better than I just did. Ignore servers #2 & 3 for the moment.

But...since there is only one of these servers it does represent a potential single point of failure. I think I want to add a couple of servers to participate in a HA cluster with this server. (servers #2 & 3)

My problem is that I can't seem to wrap my brain around how to configure the transactional replication from the new servers to the existing p2p mesh. Do I have to create publications and subscriptions on #2 &3 too? When #1 fails over to #2 does the replication just continue too or is there something else that needs to happen, like enabling a SQL Agent job?

I'm working on setting up a test environment to test which configuration works but I thought I'd ask here first before potentially wasting a lot of time on something that won't work.



I recently did this exact thing. I'm assuming everything is in the same domain.


Thank you! This is exactly what I was looking for.