Environment
We have a core sql server cluster. This cluster contains some databases that get replicated to a load-balanced sql cluster of currently 3 servers. These databases are replicated each 12 hours but will eventually be replicated every 4 hours.
Requirement
On this cluster a new database is created and we need this database to be replicated asap to the load-balanced sql cluster. A delay of seconds or minutes is allowed and writes to this database are currently and in the future low (a few per hour).
Questions
Can two different replication plans coexist side-by-side on the same environment?
Is it possible to setup a second replication routine for this scenario (continuous transaction replication) besides the current replication schema for the existing databases?
Does this create a high risk for a large existing scheduled replication job?
Our DBA says that this replication scenario creates a high risk for the existing replication configuration (2x a day).
My brainwaves
I can't imagine that this minor write activity with continuous transaction replication can create issues for the large existing replication job. I can imagine the other way around that our continuous replication will suffer twice a day due to the large replication job. We are perfectly fine with that as replication is required ASAP during regular operation.
Best Answer
Yes, you can create many publishers for the same articles that replicates to different subscribers.
Yes, if you are replicating a new database or subset of data to other server, it should be fine.
You need to think about if the distributor server is same on the publisher server or you are having a different distribution server.
When distributor and publishers share the same server instance, then it is highly likely that you will hit a performance bottleneck, depending on how much data you are replicating (all data vs subset of data) and your network latency between your publisher databases and subscriber databases.
As I mentioned in above answer, it depends on how much data you are replicating and the amount of transactions happening on publisher database. Better to follow your DBA recommendations if he/she has proper stats or baseline that will show the current behavior.