SQLSERVER2008R2 Publish subscription and related problem solving method

Objective:

The first two days received the leadership of the task of a system a customer data synchronization to another system B, after the customer entry entry only a system, B system does not provide entry entry, because a variety of reasons can not use the interface mode a system and B system directly interactive synchronization, only through the database of a library to synchronize to the B library. Originally want to pass through the trigger directly past, but in cross-server aspects encountered problems, stability is too poor to finally give up, choose to use the way to publish subscriptions; I did not think that the release of the subscription is not as smooth as expected, today on the issue of a subscription to talk about solutions, not all is just the problem I encountered;

Problems encountered:

1. If the server has a firewall enabled, you need to add 1433 port outbound inbound

2. After the publisher is successfully published, subscribe at the subscriber to show that the subscription is successful but cannot see the subscription information under Local subscription:

1) may be the release of the snapshot path permissions issue, the general default on the system disk, the path can be modified to the configurable permissions of the folder (see Release Deployment 2.3 below), the path under the Repldata folder permissions open (Note: Assigned to this folder settings, in the higher level settings may not be effective);

2) different service name and host name

You can do this by executing the same SQL query

1 Select @ @servername 2 Select serverproperty ('servername')　　　　

　　　If you do not do the following SQL modifications

 　 ifSERVERPROPERTY ('servername')<> @ @servername 　　　　 begin 　　　　 Declare @serversysnameSet @server = @ @servername 　　　　 execSp_dropserver@server = @server 　　　　 Set @server = cast(Serverproperty ('servername') assysname)execsp_addserver@server = @server,@local = 'LOCAL' 　　　　 End

View Code

　　　　Note that you have to restart after you have changed SQL server can only

　　　　3) host name is the same as other server host names

Modify the host name, restart the host, as a method to change the server name to the host name, restart the service;

However, this may still be problematic: After the publish subscription succeeds, but does not synchronize the data, at the publisher look at the synchronization state, view details will see that the Subscriber is unable to connect, using the SQL Client Remote Connection Subscriber discovery using IP can connect, but the host name mode cannot connect Here we're going to check the subscriber's login name, a login named after the original hostname + '/administrator ' is found

　　We will delete this login name and create a new login

　　　　

After modification, the publication is reinitialized at the publisher, and replication monitoring can be started to see that the subscription is running and the database data synchronization is successful

1 Deployment considerations

1, the Publisher and Subscribers must be in the same LAN

2. When publishing, SQL Server needs to have an actual server name to connect to the server. 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 re-login with the actual server name

2 Release Deployment 2.1 New publication

Expand the Replication node under the SQL Server 2008 server and discover the local publishing and local subscriptions two nodes, right-click the Local Publishing node, select New Publication

2.2 Publishing Wizard

1. If the server publishes the settings for the first time, the Publish Wizard dialog box pops up

2. Set "distributor" and select the first one

2.3 Snapshot Folder

Click "Next" to set up the "Snapshot folder" dialog box, we select the default

2.4 Publishing a database

Click "Next" to select the database you want to publish

2.5 Release types

Click "Next", select "Release Type", we select "Transactional publication".

2.6 Selecting a Publishing object

Click "Next", select the publication object in the database, select the desired table, view, or select all the tables

2.7 Project Issues

Click Next, and SQL Server requires that all objects referenced by the published stored procedure (for example, tables and user-defined functions) be available at the Subscriber. If the referenced object is not published as an article in this publication, you must manually create the objects at the Subscriber. Default

2.8 Filter Table Rows

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

2, if you need to filter the table row click "Add" will appear the following Filter dialog box, you can add SQL query statements in the filter statement

2.9 Snapshot Agent

Do not need to filter, that is, all data synchronization, click "Cancel", directly "next", go to the "Snapshot Agent" Settings dialog box, there are two choices is to create a snapshot immediately, one is to specify a plan (such as can specify XXX days XXX time run), we choose the first

2.10 Agent Security

1. Click "Next" to enter the "security of the agent" setting

2. Click "Security Settings", we select SQL SERVER user

2.11 Wizard Actions

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

2, click "Next", we give the publication named: Fabu_test

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

2.12 Setting folder permissions

Set C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql repldata file permissions after publishing creation is complete

　　　　

　　

3 Subscribing to Deployment 3.1 new subscription

Right click on "Local Subscriptions" node, open, "New subscription" dialog box

3.2 Subscription Wizard

1. Popup New Subscription Wizard point

2. Click Next, select Publisher, select Find SQL Server Publisher

3. Pop-up links to the server, linked to the publisher.

Note: The server alias, IP address and other name links are not supported, and the actual name of the server is required to link

4. Click on the link, select the publication for which you want to create one or more subscriptions, we select the database publishing Fabu_test in the publisher win-xxx, and then click "Next"

　　　　

3.3 Distribution Agent Location

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

3.4 Subscribers

In the Subscribers dialog box, select New database or build good one new database in advance to select this database

3.5 Distribution Agent Security

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

2. Enter the login name and password of the Subscriber SQL Server

3.6 Synchronization Schedule

Click "Next", set up the agent plan, we choose the default "continuous running"

3.7 Initializing subscriptions

Click "Next" to go to the "Initialize subscription" dialog and select "immediately" to initialize

3.8 Wizard Actions

1. Click "Next" in "Initialize Subscription": Select Create subscription

2. Click "Next": Complete the wizard

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

SQLSERVER2008R2 Publish subscription and related problem solving method