Go---SQL SERVER 2008 Implementing database synchronization with publish subscriptions

Transferred from: http://www.cnblogs.com/lxblog/archive/2012/11/08/2760650.html

There are many ways to synchronize databases, where SQL Server 2008 is an example of how database publishing and subscribing can be used to demonstrate database synchronization techniques. Because there is a computer, you can only perform data synchronization between two different databases under the same server.

First we set up two database lx_data1 and LxData2 in SQL Server, and set up table T_student in the two databases, the script is as follows:

CREATETABLET_student (IdIntNotNull, NameNVARCHAR (10)nulltinyint nullnvarchar (20) nullnull, score float nullconstraint pk_student_id key clustered (Id))          

The publish subscription is divided into two steps: 1, release. 2 subscriptions. You first publish the data that needs to be synchronized on the data source database server, and then subscribe to the above publication on the target database server. A publication can publish part of a table's data, or it can publish an entire table, a stored procedure, and so on. Here's a demonstration of these two procedures:

Publish

Note: SQL Server requires an actual server name to connect to the server when it is published. Connections via server aliases, IP addresses, or other alternate names are not supported. Therefore, if the current SQL Server connection is an IP address, it is best to disconnect and log back in with the actual server name.

The specific process for publishing is as follows:

1. Expand the Replication node under the SQL Server 2008 server, and you will find the local publishing and local subscriptions two nodes, right-click the Local Publishing node and select New publication:

2. If the server publishes the settings for the first time, it will pop up the Publish Wizard dialog box, set Distributor, and select the first one:

3, click "Next" will ask us "Start SQL Server Agent" way, because it is a demonstration, I choose is the manual way:

4, click "Next", will appear to set the "Snapshot Folder" dialog box, we choose the default:

5, click "Next", select the database to be published, we select the database lx_data1:

6, click "Next", select "Release Type", we select "Transactional release". Here to illustrate if you do not understand the various publishing types in this dialog box, click here to select the appropriate type of replication

7, click "Next", select the database publish object, our example database has only one table t_student, we select this table:

8. Click "Next" to go to the "Filter Table Rows" dialog box:

9, if you need to filter the table row click "Add" will appear the following Filter dialog box:

10, we do not filter, to synchronize all, click "Cancel", directly "next", go to the "Snapshot Agent" Settings dialog box:

11, there are two options to create a snapshot immediately, one is to specify a plan (such as can specify XXX days XXX time run), we select the first, and click "Next" to enter the "security of the agent" setting:

12. Click on "Security Settings" and we select the SQL SERVER User:

13. After clicking "OK", return to the "Agent Security" Settings dialog box and continue to click "Next":

14, click "Next", we give the publication named: "Lx_data1_publisher":

15. Click "Finish" to create the publication and show the results of creating the publication:

Subscription

After the publication is complete, we will subscribe to the following process:

1. Right click on the "Local subscriptions" node, open the "New Subscription Wizard" dialog box:

2. We select the database release Lx_data1_publisher in the publisher lx-pc, and then click "Next":

3. In the Distribution Agent Location dialog box, select the first item and click Next:

4. In the Subscribers dialog box, we select Subscribe to database Lx_data2 and then click Next:

5, in the "Distribution Agent Security" dialog, we click "..." to enter the security settings, we chose the SQL account:

6, enter the account number and password, click "OK", return to the "Security Settings" dialog box, click "Next":

7, we choose the default "continuous running", click "Next" to enter the "Initialize subscription" dialog box:

8, initialization time, select "Immediate" initialization, click "Next":

9. Do not create script file, click "Next":

10, click "Done", will create a subscription, and display the creation results:

At this point, the server's publications and subscriptions have been created successfully. Let's test that there is no data in the two database tables and two data is inserted into the T_student table in Lx_data1:

UseLx_data1GoInsertInto T_studentVALUES (1,‘Stallone‘,22, '  New York primary  " '  Washington Elementary  ",  ' 

We then looked at the results of two databases:

Use lx_data1gofrom t_studentwith lx_data2go fromt_student    

As a result we will find that the data is synchronized, such as:

Go---SQL SERVER 2008 Implementing database synchronization with publish subscriptions