Implementing data Synchronization using SQL Server transactional Publishing (sql2008) _mssql2008

The functionality of the transaction has been in SQL Server for a long time, because there is a recent data synchronization scheme, so there is a chance to study it again and snapshots, and found that there are many good features and improvements. Here take the sqlserver2008 transaction publishing function as an example, the way to publish a subscription to briefly introduce the operation process, on the one hand to make a summary of the backup, while sharing and exchange with you. Crap will not say more, enter today's topic:

Here is a description of the environment: first I have two machines with sqlserver2008 installed on the local LAN (note: The published snapshot version cannot be compatible with the old version of the database, meaning that the transaction or snapshot publication created under 2008 cannot be sqlserver2005 subscribed). Of course, the two machines should be placed in the same network segment, on the one hand, performance and security considerations, on the other hand, I have not yet found across different segments of the snapshot application (the kind of feasibility).

Okay, here's what I'll do with our product database to introduce how to synchronize data by publishing subscriptions.

First, create a database of our products (called dnt_new) on the database to be published, right-click the copy on the left side of the database instance, click Local Publish, and then select New publication, as follows:

In this way, the system will start the ' Publishing Wizard ' to guide everyone, here we click on "Next" in the Welcome interface, and then select the database to publish in the current window as follows:

Click Next, and then in the next window, select Transactional publishing, as shown in the following figure:

Then click Next to select the Data objects (datasheets, stored procedures, custom functions, etc.) to be synchronized, as follows:

Then there is the "Project Problem window", as it was previously logged in as DBO, so just click the next step, as shown here:

Here you can use the "add" way to filter the data information to sync, because to do the full table data synchronization, so here is not set up, interested friends can study for themselves, here next click Next, as follows:

Then, in the ' Agent Security ' window, click the ' Security Settings ' button:

In the pop-up ' Security Settings ' Sub window, set the following information and click the ' OK ' button:

Then click the Next button:

Select the Create Publication check box, and then click Next, when the wizard lets you enter a "Publish name", which is named "Dnt_new_snap":

Click the "Finish button", where the system starts to create the release object information based on the information gathered earlier, as follows:

Here, the ' Create a release ' job is done. The following is an introduction to the process of creating subscriptions. On the SQL Server instance of another machine, open the instance and use copy-new subscription, as shown in the following figure:

The system starts the New Subscription Wizard, clicks Next, and in the Publish window, in the Publisher Drop-down box, select the "Check out SQL Server Servers" entry, as follows:

Then, in the pop-up window, select the database instance that was used to create the publication and log in, at which point the information from the publisher appears in the list box below:

Select the Release object "Dnt_new_snap" that we created before, then click Next:

In the Distribution Agent Location window, select Run all agents on a distribution server, click Next, and then select the name of the subscription database to synchronize in the Subscription Database list box in the Subscribers window (new):

Click Next, and then in the Distribution Agent Security window, click the ' ... ' in the list box to set the installation and make the following settings (note the Red box section):

Then click on the "OK" button, and then click the "Next" button on the wizard, then the system will display the "Agent Plan execution Mode" window, select "Running continuously":

Click Next to select "Execute Now" in the window:

Having done this, click the Next button and then you can create the subscription object, and if everything works, SQL Server synchronizes the data tables and stored procedures specified previously to the current ' subscription database ' from the ' publisher ' side.

At this point we can add or modify the specified table data information in the table on the source database (publisher), and after waiting 1-3 seconds, the additions and modifications are synchronized to the corresponding table on the ' subscription database '. It seems the purpose is reached, hehe.

Well, today's content is here.

Note: The two machines in this article must be able to use the SQL Server client interconnect (set ' Allow remote links ' in SQL Server Studio), set the appropriate IP address, and open tcp/in the Configuration Manager IP protocol)

Author: Daizhj, the generation of earthquake troops