MSSQL database synchronization tutorial

Concept
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
SQLSERVER 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.

SQLSERVER replication technology type

SQLSERVER provides 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 "SQLSERVER2000" Database Server
(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 SQLSERVER proxy service. The precondition for performing the copy operation is that the SQLSERVER 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. Then click agent properties, click live scheduling, and set Scheduling to every day. occurred, every minute, between 0:00:00 and 23:59:59. The next step is to determine whether the replication is successful. Open C:/ProgramFiles/MicrosoftSQLServer/MSSQL/REPLDATA/unc/XIAOWANGZI_database_database and check whether there are some folders with time as the file name that will generate one in about one minute. 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-
A manual synchronization Solution
Copy codeThe Code is as follows:
-- Regularly synchronize data on the server
-- Example:
-- Test environment: SQLServer2000, remote server name: xz, User name: sa, no password, test Database: test
-- Tables on the server (query analyzer is connected to a table created on the server)
Createtable [user] (idintprimarykey, numbervarchar (4), namevarchar (10 ))
Go
-- Perform the following operations on the LAN (Local Machine)
-- Local table, state Description: null indicates new record, 1 indicates modified record, 0 indicates no change record
Ifexists (select * fromdbo. sysobjectswhereid = object_id (n' [user] ') andOBJECTPROPERTY (id, n'isusertable') = 1)
Droptable [user]
GO
Createtable [user] (idintidentity (1, 1), numbervarchar (4), namevarchar (10), statebit)
Go
-- Create a trigger to maintain the value of the state field
Createtriggert_stateon [user]
Afterupdate
As
Update [user] setstate = 1
From [user] ajoinsertedbona. id = B. id
Wherea. stateisnotnull
Go
-- To facilitate synchronization, create a connection server to the server to be synchronized
-- The remote server name is xz, the user name is sa, and no password is required.
Ifexists (select1frommaster .. sysserverswheresrvname = 'srv _ lnk ')
Execsp_dropserver 'srv _ lnk ', 'droplogins'
Go
Execsp_add1_server 'srv _ lnk ', '', 'sqloledb', 'xization'
Execsp_add1_srvlogin 'srv _ lnk ', 'false', null, 'sa'
Go
-- Create a stored procedure for synchronization
Ifexists (select * fromdbo. sysobjectswhereid = object_id (n' [dbo]. [p_synchro] ') andOBJECTPROPERTY (id, n' IsProcedure') = 1)
Dropprocedure [dbo]. [p_synchro]
GO
Createprocp_synchro
As
-- SetXACT_ABORTon
-- Start the MSDTC Service of the remote server
-- Execmaster .. xp_mongoshell 'isql/S "xz"/U "sa"/P ""/q "execmaster .. xp_mongoshell ''netstartmsdtc'', no_output "', no_output
-- Start the MSDTC Service of the Local Machine
-- Execmaster .. xp_mongoshell 'netstartmsdtc ', no_output
-- Performs distributed transaction processing. If the table uses the ID column as the primary key, use the following method:
-- BEGINDISTRIBUTEDTRANSACTION
-- Synchronize Deleted Data
Deletefromsrv_lnk.test.dbo. [user]
Whereidnotin (selectidfrom [user])
-- Synchronize new data
Insertintosrv_lnk.test.dbo. [user]
Selectid, number, namefrom [user] wherestateisnull
-- Synchronize modified data
Updatesrv_lnk.test.dbo. [user] set
Number = B. number, name = B. name
Fromsrv_lnk.test.dbo. [user]
Join [user] bona. id = B. id
Whereb. state = 1
-- Update the local flag after synchronization
Update [user] setstate = 0 whereisnull (state, 1) = 1
-- COMMITTRAN
Go
-- Create a job and regularly execute the data synchronization Stored Procedure
Ifexists (SELECT1frommsdb .. sysjobswherename = 'data ')
EXECUTEmsdb. dbo. sp_delete_job @ job_name = 'data'
Execmsdb .. sp_add_job @ job_name = 'data'
-- Create a job
Declare @ sqlvarchar (800), @ dbnamevarchar (250)
Select @ SQL = 'execp _ synchro '-- Data Processing Command
, @ Dbname = db_name () -- Name of the database for processing data
Execmsdb .. sp_add_jobstep @ job_name = 'data ',
@ Step_name = 'data synchronization ',
@ Subsystem = 'tsql ',
@ Database_name = @ dbname,
@ Command = @ SQL,
@ Retry_attempts = 5, -- number of retries
@ Retry_interval = 5 -- Retry Interval
-- Create Scheduling
EXECmsdb .. sp_add_jobschedule @ job_name = 'data ',
@ Name = 'schedule ',
@ Freq_type = 4, -- daily
@ Freq_interval = 1, -- run once a day
@ Active_start_time = 00000--0: Execution
Go