Connectivity Problems

The following are connectivity problems that may occur with replication tasks.

Distribution Task Connectivity

SQL Server replication relies on integrated security connections. A distribution task will force a trusted connection using ODBC to the subscription server. Because of this design, the distribution server must use the named pipes or a multiprotocol network library as the default network library. The Client Configuration Utility is used to set the default network library.

It is also important to understand the security context of the SQL Executive service. All replication tasks connect under the security context of the SQL Executive service. Because a trusted connection is used to connect to the subscription server, the account used by SQL Executive to log on as a service must have permissions on the subscription server. A trusted connection by a distribution task, therefore, requires that the distribution and subscription servers belong to the same domain or trusted domains. For this reason, the SQL Executive service is normally configured to log on as a domain administrator.

One simple method of testing the connectivity of replication is as follows:

Log on to the distribution server using the same account SQL Executive logs on with.

Use ISQL/w with the -E option to force a trusted connection to the subscription server.

While this test uses DB-Library and the Distribution task uses ODBC, it will still provide a reliable method of testing a trusted connection to the subscription server. Setting the subscription server to integrated security and using the odbcping utility can accomplish the same test. If a Distribution task fails to connect to the subscription or distribution server, it will display a standard ODBC error for connection failed, such as:

08001 <Microsoft><ODBC SQL Server Driver> Unable to connect to DataSource

08001 <Microsoft><ODBC SQL Server Driver><DBNMPNTW> Specified SQL Server not found

LogReader and Synctask Connectivity

Both LogReader and Sync tasks connect to the publication and distribution servers using DB-Library, forcing a trusted connection. Because the default network library needs to be named pipes or multiprotocol for the subscription server, these tasks will use these network libraries to connect to their servers. Similar techniques to test Distribution task connectivity can be applied to a LogReader or Sync task. If the LogReader or Sync task fails to connect to either server, it will display the following error in the Task History of SQL Enterprise Manager:

Unable to connect to '<server>.'

If any of these tasks fail to connect to a server, they are automatically configured to retry the connection based on the configuration of the task from SQL Enterprise Manager. By default, each task is configured to retry once per minute for a maximum of 1,440 times—once each minute for 24 hours. This can be changed by clicking Options when editing the task.

Synchronized Tables

To properly understand why a table may not be automatically synchronized after subscribing, you need to understand what each type of task does for replication.

A LogReader task reads changes from the published database transaction log and inserts jobs into the distribution database. A LogReader task is created with a name that combines the publication server name and the published database name (for example, mypub_pubs).
A Distribution task reads jobs from the distribution database and executes commands from these jobs against a subscription server. A Distribution task is created with a name that combines the publisher server/published database name and subscription server/subscribed database name (for example, mypub_pubs_mysub_pubs).
A Sync task creates a data file (BCP format) and inserts "sync" jobs into the distribution database. The Sync task performs this operation for any new subscription or any scheduled table refresh publication.

The Distribution task synchronizes the table and its data to a subscription server. However, the Distribution task cannot perform this task until the Sync task creates a job in the Distribution database for the synchronization. Remember that a Sync task is created by SQL Enterprise Manager as part of creating a publication. If you build publications manually, you must create a Sync task using the sp_addtask procedure. SQL Enterprise Manager constructs a Sync task name by combining the publication server name, published database name, publication name, and a unique number. An example task name is mypub_pubs_authors_10.

By default, the Sync task is scheduled to run every five minutes. Because the subscription may have taken place immediately after the publication was created, the Sync task may not have executed yet. If you have created a publication and subscribed to it, check the status of the Sync task in SQL Enterprise Manager. If the Sync task has successfully completed and added the proper sync jobs, it will show an entry in the Task History like:

Sync event for publication: <publication name>. Created bcp file <filename.tmp> for article <article name> sync job.

If no new subscriptions for this publication were found you will see an entry in the Task History like:

No new subscriptions were found for articles in publication: <publication name>.

The Sync task runs even if no one has subscribed. For this reason, most users will probably want to change the default schedule of the Sync task to an interval longer than five minutes. This depends on how often new subscriptions occur or if you decide to create "scheduled table refresh" publications.