SQL data synchronization

--- Trigger Synchronization

/*
Author: zhujian
*/

/* -- Example of synchronizing two databases

Data available
Srv1. the database name. author has the following fields: id, name, phone,
Srv2. database name... author has fields: id, name, telphone, adress

Requirements:
Srv1. database name... author add record, then srv1. database name... author record is added
If the phone field of srv1. library name... author is updated, the telphone field of srv1. library name... author is updated.
--*/

-- General handling steps
-- 1. Create a connection server on srv1 to operate srv2 in srv1 for synchronization
Exec sp_addrole server 'srv2', '', 'sqloledb', 'SQL Instance name or ip address of srv2'
Exec sp_add1_srvlogin 'srv2', 'false', null, 'username', 'Password'
Go

-- 2. Start msdtc (Distributed Transaction Processing Service) in the srv1 and srv2 brains and set it to Automatic startup.
My computer -- control panel -- Administrative Tools -- service -- Right-click Distributed Transaction Coordinator -- Property -- start -- and set the start type to automatic start
Go

-- 3. synchronous processing

-- A. Create a trigger in srv1. author to implement real-time data synchronization.
-- Add Synchronization
Create trigger tr_insert_author on author
For insert
As
Set xact_abort on
Insert srv2. database name. dbo. author (id, name, telphone)
Select id, name, telphone from inserted
Go

-- Modify Synchronization
Create trigger tr_update_author on author
For update
As
Set xact_abort on
Update B set name = I. name, telphone = I. telphone
From srv2. database name. dbo. author B, inserted I
Where B. id = I. id
Go

-- Delete Synchronization
Create trigger tr_delete_author on author
For delete
As
Set xact_abort on
Delete B
From srv2. database name. dbo. author B, deleted d
Where B. id = d. id
Go

 

-- 3. synchronous processing method 2: timed synchronization

-- Create the following synchronization processing stored procedure in srv1
Create proc p_process
As
-- Update modified data
Update B set name = I. name, telphone = I. telphone
From srv2. database name. dbo. author B, author I
Where B. id = I. id and
(B. name <> I. name or B. telphone <> I. telphone)

-- Insert new data
Insert srv2. database name. dbo. author (id, name, telphone)
Select id, name, telphone from author I
Where not exists (
Select * from srv2. database name. dbo. author where id = I. id)

-- Delete Deleted Data (if needed)
Delete B
From srv2. database name. dbo. author B
Where not exists (
Select * from author where id = B. id)
Go

 

-- Create a job and call the above synchronization processing stored procedure on a regular basis.

Enterprise Manager
-- Manage
-- SQL Server proxy
-- Right-click a job
-- Create a job
-- Enter the job name in "general"
-- "Step"
-- New
-- Enter the step name in "Step name"
-- Select "Transact-SQL script (TSQL)" in "type )"
-- "Database": select the database for Command Execution
-- Enter the statement to be executed in "command": exec p_process
-- OK
-- "Scheduling" item
-- Create Scheduling
-- Enter the scheduling name in "name"
-- Select your job execution schedule in "scheduling type"
-- If "repeated appears" is selected"
-- Click "change" to set your schedule

Then start the SQL Agent service and set it to Automatic startup. Otherwise, your job will not be executed.

Setting method:
My computer -- control panel -- Administrative Tools -- service -- Right-click SQLSERVERAGENT -- properties -- start type -- select "auto start" -- OK.

How to synchronize two databases

Use Database Replication Technology to synchronize data updates

Concept of Replication
Replication is a technology that copies a set of data from one data source to multiple data sources. It is an effective way to publish a set of data to multiple storage sites. With the replication technology, users can publish a copy of data to multiple servers, so that different server users can share the data within the permitted range of permissions. The replication technology ensures that data distributed in different locations is automatically updated synchronously to ensure data consistency.

The basic elements of SQL replication include
Publishing Server, subscription server, distribution server, publications, articles

