Concept of the system
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 and 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, namely:
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 "SQLSERVER2000" 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 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 is successful. Open C:/programfiles/microsoftsqlserver/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
Copy Code code as follows:
--Data on a scheduled synchronization server
--Example:
--Test environment, SQLServer2000, remote server name: XZ, username: SA, no password, testing database: Test
--Tables on the server (Query Analyzer is connected to create on the server)
Createtable[user] (Idintprimarykey,numbervarchar (4), Namevarchar (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
Ifexists (select*fromdbo.sysobjectswhereid=object_id (N ' [user] ') andobjectproperty (id,n ' isusertable ') =1)
Droptable[user]
Go
Createtable[user] (idintidentity (1,1), Numbervarchar (4), Namevarchar (a), statebit)
Go
--Create a trigger to maintain the value of the State field
Createtriggert_stateon[user]
AfterUpdate
As
Update[user]setstate=1
From[user]ajoininsertedbona.id=b.id
Wherea.stateisnotnull
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
Ifexists (Select1frommaster. Sysserverswheresrvname= ' Srv_lnk ')
Execsp_dropserver ' Srv_lnk ', ' droplogins '
Go
Execsp_addlinkedserver ' Srv_lnk ', ', ' SQLOLEDB ', ' XZ '
Execsp_addlinkedsrvlogin ' Srv_lnk ', ' false ', NULL, ' sa '
Go
--Creating a stored procedure for the synchronization process
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 for the remote server
--execmaster.. xp_cmdshell ' isql/s ' XZ '///SA '///'/Q ' Execmaster ... xp_cmdshell ' Netstartmsdtc ', no_output ', no_output
--Start the MSDTC service for this machine
--execmaster.. xp_cmdshell ' Netstartmsdtc ', no_output
--For distributed transactions, if the table uses the Identity column key, use the following method
--begindistributedtransaction
--data that is deleted synchronously
Deletefromsrv_lnk.test.dbo. [User]
Whereidnotin (Selectidfrom[user])
--Synchronizing the new data
Insertintosrv_lnk.test.dbo. [User]
Selectid,number,namefrom[user]wherestateisnull
--Synchronizing the modified data
Updatesrv_lnk.test.dbo. [User]set
Number=b.number,name=b.name
Fromsrv_lnk.test.dbo. [User]a
Join[user]bona.id=b.id
Whereb.state=1
--Update the local flag after synchronization
Update[user]setstate=0whereisnull (state,1) =1
--committran
Go
--Create a job to perform a stored procedure for data synchronization at timed intervals
Ifexists (Select1frommsdb. Sysjobswherename= ' data processing ')
Executemsdb.dbo.sp_delete_job@job_name= ' data processing '
Execmsdb.. Sp_add_job@job_name= ' data processing '
--Create job step
Declare@sqlvarchar, @dbnamevarchar (250)
Select@sql= ' Execp_synchro '--commands for data processing
, @dbname =db_name ()--database name to perform data processing
Execmsdb.. Sp_add_jobstep@job_name= ' data processing ',
@step_name = ' data synchronization ',
@subsystem = ' TSQL ',
@database_name = @dbname,
@command = @sql,
Number of @retry_attempts =5,--retries
@retry_interval =5--retry Interval
--Create a schedule
Execmsdb.. Sp_add_jobschedule@job_name= ' data processing ',
@name = ' time schedule ',
@freq_type =4,--every day
@freq_interval =1,--executed once a day
@active_start_time =00000--0 Point Execution
Go
