Two SQL Server Data synchronization Solutions

server| Solution | data | The concept of data synchronous replication
　　
Replication is the technique of copying a set of data from one data source to multiple data sources, and is an effective way to publish a single piece of data to multiple storage sites. With replication technology, users can publish a single piece of data to multiple servers so that different server users can share the data within permission. Replication technology ensures data consistency by ensuring that data is automatically synchronized and updated at different locations.
　　
The basic elements of SQL replication include
　　
Publishing servers, Subscribers, distributors, publications, articles
　　
How SQL Replication works
　　
SQL SERVER handles replication primarily with publications, subscriptions. The server on which the source data resides is the publishing server, responsible for publishing the data. The publisher copies copies of all changes to the published data to the Distributor, which contains a distribution database that receives all changes to the data, saves the changes, and distributes the changes to subscribers
　　
SQL Server replication Technology type
　　
SQL Server provides three replication technologies, respectively:
　　
1. Snapshot replication (we'll use this later)
2. Transactional replication
3. Merge replication
　　
As long as the above concepts are clear, then there is a certain understanding of replication. Next, we'll step through the process of copying.
　　
First to configure the publishing server
　　
(1) Select the specified server node
(2) Select the Publish, subscribe to server and distribute command from the Copy submenu of the [Tools] dropdown Menu
(3) The system pops up a dialog point [next] then looks at the prompt to complete the operation.
(4) The system will add a replication Monitor to the server's tree structure when the publishing server is set up. Also generate a distribution database (distribution)
　　
Second Create a publication
　　
(1) Select the specified server
(2) Select the Create and manage publications command from the Copy submenu of the Tools menu. A dialog box pops up on the system.
(3) Select the database where you want to create the publication, and then click Create Publication
(4) A dialog box pops up when you click Next in the Prompt dialog box in the Create Publication Wizard. The contents of the dialog box are three types of replication. We now choose the first one is also the default snapshot release (the other two people can go to see Help)
(5) Click Next to ask the system to specify the type of database server that can subscribe to the publication, and SQL Server allows data replication between different databases, such as Oracle or access. But here we choose to run the SQL Server 2000 database server
(6) Click on [Next] system to pop up a definition of the article dialog box is to select the table to publish
(7) then [next] until the operation is complete. When you finish creating your publication, the database that creates the publication becomes a shared database.
　　
Third Design Subscription
　　
(1) Select the Subscriber specified
(2) Select [Pull subscriptions] from the [Tools] Pull-down Menu [Copy] submenu
(3) Click Next until you are prompted to check the running state of the SQL Server Agent service, which is a prerequisite for the replication operation if the SQL Server Agent service must already be started.
(4) Click [Finish]. completes the subscription operation.
　　
Complete the above steps in fact, replication is successful. But how do you know if replication has succeeded? This is the way to quickly see if success is possible. Expand the copy below the publishing server--publish content--right key publish content--attribute-------------state and then point immediately run the agent then Point Agent property 0:00:00 scheduling to occur every day, every minute, between the and 23:59:59. The next step is to determine if replication succeeded by opening C:\Program Files\Microsoft SQL Server\mssql\repldata\unc\xiaowangzi_database_ The database below to see if there are some folders with time as the file name of a folder in almost one point. If you don't believe me, open your database. Look at the subscribed server's subscription database. See the table you just posted-
　　
A manual synchronization scheme
　　
--Data on a scheduled synchronization server
　　
--Example:
　　
--Test environment, SQL Server2000, remote server name: XZ, username: SA, no password, testing database: Test
　　
--Tables on the server (Query Analyzer is connected to create on the server)
　　
CREATE TABLE [user] (ID int primary key,number varchar (4), name varchar (10))
Go
　　
--The following on the local area network (native operation)
　　
--native table, state description: null indicates new record, 1 indicates modified record, 0 indicates unchanged record
　　
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (n ' [user] ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1)
drop table [user]
Go
CREATE TABLE [user] (id int identity (1,1), number varchar (4), name varchar (), state bit)
Go
　　
--Create a trigger to maintain the value of the State field
　　
Create trigger t_state on [user]
After update
As
Update [user] set state=1
From [user] A join inserted B on a.id=b.id
Where a.state is not null
Go
　　
--To facilitate synchronization, create a linked server to the server to be synchronized
　　
--Here's the remote server name: XZ, username: SA, no password
　　
if exists (select 1 from Master. sysservers where srvname= ' Srv_lnk ')
exec sp_dropserver ' srv_lnk ', ' droplogins '
Go
exec sp_addlinkedserver ' srv_lnk ', ', ' SQLOLEDB ', ' XZ '
exec sp_addlinkedsrvlogin ' Srv_lnk ', ' false ', NULL, ' sa '
Go
　　
--Creating a stored procedure for the synchronization process
　　
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_synchro] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_synchro]
Go
Create proc P_synchro
As
--set Xact_abort on
　　
--Start the MSDTC service for the remote server
　　
--exec Master. xp_cmdshell ' isql/s ' xz '/u ' sa '/P ' '/q ' exec master. xp_cmdshell ' net start msdtc ', No_output ', no_output
　　
--Start the MSDTC service for this machine
　　
--exec Master. xp_cmdshell ' net start msdtc ', no_output
　　
--For distributed transactions, if the table uses the Identity column key, use the following method
　　
--begin Distributed TRANSACTION
　　
--data that is deleted synchronously
　　
Delete from srv_lnk.test.dbo. [User]
where ID not in (select ID from [user])
　　
--Synchronizing the new data
　　
INSERT INTO srv_lnk.test.dbo. [User]
Select Id,number,name from [user] where ' is null
　　
--Synchronizing the modified data
　　
Update srv_lnk.test.dbo. [User] Set
Number=b.number,name=b.name
From Srv_lnk.test.dbo. [User] A
Join [user] B on a.id=b.id
where b.state=1
　　
--Update the local flag after synchronization
　　
Update [user] set state=0 where IsNull (state,1) =1
--commit TRAN
Go
　　
--Create a job to perform a stored procedure for data synchronization at timed intervals
　　
if exists (SELECT 1 from msdb.. sysjobs where name= ' data processing ')
EXECUTE msdb.dbo.sp_delete_job @job_name = ' data processing '
EXEC msdb.. Sp_add_job @job_name = ' data processing '
　　
--Create job step
　　
DECLARE @sql varchar (@dbname), varchar (250)
Select @sql = ' EXEC p_synchro '--commands for data processing
, @dbname =db_name ()--database name to perform data processing
EXEC msdb.. Sp_add_jobstep @job_name = ' data processing ',
@step_name = ' data synchronization ',
@subsystem = ' TSQL ',
@database_name = @dbname,
@command = @sql,
@retry_attempts = 5--Number of retries
@retry_interval = 5--Retry interval
　　
--Create a schedule
　　
EXEC msdb.. sp_add_jobschedule @job_name = ' data processing ',
@name = ' time schedule ',
@freq_type = 4,--Every day
@freq_interval = 1--Once a day
@active_start_time = 00000