I have an existing solution that includes Sync Services between an SQL2005 server and SQL/CE on a mobile device.
The current implementation consists of additional columns in each of the server tables for date created and date updated (both datetime) supported by tombstone tables containing the primary key and a datetime to manage the deletion of records similar to the following example...
CREATE TABLE [dbo].[users](
[id] [int] IDENTITY(1,1) NOT NULL,
[code] [varchar](30) NOT NULL,
[password] [varchar](30) NOT NULL,
[name] [varchar](60) NOT NULL,
[sync_date_created] [datetime] NOT NULL,
[sync_date_updated] [datetime] NULL,
CONSTRAINT [pk_user_profiles] PRIMARY KEY CLUSTERED ([code] ASC) ON [PRIMARY]) ON [PRIMARY]
ALTER TABLE [dbo].[users] ADD CONSTRAINT [DF_users_sync_date_created] DEFAULT (getutcdate()) FOR [sync_date_created]
CREATE TABLE [dbo].[users_tombstone](
[id] [int] NOT NULL,
[sync_date_deleted] [datetime] NOT NULL,
CONSTRAINT [pk_users_tombstone] PRIMARY KEY CLUSTERED ([sync_date_deleted] ASC, [id] ASC) ON [PRIMARY]) ON [PRIMARY]
ALTER TABLE [dbo].[users_tombstone] ADD CONSTRAINT [df_users_tombstone_sync_date_deleted] DEFAULT (getutcdate()) FOR [sync_date_deleted]
The updates and deletes are then managed using triggers...
ALTER TRIGGER [dbo].[users_update]
on [dbo].[users]
for update
as
update s
set sync_date_updated = getutcdate()
from dbo.users s
inner join inserted i
inner join deleted d on i.id = d.id
on s.id = i.id
where i.code != d.code
or i.password != d.password
or i.name != d.name
ALTER TRIGGER [dbo].[users_delete]
on [dbo].[users]
for delete
as
insert into dbo.users_tombstone (id)
select id
from deleted
Whilst inserts, updates and deletes are then selected using stored procedures similar to the following...
ALTER PROCEDURE [dbo].[UserSyncAdapter_SelectIncrementalInserts]
@sync_last_received_anchor datetime,
@sync_new_received_anchor datetime,
@sync_initialized bit
AS
set nocount on
select us.id, us.code as un, us.password as pw, us.name as nm
from dbo.users us
where us.sync_date_created > @sync_last_received_anchor
and us.sync_date_created <= @sync_new_received_anchor
order by us.id
return @@error
ALTER PROCEDURE [dbo].[UserSyncAdapter_SelectIncrementalUpdates]
@sync_last_received_anchor datetime,
@sync_new_received_anchor datetime,
@sync_initialized bit
AS
set nocount on
select us.id, us.code as un, us.password as pw, us.name as nm
from dbo.users us
where @sync_initialized = 1
and us.sync_date_created <= @sync_last_received_anchor
and us.sync_date_updated > @sync_last_received_anchor
and us.sync_date_updated <= @sync_new_received_anchor
ALTER PROCEDURE [dbo].[UserSyncAdapter_SelectIncrementalDeletes]
@sync_last_received_anchor datetime,
@sync_new_received_anchor datetime,
@sync_initialized bit
AS
set nocount on
select t.id
from dbo.users_tombstone t
where @sync_initialized = 1
and t.sync_date_deleted > @sync_last_received_anchor
and t.sync_date_deleted <= @sync_new_received_anchor
order by t.id
return @@error
order by us.id
return @@error
I recently (read today) had a situation where 91k+ updates were applied to the server database (well actually, they were all in the same table) and had to be reflected out to those devices and though I have grouped the tables, I still ended up with those 91k updates in the same group and thus ran out of memory on the device whilst deserialising.
My question is this: I would like to implement batching of updates to the devices, whilst maintaining the groups that are currently used. I have looked at various documentation for batching but none of the examples include use of SyncGroups or the use of datetime columns to retrieve the necessary updates.
Can anybody please point me to any better examples of how to use batching with SQL2005, stored procedures and SyncGroups or provide some examples of their own as to how I might introduce this without having to completely replace the current implementation?
Thanks in advance for all or any help.