Concept of Replication
Copy isDataFromDataSource copy to multipleDataThe source technology isDataAn Effective Method for publishing data to multiple storage sites. CopyDataPublish to multiple servers, so that different server users can share this copy within the permitted range of permissions.Data. Replication Technology ensures that data is distributed in different locationsDataAutomaticSynchronizationUpdate to ensureData.
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. SourceDataThe server is the Publishing Server, responsible for publishingData. The Publishing ServerDataThe distribution server contains a distributionDataLibrary, can receiveDataAnd then distribute the changes to the subscription server.
SQL SERVER replication technology type
SQL SERVER 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. It also generates a distributionDataDatabase (distribution)
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) SelectDataLibrary, and then 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] and specify the system requirements to subscribe to the publishedDataDatabase Server type, SQLSERVER allowed in differentDataDatabases such as ORACLE or ACCESSDataCopy. But here we choose to run "SQL SERVER 2000"DataDatabase 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 createdDataThe library becomes a sharedDataLibrary.
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. Then click agent properties to click live Scheduling scheduling is set to happen every day, every minute, between 0:00:00 and 23:59:59. The next step is to determine whether the replication succeeds and enable 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, you can open it.DataLibrary view the specified subscription of the subscribed ServerDataSee the table you just released ― One manualSynchronizationOfSolution
-- TimingSynchronizationOn the serverData
-- Example:
-- Test environment: SQL Server2000, remote server name: xz, User name: sa, no password, testDataDatabase: test
-- Tables on the server (query analyzer is connected to a table created on the server)
Create table [user] (id int primary key, number varchar (4), name varchar (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
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 (10), 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
-- For convenienceSynchronizationProcess, create a linked serverSynchronizationServer
-- The remote server name is xz, the user name is sa, and no password is required.
If exists (select 1 from master .. sysservers where srvname = 'srv _ lnk ')
Exec sp_dropserver 'srv _ lnk ', 'droplogins'
Go
Exec sp_addmediaserver 'srv _ lnk ', '', 'sqloledb', 'xz'
Exec sp_add1_srvlogin 'srv _ lnk ', 'false', null, 'sa'
Go
-- CreateSynchronizationStored Procedure
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 of the remote server
-- Exec master .. xp_mongoshell 'isql/S "xz"/U "sa"/P ""/q "exec master .. xp_cmdshell ''net start msdtc '', no_output" ', no_output
-- Start the MSDTC Service of the Local Machine
-- Exec master.. xp_cmdshell 'net start msdtc ', no_output
-- Performs distributed transaction processing. If the table uses the ID column as the primary key, use the following method:
-- BEGIN DISTRIBUTED TRANSACTION --SynchronizationDeletedData
Delete from srv_lnk.test.dbo. [user]
Where id not in (select id from [user])
--SynchronizationAddedData
Insert into srv_lnk.test.dbo. [user]
Select id, number, name from [user] where state is null
--SynchronizationModifiedData
Update srv_lnk.test.dbo. [user] set
Number = B. number, name = B. name
From srv_lnk.test.dbo. [user]
Join [user] B on a. id = B. id
Where B. state = 1
--SynchronizationThen update the local flag
Update [user] set state = 0 where isnull (state, 1) = 1
-- COMMIT TRAN
Go
-- Create a job and execute it regularlyDataSynchronizationStored Procedures
If exists (SELECT 1 from msdb .. sysjobs where name ='DataHandler ')
EXECUTE msdb. dbo. sp_delete_job @ job_name ='DataHandler'
Exec msdb .. sp_add_job @ job_name ='DataHandler'
-- Create a job
Declare @ SQL varchar (800), @ dbname varchar (250)
Select @ SQL = 'exec p_synchro '--DataProcessed command
, @ Dbname = db_name () -- executeDataProcessedDataDatabase Name
Exec msdb .. sp_add_jobstep @ job_name ='DataHandler ',
@ Step_name ='DataSynchronization',
@ Subsystem = 'tsql ',
@ Database_name = @ dbname,
@ Command = @ SQL,
@ Retry_attempts = 5, -- number of retries
@ Retry_interval = 5 -- Retry Interval
-- Create Scheduling
EXEC msdb .. sp_add_jobschedule @ job_name ='DataHandler ',
@ Name = 'schedule ',
@ Freq_type = 4, -- daily
@ Freq_interval = 1, -- run once a day
@ Active_start_time = 00000 -- execute at 0
|
