[Reprint] Data operations between different server databases--Replication Synchronization (Clean edition)

Backup restore: A server as the primary server, the client update is updated on a server, B server is secondary server, b timing and a consistent, the result of B a copy/backup, but there is a difference in time. Implementation method: a scheduled backup; B Timing a backup of a to restore the method is as follows: (MS-SQL) Open a Server Enterprise Manager, locate the Management-sql Server Agent-jobs, create a new job, name can be backed up, create a step in the job: SQL codebackup DATABASE [ TEST] to DISK = ' f:/sql_server/test/db. Backup/test_backup. BAK ' with init above test is the database name and then creates a schedule in the job: the specific cycle time is determined according to the actual needs, such as every 15 minutes ... Save and run the job. Set the F:/sql_server to share. Open B Server Enterprise Manager Find Management-sql Server Agent-jobs, create a new job, name can be backed up, create a step in the job: SQL codeexec master. xp_cmdshell ' net use//server-a_ip/sql_server/test/db. BACKUP Password/user:username ' above is to get in touch with A, prepare for subsequent restores, and then add a second step: SQL coderestore Database [TEST] from disk= '//server-a_ ip/sql_server/test/db. Backup/test_backup. BAK ' Note that two servers are going to have the same database storage path, and here I am backing up on f:/sql_server/test/db. BACKUP, the database is placed on the F:/sql_server/test schedule and a. Save and run the job. See if it's implemented B synchronized to a?  This is how I built 3 servers, and since this has not been the case because the server down caused by the client is not available.  ------------------------------------------create a linked server exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address ' exec sp_addlinkedsrvlogin ' ITSV ', ' false ', NULL, ' username ', ' password '--query Example SELECT * from ITSV. Database name. dbo. Table name--import Example select * into table from ITSV. Database name. dbo. Table name--Remove linked server exec sp_dropserver ' IT when no longer in use SV ', ' droplogins '--connect remote/LAN data (Openrowset/openquery/opendatasource)--1, OPENROWSET--query Example SELECT * FROM OPENROWSET (' SQ Loledb ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)--Raw cost surface select * into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name--Import the local table into the remote table Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '); ' User name '; ' Password ', database name. dbo. Table name) Select *from Local table--Update local table updated B set B. Column a=a. Column A from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name) as a inner join local table B on A.column1=b.column1--openquery usage need to create a connection--first create a connection to create a linked server exec Sp_addlinkedse RVer ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '--query select * FROM OPENQUERY (ITSV, ' select * from database. dbo. Table name ')--the local table guide Enter the remote table Insert OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ') SELECT * from local table-Update local table update b set B. column b=a. Column B from OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ') as a inner join local table B on a. Column A=b. Column A--3, opendatasource/openrowset SELECT * from OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Test.dbo.roy_ta--Import the local table into the remote table Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). database. dbo. Table name SELECT * FROM--------------------------reference:/* Author: Jiangjian *//*--The example of synchronizing two databases has data SRV1. Library Name: Author has fields: Id,name,phone, srv2. Library name. Author has fields: Id,name,telphone,adress requirements: SRV1. Library name. Author add record Srv1. Library Name: Author records increase SRV1. Library Name: Author The phone field is updated, the SRV1. Library name. Author corresponding field Telphone update--*/--Approximate process steps--1. Create a connection server on SRV1 to operate SRV1 in SRV2, implement synchronous exec sp_addlinkedserver ' SR    V2 ', ', ' SQLOLEDB ', ' srv2 ' SQL instance name or IP ' exec sp_addlinkedsrvlogin ' srv2 ', ' false ', NULL, ' username ', ' password ' Go--2. In Srv1 and SRV2 Both computers, start MSDTC (Distributed transaction Processing service), and set to start my computer automatically--control panel--management tools--services--right-click Distributed Transaction coordinator--Properties--start--and start The type is set to start the go automatically--and then create a job to call the synchronization stored procedure above. Enterprise Manager--Management--sql Server Agent--right-click Job-New Job--Enter job name in "General" item--"Step" item--New--"Step name" input Step Name--"type" selectedSelect "Transact-SQL script (TSQL)"--"database" selects the database that executes the command--Enter the statement to execute in "command": Exec p_process--OK--"dispatch" item--New schedule--"name" Enter the schedule name-"Dispatch Type "Select your job execution schedule--If you choose" Recurring "--point" change "to set your schedule and then start the SQL Agent service and set it to start automatically, otherwise your job will not be executed by the Setup method: My Computer--control Panel--management tool--service--right-click SQL serveragent--Property--Startup Type--select "Auto Start"--OK. --3. Method of implementing synchronous processing 2, timed synchronization--creating the following synchronization stored procedure in SRV1 create proc p_process as--updating modified data Update B set Name=i.name,telphon E=i.telphone from SRV2. Library name. Dbo.author B,author I where b.id=i.id and (B.name <> i.name or B.telphone &L T;> i.telphone)--Insert the new data Insert Srv2. Library name. Dbo.author (id,name,telphone) Select Id,name,telphone from author I w Here isn't exists (SELECT * from SRV2. Library name. Dbo.author where id=i.id)--Delete deleted data (if required) Delete B from SRV 2. Library name. Dbo.author b where NOT EXISTS (select * from author where id=b.id) go------------------------------- -----------------------------------------------SQL Server 2005 Synchronous replication technology The following replication steps (take snapshot replication as an example) run the platform SQL Server 20,051, quasi-Preparation work: 1. Establish a WINDOWS user, set as administrator rights, and set a password as a valid access user to publish the snapshot files. 2. It is normal for the publisher and subscriber to communicate under SQL Server (that is, you can exchange visits). Open 1433 port and set the exception 3 in the firewall. Establish a shared directory at the publisher as the repository for publishing snapshot files. For example: Under the D packing directory, the folder is named Sqlcopy 4. Set SQL Agent (both Publisher and Subscriber settings) This article was posted on www.xker.com (Small new technology Network) Open Service (Control Panel---Administrative tools---services)---Right-click the SQL Server Agent---Properties---Log on---Select this account ---Enter or select the Windows user that you created in the first step---password, enter the user password 5. Set SQL Server Authentication, resolve permissions issues when connecting (publish, Subscribers are set) Step: Object Explorer----Right-click the SQL Instance-----Properties----Security----Server Authentication------Select SQL Server and Windows, and then click OK 6. Open the SQL Server 2005 Network protocol TCP/IP and pipe naming protocol and restart the Network service. 7. In SQL Server, create the system user login name in step 1 as the owner of the publication database (set to Dbo_owner and public). 8. Establish the database and table with the system superuser sa login to SQL Server. 9. The Publisher and Subscriber registration steps are as follows: View----Click to register the server----right-clicking the database engine----New Server registration-----Fill in the remote server name to register------authentication Select SQL Server Authentication-----user name (SA) The password------to create the group (or not)-----complete. 10. For IP only, cannot use the computer name, register server alias two, start: Publisher configuration (Configure publishing and subscriptions at the Publisher) 1. Select Replication Node 2. Right-click Local publishing----Next---------the System Pop-up dialog box to see the prompt----until the Specify Snapshot folder----Enter the directory that you created in the snapshot folder (point to the shared folder that you built in step 3)------ Select the publication database-------Select the publication type-------Select the Subscriber type-------Select the object to publish------set the Snapshot Agent-------fill in the publication name. This article was published in Www.xker.COM (Small New technology network) 3. Right-click Local subscription--------Select Publisher-------Select a subscription method (if you subscribe to the server, select push subscription instead Select a pull subscription)-------Fill the Subscriber--------Select the agent plan (which is generally selected for continuous operation)---------the rest of the selection defaults. At this point, SQL SERVER 2005 synchronous replication is complete. Using replication technology, users can publish data from a single client to multiple servers, allowing different server users to share this data within the permitted scope of permissions. Replication technology ensures that data is automatically synchronized and updated at different locations to ensure data consistency, eliminating the need for programmatic client and server-side data synchronization! Greatly improve the work efficiency! ------------------------------------instant synchronization of databases via triggers---Instant synchronization of instances of two tables:--Test environment: SQL2000, remote hostname: XZ, user name: SA, Password: None, database name: test--Create a test table, cannot use the identity column master key, because the normal update cannot be done--create a test table on this machine, the remote host also do the same table operation, but do not write the trigger if exists (SELECT * from dbo.sysobjects WHERE id = OBJECT_ID (n ' [Test] ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1) drop table [test]create table test (id int NOT NULL Constra int Pk_test primary KEY, name varchar (TEN)) go--create a synchronous trigger creating trigger T_test on Testfor insert,update,deleteasset Xact_abo RT on--initiates the remote server's MSDTC service exec master. xp_cmdshell ' isql/s "XZ"/U "sa"///"/q" exec master. xp_cmdshell ' net start msdtc ', no_output "', no_output--initiates the native MSDTC service exec master. xp_cmdshell ' net start msdtc ', no_output--for distributed transactions, if the table uses the identity column master key, use the following method BegiN distributed Transactiondelete from OPENROWSET (' SQLOLEDB ', ' xz '; ' Sa '; ', test.dbo.test) where ID in (select ID from deleted) inserts into OPENROWSET (' SQLOLEDB ', ' xz '; ' Sa '; ', test.dbo.test) SELECT * from insertedcommit trango--inserting data test insert INTO Testselect 1, ' AA ' union ALL select 2, ' BB ' Unio n All select 3, ' C ' UNION ALL Select 4, ' DD ' UNION ALL SELECT 5, ' AB ' union ALL select 6, ' BC ' union ALL Select 7, ' DDD '--delete data test del Ete from test, where ID in (1,4,6)--Update the data test, set name=name+ ' _123 ' where ID in (3,5)--Displays the result of the test select * from Test a ful L Joinopenrowset (' SQLOLEDB ', ' xz '; ' Sa '; ', test.dbo.test) b on A.id=b.id-----------------------------------A. Method of database Mirroring B. Timed DTS or SSIS Method of incremental Transfer C. Transfer log implementation d.windows service implementation

　　

[Reprint] Data operations between different server databases--Replication Synchronization (Clean edition)