SQL Server 2005 database replication Detailed introduction _mssql2005

For a geographically dispersed large enterprise organization, to build a large enterprise management information system with typical distributed computer features, it is always necessary to solve a very important problem: how to ensure the consistency of shared data among different database servers. The important problem is that the organization changes the local database with the same structure in different locations, but it is necessary to ensure that the modified database has the same result, the essence of which is that the changes to the local database are reflected in other remote databases with the same data. So how to solve this problem requires the use of database replication technology.

SQL Server provides built-in replication capabilities, and replication components are not add-on products, but part of the core engine. With the support of replicating this technology, users can maintain multiple copies of data across LAN, WAN, or Internet data servers to ensure data consistency.

Copy model;
SQL Server uses the term publish and subscribe to describe its replication activity, which is called publishing, which replicates data to other database servers (subscribers) that receive replicated data from another server (publisher). Although the publication and subscription of the object is to copy the data, but the publication and subscription is not different angle of the same data operation, but reflects a certain level and order (always publish first, then subscribe). The replication component of SQL Server has publications and papers. Publishers, subscribers, push subscriptions, and pull subscriptions.

(1) Publications and papers

A paper is a collection of data that is copied, a paper is generally the table we speak of, the paper is the basic unit of the publication. A publication is a collection of papers. It can include one or more paper subscribers, subscribing to a publication instead of a paper in a publication, which makes subscriptions simpler.

(2) Publisher

Publishers are the servers that publish their publications. Publisher server to maintain the source database and information about the publication. Make the data available for replication. In addition to deciding which data will be replicated, publishers want to detect which replicated data has changed and replicate these changes to the Distributor's distribution database.

(3) Distributors

A Distributor is a server that sends copies of data or transactions from publishers to the appropriate subscribers, and is responsible for maintaining the distribution database.

(4) Subscribers

A subscriber is a copy machine that stores replicated data. and a server that receives and maintains published data, subscribers can also modify the publication data, although subscribers can modify the data, but it is still a subscriber and, of course, subscribers can also be publishers of other subscribers.
Publishers, distributors, and subscribers do not actually necessarily refer to each other as separate servers. It is simply a description of the different roles that SQL Server plays in the replication process. SQL Server allows a single SQL Server server to play a different role. For example, a publisher's server can publish publications, as well as distributors to store and transfer snapshot replication and transactional replication. In practice, the user decides whether to have one server play one or more roles. In large programs that are based on considerations of replication system performance, for example, in order to increase the efficiency of distributors to replicate publications from the distribution database to subscribers, and to reduce the load on publishers ' servers, users often do not allow a server to act as both Publisher and Distributor. Instead, it allows other server stations to assume the distributor task, thereby improving the performance of publishers and distributors.

(5) Subscription type

Push and pull subscriptions
A push subscription is a copy of all changes that occur in the publication database by the Publisher to the Subscriber, instead of making a request to the subscriber, as long as the publication database changes, the Publisher will automatically put this change in the order of the subscribers, in the case of high data synchronization requirements, it is best to use push subscriptions, A pull subscription is a change in the subscriber's request to the Publisher to replicate the publication database over a period of time.

Replication agent:

(1) Snapshot Agent

The Snapshot Agent creates and stores snapshot files on the Distributor. The task is to prepare schema and initial data for published tables and other objects in the distribution database, to store snapshot files, and to record synchronization information between the publication database and Subscribers, which runs on the Distributor server and is connected to publishers. Each publication has its own Snapshot Agent. Used with various types of replication.

(2) Log reading Agent

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

(3) Distribution Agent

The Distribution Agent is used in conjunction with snapshot replication and transactional replication. It uses the initial snapshot to the subscriber and moves the transactions saved in 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) Merge Agent:

The Merge Agent is used with merge replication. It uses the initial snapshot to the subscriber and moves and coordinates the incremental data changes that occurred. Each merge subscription has its own Merge Agent, which connects to both the Publisher and the Subscriber and updates them. 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 the Merge Agent, only one instance of a Queue Reader Agent serves all publishers and publications for a given distribution database.
Replication type: SQL Sever provides three major types of replication: snapshot replication, transactional replication, merge replication. The corresponding replication types can be used in practical applications, and each type of replication implements data consistency on different programs.

(1) Snapshot replication

snapshot replication, as its name says, refers to taking pictures of published data in a publishing database at a given moment, and then copying the data to the Subscriber server. Snapshot replication is simpler, and it replicates only the instantaneous data of a database at a given moment,
Snapshot replication is the delivery of the entire publication to subscribers, the publication of a "photo" at a time, a file that describes the current state of the data in the publication database, and then copies it to the database of subscriptions at the appropriate time. Snapshot replication is not a constant monitoring of the changes in the publication database, which is a scan of the publishing 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, there is a lot of data to replicate. Therefore, the requirements of network resources 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 to ensure data consistency between publishers and Subscribers. Snapshot replication is typically used on the following occasions:
A large number of changes have been made over a period of time, but the amount of data is small and the period of change is long.

