Sql Server replication: Initial snapshot for publication is not yet available?

Question

I had setup transactional replication and my distributor, publisher are on same windows server and 2 subscriber one on same server and another on other windows server both using push snapshot.

I am getting the message in the synchronization status, that snapshot is not yet available, just a few hours back it was working fine every transaction was synchronized. The only thing i did in between was, added more articles(stored procedures, etc.) to the publication using its properties.

Is it causing the issue here, there is no error message displayed. Just that I am not able to get the data replicated

Answer 1

Once you add articles to a transaction replication that is synced from snapshot(@sync_type = 'automatic'), you need to run the snapshot agent to generate the snapshot for the added article. Note, depending on the immediate_sync parameter, it will create snapshot of either the entire publication(1) or just the article that you have added(0).

Answer 2

I think after adding the stored procedures, there might be a problem with them. Maybe they are corrupt; in my case they were corrupt as they referred to an article, that was not in the replicated database. So, my advice it check the stored procedures or add them one by one (though not the best approach). Whenever I have dealt a situation like yours, I have always assumed, there is something wrong with the published database and all of the time it was. Resolve the issue and reinitialize and it will do the job, and your regular process will start working.

Answer 3

when you add new articles to a transactional replication publication you will need to run another snapshot, to add those new articles to all of the current subscribers.

this is an example from a similar question, where how to add another articles to a current active publication is shown.

---------------------------------------------------------------------------------------------------
-- add the table back to the replication
---------------------------------------------------------------------------------------------------

SET TRANSACTION ISOLATION LEVEL read committed;

EXEC sp_addarticle @publication = N'Product_Staging', 
                   @article = N'CategoryImportMap',
                   @source_object=N'CategoryImportMap',
                   @destination_table =N'CategoryImportMap'
GO

--========================================================================
-- REFRESH THE SUBSCRIPTIONS
--========================================================================
EXEC sp_refreshsubscriptions @publication = N'Product_Staging'
GO

--========================================================================
-- Start the Snapshot Agent job.
--========================================================================
EXEC sp_startpublication_snapshot @publication = N'Product_Staging'
go


-- check the number of rows (source and destination) for the new articles

Now another thing worth adding is that depending on the settings of the publication you will need to run a snapshot for all the articles (which can be very resource intensive) or a snapshot only to add the new article(s) which is generally what we want.

this is very well explained in this answer by Cozzaro Nero

Make sure that immediate_sync and allow_anonymous are set to FALSE (0) to avoid the creation of the full snapshot and not just the snapshot of the article/s changed.

SELECT immediate_sync, allow_anonymous 
FROM Your_Published_DBName.dbo.syspublications

If those values are not set to FALSE you can change them with the following:

EXEC sp_changepublication
@publication = 'Product_Staging',
@property = N'allow_anonymous',
@value = 'false'

When immediate_sync is disabled the transactions are deleted as soon as they are replicated to the subscriber. It is generally safe to disable unless there is a specific business need to add new subscribers or reinitialise existing.

EXEC sp_changepublication
@publication = 'Product_Staging',
@property = N'immediate_sync',
@value = 'false'

I also use these scripts to check and monitor some of my transactional replication settings.

when I get this type of message:

The initial snapshot for publication 'FMS_PUB' is not yet available.

I do the following:

1. I script my subscriber - the drop and the create (because you're gonna need them later on)
2. I drop the subscriber in question
3. I re-create the subscriber from the script above
4. Open the replication monitor
5. mark the subscription for reinitialization using a new snapshot
6. run the snapshot agent for this publication
7. restart the log reader for the subscription (maybe this is just a bad habit)

That generally solves this problem.

This can all be done by script too instead of the replication monitor.