SQL Server transactional publication for data synchronization

The functionality of the transaction has been in SQL Server for a long time, because it has recently been a data synchronization scheme, so there is a chance to study it again and snapshots, and found that there are a lot of good features and improvements. Here, as an example of sqlserver2008 's transactional publishing function, the way to publish a subscription briefly introduces the operation Flow, on the one hand to do a summary backup, on the other hand to share and exchange with you. Crap not much to say, into the topic of today:)

Here's an explanation of the environment: first I have two machines with sqlserver2008 installed on the local area network (note: The released snapshot version cannot be compatible with the older version of the database, meaning that 2008 transactions or snapshot publications created under the sqlserver2005 subscription cannot be subscribed to). Of course, the two machines to be placed in the same network segment, on the one hand, performance and security considerations, on the other hand, I have not yet found a snapshot application across different network segments (the kind of feasibility).

Well, here I'm going to take a look at our product database to explain how to synchronize data by publishing a subscription.

First, create a database of our products on the database to be published (this is called dnt_new), then right-click on the "Copy"--"local publishing" navigation on the left side of the DB instance and select "New Publication", as follows:

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

Click Next, then select "Transactional publication" in the next window, such as:

Then click Next, select the Data Objects (data tables, stored procedures, custom functions, etc.) to synchronize, as follows:

Then there is the "Project problem window" because it was previously logged in as DBO, so just click on the next step, such as:

Here can be "add" way to filter to synchronize the data information, because to do the full table data synchronization, so here do not set up, interested friends can do their own research, here and then click Next, as follows:

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

Set the following information in the pop-up ' security Settings ' Subwindow and click on the ' OK ' button:

Then click the Next button:

Select the Create Publication check box, and then click Next, and the wizard will let you enter the publication name, which is named "Dnt_new_snap":

Click the "Finish button", where the system begins to create the information for that publication based on the information previously collected, as follows:

Here, the ' Create a release ' job is done. The process of creating a subscription is described below. On the SQL Server instance of another machine, open the instance and use copy-new subscription, such as:

The system launches the New Subscription Wizard, click Next, and in the Publish window, in the Publisher drop-down box, select the "Check and publish SQL Server Servers" entry, as follows:

Then, in the pop-up window, select the database instance that you used to create the publication and check in, and the publisher information appears in the list box below:

Select the publication object "Dnt_new_snap" that we created earlier, and then click Next:

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

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

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

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

After this step, click the Next button and then you can create the subscription object, and if everything works, SQL Server synchronizes the previously specified data tables and stored procedures 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 for 1-3 seconds, the additions and modifications are synchronized to the corresponding table on the ' subscription database '. It seems that the aim is achieved, hehe.

Well, today's content is here.

Note: The two machines in this article must be able to use SQL Server client interconnect (SET ' Allow remote link ' in SQL Server studio, set the appropriate IP address, and enable the TCP/IP protocol in Configuration Manager)

SQL Server transactional publication for data synchronization