how to Synchronize Remote Server DB with Local DB

Question

I would like to get all the details of a single table from Remote server DB to my local DB, during
page load event or some other good approach, which should happen as a back end process can any one help me over this issue.

1. Single Application created in Desktop and Web Application.
2. When User enroll new customer in Desktop Application that new Customer should be added in the Web Application Db when the Application is started.

NOTE:

Server DB table Columns may slightly differ from local DB. Each time when a new user is added in the server, it should update the local DB when the UserPage.aspx page is loaded.

Tools using: ASP.NET,SQL SERVER 2008.

Eg: Let the DB name be sample and the table name is customer

Table Header in Server DB: Cus_id,Cus_name,Cus_address,Cus_email,Cus_mob,Link_Id
Table Headers in Local DB: Cus_id,Cus_name,Cus_address,Cus_email,Cus_mob,Cus_password,Link_Id

here the Link_id is used as common for Desktop and web Application..initially in web application,
when a new user is added all the data are stored in DB except the
Link_id, which is get as a response from the server and save in the local DB.

Thanks.

Answer 1

you can use ms sync framework 2.1 it allows user to synchronize from both side(client-server)...and you can schedule sync fun call

Answer 2

I would recommend this approach:

1. Create a staging table in your local database;
2. Create a trigger in your local database on change of the tables you need to sync;
3. Update the staging table;

4. Synchronize the staging table to the server once in a while (one per minute / hour / day, depending on your needs);

   A) Create a linked database connection in your local database. Create a procedure that synchronizes the data from the staging table to the server database;

   B) Or sync the database using ASP.NET by reading the local database and writing to the server database.

This solution is better then doing this directly in ASP.NET, because when you have availability problems with your server, this will still work.

A full working example:

create table x
( id          numeric(18, 0) identity(1,1)  not null
, description nvarchar(1000)                not null
)
go

create table x_staging
( id          numeric(18, 0) not null
, description nvarchar(1000) not null
, synced      bit            not null default 0
)
go

/*
 * create this one on remote server in a database called test

create table remote_table
( id          numeric(18, 0) identity(1,1)  not null
, source_id   numeric(18, 0)                not null
, description nvarchar(1000)                not null
)
go
*/

create trigger x_ori on x
after insert
as
begin
  insert into x_staging
  ( id
  , description
  , synced
  )
  select id
  ,      description
  ,      0 -- false
  from   inserted
  ;
end
go

create procedure sync
as
begin
  declare @id numeric(18,0)
  declare @description nvarchar(1000)
  declare @x_cursor cursor

  set     @x_cursor = cursor for
  select  id
  ,       description
  from    x_staging

  open    @x_cursor
  fetch next
  from  @x_cursor into @id, @description
  while @@fetch_status = 0
  begin
    insert
    into   [REMOTE_SERVER].test.dbo.remote_table
    ( source_id
    , description
    )
    values
    ( @id
    , @description
    )
    ;
    update x_staging
    set    synced = 1
    where  id = @id
    ;
  fetch next
  from @x_cursor into @id, @description
  end

  close @x_cursor
  deallocate @x_cursor
end
go

insert
into   x
( description
)
values
( 'test'
)
go

begin
  exec sync;
end

Calling sync will do the synchronization. Be aware to create the remote_table on the other server and create a database link.