0

We have to set up replication between two SQL Server databases residing on two different servers connected to the same domain. Our concern is that if the publisher is down, or distribution database is down, will it by any way affect the availability of subscription database? Here I mean to say if server on which distribution resides goes off while it is updating the tables on subscriber will subscription database tables be accessible? Does distributor acquire a lock on subscriber for updating the rows? We are planning to set up transactional replication.

2
  • When you say "subscription database" do you mean the distribution meta data or the application database on the subscriber instance? Commented May 9, 2016 at 13:59
  • its application database on the subscriber instance.
    – Niraj
    Commented May 9, 2016 at 14:10

1 Answer 1

2

There are locks taken on the subscriber during data transfer which can cause blocking on the subscriber - so you can definitely encounter issues there (and there are options to modify this behaviour at the risk of not being able to rollback failed transactions).

However there aren't any other magical locks or connections between the servers.

But also when the publisher/distributor go down I've never seen the insert also fail to go down and not release the locks (aside from standard rollback time). I'd hesitate to say it wasn't possible but it would be abnormal and I haven't seen it.

The much more common scenario is that the subscriber goes down, log records accumulate in the publisher, fill the disk and your publisher goes down. A large log file and monitoring of backups, space, and the replication itself are essential.

2
  • will the snapshot replication be a good candidate in this case if we dont want to effect the subscriber, as the number of updates will be less frequent from publisher to subscriber. we can prefer to update the tables every hour, the number of records in tables are less than 50,000, and they change less frequently.
    – Niraj
    Commented May 9, 2016 at 12:35
  • Every time the snapshot gets applied you'll get a table lock and that's going to block queries until the whole thing is reapplied, and so snapshot is usually used if you have maintenance windows to apply them. If there aren't frequent changes then I'd probably go with transactional. Commented May 9, 2016 at 12:38

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.