I have a MS SQL Server based application with non-trivial schema (has indexing, clustering, transactions, views, stored procs, ...). The application itself has some features that need up-to-date data and some that would work fine with cached data. Basically, all inserts and updates are wrapped in transactions.
The application is quite heavy on DB traffic, and especially when the same MS SQL Server instance is shared across multiple regions around the globe, the latency becomes a problem for many of the sites.
All sites must be able to R/W the database and due to the schema complexity, I'm assuming transactional replication is the only option. I also assume this causes write operations to be even slower, but I'd expect read operations will be much faster. Please correct my assumptions if they are wrong!
Having all above in mind, let's say I setup one main server that is the only replication publisher and also used as distribution server, and all regional sites have their own servers set as subscribers. Replication method is transactional.
- First of all, are my assumption/reasoning accurate? Or is there a better way to deal with the original issue? Does the describe setup make even sense?
- If one subscriber has bad network performance, does that affect the performance of other subscribers?
- Distribution server needs to share synchronization files via file share for all subscribers. I'd assume accessing files over network across regions is a performance nightmare. But how bad nightmare compared to using single centralized SQL Server instance?
- Can a subscriber drop out from the system and/or join it without affecting other subscribers?
- Is there some benefit using push vs pull type distribution given my scenario? I'd assume pull (subscribers have the distribution agents) would be more forgiving for possibly dropping subscribers.
The application at the subscriber environment uses the "subscriber database" like it was the one and only database of the whole system, in other words it reads and writes to it.
I suggested transactional replication because I understood it keeps all replicated databases exact and this might be only possibility too given the DB schema complexity. I also assume if subscriber tries to open transaction lock, the lock request always cascades up to the publisher because otherwise there could be conflicts.