High Availability of sqlserver-replication and Publishing

SQL Server 2005 provides all major high availability features in SQL Server 2005 Standard Edition and SQL Server 2005 Enterprise Edition, data mirroring, replication, failover clusters, and log transmission functions are provided without additional investment. The following describes how to copy data.

Replication-(publishing and subscription) is a group of technologies that can replicate and publish data and objects in a database to another database and synchronize data between databases, maintain data consistency.
Introduction to replication:
Replication in SQL 2005 not only copies the content of a database to one or more servers over the network, but also sends changes to the database over the network to other servers. This means ensures data consistency among multiple servers. The data of any server changes, and the data of all other servers changes.
Copy introduction:The copied components include the Publishing Server, publishing, project, distribution server, subscription server, and subscription.

• the publisher is responsible for providing the database content to other servers. The Publishing Server can have one or more releases. Each release defines a set of logically related objects and data to be copied.
• publishing is equivalent to a collection of one or more projects in the database. The Publishing Server publishes data to other servers in units of publishing. Each Publishing Server can only contain content from a single database, but not from multiple databases.
• Project, equivalent to Article in the publication, it is the objects and data to be published by the publishing server. It can be database objects such as data tables or stored procedures, all or part of records in data tables, or even some fields in records. Multiple project teams are released as one.
• the distributor, equivalent to a publisher, is responsible for transmitting the data to be published by the publisher to the subscription server.
• the subscriber is equivalent to a subscriber used to receive data transmitted by the publisher or distributor. Usually there is only one Publishing Server, and the subscription server can be one or more.
• subscription refers to the request to pass a publication to the subscription server. In SQL 2005, two subscription methods are available: Push subscription and request subscription. The push and subscription are actively transmitted by the publishing server to the subscription server. As long as the specified time is reached, the Publishing Server will send the data to the subscription server. The request subscription is opposite to the sending and subscription, when the specified time is reached, the subscriber server requests to subscribe to the Publishing Server.
  

During the replication process, there is also a component named "Copy proxy. Replication can use many independent proxies.ProgramTo perform and track the tasks related to changing and distributing data. The replication proxy is responsible for copying and moving data on the publisher and subscription server.
What should the distribution server do if the Publishing Server can pass the publishing to the subscription server? This problem is as if you can go to the publishing house to buy books, and what else do the bookstore.One important reason is that it can reduce the burden on the Publishing Server..
Replication type:
Different applications have different replication requirements. SQL 2005 provides three different replication types for selection. Snapshot copy, transactional copy, and merge copy .
Snapshot replication It is a simple replication method. Like taking a photo, it is published to the subscription server in an instantaneous state at a specific time without monitoring data updates. In the event of synchronization, the Publishing Server generates a complete snapshot and sends it to the subscription server.
Since the subscription server only accepts the content transmitted by the Publishing Server unilaterally, This method is suitable for a large amount of data changes, but rarely happens. For example, the database changes of the subscription server are unimportant, the Publishing Server has few updates, the subscription server only needs to copy a small amount of data from the Publishing Server, and a large number of changes occur on the publishing server in a short period of time.
Because snapshot replication transfers the entire release to the subscription server, this replication type is not suitable when the data volume is large. This type increases Network burden and affects network performance.
By default, snapshot replication is a read-only release. changes made to the subscription server cannot be transferred to the Publishing Server.
Transactional Replication Type Publishing is based on transactions. When rectification and replication are used, the system records the data changes and architecture changes made on the publisher, and transmits the changes to the subscription server in the form of transaction records when they occur. Therefore, the database of the subscription server has the same modification as that of the Publishing Server.
Transactional replication is usually used when the database content on the Publishing Server changes frequently and needs to be published to the subscription server in a short time. By default, the subscription server for transactional publishing is read-only, and changes to the subscription server cannot be returned to the Publishing Server.
Before performing transactional replication, you must perform a snapshot copy to publish the content of the Publishing Server to the subscription server in the form of a snapshot, then, execute the transactions published in transactional replication mode on the subscription server to maintain database synchronization. This method is similar to restoring transaction log backup.
The combined replication type usually starts from snapshot replication. After snapshot replication, you can use triggers to track data changes and architecture changes made on the publisher and subscription servers. Merge replication allows both the publisher and the subscription server to modify data at the same time, and allows the data modified on the subscription server to be reflected in the publisher. Therefore, it is called "merge replication ".
Merge replication is mostly used when both the publishing server and the subscription server modify data. You can use the combined replication function to update the same data record on multiple subscription servers at different times and pass the changes to the publisher and other subscription servers. You can also change the data offline after the subscription server receives the data, and then synchronize the data with the publishing server and other subscription servers online.
Copy proxy : Replication uses many stand-alone programs called proxies to execute tasks associated with tracking changes and distributing data. In SQL 2005, the replication proxy is divided into the following types.

