1

I've got transactional replication configured from a database called DBProd to another database called DBWarehouse ; everything works fine, and transaction are usually replicated instantaneously to the warehouse .... which is my problem.

I'd like to add a slight delay to the replication (something like 10 minutes), so that the replicated database can be used to access a previous version of the database (in case a bug occurs for example)

Is there a simple way to achieve this ?

Brann
  • 31,689
  • 32
  • 113
  • 162

2 Answers2

2

There is not a way to add a delay per transaction. You can change the pollinginterval parameter for the distribution agent (http://technet.microsoft.com/en-us/library/ms147328.aspx) to be longer, but all transactions made up until the polling time would be moved.

Note that delaying the polling interval also delays the ability of you to clear out the inactive portions of the transaction log. The log records will not be inactive until they are moved, so plan for that.

This is similar to the log shipping delay. Everything up until the log backup is sent over and all restored at that time.

If you are looking for something like 1:00 make change A on primary 1:10 make change b on primary 1:30 move change A to secondary 1:40 move change B to secondary

you cannot do this. The transactions are moved in batches, not according to some delay based on that time of the transaction commit.

way0utwest
  • 644
  • 1
  • 6
  • 8
0

You should be able to do this in the Subscriber Scheduling options.

Neil Knight
  • 47,437
  • 25
  • 129
  • 188
  • I haven't found where to do that yet. The job is constantly running to replicate transactions, and launching it periodically wouldn't achieve the desired result (ie if a modification is done right before the scheduled update time, it will be replicated almost immediately) – Brann May 04 '10 at 16:13
  • Here is a nice step-by-step walkthrough: http://www.codeproject.com/KB/database/sql2005-replication.aspx It has the Synchronization Schedule (at the end) where you can configure the settings. – Neil Knight May 04 '10 at 16:24
  • @Ardman : thanks ; but I can't see how this would address the issue raised in my comment (ie if a modification is done right before the scheduled update time, it will be replicated almost immediately).Wouldn't that be a problem ? – Brann May 04 '10 at 16:32
  • @Brann: my understanding of the delay was that it wouldn't replicate until the time has passed. I maybe wrong with this one. – Neil Knight May 05 '10 at 07:26