Is database locked when it's in replication process?

Question

I'm doing POC on database replication, and I'm stuck with question from my boss. Is database locked when it's in replication process on SQL Server 2005?

Answer 1

Since you refer to "replication" in a very general manner I'm guessing that you probably haven't fully researched the replication functionality in SQL Server 2005.

There are different "flavors" of replication technologies in SQL Server (publisher / subscriber, database mirroring, etc). Depending on what you're trying to do you may opt to choose one replication strategy over another (scalability versus availability, for example). Your replication latency requirements, throughput needs, and desired overhead are all going to make differences in what you choose.

Reading up on the various replication strategies would probably be your best bet, for starters.

Answer 2

I am no expert on MS SQL Server but this might be of your interest.

locking during snapshot generation for transactional replication is much reduced by default in SQL2005, and you can further reduce it by using the new 'database snapshot' sync_method if you are running Enterprise Edition.

It depends on the type of replication you are using, for more reference see http://msdn.microsoft.com/en-us/library/ms152531.aspx

Answer 3

Exactly what happens during replication depends on what kind of replication you're doing - however the Host database should not be locked. In a log shipping situation the server being log shipped TO will be locked during the restore, no matter what, however you can set it to be read only and accessible when not restoring. With mirroring you should be able to run queries (selects) against your mirrored (secondary) server at any time.

Answer 4

you need to better define what you mean by the following: replication, process and locking.

'Locking' inside a sql server is a normal part of its operation. Read this for a crash course http://www.sqlteam.com/article/introduction-to-locking-in-sql-server