Monthly Archives: January 2013

Replication without Snapshot Initialisation

Recently I had a requirement for a customer that was not really “by the book”.
The requirement was to replicate a table, but only replicate anything new coming in, no historical data. The table has close to 7 Billion rows and is several Terabytes big.
Replicating all the historical data would kill the target server which just hasn’t the capacity. The target server will only keep a rolling 30 days of data and discard everything older.
That said, I had to find a way replicating all new transactions without the need of a Snapshot or Backup initialisation. This is not as straight forward as it sounds, but then again also not really too complicated, once you know how.
After some trial-and-error on a test lab installation I got it going. Here is an account of how-to:

Create the schema on the target server

To use replication without snapshot initialisation you will have to need all the data structures set up before you start. Create the target table on the target server. Don’t forget to plan how to handle identity columns. (If you need some data in there, also transfer and – if need be – modify the data to your needs.)

Create publication on source server

What I like to do is to use the wizard to create my Publications but not actually create the publication, just create the scripts. Then check and modify them before actually creating the publication. (And the same for subscriptions)

exec sp_addpublication 
         @publication = N'ReplWoSnapshot'
        ,@description = N'Transactional publication of database ''Source'' from Publisher ''SourceServer''.'
        ,@sync_method = N'concurrent'
        ,@retention = 0
        ,@allow_push = N'true'
        ,@allow_pull = N'true'
        ,@allow_anonymous = N'false'
        ,@enabled_for_internet = N'false'
        ,@snapshot_in_defaultfolder = N'true'
        ,@compress_snapshot = N'false'
        ,@ftp_port = 21
        ,@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'
        ,@allow_sync_tran = 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'
        ,@allow_partition_switch = N'true'
        ,@replicate_partition_switch = N'false'

Important here is that Immediate Synchronosation is off. Also switch of Initialisation from Backup.
Note: The last two parameters are needed in this case since it is a partitioned table I am replicating and I want to still be able to switch partitions on the source database without generating errors. But I don’t need the partition switch to be replicated. The target database will have its own split-switch-merge logic.

exec sp_addpublication_snapshot 
         @publication = N'ReplWoSnapshot'
        ,@frequency_type = 1
        ,@frequency_interval = 1
        ,@frequency_relative_interval = 1
        ,@frequency_recurrence_factor = 0
        ,@frequency_subday = 8
        ,@frequency_subday_interval = 1
        ,@active_start_time_of_day = 0
        ,@active_end_time_of_day = 235959
        ,@active_start_date = 0
        ,@active_end_date = 0
        ,@job_login = N'login'
        ,@job_password = 'pwd'
        ,@publisher_security_mode = 1

The snapshot agent is still created as usual even though it won’t be used.

exec sp_addarticle 
          @publication = N'ReplWoSnapshot'
        , @article = N'Source Table'
        , @source_owner = N'dbo'
        , @source_object = N'Source Table'
        , @type = N'logbased'
        , @description = null
        , @creation_script = null
        , @pre_creation_cmd = N'drop'
        , @schema_option = 0x00000000081B509F
        , @identityrangemanagementoption = N'manual'
        , @destination_table = N'Source Table'
        , @destination_owner = N'dbo'
        , @vertical_partition = N'false'
        , @ins_cmd = N'CALL sp_MSins_dboSourceTable'
        , @del_cmd = N'CALL sp_MSdel_dboSourceTable'
        , @upd_cmd = N'SCALL sp_MSupd_dboSourceTable'

Also all the articles are added as usual.

Create subscription

Again, I used the wizard to create the subscription, but only let it generate the scripts. In my case I created a Pull subscription, but it should also work with a Push subscription.

First add it on the distributor (= soruce server in this case)

exec sp_addsubscription 
         @publication = N'ReplWoSnapshot'
        ,@subscriber = N'Target Server'
        ,@destination_db = N'Source Dtaabase'
        ,@sync_type = N'replication support only'
        ,@subscription_type = N'pull'
        ,@update_mode = N'read only'

Here lies the most important part of the process: the Synchronisation Type. You need to use Replication Support Only. This ensures that no initialisation is attempted, just anything coming in will be replicated without questions.

Now add the subscription on the target server.

exec sp_addpullsubscription 
		 @publisher = N'Source Server'
		,@publication = N'ReplWoSnapshot'
		,@publisher_db = N'Source DB'
		,@independent_agent = N'True'
		,@subscription_type = N'pull'
		,@description = N''
		,@update_mode = N'read only'
		,@immediate_sync = 0

Again, Immediate Synchronisation must be switched off.

exec sp_addpullsubscription_agent 
		 @publisher = N'source server'
		,@publisher_db = N'Source DB'
		,@publication = N'ReplWoSnapshot'
		,@distributor = N'Source Server'
		,@distributor_security_mode = 1
		,@distributor_login = N''
		,@distributor_password = null
		,@enabled_for_syncmgr = N'False'
		,@frequency_type = 64
		,@frequency_interval = 0
		,@frequency_relative_interval = 0
		,@frequency_recurrence_factor = 0
		,@frequency_subday = 0
		,@frequency_subday_interval = 0
		,@active_start_time_of_day = 0
		,@active_end_time_of_day = 235959
		,@active_start_date = 20130111
		,@active_end_date = 99991231
		,@alt_snapshot_folder = N''
		,@working_directory = N''
		,@use_ftp = N'False'
		,@job_login = N'login'
		,@job_password = 'pwd'
		,@publication_type = 0

The subscription agent is then added as usual.

With these few steps the replication is fully configured and running. New transactions from the creation of the Publication on are replicated to the subscriber without any need of initialisation, be it from Snapshot or from Backup.

But there is one more thing you should do!
You need to be aware that a lot of data is actually missing in the target database. So every Update and Delete on a record in the source database that is not available in the target database will cause an error in the replication and acutally stop the replication.
Even when you are sure only Inserts happen on the source server, you should take precautions for transactions being executed for data that is not available on the target system.

Skip Consistency Errors in Replication

In order to avoid these errors (or actually tell the replication to ignore them) you need to use Agent Profiles.
Please find a step-by-step guide here on
How to: Work with Replication Agent Profiles (SQL Server Management Studio)
For this particular distribution we need to use an Agent Profile called Continue On Data Consistency Errors.
To use this profile:

  • Open the Replication Monitor
  • Right-Click on the distribution and choose Agent Profiles
  • Tick the box next to Continue On Data Consistency Errors
  • Click the button Change Existing Agents (if you just click OK, only newly created Agents will use this profile).
  • Caveat 1:

    Please be aware that Replication without Initialisation might not be the best solution for your requirements (or even this requirement described here). In fact Service Broker might provide a more suitable solution or some ETL process or something hand-made.
    Please always look into all kinds of solution and don’t just take the first that offers itself (we do that often enough anyway). It might not be the best one.

    Caveat 2:

    The description given here is no full step-by-step guide. It pre-requisits some knowledge of SQL Server replication. Some obvious steps are omitted, others vaguely described.
    Please always refer to the SQL Server documentation for full description of all parameters and steps.

    %d bloggers like this: