"Go" SQL Server subscription publication, snapshot publication (i)

original link: 11646023SQL SERVER 2012 uses subscription publishing to synchronize databases (i)September 16, 2013 16:51:00Hits: 6517

If the enterprise is bigger, there will be branch offices. The branch and the head office are both unified and independent. This is the business, the technology to serve the business, then the question before us is how to make the data both unified and independent? In fact, SQL Server has provided us with a good solution: publish, subscribe.

Open the Object Explorer for SQL Server2012 we can see that there is a "copy" node in it. (Fig. 1)

Let's take a quick look at the concept of replication:

Replication is the replication and distribution of data or database objects from one database to another, and data synchronization to keep the source and target databases consistent. With replication, data can be distributed to different locations and distributed to remote or mobile users on local and wide area networks, dial-up connections, wireless connections, and the Internet.

Replication consists of publishers, distributors, and subscription servers:

Publisher: 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.

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

Subscribers: Subscribers are copies of the publisher's data and receive maintenance data.

Click to open the replication node we see the following two self nodes: "Local publishing", "Local Subscription" (Figure 2)

Let's take a classic example to explain the release, subscription:

Publisher is similar to a newspaper, the newspaper provides the content of the newspaper and printing, is the data source; The Distributor is the equivalent of a post office, which sends (distributes) newspapers and periodicals to subscribers; Subscribers are the equivalent of subscribers, receiving newspapers from the post office.

The Publisher provides data to other locations through replication, and the distributor acts as a store for replicating specific data associated with one or more publishers. Each publisher is associated with a single database (known as the distribution database) at the Distributor. The distribution database stores replication state data and metadata about the publication, and in some cases, queues the data that is moved from the Publisher to the Subscriber. In many cases, a database server instance acts as the Publisher and distributor two roles. This is known as the local Distributor. A Subscriber is a DB instance that receives replicated data. A subscriber can receive data from multiple publishers.

Okay, let's Digest the theory, let's create a publisher:

On the local publishing node, right-click New publication because I did not install the replication component, there was a bit of an accident (Figure 3)

Rerun the SQL SERVER2012 Setup Wizard and select the SQL Server replication feature (Figure 4)

Next and next to completion (Figure 5)

This time click on "New Release" Hehe, the interface is different (Figure 6)

Click Next (Figure 7)

Let's experiment with "local Distributor" mode and click Next, because I didn't start SQL Server Agent before, so there was a little bit of an accident (Figure 8)

Microsoft has explained very clearly, directly click Next (Figure 9)

Is it a bit confusing to get to this step? All right, let's digest a few concepts first:

Push subscription: Push subscription refers to the Publisher to copy all the modified data to subscribers, it is recommended to use push subscription. 　　 Pull subscription: Pull subscription refers to the data that a subscriber will require to replicate changes to the publishing database over a period of time to the publisher. Publish, distribute, and subscribe can be deployed on stand-alone servers or on a single SQL Server, which can improve performance by deploying separately.

Click Next, because I did not set up my own database so there was a little accident (Figure 10)

All right, you're going to overdo it. Database (Figure 11)

This time the interface is different (Figure 12)

Click Next (Figure 13)

Microsoft's release type description does not seem to be well understood:

(1) Snapshot publication snapshot publication refers to the publication data in the publication database at a certain moment, and then copy the data to the Subscriber server. Snapshot copy implementation is relatively simple, it is only a moment of time the database is replicated, snapshot replication is the entire publication to the subscribers, is at some point the publication of the data for a "photo", a document describing the current state of the data in the publication database a file, Then copy it to the subscribed database at the appropriate time, and snapshot replication does not constantly monitor changes in the publication 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 situations where a large number of changes occur over a certain period of time, but the amount of data is small and the period of change is longer.

(2) A transactional publication snapshot publication sends the entire data set to the subscriber, resulting in a replication lag problem due to the large size of the replication cycle. Then transactional replication uses the transaction log to generate transactions that replicate to subscribers, because it replicates only transactions that are changed, so lag is much lower than snapshot replication.

(3) Merge publication merge publications are designed for mobile users and can be modified at the Publisher or at the Subscriber, when the Merge Agent is running, and these modifications are synchronized and 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 collects the GUIDs in the data table when the Merge Agent runs, indicating 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, the corresponding action, such as Insert,update,delete, resolves the conflict in a user-specified manner if both parties have GUIDs, and the default publisher takes precedence.

Let's choose the simplest release mode (snapshot release) and click Next, because I don't have any data tables in the Publish database so there's been a bit of an accident (Figure 14)

Create Account Data Sheet (Figure 15)

Continue with our launch Tour (Figure 16)

The objects to be published are very detailed, from the data table to each field in the data table, the project properties are also very detailed (Figure 17)

Click Next for more surprising filters (Figure 18)

Microsoft is always very mature, and all we can think of is him. Click Next (Figure 19)

The Snapshot Agent, which generates the schema, data, and data required to track the replication process;

Take a look at the core interface of the Snapshot Agent (Figure 20)

Set up your account (Figure 21)

Give a name for publishing (Figure 22)

Finally, it was done, but there was a little accident (Figure 23)

Take care of the following code:

[SQL]View PlainCopy

1. sp_configure ' show advanced options ', 1;
2. GO
3. RECONFIGURE;
4. GO
5. sp_configure ' Agent XPs ', 1;
6. GO
7. RECONFIGURE
8. GO

Accomplished (Fig. 24)

"Go" SQL Server subscription publication, snapshot publication (i)