SQL SERVER uses subscription publishing to synchronize databases

First,database replication involves

1. Publishing Server:

The source server for the data, maintains the source data, determines what data will be distributed, detects which data has been modified, and submits that information to the Distributor.

2. Distribution server:

The Distributor is responsible for transferring data from the Publisher to the Subscriber.

3. Subscriber server:
A subscriber is a copy of the publisher's data and receives maintenance data.

4. Subscription type
Push Subscriptions
Push subscription refers to the replication of all the modified data to subscribers by the publisher, which is recommended to use push subscriptions when data synchronization is cost-effective.
Pull Subscription
Pull subscriptions are data that a subscriber will require to replicate the publication database for a period of time to the publisher.

Publish, distribute, and subscribe can be deployed on stand-alone servers or on a single SQL Server, but separate deployments can certainly improve performance.

Second, replication agent:

　　1. Snapshot Agent
The Snapshot Agent creates and stores snapshot files on the Distributor by preparing the schema and initial data of the published table and other objects in the distribution database, storing the snapshot files, and recording the synchronization information between the publishing database and the Subscriber, which is run on the Distributor server and connected to the publisher. Each publication has its own Snapshot Agent. Used in conjunction with various types of replication.

　　2. Log Reading Agent
The Log Reader Agent is used with transactional replication. It moves the transaction marked as replicated in the transaction log at the Publisher to the distribution database. Each database that is published with transactional replication has its own Log Reader Agent that runs at the distributor and connects to the Publisher (the Distributor and publisher can be the same computer).

　　3. Distribution Agent
The Distribution Agent is used with snapshot replication and transactional replication. It uses the initial snapshot for the subscriber and moves the saved transactions from the distribution database to the Subscriber. The Distribution Agent can run either at the Distributor (for push subscriptions) or at the Subscriber (for pull subscriptions).

　　4. Merger Agent
The Merge Agent is used with merge replication. It uses the initial snapshot for the subscriber and moves and coordinates the incremental data changes that occur. Each merge subscription has its own Merge Agent, which connects to and updates the Publisher and Subscribers at the same time. The Merge Agent can run either at the Distributor (for push subscriptions) or at the Subscriber (for pull subscriptions). By default, the Merge Agent uploads changes at the Subscriber to the Publisher, and then downloads the changes at the Publisher to the Subscriber.

　　5. Queue Reading Agent
The Queue Reader Agent is used with transactional replication that contains queued updating options. The agent runs at the distributor and moves the changes made at the Subscriber back to the publisher. Unlike the Distribution Agent and Merge Agent, only one instance of the Queue Reader Agent serves all publishers and publications for a given distribution database.

Iii. Type of replication:

SQL Sever provides three major types of replication: snapshot replication, transactional replication, merge replication. The corresponding replication types can be used in real-world applications, and each type of replication implements data consistency across different programs.

　　1. Snapshot replication
As the name suggests, snapshot replication refers to taking photos of published data in a publication database at a given moment, and then copying the data to the Subscriber server. The snapshot replication implementation is simple, and it replicates only the instantaneous data of the database at a certain moment,
Snapshot replication is the transfer of the entire publication to subscribers, that is, a "photo" of the published data at a moment, a file describing the current state of the data in the publication database, and then copying it to the database on the subscription at the appropriate time. Snapshot replication does not constantly monitor changes in the publishing database, it is a scan of the publication database, sending data from all published data from the source database to the target database, not just the changed data. If the amount of data is large, then there is a lot of data to replicate. Therefore, the network resource requirements are very high, not only to have a faster transmission speed, but also to ensure the reliability of transmission.

Snapshot replication is the simplest type of replication that guarantees data consistency between publishers and Subscribers. Snapshot replication is typically used in the following situations:
A large number of changes occur within a certain period of time, but the amount of data is small and the period of change is longer.

　　2. Transactional replication
Snapshot replication is the replication lag issue that occurs when the entire data set is sent to the Subscriber because of the large volume that causes the replication cycle to be long. Then transactional replication uses the transaction log to generate transactions that replicate to subscribers because it replicates only transactions that are also variable, so lag is much lower than snapshot replication because it will continue to be applied at the Subscriber at all time.
There are three components of transactional replication:
The Snapshot Agent, which generates the schema, data, and data required to track the replication process;
Distribution Agent: It distributes snapshots and subsequent commands;
Log Reader Agent: It reads the transaction log of the published data.
In transactional replication, this change is immediately communicated to subscribers when the publication database changes. And in a relatively short period of time (a few seconds), rather than a long time interval like snapshot replication. Therefore, transactional replication is a method of distributing data from source to destination in near real time. The frequency of transactional replication is high for some reason. It is important to ensure a reliable network connection between subscribers and publishers.

　　
　　3. Merge replication
Merge replication is designed for mobile users and can be modified at the Publisher or at the Subscriber, which is synchronized when the Merge Agent runs, and is used for both the publisher and the subscription service to modify the data. Works as follows: Implement the trigger on each table that you want to replicate, and use the Include GUID column to uniquely identify each row in the table that you want to copy. When you modify any of these tables, the changes are recorded in a data table that, when the Merge Agent runs, collects the GUIDs in the data tables that indicate the rows that were modified at the publisher and at the Subscriber. For data that is modified only at the publisher or at the Subscriber, such as insert,update,delete, if both parties have GUIDs, the conflict is resolved in a user-specified manner, with the default publisher server Volt-first.

