By: Greg Robidoux | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | > Replication
Problem
SQL Server replication has been around for quite some time. I think just about every DBA has setup replication in some kind of test environment if not fully deployed this in a production environment. One of the issues that you are faced with when setting up transactional replication is getting your subscribers all of the initial data prior to replicating new transactions. There are various ways this can be done, but probably the most used technique is using the snapshot process.
When the normal snapshot process runs it captures all of the data from your tables and BCPs the data out to text files and in return BCPs the data into your subscriber databases. When doing transactional replication in SQL Server 2000 from SQL Server to SQL Server the default snapshot process uses the native method which locks the table that is being replicated until the entire snapshot is complete. Depending on the size of the table this could take some time and therefore block other processes trying to access the table until the snapshot is complete. So how can you get around this problem?
Solution
When adding a new transactional publication, SQL Server offers you many options to set for the publication. One of these options is the sync_method. This option lets you specify how the snapshot process works and also the locking behavior for the tables that are being replicated.
Here is the info about this option from SQL Server 2000 Books Online
| Method | Description |
| native (default) | Produces native-mode bulk copy program output of all tables. |
| character | Produces character-mode bulk copy program output of all tables. |
| concurrent | Produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot |
| concurrent_c | Produces character-mode bulk copy program output of all tables but does not lock tables during the snapshot. |
Here is the info about this option from SQL Server 2005 Books Online
| Method | Description |
| native | Produces native-mode bulk copy program output of all tables. Not supported for Oracle Publishers. |
| character | Produces character-mode bulk copy program output of all tables. For an Oracle Publisher, character is valid only for snapshot replication. |
| concurrent | Produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot. Only supported for transactional publications. Not supported for Oracle Publishers. |
| concurrent_c | Produces character-mode bulk copy program output of all tables but does not lock tables during the snapshot. Only supported for transactional publications. |
Depending on the types of database servers being used as well as the version of SQL Server that is being used the snapshot process can work quite differently and therefore cause blocking issues when the snapshot is produced.
The native method will bulk copy the data out of SQL Server, but locks the table until the process is complete. If the table is quite large and the activity is high on this table there can be a significant amount of blocking that can occur. With the concurrent method SQL Server does not lock the tables during the snapshot and allows you to continue to access the table while the snapshot is running.
In SQL Server 2000 when setting up transactional replication, the default for the sync_method is native, but in SQL Server 2005 this has been changed to concurrent. Here are some notes for SQL Server Books Online for both versions.
| SQL Server 2000 Books Online NULL (default) Defaults to native for Microsoft SQL Server Publishers. For non-SQL Server Publishers, defaults to character when the value of repl_freq is Snapshot and to concurrent_c for all other cases. |
| SQL Server 2005 Books Online Transactional Publications Use Concurrent Snapshots by Default In SQL Server 2000 concurrent snapshots were available for transactional publications, but they were not used by default. Concurrent snapshots reduce the amount of time that locks are held during snapshot generation, allowing users to work uninterrupted while snapshot files are created. In SQL Server 2005, concurrent snapshots are used by default; a value of 'concurrent_c' is used for the @sync_method parameter of sp_addpublication (Transact-SQL). |
SQL Server 2005
In the notes above from books online it mentions that is uses the concurrent_c method. When running some tests in SQL Server 2005 I noticed that it actually used the concurrent method as shown below.
| exec sp_addpublication @publication = N'Test2', @description = N'Transactional publication of database ''Test1'' from Publisher ''EDGEWOOD-NB3\SQL2005''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false' GO |
Also, when setting this up and using transactional replication in SQL Server 2005 between two SQL Servers the default process is to use stored procedures to replicate data between servers. If the delivery format is changed to not use stored procedures for Inserts, Updates and Deletes the sync_method is changed to native.
If this is scripted out and the sync_method is changed to concurrent and the publication recreated using this updated script this option stays as concurrent and replication seems to work without an issue.
SQL Server 2000
When setting up transactional replication in SQL Server 2000, the following command is generated when setting up a publication and not using stored procedures to replicate the data. By default the sync_method is native.
| exec sp_addpublication @publication = N'Test1', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication of Test1 database from Publisher EDGEWOOD-NB3\SQL2000.', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 336, @allow_queued_tran = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @logreader_job_name = N'EDGEWOOD-NB3\SQL2000-Test1-2' |
When trying to change this to the concurrent method and not using stored procedures to replicate the data you get the following error when the entire script is run to create the publication.:
| Server: Msg 21153, Level 16, State 1, Procedure sp_addarticle, Line 990 Cannot create article 'Table1'. All articles that are part of a concurrent synchronization publication must use stored procedures to apply changes to the Subscriber. |
So you can see there are some differences here between how the SQL Server 2000 and SQL Server 2005 transactional replication snapshot process is configured and works.
If you do use stored procedures to replicate the data you can change the sync_method to concurrent and the SQL Server 2000 publication creates with out any problems.
Next Steps
- Next time you setup transactional replication and you are using snapshots, check the sync_method option to ensure you are getting the most efficient configuration for creating your snapshot data.
- Be aware of the differences between SQL Server 2000 and SQL Server 2005 if you are migrating to SQL 2005.
- If you have very large tables and little time for downtime to setup subscribers look at using the concurrent sync_method.
- Thanks to Tom Willwerth and Joe Leboeuf for this tip idea
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
