locked
Minimizing Locks during Transactional replication Snapshots - I'm running out of hair to pull RRS feed

  • Question

  • Found this while reading about Concurrent Snapshots

    "Foreign key constraints, check constraints, and triggers at the Subscriber do not require the NOT FOR REPLICATION option because they will be disabled during the concurrent snapshot generation and will be enabled after the snapshot is generated".

    Disabled where at the publisher?  Surely this would involve a significant amount of locking to accomplish and would explain the Blocking and Deadlocks I have been getting while attempting to generate a partial Snapshot.  Is this still the case with SQL2012 and later?

    Is there a deep dive into the Snapshot process and locks it takes?

    Here are my settings.  Can you spot any glaring mistakes?  Is there more I can do to prevent locking/blocking on my publisher during full and partial snapshots?  All I can think of now is that I should have MaxBcpThreads at 16 or something to minimize the time it takes to create the BCP files for each article. 

    :SETVAR filterClause 1=1 -- 1=0 no rows to move just structure for Fast Staging DW Build and compare
    :SETVAR MaxBcpThreads 1 -- Used to set the Agent Profile for the Snapshot Agent.  Set to 1 for Prod to avoid the locking we saw using the default since Sql Server 2012
    :SETVAR SyncMethod Concurrent -- Concurrent Concurrent for Tx Minimal Locking on Snapshot Single Thread Distribution;


    exec sp_addpublication @Publication=N'$(PublicationName)', 
    @description = N'Transactional publication of database ''$(PublicationDatabaseName)'' from Publisher ''$(PublicationServerName)''.',
    @sync_method = N'$(SyncMethod)',   -- Concurrent for Tx publ only is Native Mode with no Locks;  
     @retention = 0, 
     @allow_push = N'true',  
     @allow_pull = N'false', -- May 2015 Should this not be false?  not sure what impacts this might have on locking
     @allow_anonymous = N'false',     -- Must be False to have immediate_sync be false which is important (see immediate sync)
     @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'false',    -- *** use false to support Delta Snapshots.  
     @allow_sync_tran = N'false', 
     @autogen_sync_procs = N'false', 
     @allow_queued_tran = N'false', 
     @allow_dts = N'false', 
     @replicate_ddl = 1, -- *** To replicate DDL changes such as Column Changes to existing Tables.  FKs and Defaults suppressed by the Schema_Option 0x200, 0x800 in sp_addArticle calls
     @allow_initialize_from_backup = N'true', 
     @enabled_for_p2p = N'false', 
     @enabled_for_het_sub = N'false'

    As for my articles I do this for all of them

    exec sp_addarticle @Publication=N'$(PublicationName)',@Filter_Clause = '$(FilterClause)',  @article = N'Attachments', @source_owner = N'dbo', @source_object = N'Attachments', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x00000004080350DF, @identityrangemanagementoption = N'manual', @destination_table = N'Attachments', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false',  @ins_cmd = N'CALL [dbo].[sp_MSins_dboAttachments]', @del_cmd = N'CALL [dbo].[sp_MSdel_dboAttachments]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_dboAttachments]'

    @schema_option

    -- HexValue Schema Options Enabled
    -- 0x01 Generates object creation script
    -- 0x02 Generates procs that propogate changes for the article
    -- 0x04 Identity columns are scripted using the IDENTITY property
    -- 0x08 Replicate timestamp columns (if not set timestamps are replicated as binary)
    -- 0x10 Generates corresponding clustered index
    -- 0x40 Create corresponding nonclustered indexes
    -- 0x80 Replicate pk constraints
    -- 0x1000 Replicates column-level collation
    -- 0x4000 Replicates UNIQUE constraints
    -- 0x010000 Replicates CHECK constraints as NOT FOR REPLICATION so are not enforced during sync
    -- 0x020000 Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so are not enforced during sync
    -- 0x08000000 Creates schemas not present on subscriber
    -- 0x0400000000 Replicates compression option for data & indexes

    Any suggestions as to what to try next to avoid Blocking/Deadlocks?


    ...Ray

    Monday, May 11, 2015 8:57 PM

All replies

  • Could my nifty FilterClause trick be not so nifty and be causing me problems at Snapshot time?

    Should I avoid moving DDL across so that the Snapshot does not have to meddle with FKs and triggers and keep a minimal footprint?

    What will cause less locks in a concurrent snapshot against a busy 24/7 OLTP database, higher or lower MaxBCPThreads?


    ...Ray

    Monday, May 11, 2015 9:26 PM
  • https://technet.microsoft.com/en-us/library/aa179423(v=sql.80).aspx

    Foreign key constraints, check constraints, and triggers at the Subscriber do not require the NOT FOR REPLICATION option because they will be disabled during the concurrent snapshot generation and will be enabled after the snapshot is generated.

    • Edited by AV111 Tuesday, May 12, 2015 12:28 AM
    Tuesday, May 12, 2015 12:24 AM
  • They are disabled at the subscriber.

    Note that if you are using the enterprise edition of SQL Server you can generate a snapshot from a database snapshot. Use the sync_method 'database snapshot' in sp_addpublication.

    Have obtained a blocking report/deadlock report to see exactly what is blocking and deadlocking?


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Tuesday, May 12, 2015 2:02 AM
    Answerer
  • I find this answer very interesting but can't find much related documentation. I see that 'database snapshot' is one of the sync_methods available starting with SQL 2016. Why isn't it available when you create the replication from wizard?

    I tried on SQL 2012 too and MSPublications sync_method returns 5, the same value as on SQL 2016 so it may be supported on SQL 2012 too?

    Any further links or indication would be very much appreciated.

    ---

     Xabi.

    Tuesday, April 7, 2020 6:18 PM