?
Solved

One Publisher, one Distributor, two subscriber -- The initial snapshot for publication 'PublicationName' is not yet available

Posted on 2010-08-23
7
Medium Priority
?
663 Views
Last Modified: 2012-06-27

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!
0
Comment
Question by:Faiga Diegel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 12

Expert Comment

by:mcv22
ID: 33506567
Just start the snapshot agent again and let it generate a new snapshot.
0
 
LVL 12

Expert Comment

by:mcv22
ID: 33506573
It will only affect the new subscribing server if you generate a new snapshot as long as you don't reinitialize the older subscription
0
 
LVL 15

Author Comment

by:Faiga Diegel
ID: 33506774
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
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 12

Expert Comment

by:mcv22
ID: 33506780
0
 
LVL 15

Author Comment

by:Faiga Diegel
ID: 33513492

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.
0
 
LVL 12

Accepted Solution

by:
mcv22 earned 2000 total points
ID: 33513691
"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)?"

Yes

"If I start the Snapshot Agent, does SQL Server knows that it will only do it to the  Subscription2? And not the old Subscription1?"

Yes. As long as you don't reinitialize an active subscription, SQL server won't reapply a snapshot to an existing subscription (subscription1 in your case).

Re-doing a snapshot will export data from the source table to flat file and will need to momentarily hold a lock on the published table (to make sure it gets a consistent set of data) but it doesn't hold a lock during the entire time it does a snapshot. I would still recommend doing it off-hours to minimize the contention on the published server during snapshot creation.
0
 
LVL 15

Author Comment

by:Faiga Diegel
ID: 33513706
Thank you! Great help!
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question