Hi,
I setup a sync between a windows form application with a sql server 2008 database. I tried to use the batch sp from
http://msdn.microsoft.com/en-us/library/bb902828.aspx, but I found it requires to add updatetimestamp and inserttimestamp. In my current enviornment, we are not allowed to change table schema on server side. we can enable change track for specific table. I also change the script based on the one in MSDN, and want to know whether it makes sense.
Table
CREATE TABLE [SyncTest].[OrderTable](
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Number] [int] NOT NULL,
[UserId] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SP
CREATE PROCEDURE usp_GetNewBatchAnchor (
@sync_last_received_anchor bigint,
@sync_batch_size bigint,
@sync_max_received_anchor bigint out,
@sync_new_received_anchor bigint out,
@sync_batch_count int output)
AS
insert into ParameterList (p1,p2,p3,p4,p5)
values (@sync_last_received_anchor,
@sync_batch_size,
@sync_max_received_anchor,
@sync_new_received_anchor,
@sync_batch_count)
-- Set a default batch size if a valid one is not passed in.
IF @sync_batch_size IS NULL OR @sync_batch_size <= 0
SET @sync_batch_size = 1000
-- Before selecting the first batch of changes,
-- set the maximum anchor value for this synchronization session.
-- After the first time that this procedure is called,
-- Synchronization Services passes a value for @sync_max_received_anchor
-- to the procedure. Batches of changes are synchronized until this
-- value is reached.
IF @sync_max_received_anchor IS NULL
--for tables which don't have timestamp columns.
--SELECT @sync_max_received_anchor = MIN_ACTIVE_ROWVERSION() - 1
SELECT @sync_max_received_anchor = CHANGE_TRACKING_CURRENT_VERSION()
-- If this is the first synchronization session for a database,
-- get the lowest timestamp value from the tables. By default,
-- Synchronization Services uses a value of 0 for @sync_last_received_anchor
-- on the first synchronization. If you do not set @sync_last_received_anchor,
-- this can cause empty batches to be downloaded until the lowest
-- timestamp value is reached.
IF @sync_last_received_anchor IS NULL OR @sync_last_received_anchor = 0
BEGIN
--SELECT @sync_last_received_anchor = MIN(TimestampCol) FROM (
-- SELECT MIN(UpdateTimestamp) AS TimestampCol FROM Sales.Customer
-- UNION
-- SELECT MIN(InsertTimestamp) AS TimestampCol FROM Sales.Customer
-- UNION
-- SELECT MIN(UpdateTimestamp) AS TimestampCol FROM Sales.OrderHeader
-- UNION
-- SELECT MIN(InsertTimestamp) AS TimestampCol FROM Sales.OrderHeader
--) MinTimestamp
--SELECT @sync_last_received_anchor = MIN(TimestampCol) FROM (
-- SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.TestTable')) AS TimestampCol -- MIN(UpdateTimestamp) AS TimestampCol FROM dbo.TestTable
-- --UNION
-- --SELECT MIN(InsertTimestamp) AS TimestampCol FROM dbo.TestTable
--) MinTimestamp
select @sync_last_received_anchor = MIN(TimestampCol) from
(
select --TABLE_SCHEMA+'.'+TABLE_NAME as name,
--object_id(TABLE_SCHEMA+'.'+TABLE_NAME) as id,
CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME)) AS TimestampCol
from INFORMATION_SCHEMA.TABLES
--WHERE TABLE_SCHEMA = 'dbo'
) as timetable
SET @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size
-- Determine how many batches are required during the initial synchronization.
IF @sync_batch_count <= 0
SET @sync_batch_count = ((@sync_max_received_anchor / @sync_batch_size) - (@sync_last_received_anchor / @sync_batch_size))
END
ELSE
BEGIN
SET @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size
-- Determine how many batches are required during subsequent synchronizations.
IF @sync_batch_count <= 0
SET @sync_batch_count = ((@sync_max_received_anchor / @sync_batch_size) - (@sync_new_received_anchor / @sync_batch_size)) + 1
END
-- Check whether this is the last batch.
IF @sync_new_received_anchor >= @sync_max_received_anchor
BEGIN
SET @sync_new_received_anchor = @sync_max_received_anchor
IF @sync_batch_count <= 0
SET @sync_batch_count = 1
END
insert into ParameterList (p1,p2,p3,p4,p5)
values (@sync_last_received_anchor,
@sync_batch_size,
@sync_max_received_anchor,
@sync_new_received_anchor,
@sync_batch_count)
GO
/*
GRANT EXEC ON usp_GetNewBatchAnchor TO PUBLIC
GO
*/
There are couple places I made change.
1: I used CHANGE_TRACKING_CURRENT_VERSION() instead of MIN_ACTIVE_ROWVERSION() because I don't have updatestamp and inserttimestamp in table.
2: I used CHANGE_TRACKING_MIN_VALID_VERSION() function and INFORMATION_SCHEMA.TABLES system view to find out the lowest row from all tables.
another quesiton regarding change track auto clean is that there is a time window in SQL 2008 to clean track change automatically. If we fully rely on change track, we are going to lost some date if client doesn't sync with server with this time window. Anyone knows how to prevant it without change table schema?
Thanks advance
Frank
