SQL SERVER 2012 uses subscription publishing to synchronize databases

    Software is bigger, customers more, a database server is far from enough, when there is a data server gua, then the whole system will crash, so must consider the database of automatic synchronization and backup, when a database service

Downtime, it is natural to use a data server to start up to ensure the availability of the entire software system. The backup database and the total database are both unified and independent. This is the business, the technology to serve the business, then the question before us is

How do you make 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, you can use the LAN and WAN,

Dial-up Connections, wireless connections, and the Internet distribute data to different locations and distributed to remote or mobile users.

    Replication consists of publishers, distributors, and subscription servers:

1. 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.

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

3. 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, he sends (distributes) newspapers and periodicals to subscribers; Subscribers are the equivalent of subscribers, from the Post office

Received the press.

    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 server at the Distributor

Database (called a distribution database). 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 a very

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 be used from multiple

The publisher to receive data from a server.

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, next to finish.

Click "New Release" Again this time, the interface is different (Figure 5)

Click Next (Figure 6)

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 7)

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

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, type the Database Publishing Wizard (select the database you want to publish) (Figure 9)
Click "Next" 10
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 replication implementation is simple, it is only a moment of the database of the instantaneous data, snapshot
Replication is the transfer of the entire publication to subscribers, which is a "photograph" of the published data at a time, generating a file that describes the current state of the data in the publication database, and then copying it to
On the subscribed database, snapshot replication does not constantly monitor changes in the publication database, it is a scan of the publication database, sending all published data from the source database to the target database without
Just a change of 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
A type of replication that ensures consistency of data 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 transaction logs to generate transactions that are replicated to subscribers.
The lag is much lower than snapshot replication because it replicates only transactions that change.
(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,
When the Merge Agent runs, it collects GUIDs in the data tables that indicate which rows were modified at the publisher and at the Subscriber. For data that is modified only at the publisher or at the Subscriber, the corresponding actions are made directly, such as
Insert,update,delete, if both parties have a GUID, the conflict is resolved in a user-specified manner, and the default publisher server 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 11)
Create a userlist data sheet (Figure 12)
Continue with our launch Tour (Figure 13)
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 14)
Click Next to see the "Add" button in the top right corner and a more surprising filter (Figure 15)
Microsoft is always very mature, and all we can think of is him. Click Next (Figure 16)
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 17, Figure 18)

Set up your account (Figure 19)

Click Next (Figure 20)

Give a name for publishing (Figure 21)

Finally, it's done. (Fig. 22)

SQL SERVER 2012 uses subscription publishing to synchronize databases