views:

163

answers:

0

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.