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?

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.

  • 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. – Stockburn Sep 21 '17 at 0:10
  • Second question, can the secondary publication be filtered, only specific articles? – Stockburn Sep 21 '17 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. – SqlWorldWide Sep 21 '17 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. – Stockburn Sep 21 '17 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://docs.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 – Stockburn Sep 21 '17 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://docs.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. – Stockburn Sep 21 '17 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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