To configure replication:
Whether it's snapshot replication, transactional replication, or merge replication, there are several steps to creating replication:
1. Create a publisher. Select the server you want to publish. If you have a condition, you can also distribute the server, where we set the Publisher and Distributor on the same computer.
2. The proxy service must be turned on either by the Publisher or the Subscriber.
3. Create a publication. Publish the databases and objects that will be needed.
4. Choose a release type that suits you.
5. Sets the replication agent and security, which specifies the user account that can run the agent.
Create a Subscriber that can use this publication.

Iv. The specific process of publication is as follows:

1. Expand the Replication node under the SQL Server 2008 server, and you will find the local publishing and local subscriptions two nodes, right-click the Local Publishing node and select New publication:

2. If the server publishes the settings for the first time, it will pop up the Publish Wizard dialog box, set Distributor, and select the first one:

3, click "Next" will ask us "Start SQL Server Agent" way, because it is a demonstration, I choose is the manual way:

4, click "Next", will appear to set the "Snapshot Folder" dialog box, we choose the default:

5, click "Next", select the database to be published, we select the database lx_data1:

6, click "Next", select "Release Type", we select "Transactional release". Here to illustrate if you do not understand the various publishing types in this dialog box, click here to select the appropriate type of replication

7, click "Next", select the database publish object, our example database has only one table t_student, we select this table:

8. Click "Next" to go to the "Filter Table Rows" dialog box:

9, if you need to filter the table row click "Add" will appear the following Filter dialog box:

10, we do not filter, to synchronize all, click "Cancel", directly "next", go to the "Snapshot Agent" Settings dialog box:

11, there are two options to create a snapshot immediately, one is to specify a plan (such as can specify XXX days XXX time run), we select the first, and click "Next" to enter the "security of the agent" setting:

12. Click on "Security Settings" and we select the SQL SERVER User:

13. After clicking "OK", return to the "Agent Security" Settings dialog box and continue to click "Next":

14, click "Next", we give the publication named: "Lx_data1_publisher":

15. Click "Finish" to create the publication and show the results of creating the publication:

Subscription

After the publication is complete, we will subscribe to the following process:

1. Right click on the "Local subscriptions" node, open the "New Subscription Wizard" dialog box:

2. We select the database release Lx_data1_publisher in the publisher lx-pc, and then click "Next":

3. In the Distribution Agent Location dialog box, select the first item and click Next:

4. In the Subscribers dialog box, we select Subscribe to database Lx_data2 and then click Next:

5, in the "Distribution Agent Security" dialog, we click "..." to enter the security settings, we chose the SQL account:

6, enter the account number and password, click "OK", return to the "Security Settings" dialog box, click "Next":

7, we choose the default "continuous running", click "Next" to enter the "Initialize subscription" dialog box:

8, initialization time, select "Immediate" initialization, click "Next":

9. Do not create script file, click "Next":

10, click "Done", will create a subscription, and display the creation results:

At this point, the server's publications and subscriptions have been created successfully. Let's test that there is no data in the two database tables and two data is inserted into the T_student table in Lx_data1:

Use Lx_data1goinsert into t_student values (1, ' Sylvester Stallone ', 22, ' New York Elementary ', ' one shift ', ' ') insert into t_student values (2, ' Schwarzenegger ', 23, ' Washington Elementary ' , ' One shift ', 90)

We then looked at the results of two databases:

Use Lx_data1goselect * from T_studentuse lx_data2goselect * from T_student

As a result we will find that the data is synchronized, such as:

Five, matters needing attention

1, whether it is publishing replication or subscription services must be installed in SQL Server native operation, cannot be a remote connection operation

2. Share this folder, and then use the UNC path of the shared folder as the snapshot folder path. This folder provides read and write permissions to SQL Server service and Agent service.

3, SQL Server another image to achieve a copy of the data, the image is only the primary server read and write, from the server is not support read and write, and replication can be read from the server to write the master server. Therefore, the realization of read-write separation is often achieved through database replication.

4. UNC can do network-driven mapping or FTP connection

5, the Redistribution server set up the subscription when the system will be prompted to use the computer name, and the computer name in different networks can not directly access, this time need to set the alias, alias to note that both the client and 32client to add

SQL SERVER uses subscription publishing to synchronize databases (GO)