2

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.

Improve this question

1 Answer 1

Reset to default
1

Can two different replication plans coexist side-by-side on the same environment?

Yes, you can create many publishers for the same articles that replicates to different subscribers.

Is it possible to setup a second replication routine for this scenario (continuous transaction replication) besides the current replication schema for the existing databases?

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.

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).

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.

Improve this answer
2
  • I think the distributer is running on the publisher as I've never heard something like it in our production network. Replication is a performance hit but we want to use it to make our public web infrastructure highly available. It isn't that a lot of writes are done on this database. As I said we are talking about a few writes each hour.
    – Ramon Smits
    Aug 23, 2013 at 6:43
  • We as in (dev/devops) do not have any rights to do anything on the production databases so we have to follow dba but in this case I cannot imagine that continuous transactional replication would create such a performance hit on our system. Our dba says that when this replication job would fail that the other scheduled replication job would fail as well. That doesn't make any sense to me.
    – Ramon Smits
    Aug 23, 2013 at 6:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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