accumulating the result of a snapshot replication filtered by date

hi all,

Im running a snapshot replication on table.

first, i gonna copy the entire table with a regional filter other than date

second i'm going to replicate a days worth of data by using both the regional filter and date filter (getdate) to capture a days wordth of data.

the publication are working fine.

my question is how am i going to accumulated the result of

the filtered snaphot replication in a single table in my db.

many thanks,

joey

Hi Joey,

You can configure your article to use 'delete' as the pre-creation command which corresponds to the 'Action if name is in use - Delete data. If article has a row filter delete only data that matches the filter' option on the article properties page. As the UI description implies, this will likely give you what you need although you need to watch out for the following:

1)

Using getdate() (and dateadd(getdate(),) most likely) to define a time range in your article filter may (or will) produce surprising result as the filter will be evaluated at different point in time as well as on different machines that don't have perfectly synchronized clocks. In the context of snapshot replication, the filter will be first evaluated at the publisher for the date range of data to be bcp'd out and then it is evaluated at a later time on the subscriber for the range of data that needs to be removed before the new data comes in. Since there is inevitably a time delay between when the snapshot is generated and when it gets delivered to the subscriber, the two date ranges will not coincide perfectly as you would hope.

Now, since time goes in only one direction, the worst that can happen to you is that the distribution agent will try to bcp in data that you already have at the subscriber (but not removed by the pre-creation command) at the lower end of your date range. This is unfortunately something that is not very well handled today as the distribution agent will likely fail with a primary key violation error while giving you very few options for recovery other than manually delete the overlapping data. However, if there are well-defined date gaps (say, off business hours) in your data, you can make the low end of your date range coincide with the gap. Note thta this can be tricky to maintain as the snapshot agent schedule and the date gap may change in the future.

From my perspective, the only robust way to achieve what you want is to keep track of the latest timestamp of the last snapshot iteration and use that to define an explicit date range you need in your filter before starting the next snapshot iteration. Here is a rough outline on how you may achieve this:

i. Call getdate() to get the upper bound of new date range

ii. Use the upper bound of the previous snapshot iteration and the new upper bound to define a new filter clause with the date values hardcoded and pass that into sp_articleview to generate a new synchronization view that the snapshot agent will use to bcp out data.

iii. Wait until the new snapshot is distributed to the subscriber and then update latest timestamp of the last snapshot at the publisher.

The last step is actually important because you may end up with a gap at the subscriber if your lower bound is defined to be too far ahead of what the subscriber has actually received. As such, it may be worthwhile to generalize the procedure above by keeping track of the upper bounds of the last couple of snapshot iterations and choose a more conservative lower bound to be further back in time (the pre-creation command should take care of removing any overlapping data).

At this point, you are probably wondering why we make what is arguably a common scenario so difficult, and I would say that you are absolutely right in thinking so. But given that things are what they are today, I can only encourage you to log a DCR at http://lab.msdn.microsoft.com/productfeedback/Default.aspx so we can allocate resources to make data archival scenarios simpler using snapshot replication.

2) (Well, I am not quite done yet :)

Since the subscriber data is most likely not empty when the distribution agent bcp data in, you will most likely not get the performance benefits of minimally-logged bulk-load. Because of this, you may need to be careful that you are not moving too much data in each snapshot iteration.

Hope that helps.

-Raymond