We help IT Professionals succeed at work.

SQL Server Replication

377 Views
Last Modified: 2012-06-27
I have a publisher with a stable internet connection.  I have a subscriber with a very unstable internet connection.  It drops randomly and I need to have the replication work when it comes back up without reinitializing subscriptions.  

But it seems like I keep having to reinitialize all subscriptions in order for the replication to start back up.  Then it gets part of the way threw and restarts, likely due to the internet connection dropping.  It takes me several minutes to get everything back down to the subscriber and my ap is offline during this time.  And if the internet connection drops I have no way of telling my users how long it will be offline.  I'm totally at the mercy of the internet connection.  When the replication stops at least I have most of the data, but once I reinitialize all subscriptions my tables are wiped out and the ap is completely offline until it finishes.  This is because it is moving all these large tables again and not just the changing transactions.  How do I get around this?

These two lines are what I see in replication monitor when the replication appears to not be working.  ie. someone has entered something at the publisher and I don't see it at the subscriber.
**No Replicated Transactions Are available
**Initializing

This is the error that comes up part way threw the replication when the connection is dropped...
**TCP Provider: An existing connection was forcibly closed by the remote host.
Then the replication restarts again....unless the whole table got copied in that case it skips that table.

Thanks in advance for your help.
Comment
Watch Question

AnujSQL Server DBA
Top Expert 2011

Commented:
What type of replication are you using?

Author

Commented:
transactional

Author

Commented:
here is another example.  i just realized that i'm missing a table i really need sent to the subscriber.  i added it to my articles.  is there any way of just sending that table without reinitializing and sending every table all over again.

right now it says the initial snapshot for lookup_contractor is not yet available in replication monitor.  so am i able to just do a snapshot for that table??
AnujSQL Server DBA
Top Expert 2011

Commented:
First, Transactional replication is not a good option as this needs less latency between the publisher and subscriber, this means that this needs a good network connection.

You can consider Merger replication with download only, here you have the option to start the synchronization manually or schedule or run continuously.

I didn't understand why you are re-initializing your subscribers, In merge replication you don't need to re-initializing unless you have validation failure. When you add new article you should generate the snapshot only no need of re-initializing the subscribers, even though this will generate snapshot for entire articles it applies the snapshot ONLY for the NEW ARTICLE on subscribers, so other articles will be undisturbed.

Author

Commented:
Maybe that is what I'm missing.

I thought anytime I added an article I needed to right click on the publication and say reinitialize all subscriptions.

How do I go about generating a new snapshot only?  Do I just bring up the snapsnot agent and click on start?  If so, do I need to do anything with the log reader agent?  I'm confused as to what these two agents actually do and when I would need to restart one or the other.
AnujSQL Server DBA
Top Expert 2011

Commented:
Yes, bring up snapshot agent and start. Snapshot helps to apply initial copy of your schema and data to the subscribers and it is used in all kind of replication. Whereas Log reader agent only found in Tranasactional replication and copies the transactions that are marked for replication in the publisher to the distribution database, from there distribution database distributes these changes to each subscriber.

Author

Commented:
I'm still getting that the initial snapshot for the table is not yet available after I click on start.  Is there something else I need to do?

Author

Commented:
Oh ignore that last comment.  It went after a while, guess it just took a while.

Author

Commented:
another scenario i have is that i have filter rules with one of the tables and i want to change those.  can i change those without having to do the reinitialize all subscriptions?  am I able to just do the start on the snapshot again with that as well?

do you know when you would have to do the reinitialize?  i was under the presumption i had to do it anytime i needed any changes sent (ie. new tables, remove tables, changes to tables, etc)
AnujSQL Server DBA
Top Expert 2011

Commented:
Subscriber must be re-initialized if you are adding or removing any filters, this is because of simple reason that the subscriber should only have the data that is filtered.

Author

Commented:
So all tables have to be resent and not just the the table you are adding the filter to?

I cann't do merge replication because I can't take the chance of an accidental update on my subscriber going to my publisher.  From my understanding merge is two way?
SQL Server DBA
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.