One Publisher, one Distributor, two subscriber -- The initial snapshot for publication 'PublicationName' is not yet available
Currently I have a publisher on server 1, a Distributor on server2 and a subscriber on server2.
I am using Transactional Replication. I only have few articles (more or less 20 tables) but half of the tables spans up to 10 millions, others probaly around 2-3 million.
The Publiser (Server1) to Distributor (Server2) to Subscriber (Server2) -- I believe I'm using push subscription from the Distributor -- works fine for a year now.
Now I'm adding another subscriber for my Server3, same publication. What I did was:
1) From the Publisher, right click New Subscription
2) Choose the publication from the list
3) Choose Run all agents at the Distributor
4) Add my subscriber (Server 3) with the database
5) Use "Run under SQL Server Agent service account"
6) I chose "Run continously" on the Synchronization Schedule
7) Then I chose Immediately for Initialize subscription
8) And the rest is history :) (next, next)
Looking into my Replicaiton Monitor, I can see a message "The initial snapshot for publication 'PublicationName' is not yet available". Do I just have to reinitialize my new subscription (by right clicking Reinitialize)? I read this one: http://www.sqlservercentral.com/Forums/Topic342644-7-1.aspx having issues with multiple subscriptions (though it's SQL 7, 2000). I'm afraid my current subscription would get affected when reinitializing a new subscription.
Would my publisher be affected (performance wise) when reinitializing a new subscription since I have millions of data rows on my publication articles?Isn't it should be on the distributor resource that will get clogged, not the publisher?
Any insights, suggestions, advice are welcome!
mcv22
Just start the snapshot agent again and let it generate a new snapshot.
It will only affect the new subscribing server if you generate a new snapshot as long as you don't reinitialize the older subscription
ASKER
When you say "Just start the snapshot agent again ", is it the Agent job on my distributor? Thats runs the following command:
-Subscriber [Server3] -SubscriberDB [SubscriberDB] -Publisher [Server1] -Distributor [Server2] -DistributorSecurityMode 1 -Publication [PublicationName] -PublisherDB [PublishedDB] -Continuous
-Subscriber [Server3] -SubscriberDB [SubscriberDB] -Publisher [Server1] -Distributor [Server2] -DistributorSecurityMode 1 -Publication [PublicationName] -PublisherDB [PublishedDB] -Continuous
ASKER
Got it.
When you say "as long as you don't reinitialize the older subscription", is this the right click >> Reinitialize (on the Subscription1 under my publication)?
If I start the Snapshot Agent, does SQL Server knows that it will only do it to the Subscription2? And not the old Subscription1?
Thanks for all your inputs. I can replicate this on a non production environment but performance wise, we do not have millions and millions of row in non production so I can't really be sure that my Publication DB won't get affected <<-- this is my goal.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you! Great help!