• Snapshot proxy: The snapshot proxy is usually used with various types of replication. The task of the snapshot agent is to prepare the architecture and initial data of the published table, as well as other objects, store snapshot files, and record the synchronization information to be distributed to the subscription server. The Snapshot Agent runs on the distributor.
• Log Reader Agent: The Log Reader Agent is usually used with transactional replication. The task of the log reader agent is to move the transaction logs marked as copies on the Publishing Server to the distribution database. Each database has a dedicated Log Reader Agent to read its transaction records. The proxy runs on the distributor and connects to the publisher.
• Distribution agent: The distribution agent is usually used together with snapshot copy and transactional copy. The task of the distribution agent is to pass the contents of snapshot and transactional replication to the subscription server. The distribution agent applies the initial snapshot to the subscription server and moves the transactions stored in the distribution database to the subscription server. The distribution agent can run on both the distribution server and the subscription server.
• Merge proxy: The merge proxy is usually used together with the merge replication. Its task is to synchronously update the data content of each server and resolve the conflict in the event of a conflict. The merge proxy applies the initial snapshot to the subscription server, and then moves and coordinates the data changes between the distribution server and the subscription server. Each merge subscription has its own merge proxy, which connects to the publisher and subscription server at the same time and updates them. The merge proxy can run on both the distribution server and subscription server. By default, the merge proxy transfers the data changes on the subscription server to the Publishing Server, and then transmits the data changes on the Publishing Server to the subscription server.
• Queue Reader Agent: The Queue Reader proxy is usually used together with transactional replication that includes queuing update options. The Queue Reader Agent runs on the distributor and moves the data changes made on the subscription server to the publisher. Unlike the distribution agent and merge agent, only one instance of the Queue Reader Agent provides the server for all the publishing servers and publishing servers of the given distribution database. In transaction replication, you can modify the database of the Publishing Server from the subscription server by means of immediate update and queuing update. When the queue update method is used, the Queue Reader Agent is responsible for moving data in the queue to the release.
  

after learning about the basic concepts of replication, perform practice : describes how to configure the Publishing Server, distribution server, and subscription server, and how to implement replication.
steps to create a copy: whether it is snapshot copy, transactional copy, or merge copy, follow these steps.
Step 1: Create a publishing server . Select the server to be published. If possible, you can also create a distributed server. The Publishing Server and the distribution server can be the same server.
Step 2: Start the SQL Server proxy . If the SQL Server Agent is not started, select whether to automatically start the SQL Server Agent when the computer is started.
Step 3: Create release . When a Publishing Server is created, the system will create a Publishing Server. The publishing content includes databases, data tables, stored procedures, and user-defined functions.
Step 4: select the release type . Choose whether to publish a snapshot, publish a transaction, or publish a combination.
Step 5: set replication proxy and Security . Set the account and password for running the agent.
Step 6: Create a subscription server and subscription . after publishing, you must create a subscription server and subscription, including the subscription method and subscription scheduling.

reference example:
1. Two SQL Server Data Synchronization Solutions,
2. Another example
3. Solve Two Database Synchronization problems by using database subscription replication
4. http://blog.csdn.net/no_miss/archive/2006/08/10/1047550.aspx