While setting up SQL Server Replication (Transactional Replication to be specific), I had successfully created the Publication and the Subscriptions, but nothing was being replicated between the publisher SQL Server instance and the subscriber SQL Server instance.
When viewing the subscription details through the Replication Monitor tool, the subscription was not throwing an error, and hanging with the following message:
The initial snapshot for publication is not yet available.
In the post I will explain why this is appearing, and how to resolve the issue and generate the required Replication Snapshot.
A Snapshot is generated by the Snapshot Agent, and used by Transactional Replication (as well as other types of replication) to initialize an object that is being replicated with the full current data-set, allowing that object to start receiving any new transactions issued after the snapshot has been taken.
There are other ways you could initialize an object in a subscription, including:
- Initialize Subscription from Backup.
- Initialize Subscriptions Manually.
- Generate a Snapshot through the Snapshot Agent.
There are multiple reasons why an initial snapshot would not be available for a subscription to use, some of these are:
- The Snapshot Agent did not run after a new subscription has been created: Basically if a new subscription is created, the Snapshot Agent needs to run once to generate a recent snapshot, in order to initialize a subscription.
- The Snapshot Agent did not run after a subscription has been initialized: Similar scenario to the above, if a subscription has been initialized you need to run the Snapshot Agent and generate a recent snapshot.
- A database that contains a subscription has been restored: If a database has been restored, you will need to initialize the subscription by generating a new Snapshot.
Its important to note that although your Snapshot Agent might show that you have a Snapshot ready, it might not be a recent snapshot, and you might require to update this snapshot in order for replication to resume.
How to solve the “Initial Snapshot for Publication is not yet Available Error
Well, as the above suggests, you need to run the Snapshot Agent and generate a recent snapshot, this can be done through the Replication Monitor, or by running the Snapshot Agent job in SQL Server Job Agent.
If you have regular new subscriptions being added/initialized in a Transactional Replication topology, and depending on your data size, it might be recommended to regularly generate a new snapshot through scheduling the Snapshot Agent job in Job Agent.
Anyway hope this helps!