Hopefully, there's a very "simple" answer to this question:
- Can a SQL Server 2012 subscription database in a transactional replication setup also be used for publication?
Add a comment
|
3 Answers
Yes you can but not easy to maintain. If your ever get out of sync and you have to reinitialize, resnap of the first set of publication and subscription; you will need to remove the publication property of the subscription. Otherwise you cannot do a full snapshot.
answered Sep 20, 2017 at 23:29
-
Thank you for the information, I am in the process of testing this approach but was struggling to understand how the publication off the sub would work and a new distributor etc. Sep 21, 2017 at 0:10
-
Second question, can the secondary publication be filtered, only specific articles? Sep 21, 2017 at 0:27
-
You have to do the full setup again, so you can put any article in any publication. I used it before and work smoothly except if you have to full sync. Sep 21, 2017 at 1:26
-
I can see how that would be horrific, again thank you for the advice, now it is a question of should we do it considering the overhead and potential issues. Sep 21, 2017 at 1:47
It sounds like you might need to consider Peer to Peer Replication. You didn't provide too much info on the current setup.
See if this address your needs: https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-transactional-replication
-
Henrico, sorry wanted to keep it short as I didn't see how a sub database can also have publications against it, need trans rep to one server in near real time, then a subset of this database to another server also in near to real time, as you can imagine it is proving awkward Sep 21, 2017 at 0:08
It almost sounds like you want to implement multi-master replication. But not quite, as from the comments, I gather you want to filter the re-publication? Otherwise, you could have just added another subscription to the original publication... See this for more information: https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/replication-publishing-model-overview
There is nothing stopping you from doing this, as you could just use the existing Distributor, and a filtered publication will work on the re-published database, as it would be a separate transaction-log. But "chaining" this type of topology is like asking for trouble...as alluded to earlier, a management-nightmare!
-
Hi Nico, thanks for the information, I am in the process of explaining the overheads of this setup, It's a question of, yes we can do it but should we do it. The client wants to limit the impact at the live source DB effectively building almost an ODS database in the middle and hitting that with the filtered republication, eventual plan is more than one filtered republication based on company data, my job is to do a proof of concept, like everyone on SE I think it will be a nightmare to administer. Sep 21, 2017 at 22:06