I have seen a couple interesting posts around batching wtih datetime these days. as Jin said, this approach is possible techinically. however I think it is worth to emphasize that timestamp or min_active_rowversion is the highly recommended anchor type to accuracy and data convergence's puspose. there are a lot of sample code using Datetime as the anchor type as this is the easy and most straitfoward way to show the idea of sync services and for demo purposes.
Also, the new db sync providers shipped with MSF V2 (
http://msdn.microsoft.com/en-us/sync/default.aspx ) has much robust batching support so I also suggest to evaluate that to see if this can be a better fit to your sceanrio and business needs.
BTW, I have played with the batching with anchor type a bit and proved ( as the concept level ) that it can be used with batching, there are a few issues ( and some a bit difficult to resolve ) around it and I believe there will be other issues too if we play with it further -- that is also a reason I wont' recommend this.
below is the stord proc I used.
/*
* Important Note: this script is merely used to verify that datetime can be used as anchor type for Sync Service V1 batching.
* Datetime as anchor is hightly NOT recommended for sync services, regardless batching is used or not
* This proc assumes 1 second for batch size as default and '1901-01-01 00:00:000' is the earliest datetime used in your tracking layer
*
*/
create procedure dbo.sp_new_batch_anchor (
@sync_last_received_anchor datetime,
@sync_batch_size Int,
@sync_max_received_anchor datetime output,
@sync_new_received_anchor datetime output,
@sync_batch_count Int output)
as
print @sync_last_received_anchor
print @sync_batch_size
print @sync_max_received_anchor
print @sync_new_received_anchor
print @sync_batch_count
-- convert the datetime value to timestamp for easy comuputing
declare @sync_last_received_anchor_dbts bigint
declare @sync_max_received_anchor_dbts bigint
declare @sync_new_received_anchor_dbts bigint
declare @minDateTimeVal bigint
declare @minDateTime datetime
declare @maxDateTimeVal bigint
declare @maxDateTime datetime
declare @tempBatchCount bigint
print 'start proc'
select @minDateTime = '1901-01-01 00:00:000'
select @minDateTimeVal = convert ( bigint, convert( timestamp, convert ( binary(8), @minDateTime ) ) )
select @maxDateTime = getdate()
select @maxDateTimeVal = convert ( bigint, convert( timestamp, convert ( binary(8), @maxDateTime ) ) )
select @tempBatchCount = @sync_batch_count
print 'min and max datetime values'
print @minDateTime
print @minDateTimeVal
print @maxDateTime
print @maxDateTimeVal
if ( @sync_last_received_anchor is null OR @sync_last_received_anchor <= @minDateTime )
select @sync_last_received_anchor_dbts = @minDateTimeVal
else
select @sync_last_received_anchor_dbts = convert ( bigint, convert ( timestamp, convert ( binary(8), @sync_last_received_anchor) ) )
print 'last rec anchor value'
print @sync_last_received_anchor_dbts
if ( @sync_max_received_anchor is null OR @sync_max_received_anchor <= @minDateTime )
select @sync_max_received_anchor_dbts = @maxDateTimeVal
else
select @sync_max_received_anchor_dbts = convert ( bigint, convert (timestamp, convert ( binary(8), @sync_max_received_anchor ) ) )
if @sync_batch_size is null or @sync_batch_size <= 0
set @sync_batch_size = 300 -- 1 SECOND
print 'batch size : '
print @sync_batch_size
-- simplest form of batching
if @sync_last_received_anchor_dbts is null or @sync_last_received_anchor_dbts <= @minDateTimeVal
begin
print 'simple batching for inital sync'
set @sync_new_received_anchor_dbts = @maxDateTimeVal
print @sync_new_received_anchor_dbts
print 'get batch count'
if @sync_batch_count is null or @sync_batch_count <= 0
set @tempBatchCount = (@sync_max_received_anchor_dbts / @sync_batch_size) + 1
end
else
begin
print 'simple batching for subsequent sync'
set @sync_new_received_anchor_dbts = @sync_last_received_anchor_dbts + @sync_batch_size
if @sync_batch_count is null or @sync_batch_count <= 0
begin
print 'get batch count'
set @tempBatchCount = (@sync_max_received_anchor_dbts / @sync_batch_size) - (@sync_new_received_anchor_dbts / @sync_batch_size) + 1
end
end
--- ensure batch count is Int32 value
--- better logic will be needed
print 'temp batch count:'
print @tempBatchCount
if ( @tempBatchCount > 1000 )
set @sync_batch_count = 100;
else
set @sync_batch_count = @tempBatchCount
print @sync_batch_count
-- check if this is the last batch
if @sync_new_received_anchor_dbts >= @sync_max_received_anchor_dbts
begin
set @sync_new_received_anchor_dbts = @sync_max_received_anchor_dbts
if @sync_batch_count <= 0
set @sync_batch_count = 1
end
print @sync_batch_count
print 'max rec and new rec anchor dbts values'
print @sync_max_received_anchor_dbts
print @sync_new_received_anchor_dbts
print 'convert back to datetime for return values'
-- convert the values back to datetime before proc returns
select @sync_max_received_anchor = convert ( datetime, convert (timestamp, convert ( binary(8), @sync_max_received_anchor_dbts ) ) )
select @sync_new_received_anchor = convert ( datetime, convert (timestamp,convert ( binary(8), @sync_new_received_anchor_dbts ) ) )
print 'max rec and new rec anchor date values'
print @sync_max_received_anchor
print @sync_new_received_anchor
-- end of proc
This posting is provided "AS IS" with no warranties, and confers no rights.
