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.

  • When you say "subscription database" do you mean the distribution meta data or the application database on the subscriber instance? – Michael Green May 9 '16 at 13:59
  • its application database on the subscriber instance. – Niraj May 9 '16 at 14:10

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.

  • 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 May 9 '16 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. – Cody Konior May 9 '16 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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