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.
Check the Type of Replication and start from the side the replication Agent is executed from normally.
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.
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
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.
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.
