April 10, 2002 at 7:00 am
Does anyone have any experience of enabling the two built-in Replication Alerts 'Replication: Subscriber has failed data validation' and 'Replication: Subscriber has passed data validation'?
I wish to raise an alert if the data in the subscriber is any way out of sync with the publisher (allowing for latency between the two). I have enabled these two alerts, but doesn't appear to raise any errors. Do I have to schedule a job to execute sp_publication_vlaidation as well? Or have I missed out a step?
Thanks in advance
April 10, 2002 at 7:11 am
Yes, you need to run validation. It offers several options to set the depth of the validity check. I run one weekly but so far have never had an issue.
Andy
April 10, 2002 at 8:11 am
Thanks for that - I have another question you may be able to help me with.
I wish to re-synchronise an out-of-sync pull subscription from a database that is still being hit with transactions while I'm doing it. what is the correct process, bearing in mind that our network security will not permit the snapshot agent to see the snapshot folder?
Do I create a snapshot, then ftp it, bcp it in, then start up transactional replication?
What are the processes for doing this in terms of the order of creating the snapshot job, then enabling the pull subscription without it expecting a snaphot initialisation?
Thanks a lot
April 10, 2002 at 10:25 am
Once you know a pub is out of sync, you just right click it and select reinitialize. Then you can generate a new snapshot. A snapshot consists of a bcp file per table plus sql scripts to build the tables on the subscriber. Been quite a while since I used pull, would have to look to see what you need, but I believe as long as the agent on the subscriber has access to the data it does the rest.
Andy
April 12, 2002 at 2:30 am
Trouble is, in practice I can't get it to work like that.
Here's what I do and why it isn't working:
1) create publication.
2) I check the distribution database and msrepl_Transactions table and it is storing the 'pending' transactions.
3) Create a pull subscription that generates a snapshot. The distribution agent fails as it cannot load the snapshot files.
4) Pull snapshot over manually and bcp it in.
5) Cannot do anything with the pull subscription I have already created - it still says it needs a snapshot applied first.
6) Delete existing subscription and create new one (you can't have more then one subscriber per DB). Only this time say the schema's are sync'd and does not require a snapshot.
7) Start new pull subscription and it sits there saying 'No replicated Transactions'.
When I look at MSRepl_Transactions, the previously queued records have been deleted. Then I'm back to square one, with out-of sync data.
I think there's something basic that I've missed out at the point I wish to pull over the queued t/x - but can't think what it is!
Thanks in advance
April 12, 2002 at 5:03 am
Before we venture too far, why is it you cant get a folder with the needed permissions? Seems like that is the easiest way by far to resolve the issue.
Andy
April 12, 2002 at 5:20 am
Well, might be too long a story to go into, but the source DB is behind a 3rd party firewall, the destination DB is also behind a corporate firewall... Netbios may or may not be enabled on source DB for file share... no one seems to know. The 3rd party don't seem to know their arse from their elbow, don't return calls/emails, severely restrict access to the servers etc.. etc..
Anyway, to cut a long story short, I think I sussed it out.
I've created another publication that is solely a snapshot. While this is being loaded I have stopped the transactional replication subscription. Once it is in, I will restart it, it should catch up, then the snapshot pub shouldn't be required anymore.
May have been a case of trying of staring at the problem so intently that the obvious solution didn't leap out until I walked away for a while.
Thanks for assistance anyway.
April 12, 2002 at 5:52 am
Not sure that is going to give you exactly what you need, even though you have the transactional pub stopped how will you know exactly which transactions have been applied and which have not?
Your situation does make sense, nothing gets done the easy way any more!
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply