Data synchronization between two SQL servers

Microsoft SQL Server 2000 replication concept: A group of technologies that replicate, distribute, and synchronize data and database objects between databases to ensure consistency. Data can be distributed to different locations by means of replication. data can be distributed to remote or mobile users through a LAN, a dial-up connection, or the Internet. Replication also improves applicationsProgramPerformance: physically separates data (for example, separating online transaction processing (OLTP) from Decision Support Systems) based on data usage, or distributes database processing across multiple servers. The basic elements of SQL replication include: Publishing Server, subscription server, distribution server, publishing, and project. Publishing Server The Publishing Server is a server that provides data to be copied to other servers. The Publishing Server can have one or more releases. Each release represents a set of Logical Data. In addition to specifying which data needs to be replicated, the publisher also detects the changed data during transaction replication and maintains information about all the releases on this site. Distributor A distributor is a server that hosts distributed databases and stores historical data and/or transactions and metadata. The role of the distributor varies depending on the replication type. For more information, see replication types. A remote distribution server is a replication-independent distribution server. A local distribution server is a replication distribution server. Subscription Server The subscription server is the server that receives the copied data. The subscription server subscribes to a release instead of a project separated from the release. The subscription server only subscribes to the desired release, rather than all the available releases on the Publishing Server. Based on the replication type and the selected replication options, the subscription server can also spread the data changes back to the Publishing Server or re-publish the data to another subscription server. Release Publishing is a collection of one or more projects in a database. The grouping of multiple projects makes it easier to copy a set of data related to the specified logic and database objects together. Project A project is a data table, data partition, or database object to be copied. The project can be a complete table, a few columns (using vertical filtering), and several rows (Using Horizontal Filtering), stored procedure or view definition, execution of stored procedure, view, index view or user-defined function. Subscription Subscription is a request to copy data or database objects. Subscription defines the time and place at which the subscription will be published and received. Subscription synchronization or data distribution can be requested by the Publishing Server (Force subscription) or the subscription server (request subscription. Publishing supports the combination of forced subscription and request subscription. Concept of Replication Replication is a technology that copies a set of data from one data source to multiple data sources. It is an effective way to publish a set of data to multiple storage sites. With the replication technology, users can publish a copy of data to multiple servers, so that different server users can share the data within the permitted range of permissions. The replication technology ensures that data distributed in different locations is automatically updated synchronously to ensure data consistency. The basic elements of SQL replication include Publishing servers, subscription servers, distribution servers, publications,Article How SQL replication works SQL Server mainly uses publications and subscriptions to process replication. The server on which the source data is located is the publishing server, which is responsible for publishing data. The Publishing Server copies all changes to the data to be published to the distribution server. The distribution server contains a distribution database that can receive all changes to the data and save the changes, and 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. A distribution database (distribution) is also generated) 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) Select the database for which you want to create the publication, and 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] system requirements to specify the type of database server that can subscribe to the release. sqlserver allows data replication between different databases, such as oracle or access. But here we choose to run the database server "SQL Server 2000" (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 created, the database for creating the publication becomes a shared database. 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, click live scheduling, and set Scheduling to every day. occurred, 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, open your database. Check the specified subscription database of the subscribed server to see if you have seen the table you just released- /****************** FAQ: permission issues The domain user account that starts the proxy must have the permission to read the distribution server directory. Your account is not read\ Servera \ D $ \ ProgramFiles \ Microsoft SQL Server \ MSSQL \ repldata \ UNC \ permission for files under this directory. The domain user account that starts the proxy must have the permission to read the distribution server directory. ---- ^ Start user in the control panel --> Manage --> service --> SQLAgent --> attribute ------------------------------------------------- Here is an example of preparation: 1. On the Publishing Server, the subscription server creates a Windows user with the same name, and sets the same password as the valid user who accesses the snapshot folder. My computer -- Control Panel -- Management Tools -- Computer Management -- Users and groups -- Right-click the user -- Create a user -- Create a Windows login user affiliated to the Administrator Group 2. Create a new shared directory on the Publishing Server as the directory for storing the published snapshot files. perform the following operations: My computer -- D: \ creates a directory named pub -- Right-click the newly created directory -- Property -- share -- Select "share this folder" -- Use the "permission" button to set specific user permissions to ensure that the user created in step 1 has all permissions on the folder -- OK 3. Set the startup user of the SQL proxy (SQLServerAgent) Service (this setting is done by the publishing/subscription server) Start -- program -- management tool -- service -- Right-click SQLServerAgent -- Property -- login -- select "this account" -- Enter or select the Windows logon user name created in step 1. -- Enter the user's password in "password" 4. Set the SQL Server Authentication Mode to solve the permission issue during connection (this setting is done by the publishing/subscription servers) Enterprise Manager -- Right-click an SQL instance -- Properties -- Security -- Authentication -- Select "SQL Server and Windows" -- OK 5. Register with each other on the Publishing Server and subscription Server Enterprise Manager -- Right-click the SQL server group -- Create an SQL Server registration... -- Next -- available server, enter the name of the remote server you want to register -- add -- Next -- connect to use, select the second "SQL Server Authentication" -- Next -- enter the user name and password -- Next -- select an SQL Server group or create a new group. -- Next -- complete 6. Register a server alias for an IP address-only computer name. (Configure on the connection end. For example, if the server is configured on the subscription server, enter the IP address of the Publishing Server in the server name) Start -- program -- Microsoft SQL Server -- client network utility -- Alias -- add -- Select "TCP/IP" for the network library -- enter the SQL server name as the server alias -- Connection parameter -- enter the SQL Server IP address in the server name -- If you have modified the SQL port, deselect "dynamically determine port" and enter the corresponding port number. --------------------------------------------------- Official start: 1. Configure the Publishing Server A. Select the specified [server] Node B. Select the [Publish, subscribe server, and distribute] command from the [copy] submenu in the [tools] drop-down menu. C. In the dialog box that appears, click [next] and follow the prompts. -- Until "specified snapshot folder" -- Enter the directory created during preparation in the "snapshot folder": \ <Server Name> \ pub 1. [next] The operation is complete. D. After setting the Publishing Server, the system will add a replication monitor to the tree structure of the server. A distribution database (distribution) is also generated) --------------------------------------------------------------------------- 2. Create a release A. Select the specified server B. Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu. At this time, the system will pop up A dialog box C. Select the database for which you want to create a release, and click [Create release] D. Click [next] in the prompt dialog box of [Create release wizard]. A dialog box is displayed. Dialog The content in the dialog box is the three types of replication. Now we select the first one, that is, the default snapshot release (the other two You can check the help) E. Click [next] system requirements to specify the type of database server that can subscribe to the release. sqlserver allows Data is replicated between different databases, such as oracle or access. But here we choose to run "SQL Server 2000" Database Server F. Click [next] and select the objects to be published (such as tables, views, stored procedures, and tables) G. Then [next] until the operation is completed. After the publication is created, the database for creating the publication becomes A shared database. --------------------------------------------------------------------------- 3. Design subscription A. Select the specified subscription Server B. Select [subscribe request] from the [tools] drop-down menu in the [copy] submenu C. Follow the prompts and click [next] until the system prompts you to check the running status of the SQL Server proxy service. The precondition for the replication operation is that the SQL Server proxy service must be started. D. Click [finish] to 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, and then click agent properties to view live Scheduling Set scheduling to happen every day, every minute, between 0:00:00 and 23:59:59. The next step is to determine whether the replication is successful. Open 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 still don't believe it, open your database and check whether the table you just published is displayed in the specified subscription database of the subscribed server. Another manual synchronization Solution -- Regularly synchronize data on the server -- Example: -- Test environment: SQL Server2000, remote server name: xz, User name: SA, no password, Test Database: 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 -- To facilitate synchronization, create a connection server to the server to be synchronized -- 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 -- Create a stored procedure for synchronization 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 -- Synchronize Deleted Data Delete from srv_lnk.test.dbo. [user] Where id not in (select ID from [user]) -- Synchronize new data Insert into srv_lnk.test.dbo. [user] Select ID, number, name from [user] Where state is null -- Synchronize modified data 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 -- Update the local flag after synchronization Update [user] Set state = 0 where isnull (State, 1) = 1 -- Commit tran Go -- Create a job and regularly execute the data synchronization Stored Procedure If exists (select 1 from MSDB .. sysjobs where name = 'data ') Execute MSDB. DBO. sp_delete_job @ job_name = 'data' Exec MSDB .. sp_add_job @ job_name = 'data' -- Create a job Declare @ SQL varchar (800), @ dbname varchar (250) Select @ SQL = 'exec p_synchro '-- Data Processing Command , @ Dbname = db_name () -- Name of the database for processing data Exec MSDB .. sp_add_jobstep @ job_name = 'data ', @ Step_name = 'data synchronization ', @ 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 = 'data ', @ Name = 'schedule ', @ Freq_type = 4, -- daily @ Freq_interval = 1, -- run once a day @ Active_start_time = 00000 -- execute at 0 Go