Sql-server – Multiple Replication Type Publications on Single DB

blockingreplicationsql-server-2008-r2transactional-replication

I have 2x critical DB's which until recently; I was reporting on as well as using in production. DB1 is a Vendor DB and DB2 an in-house DB used in conjunction with DB1 for custom tables and views (as they are not allowed by the vendor DB1)

When considering replication options for a reporting server, I was limited with DB2 as a number of the custom tables which have been added over time do not have Primary Keys, so transactional was out, leaving really only Snapshot as an option. DB 1 is working well with transactional.

Having now run this set-up for a while, with the Snapshot of DB2 (around 1GB) running every 20 mins, I am getting some performance implications specifically around blocking when the snapshot is generating.

Ideally I would like some of the key tables to run transactionally, whilst a single snapshot takes care of the remainder of the DB out-of-hours, but cannot find much guidance on this anywhere.

My real question is, is it viable to run 2 types of replication on the same database as long as the articles do not cross-over?

Many Thanks in advance!

Best Answer

The following article appears to answer this question:

https://technet.microsoft.com/en-us/library/ms172911%28v=sql.105%29.aspx

Yes, you can run multiple publications from the same Db as long as there are no intersecting articles.