How SQL replication works
SQL SERVER mainly uses publications and subscriptions to process replication. The server on which the source data is located is the publishing server, which is responsible for publishing data. The Publishing Server copies all changes to the data to be published to the distribution server. The distribution server contains a distribution database that can receive all changes to the data and save the changes, and then distribute the changes to the subscription server.

SQL SERVER replication technology type, three Replication technologies:
1. Copy a snapshot (this will be used if we stay there)
2. Transaction Replication
3. Merge and copy

As long as the above concepts are clarified, we will have a certain understanding of replication. Next we will perform the copy step.
First, configure the Publishing Server
(1) Select the specified [server] Node
(2) Select the [Publish, subscribe server, and distribute] command from the [copy] sub-menu in the [tools] drop-down menu.
(3) In the dialog box that appears, click [next] And then follow the prompts until the operation is completed.
(4) After setting the Publishing Server, the system adds a replication monitor to the tree structure of the server. A distribution database (distribution) is also generated)

Create a publication
(1) Select the specified server
(2) Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu. A dialog box is displayed.
(3) Select the database for which you want to create the publication, and click [Create release]
(4) In the prompt dialog box of the [Create release wizard], click [next]. A dialog box is displayed. The dialog box contains three types of copies. Now we select the first one, that is, the default snapshot release (the other two can be checked for help)
(5) Click [next] system requirements to specify the type of database server that can subscribe to the release. SQLSERVER allows data replication between different databases, such as ORACLE or ACCESS. But here we choose to run the database SERVER "SQL SERVER 2000"
(6) Click [next]. A dialog box for defining the document is displayed, that is, the table to be published.
(7) then [next] until the operation is completed. After the publication is created, the database for creating the publication becomes a shared database.

Design subscription
(1) Select the specified subscription Server
(2) select [request subscription] from the [tools] drop-down menu in the [copy] submenu
(3) Click [next] until the system prompts you to check the running status of the SQL server proxy service. The precondition for performing the copy operation is that the SQL SERVER proxy service must be started.
(4) Click [finish]. Complete the subscription operation.
The above steps are actually copied successfully. But how can we know whether the replication is successful?
This method can be used to quickly check whether the operation is successful.
Expand copy under the Publishing Server -- publish content -- Right-click Publish content -- properties -- click live -- status, and then click Run Agent immediately, and then click agent properties to view live Scheduling
Set scheduling to happen every day, every minute, between 0:00:00 and 23:59:59.
The next step is to determine whether the replication is successful.
Open C: \ Program Files \ Microsoft SQL Server \ MSSQL \ REPLDATA \ unc \ XIAOWANGZI_database_database
Check whether there are some folders that use time as the file name.
If you do not believe it, open your database. Check the specified subscription database of the subscribed server to see if you have seen the table you just released.

/****************** FAQ: permission issues

The domain user account that starts the proxy must have the permission to read the distribution server directory.
Your account does not have the permission to read Files in the \ ServerA \ D $ \ Program Files \ Microsoft SQL Server \ MSSQL \ repldata \ unc \ directory.

The domain user account that starts the proxy must have the permission to read the distribution server directory.
---- ^ Start user in the control panel --> Manage --> service --> sqlagent --> attribute

Http://blog.csdn.net/zlp321002/archive/2005/10/29/519239.aspx

Use logs! Manual log Transmission
1. Make a full backup on the source server, copy the full backup to the target server for restoration, and remember to use the Nocovery or Standby parameter for restoration.
2. Arrange a log backup job on the source server, direct the backup file directory to the target server (create a directory in advance), and write a loop in the job (do not exit ), define an intermediate switch variable (self-implemented, accessible to both servers), 'open 'status, and perform log backup. Once completed, set the intermediate variable to 'off '.
3. Arrange the log restore job on the target server. When the intermediate variable is 'off', perform the restoration. After the restoration, set the intermediate variable to 'off '.
4. In this case, the database is basically real-time.

Http://blog.csdn.net/softj
You shoshould change your not in to a left join or a not exists. The not exists performs much better. Just an fyi.