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.
Sql-server – will subscriber be available, if publisher or distributor is down
sql servertransactional-replication
Related Question
- Sql-server – how to find the name of the distributor server and publisher server from the subscriber server/subscriber database
- Sql-server – What about redundancy if distributor goes down
- Sql-server – Why does Publisher need to be able to connect to Remote Distributor
- Sql-server – Convert existing subscriber Database to a publisher database. Current publisher will be subscriber
- Sql-server – SQL Server – Basic Availability Groups used for Publisher, Distributor and Subscriber databases
- Sql-server – Subscriber Node down in transactional replication
- Sql-server – SQL server replication (publisher, subscriber, distributor) on top of AlwaysOn
Best Answer
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.