(2) Transactional replication

Snapshot replication is the entire dataset to the Subscriber, due to the large size of the replication cycle is long, resulting in replication latency problems. Transactional replication uses the transaction log to generate transactions that will replicate to subscribers because it replicates only transactions, so latency is also much lower than snapshot replication because it will continue to be applied at the Subscriber at all time.

Transactional replication has three components:

The Snapshot Agent, which generates the schema, data, and data needed 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 passed on to subscribers when the publication database changes. And completes in a relatively short time (a few seconds), rather than taking a long time interval like snapshot replication. Therefore, transactional replication is a method of distributing data from source to destination in a near real-time manner. Transactional replication is a high frequency for some reason. It is important to ensure that there is a reliable network connection between the Subscriber and the publisher.

(3) Merge replication

Merge replication is designed for mobile users and can be modified at the Publisher or at the Subscriber, and will be synchronized when the Merge Agent is run, and used for both the publisher and the subscription service to modify the data. It works as follows: Implement triggers on each table that you want to replicate, and use the containing GUID column to uniquely identify each row in the table that you want to replicate. When you modify any of these tables, the changes are logged in a datasheet that, when the Merge Agent runs, collects the GUIDs in the datasheet that indicate the rows that were modified at the Publisher and Subscriber. For data that is modified at the Publisher or subscriber directly, such as insert,update,delete, if both parties have GUIDs to resolve the conflict in the manner specified by the user, the default publisher is first.

Configure replication: Whether snapshot replication, transactional replication, or merge replication, the following steps are needed to create 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 up the Publisher and Distributor on the same machine.
2. The agent service must be turned on either the Publisher or the Subscriber.
3. Create a publication. Publish the database and objects that are needed.
4. Select a publication type that is appropriate for you.
5. Set up replication agent and security, which specifies the user account that can run the agent.

Create Subscribers that can use this publication.
Now our experiment is based on the following figure:

We copy the DB1 database on the SERVER1 to the SERVE2 server
The specific actions are as follows:
SERVER1 Action: Instance name----Replication----Local Publishing-----New publication

Here we choose to use the publisher itself as its own distributor. A snapshot folder is specified below to hold the snapshot information and select the publication database in turn DB1

Next specify the type of replication; we'll take snapshot replication first.

Several release types have been explained earlier, and transactional publications with updatable subscriptions are one of the transactional publications.

In this dialog box, you can specify the Windows user that is used to run the Snapshot Agent at the Distributor. The Windows user is also known as the process account because the agent process is running under that account.

The publication completes successfully, and if you want to modify the publication properties, you can publish it locally in the replication---------See the publication

The following is a subscription that can be made at the Publisher or at the Subscriber.

Subscribe at Subscriber:

SERVER2 Action: Instance name-----Replication-Local subscription----new Subscription
Subscribe at Publisher: Instance name-----Replication-Local publishing------Right-click Properties---select New Subscription

The next two things are the same:

Select the Publisher, and then specify the type of replication: we choose to push replication and select a database for SERVER2

Pop up the Distribution Agent Security dialog box as shown in the figure. In this dialog box, you can set up an account that runs the Distribution Agent process when synchronizing subscriptions:

For push subscriptions, the Distribution Agent runs on the Distributor. For pull subscriptions, the Distribution Agent runs at the Subscriber. In this example, a push subscription is used, so users who should use the publisher
Connecting to the Distributor: For push subscriptions, you can only connect to the Distributor by simulating the process account.
For pull subscriptions, you can connect using either an impersonation account or a SQL Server user.
Connecting to Subscribers: For push subscriptions, you can connect by simulating processes or SQL Server users.
For pull subscriptions, only analog accounts can be used to connect.

Test:
At this point we can see that the YG table in the publisher has been replicated to the subscriber.
We insert a record in the YG table in the SERVER1 DB1 database, Duffy male 29

As for transactional replication and merge replication, the process is about the same as just paying attention to the table's primary key and GUID issues, which are no longer described here, and if you have questions or disagreements please criticize the designation

Duffy
2008-06-30

Common mistakes;

1. Agent service must be run beforehand

2. In the case of a ghost system where the computer name is inconsistent with the name of the server that is recognized in SQL Server, use the following code to synchronize the update.

If SERVERPROPERTY (' servername ') <> @ @servername 
begin 
declare @server sysname 
set @server = @@ ServerName 
exec sp_dropserver @server = @server 
Set @server = Cast (serverproperty (' servername ') as sysname) 
exec sp_addserver @server = @server, @local = ' local ' 

Restart SQL SERVER Core Services and agent services

This article comes from "Duffy" blog