How to use 'database snapshot' sync_mode while creating a new Tran/snapshot publication using sp_addpublication

  • Article

Writing this blog post to ensure other can try - how to use 'database snapshot' mode of snapshot generation (i.e. @sync_mode parameter value of sp_AddPublication). I am jotting down steps to ensure that when the snapshot agent runs, it will produce the snapshot from a "database snapshot".

Some description about 'database snapshot' option for @sync_mode parameter.

<BOL>
[ @sync_method=] 'sync_method'
Is the synchronization mode. sync_method is nvarchar(13), and can be one of the following values.

database snapshot
 Produces native-mode bulk copy program output of all tables from a database snapshot. This option requires SQL Server 2005 Enterprise Edition Service Pack 2 or a later version.
 
database snapshot character
 Produces character-mode bulk copy program output of all tables from a database snapshot. This option requires SQL Server 2005 Enterprise Edition Service Pack 2 or a later version.
</BOL>

When the snapshot agent runs, it places Shared Lock on all the published tables. This can prevent This can prevent updates from being made on the publishing tables.
To solve this problem, concurrent snapshot processing (@sync_mode parameter value set to 'concurrent' option) was introduced. In SQL Server 2005, Concurrent snapshot is by default.

The option of 'database snapshot' was introduced in SQL Server 2005 Service Pack 2. This was specially meant for those situations where using Concurrent snapshot was not possible. Concurrent snapshot is not an option for snapshot publications.

Resolution:
 Here is a step-by-step way of how you can confirm if the 'database snapshot' is being utilized:
  > Use the following command to change your publication's @sync_mode parameter (@sync_mode parameter is not available in Publication Addition GUI (SSMS) screens) on the published database:
  sp_changepublication '<Publication Name>', 'sync_method', 'database snapshot', 1, 0
 
  Note: Changing @sync_mode parameter value invalidates current snapshot and new snapshot must be generated.
  > Create a database snapshot on the published database using following command:
   CREATE DATABASE <db snapshot name> ON (NAME = <published db name>, FILENAME = '<filepath and name>')
   AS SNAPSHOT OF <published db name>;
   GO
 
  Note: Its not necessary to set the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION at the db level
  Note: Even if you publish 1 small table from this database, the snapshot is taken at the entire db level. The database snapshot generation process is usually very quick.
 
  > Run the snapshot agent
 
  If you trace snapshot processing using profiler, you will see a different database name when the actual data from published table is being exported by snapshot.
 
  As an example, look at the following event capture from profiler. Please concentrate on the 'DatabaseName' column here:
  <TRACE>
  *****Sync-Mode when set to 'Database Snapshot'*******
  Event: SQL:StmtCompleted 
  TextData: select * from [dbo].[syncobj_0x3331303743363532] where ([col1] > 611677) and ([col1] <= 739154) order by [col1] ASC 
  Application Name: Snapshot BCP thread #2 for publication TestTab 
  DatabaseID: 49 
  DatabaseName: Replication Snapshot - TestTab - pubs 
 
  *****Sync-Mode when set to 'Native/Character'*******
  Event: SQL:StmtCompleted
  TextData: select * from [dbo].[syncobj_0x3331303743363532] where ([col1] > 739154) and ([col1] <= 872875) order by [col1] ASC
  Application Name: Snapshot BCP thread #2 for publication TestTab 
  DatabaseID: 23 
  Database Name: pubs
  </TRACE>

The above steps are purely out of my testing and there can be another way to make it work.