Two SQL Server Data synchronization Solutions

server|sqlserver| Resolution | data | Before the data synchronization some days meet special needs, two SQL Server servers on one side, the need for high efficiency data synchronization.     We know that it is a tricky problem to realize the synchronization of two database data with high accuracy and high efficiency through Internet network. On the web for some information, there is an article for reference http://www.sxsoft.com/technology_test/show_news.asp?id=450, the article describes the SQL Server publishing and subscription methods to synchronize data. According to the article did the next experiment, is OK, have the following ideas: 1 The Internet is better to use merge replication.     snapshot replication is suitable for situations where data changes are small, and transactional replication is suitable for long-term connections. 2 The Publisher uses FTP to publish. This avoids problems caused by many configurations.          is to configure the Publisher. Note that from the FTP root to the client path to this folder column, enter "/ftp/" because the "/ftp/" folder is created automatically by the system and the Subscriber is not found by itself. I think there may be other ways to synchronize data, expect and discuss it.
         [Article reprint]http://www.sxsoft.com/technology_test/show_news.asp?id=450 Using database replication technology to realize data synchronization update   author:lobeben  Source: Lobeben Blog Using Database replication technology to implement the concept of data Synchronization update replication is the technique of copying a set of data from one data source to multiple data sources, is an effective way to publish a 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, article SQL replication how SQL SERVER handles replication primarily with publications, 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 then distributes the changes to the Subscriber SQL Server replication technology type SQL Server provides three replication technologies: 1, snapshot replication (we'll use this for 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 Specify server node (2) from the [Tools] drop-down menu in the Copy submenu [Publish, Subscriber and Distribution command (3) The system pops up a dialog point [next] and 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 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 (3) Select the database where you want to create the publication, and then click Create Publication (4) In the Create Publication Wizard dialog box, click Next to the system to pop up a dialog box. 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]System requirements Specify the type of database server that can subscribe to this publication, and SQL Server allows data replication between different databases, such as Oracle or access. But here we choose to run SQL Server 2000 database Server (6) Click the next system to pop up a dialog box that defines the article (7) and 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 specified Subscriber (2) from the Tools Drop-down menu, select [Pull Subscriptions] (3) Click Next until you are prompted to check the running state of the SQL Server Agent service. The prerequisite for performing a copy operation is SQL The Server Agent service must already 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 succeeded by opening C:\Program Files\Microsoft SQL Server\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. See if you see the table you just published-a manually synchronized scenario-the data on a scheduled synchronization server-Example:--test environment, SQL Server2000, remote server name: XZ, User name: SA, no password, testing database: Test-tables on the server (Query Analyzer is connected to the server to create) create TABLE [user] (ID int primary key,number varchar (4), name varchar ( ) Go--the following on the local area network (local operation)--local table, state description: null indicates new record, 1 indicates modified record, 0 indicates 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 (1,1), number varchar (4), name varchar (), state bit) go-create triggers, maintain the value of the state field create trigger t_state on [user] after update as update [user] set state=1 f Rom [user] A join inserted B on a.id=b.id where a.state isn't null go--for ease of synchronization, create a linked server to the server to be synchronized--here's the remote server name: 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 synchronized stored procedure if exists (SELECT * from Dbo.sysobj ECTS 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--Starts the MSDTC service for the remote server--exec master. xp_cmdshell ' isql/s ' xz '/u ' sa '/P ' '/q ' exec master. xp_cmdshell ' net start msdtc ', No_output ', no_output--Start the MSDTC service on this computer--exec master. xp_cmdshell ' NET-start MSDTC ', No_output--for Distributed transaction processing, if the table uses the identity column as the key,--begin the distributed TRANSACTION by the following method--the deleted data delete from Srv_lnk.test . dbo. [user] where ID not in (select ID from [user])--Synchronizes the new data insert into srv_lnk.test.dbo. [user] Select Id,number,name from [user] where ' is null--synchronized 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--updates the local flag after synchronization update [user] set state=0 where IsNull (state,1) =1--commi T TRAN Go--Creates a job that performs data synchronization stored procedure 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 job steps declare @sql varchar (a), @dbname varchar Select @sql = ' EXEC p_synchro '- Data Processing command, @dbname =db_name ()--database name exec msdb for data processing. Sp_add_jobstep @job_name = ' data processing ', @step_name = ' synchronization ', @subsystem = ' TSQL ', @database_name = @dbname, @command = @sql, @retry _attempts = 5--Number of retries @retry_interval = 5--Retry Interval--Create dispatch EXEC msdb. Sp_add_joBschedule @job_name = ' data processing ', @name = ' time schedule ', @freq_type = 4,--Daily @freq_interval = 1, every day @active_start_time = 00000 --0 Point execution go