Large Volume Data Synchronization Process Between Central Database (Remote) and Branch Database

Background

Since last week, I have faced some problems while generating some comparison reports from different branches. I have noticed all of my branches are on each individual databases, due to which I can’t give a comparison report to my management.

So I decided to merge my entire database within one database and all the time, it will synchronize with Central DB.

Using the Code

In this process, you need to follow a process.

1. Create a similar Temp Database (for which database you want to sync) in branch Server .
2. Create trigger for insert, update and delete and put data into Temp DB on your local server that's why you will save from any interruption of internet services.
3. Create a scheduler to send data into your Central Database.
4. Create a Central Database with your own structure. It will be same or different than the branches.
5. Then you can get any data from Central DB.

How to Create Temp DB?

It's simple.

1. Right click on database
2. Click on Task-> Generate Scripts
3. Next->Next->Next.... you will get a script

Then, run this script on your newly database and it will create a similar database.

2. Create Trigger for Insert Update Delete

//Insert Trigger

USE [TestDataBase]
GO
/****** Object:  Trigger [dbo].[trgAfterInsert]    Script Date: 12/05/2014 16:45:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trgAfterInsert] ON [dbo].[tblPerson] 
FOR INSERT
AS
    declare @empid int;
    declare @empname varchar(100);
    declare @branch decimal(10,2);

    select @empid=i.day_id from inserted i;    
    select @empname=i.day_name from inserted i;    
    select @branch='12';
BEGIN DISTRIBUTED TRANSACTION

           
       INSERT INTO temp_tblPerson(day_id, day_name,Operations)
       SELECT 
           *,Operations='IN'
       from inserted

    PRINT 'AFTER INSERT trigger fired.'
COMMIT TRAN


//Update Trigger
USE [TestDataBase]
GO
/****** Object:  Trigger [dbo].[tblPerson_update]    Script Date: 12/05/2014 16:45:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tblPerson_update] 
   ON  [dbo].[tblPerson] 
   AFTER UPDATE
AS 
BEGIN
  declare @day_id int;
    declare @day_name nvarchar(100);

    SELECT @day_id =i.day_id from inserted i; 
    SELECT @day_name =i.day_name from inserted i;
    update temp_tblPerson set day_name=@day_name,Operations='UP' where day_id=@day_id
END

//Delete Trigger

USE [TestDataBase]
GO
/****** Object:  Trigger [dbo].[tblPerson_BeforeDelete]    Script Date: 12/05/2014 16:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tblPerson_BeforeDelete]
    ON [dbo].[tblPerson]
    FOR DELETE
AS
  BEGIN
    INSERT INTO temp_tblPerson(day_id, day_name,Operations)
       SELECT 
           d.day_id, d.day_name,Operations='DL' 
       FROM deleted d 
  END

Some problems can arise how to trace, its update or delete command from Temp DB when it will send data to centralDB?

So just create an extra column of your tempTable that is operationsType. Then, you can easily execute your command on central DB. I have created a columns Operations to do that.

Operations='DL'

Now you need to create a Scheduler to Send Data in CentralDB.

First, you need to create a Link server to do that.

Follow the process to create a Link Server.

http://www.codeproject.com/Articles/35943/How-to-Config-Linked-Servers-in-a-Minute

After Create Link Server, you need to create a scheduler to send this data to server.

1. First goto SQL Server Object->
2. Goto Jobs-> Right Click
3. Create New Job
4. Fill up all

a. General->Steps->Schedules and then save

Create a procedure that will insert, update or delete data from CentralDB from TempDB.

In New Schedule, you can write SQL, Execute procedure in steps tab where you will insert, update or delete your data from your remote service.

Thanks!