4

I'm trying to set up replication between of a database between two servers. The servers are both Server 2012 R2 running SQL SQL Server 2014 SP2 CU5 (12.0.5546.0).

I'm following the tutorial from Microsoft and am stuck on this section (Lesson 2: Creating a Subscription to the Transactional Publication)

I have completed each of the steps as described, but when I view the synchronization status, I get the error

*The process could not connect to Subscriber ''.

I have gone through and confirmed that I have created all the required local users and re-set their passwords to ensure they are correct. Both machines are on the same domain connected on the same physical LAN.

Any suggestions on how to go about diagnosing? I'm fairly certain it's security related, but just diagnosing what has me stumped.

  • Are there any further details in the SQL Server Agent log, SQL Server ERRORLOG, ...? – John aka hot2use Sep 6 '17 at 6:46
  • Could you tell us, what permissions you gave to the login connecting to the Subscriber? – S4V1N Sep 6 '17 at 6:52
  • Modified my answer as I mistook part of your question. Though I think the troubleshooting steps are a lifesaver for many folks!! So I kept that – clifton_h Sep 6 '17 at 7:09
  • @S4V1N, When configuring the subscriber, I gave the login "repl_distribution" db owner on the database i'm writing to. Is that what you were chasing (i'm pretty new to this functionality hence following the article) – Stephen Pefanis Sep 7 '17 at 3:12
  • In the end of messing around I think my issue was relating to the user account beeing used for the "Subscriber connection". Both of the servers had an SA account with different passwords. I specified this manually on the subscriber to another account and it seemed to work. – Stephen Pefanis Sep 7 '17 at 4:36
2

I recently started to document Replication issues since they are usually unique, and can be troublesome.

if the Subscriber is NULL

Likely this is a problem where the subscription was setup on one side yet not on the other or became corrupt so that an empty value returns. In these cases, recreating Subscription Agent is often the best solution to clean out the errors in the Distribution database.

  • Generate the publication scripts via SSMS for creating and dropping the subscriptions onto their own query window.

  • ONLY run the drop subscription to the problem Subscription.

  • Manually ensure this subscription no longer exists as a job and/or subscription on the Subscriber side.

  • Run the create subscription statement. Check that the @sync_type is also set properly. Sometimes this can be incorrect.

  • Generate the snapshot by either starting the Snapshot Agent for the publication or right click on the publication in SSMS and select the Reinitialize Subscription... and select generate new snapshot.

If the Subscriber is not NULL but still has problems

The SQL Agent or some AD account used during the replication process need to be checked.

  1. Check the Type of Replication and start from the side the replication Agent is executed from normally.

  2. Check if the replication is supported.

    • It is considered supported if the Distributor and the Subscriber are at least 2 Major editions from each other. For example, SQL Server 2008 R2 to 2014 is supported but not 2008 R2 to 2016 and vice versa. Deprecated Features in SQL Server Replication | Microsoft Docs

    • If unsupported...well, you can guarantee odd issues come up and this will require lots of patience.

  3. Check the Agents Involved using the following query on each Instance (Distributor and Subscriber)

    SELECT servicename, service_account FROM sys.dm_server_services

    • Ensure the Instance Has a valid Login and that the database has a valid User Login Mapped to that database as db_owner
  4. Check the Replication Agents from The source of the Subscription Agent (Pull on the subscriber and Push on the Distributor)

    • The Distribution Agent is db_owner of the database for Replication.
    • The subscription Agent is also db_owner of the replicated database and a member of the PAL or Publication Access List | Microsoft Docs.
  5. Check the AD account is valid on the server box.

    • the Agent must be able to Login into the server if using an AD account or directly to the Instance via a valid SQL Server Login. Please note that a SQL Server Login is not sent using very robust encryption methods and is considered a weak form of security. Utilize Active Directory Objects instead to hide the credentials from unwanted intruders.

    • For both servers, verify the AD account is also valid for the Snapshot Folder used by replication. If the Agent is the Distributor, it needs at least write access. If the Agent is the subscriber, it needs at least read access.

Most likely, one of these steps will reveal the mystery...though, for unsupported replications I have seen a few odd situations.

  • Modified answer to correctly answer the question about the '.' Server as well as provide useful troubleshooting steps. – clifton_h Sep 6 '17 at 7:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.