Two SQLServer data synchronization solutions

xiaoxiao2021-03-06  19

There are special needs, two SQLServer server days, and data synchronization with high effectiveness.

Everyone knows that the two database data synchronization is a tricky problem with both database data to achieve high and accuracy and high effect.

There is some information on the Internet, there is an article for reference.

Http://www.sxsoft.com/technology_test/show_news.asp?id=450, the publishing and subscription methods of SQL Server are introduced in the article to synchronize data. According to the article, the test is made, it is ok, there are some experiences:

1) It is better to use the merge replication in the Internet. Snapshot replication is suitable for applications where data changes, and transaction replication is suitable for long-term connections.

2) Publishing server uses FTP to publish. This will avoid problems caused by many configurations. That is to configure the publishing server, pay attention to the "FTP root to the client path" column to enter "/ ftp /", because this "/ ftp /" folder is automatically created by the system, and the subscription server is looking for yourself. Not.

I think there may be some other ways to synchronize, expect, and everyone.

[Article reprint] http://www.sxsoft.com/technology_test/show_news.asp?id=450

Using database replication technology to implement data synchronization update

Author: lobeben Source: lobeben's Blog

Using database replication technology to implement data synchronization update

Copy concept

Copy is a technique that copies a set of data from a data source to multiple data sources, which is a valid approach to multiple storage sites. Using replication technologies, users can publish a data to multiple servers, allowing different server users to share this data within the scope of permission. Replication technology ensures that the data distributed directly in different locations automatically synchronously updates, thereby ensuring the consistency of data.

SQL copying basic elements include

Publishing server, subscription server, distributing server, publication, articles

SQL copying work principle

SQL Server mainly uses publications, subscribers to process replication. The server where the source data is located is a publishing server and is responsible for publishing the data. The publishing server copies copies of all changes to the data to be published to the distribution server, the distribution server contains a distribution database to receive all changes in data, and save these changes, then distribute these changes to the subscriber

SQL Server replication technology type

SQL Server provides three replication technologies, namely:

1, snapshot replication (I will use this)

2, transaction copy

3, combined copy

As long as you understand the above concepts, then you have a certain understanding of the copy. Next, we will implement the steps of copying step by step.

First to configure the publishing server

(1) Select the specified [Server] node

(2) Select [Publishing, Subscriber and Distribution] command from the [Copy] submenu of [Tool] drop-down menu

(3) The system pops up a dialog box point [Next] and then look at the prompt that has been operational.

(4) After completing the settings of the publishing server, the system will add a replication monitor to the tree structure of the server. Also generate a distribution database (Distribution)

Second creation publication

(1) Select the specified server

(2) Select [Create and Manage Publish] commands from the [Copy] submenu of the [Tools] menu. At this point, a dialog box will pop up.

(3) Select the database to create a publication, and then click [Create Publish]

(4) Click the [Next] system in the [Create Publish Wizard] to pop up a dialog box. The content on the dialog is three types of replication. We now choose the first one is the default snapshot release (other two can go see help)

(5) Click [Next] System Requirements Specify that you can subscribe to the published database server type, SQL Server allows data replication between different databases such as Oracle or Access. But here we choose the database server running "SQL Server 2000"

(6) Click [Next] The system pops up a dialog box for a definition article is also the table to be published.

(7) Then [Next] until the operation is completed. The database created after the publication is completed, it will become a shared database.

Third design subscription

(1) Select the specified subscription server

(2) Select [Reproduction] submenu from [Tool] drop-down menu [request subscription]

(3) Follow [Next] to operate until the system will prompt the SQL Server Agent Service's running status, the prerequisite for performing replication operations is that the SQL Server Agent service must have started.

(4) Click [Finish]. Complete the subscription operation.

Completing the above steps is actually replication. But how do you know if replication is successful? This can be successfully seen in this way. Expand the copy of the publishing server - Publish content - Right click on the content - Property - Hit - Status The Status is immediately running the agent, then the agent property hits the schedule to set the schedule to every day, every minute It is between 0:00:00 and 23:59:59. The next step is to determine whether the copy is successful. C: / program files / microsoft SQL Server / MSSQL / RepLData / UNC / XIAOWANGZI_DATABASE_DATABASE The following is a folder difference between the folder with time as the file name. If you don't believe, open your database to see if you have a subscription database of the subscription database. Is it seeing the table you just released -

A manual synchronization plan

- Data on the timed synchronization server

--example:

- Test environment, SQL Server2000, Remote server name: XZ, username: SA, no password, test database: TEST

- Table on the server (query analyzer is connected to the server)

Create Table [User] (ID INT Primary Key, Number Varchar (4), Name Varchar (10))

Go

- The following on the LAN (this machine)

- This table, state description: null indicates a new record, 1 means a modified record, 0 means no change record

IF exists (select * from dbo.sysobjects where id = Object_id (n '[user]') And ObjectProperty (ID, n'uSERTABLE ') = 1)

Drop Table [user]

Go

Create Table [User] (ID Int Id Id IDENTIN), 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

- Create a link server to the server to be synchronized for easy synchronization processing

- The remote server here is: XZ, the username is: 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

- MSDTC service that launches remote server

--EXEC MASTER..XP_CMDSHELL 'ISQL / S "XZ" / u "sa" / p "" / q "EXEC MASTER.. pP_CMDSHELL' '' NET Start MSDTC '', NO_OUTPUT" ', NO_OUTPUT

- Start this machine's MSDTC service

--exec master..xp_cmdshell 'Net Start MSDTC', NO_OUTPUT

- Distributed transaction processing, if the meigo list is the primary key, use the following method

- Begin Distributed Transaction

- Synchronous delete data

Delete from srv_lnk.test.dbo. [user]

WHERE ID NOT IN (SELECT ID from ")

- Synchronize new data

INSERT INTO SRV_LNK.TEST.DBO. [User]

Select ID, Number, Name from [user] Where State Is Null

- Synchronous 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 logo of this machine after synchronization

Update [user] set state = 0 Where isnull (state, 1) = 1

--Commit TRAN

Go

- Create a job, scheduled to perform data synchronization stored procedures

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 steps

Declare @SQL VARCHAR (800), @ dbname varchar (250)

SELECT @ SQL = 'exec p_synchro' - command of data processing

, @ dbname = db_name () - Database name of execution 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, - Retry

@retry_interval = 5 - Retry Interval

- Create schedule

EXEC MSDB..SP_ADD_JOBSCHEDULE @JOB_NAME = 'Data Processing', @ Name = 'Time Arrangement',

@Freq_Type = 4, - daily

@freq_interval = 1, - Perform once a day

@Active_start_time = 00000 --0

Go

转载请注明原文地址:https://www.9cbs.com/read-42724.html

New Post(0)