Experience in designing SQL Server Data Synchronization

Original article address:

Http://www.cnblogs.com/thtfria/archive/2012/04/04/2432167.html

 

However, this project has been in the past n days, but the data synchronization section was initially some of my design ideas. This project is a one-card project of an electric power company. In the future, it will be required in more than 20 factories in China.

The basic data is stored in the Headquarters according to the project requirements, and then synchronized to each factory. The detailed requirements are not described in detail. Go to the topic.

Synchronization Method: synchronize data between databases.Program.

Steps:

1. Create a trigger for the table at the headquarters to record the actions of adding, deleting, and modifying tables, record the action to an intermediate table (the intermediate table requires a synchronization field to indicate whether the data has been synchronized)

Create trigger [tg_jc_zzjg] on [DBO]. [jc_zzjg]
For insert, delete, update
As
Begin
-- Insert 1
If exists (select 1 from inserted) and not exists (select 1 from deleted)
Begin
Insert into t_jc_zzjg
(Czlx, BH, MC, QMC, sjbh, lxr, lxdh, lxdz, BZ, gsbj, gsbh)
Select 1, I. BH, I. MC, I. QMC, I. sjbh, I. lxr, I. lxdh, I. lxdz, I. BZ, I. gsbj, I. gsbh
From inserted I
End
-- Update 2
If exists (select 1 from inserted) and exists (select 1 from deleted)
Begin
Insert into t_jc_zzjg
(Czlx, BH, MC, QMC, sjbh, lxr, lxdh, lxdz, BZ, gsbj, gsbh)
Select 2, U. BH, U. MC, U. QMC, U. sjbh, U. lxr, U. lxdh, U. lxdz, U. BZ, U. gsbj, U. gsbh
From inserted u
End
-- Delete 3
If not exists (select 1 from inserted) and exists (select 1 from deleted)
Begin
Insert into t_jc_zzjg
(Czlx, BH, gsbh)
Select 3, D. bh, D. gsbh
From deleted d
End
End

2. Establish remote connection between branches

// Add a remote connection

Exec sp_addmediaserver @ Server = 'zbezcas ', @ srvproduct = '', @ provider = 'sqloledb', @ provstr = 'driver = {SQL Server}; server = '2017. 0.0.1 '; uid = 'sa'; Pwd = 'sa ';

// Set the login name for the connection

Exec sp_add1_srvlogin 'zbezcas ', 'false', null, 'sa', 'sa'

3. Create synonyms for sub-factories to simplify table access (this step is just a simplified operation, and it doesn't mean too much)

Create synonym t_jc_zzjg for zbezcas. ezcas. DBO. t_jc_zzjg

4. Execute synchronization data in the factory, and mark the write-back synchronization as "synchronized"

The specific synchronization method uses the stored procedure to directly synchronize the data that has not been synchronized in the t_jc_zzjg (simplified remote table) Table. After synchronization, the write-back synchronization mark is used.