1

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.

  1. 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?
  2. If one subscriber has bad network performance, does that affect the performance of other subscribers?
  3. 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?
  4. Can a subscriber drop out from the system and/or join it without affecting other subscribers?
  5. 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.

0

1 Answer 1

0

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.

  1. First of all, are my assumption/reasoning accurate? Or is there a better way to deal with the original issue?

I don't believe it should affect write operation speed at the Publisher. The transactions should occur just the same, will get logged, and the Distributor will generate a series of commands to distribute those changes to the Subscribers. Read operations may get faster by alleviating the number of concurrent connections on the Publisher by dividing those up among the Subscriber servers. You may also encounter different timings of blocking of the tables being modified at the Subscriber side, which may or may not appear to be better for read performance.

Does the describe setup make even sense?

You need to be able to write from every server and regular Transactional Replication can't do this. Instead, you'll want to use either Bidirectional Transactional Replication or Peer-to-Peer Replication.

  1. If one subscriber has bad network performance, does that affect the performance of other subscribers?

As far as I'm aware, it should not.

  1. 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?

The limitation is your network bandwidth at that point. If your network is on a 10 Gigabit line (or more) than it should be negligible. Slower lines work fine as well. A single centralized server doesn't have the concern of network latency (between multiple servers) so of course it's better in that regard. Which architecture will work best just depends specifically on the problem you're trying to solve though. If you're having contention issues with concurrent readers and writers blocking each other, then you may be better off using optimistic concurrency which can be accomplished with either Snapshot Isolation or RCSI (Read Committed Snapshot Isolation).

  1. Can a subscriber drop out from the system and/or join it without affecting other subscribers?

Yes, I believe so. Dropping a Subscription is easy and should not affect the other Subscribers. Adding a Subscription is simple too. As long as there's a valid Snapshot of the Publication already generated, a new Subscriber should be able to re-use that pre-existing Snapshot (as opposed to having to generate a new one that gets synchronized for all Subscribers).

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

Pull Subscriptions distribute the load across the Subscribers. It's usually the recommended implementation when some synchronization latency is tolerable in a topology with many Subscribers. Otherwise, if near real-time synchronization is needed, a Push Subscription is the better choice, but the Distribution server's hardware should be provisioned accordingly.

Your Answer

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

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