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!