1

When the initial snapshot is being generated while configuring SQL Server Transactional Replication, does anyone know if the snapshot agent places locks on the tables (articles) you have selected? I have some tables that contain 2+ millions rows and wanted to know if SQL Server actually places table locks to prevent updates while the publishing database is online. If locks are placed, then I want to run the initial snapshot during off peak hours in production.

Thanks!

sydney
  • 131
  • 8
  • 19

3 Answers3

0

In Transactional replication or any other type of replication the starting point is a snapshot of the database. The initial step of creating the snapshot is exactly the same in any type of the replication.

SQL Server does not obtain any kind of locks at all when creating a snapshot, it literally is a snapshot of the database at a certain point in time and creating snapshot does not interfere with any transactions. Uncommitted transactions are rolled back in the snapshot once it is created.

To read more about how database snapshot works read this article from MSDN How Database Snapshots Work

M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • Here's an excerpt from an old article found in this link: Concurrent Snapshot Processing Typically with snapshot generation, SQL Server will place shared locks on all tables published as part of replication for the duration of snapshot generation. This can prevent updates from being made on the publishing tables. Concurrent snapshot processing, available only with transactional replication, does not hold the share locks in place during the entire snapshot generation, therefore, it allows users to continue working uninterrupted while SQL Server 2000 creates initial snapshot files. – sydney Aug 28 '15 at 00:43
  • Sorry my comments aren't that clear. The above excerpt was found in this link: https://technet.microsoft.com/en-us/library/Aa179423%28v=SQL.80%29.aspx and I'm not sure if it still applies to SQL Server 2012. That's why I was a bit confused. – sydney Aug 28 '15 at 00:50
  • @sydney The article you have shared is for SQL Server 2000 and things changed a lot in 2005 and then later in 2008 not sure what version OP is on but I assumed OP is on 2008 and for 2008 this behaviour of shared locks was changed , [`How Transactional Replication Works 2008 R2`](https://technet.microsoft.com/en-us/library/ms151706(v=sql.105).aspx) if you read the section for `Concurrent Snapshot Processing` you will see it does not obtain any locks at all when generating initial snapshot. I appreciate your constructive criticism :) – M.Ali Aug 28 '15 at 00:55
  • @Sydney and to be perfectly honest I did not know about this behaviour of version 2000, thanks for sharing this, you have just taught me something I didn't know :) – M.Ali Aug 28 '15 at 00:57
  • So, snapshot is an overloaded term. The article linked to in the original answer is for database snapshots (i.e. `create database [foo] ... as snapshot of [bar]` while the OP was looking for replication snapshots (i.e. a method to initialize replication). Ironically, you can use the former in the service of the latter (see my answer elsewhere on this page). – Ben Thul Aug 28 '15 at 04:26
0

If you're running on an edition of SQL Server that supports database snapshots (as in create database [foo]... as snapshot of [bar]), then you can optionally use those as the basis of the snapshot. Check the @sync_method parameter of sp_addpublication. The caveat is that you still probably want to do it during a non-busy time of the day because of how database snapshots work (i.e. copy-on-write will slow down any write activity), but you won't be contending on locks.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • I think it's probably safer to run the initial snapshot (in my case) during off peak especially our OLTP application is very busy during business hours. From what I have been reading and from the links provided in above, BOL says there are no locks placed in concurrent snapshot processing which is the "default" in transactional replication and this pertains to SQL 2012. – sydney Aug 28 '15 at 12:41
  • You can also initialize from a backup of the publisher which avoid the snapshot altogether. – Ben Thul Aug 28 '15 at 13:58
0

Starting SQL Server 2005, the default @sync_method value for sp_addpublication is "concurrent", which means the tables are not locked during snaphsot agent run. Note this is not entirely true - the snapshot agent places schema locks on the tables, but the duration of that lock is mere seconds at most.

So if you set @sync_method = "concurrent", then no, updates, in theory, will not be blocked. If @sync_method = "native" (default in SQL Server 2000) or "character", then yes, updates will be blocked.

Greg
  • 3,861
  • 3
  • 23
  • 58
  • Is there anyway I can check the default @sync_method on my sql instance before I actually start configuring the replication? – sydney Aug 28 '15 at 13:55
  • that is a publication property, you set it when you create your publication. – Greg Aug 28 '15 at 16:23