Two SQL Server Data synchronization Solutions

The concept of replication
Replication is the technique of copying a set of data from one data source to multiple data sources, which is an effective way to publish a piece of data to multiple storage sites. Using replication technology, users can publish a single copy of the data to multiple servers, allowing different server users to share this data within the permitted scope of permissions. Replication technology ensures data consistency by ensuring that data is automatically synchronized and updated across locations.
The basic elements of SQL replication include
Publishing servers, Subscribers, distributors, publications, articles
How SQL Replication works
SQL SERVER handles replication primarily in the form of publications and subscriptions. The server on which the source data resides is the publishing server, which publishes the data. The publishing server copies copies of all changes to the published data to the Distributor, which contains a distribution database that can receive all changes to the data, save the changes, and then distribute those changes to subscribers
Types of SQL Server replication technologies
SQL Server provides three replication technologies, namely:
1. Snapshot copy (we'll use this later)
2. Transactional replication
3. Merge replication
As long as the above concepts are clear, then the copy will have a certain understanding. Next we'll step through the steps to replicate.
First to configure the publishing server
(1) Select the Specify [Server] node
(2) Select the [Publish, subscribe, and Distribute] command from the [Copy] submenu of the [Tools] drop-down menu
(3) The system pops up a dialog point [next] and then looks at the prompt until the operation is complete.
(4) The system adds a replication Monitor to the server's tree structure when the publishing server's settings are completed. Also generates a distribution database (distribution)
Second Create publication
(1) Select the specified server
(2) Select the [Create and manage publications] command from the [Copy] submenu of the [Tools] menu. The system pops up a dialog box
(3) Select the database where you want to create the publication, and then click [Create Publication]
(4) Click [Next] in the [Create Publication Wizard] prompt dialog box and a dialog will pop up. The contents of the dialog box are the three types of replication. We're going to choose the first one. The default snapshot publication (two other people can go and see Help)
(5) Click Next 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 [Next] the system pops up a dialog box that defines the article, that is, select the table to publish
(7) then [next] until the operation is complete. The database that created the publication after the creation of the publication has become a shared database.
Third Design Subscription
(1) Select the specified subscriber
(2) SELECT [Copy] submenu from the [Tools] drop-down menu [request subscription]
(3) Follow the [next] action until you are prompted to check the health status of the SQL Server Agent service, and the prerequisite for the replication operation is that the SQL Server Agent service must already be started.
(4) Click [Finish]. completes the subscription operation.
Complete the above steps in fact copy is the success. But how do you know if replication is successful? This method can be used to quickly see if it is successful. Expand Publishing Server Copy below--Publish content--right-click publish Content--Properties--click Live--state then point to run the agent immediately then point Agent Properties Live Scheduler Set the schedule to occur every day, every minute, between 0:00:00 and 23:59:59. The next step is to determine if replication is successful open C:Program FilesMicrosoft SQL servermssqlrepldatauncxiaowangzi_database_ Database below to see if there are some time as the file name of the folder almost a point in the production of one. If you don't believe it, open your database and see if you see the table you just published, under the specified subscription database of the subscribed server-
A manual synchronization scheme
--timed synchronization of data on the server
--Example:
--Test environment, SQL Server2000, remote server name: XZ, username: SA, no password, test database: Tests
--Table on the server (Query Analyzer connected to server created)
CREATE TABLE [user] (ID int primary key,number varchar (4), name varchar (10))
Go
--Below the local area network (native operation)
--native table, state description: null for new record, 1 for modified record, 0 for 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 (max), number varchar (4), name varchar (ten), 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 synchronous processing, create a linked server to the server to synchronize
--Here the remote server name is: 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
--Create a stored procedure for synchronous processing
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 on the remote server
--exec Master. xp_cmdshell ' isql/s "XZ"/U "sa"///"/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 transaction processing, if the table uses the identity column master key, use the following method
--begin Distributed TRANSACTION
--Synchronizing deleted data
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 state 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 flag of the machine after synchronization
Update [user] set state=0 where IsNull (state,1) =1
--commit TRAN
Go
--Create a job, schedule a stored procedure that synchronizes data synchronously
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 a job step
DECLARE @sql varchar (+), @dbname varchar (250)
Select @sql = ' EXEC p_synchro '--Data processing commands
, @dbname =db_name ()--the database name to perform data processing
EXEC msdb: Sp_add_jobstep @job_name = ' data processing ',
@step_name = ' data synchronization ',
@subsystem = ' TSQL ',
@[email protected],
@command = @sql,
@retry_attempts = 5,--Retry count
@retry_interval = 5--Retry interval
--Create a schedule
EXEC msdb: sp_add_jobschedule @job_name = ' data processing ',
@name = ' time schedule ',
@freq_type = 4,--Daily
@freq_interval = 1, executed once per day
@active_start_time = 00000--0 point execution
Go

Two SQL Server Data synchronization Solutions