April 10, 2008 at 6:54 am
hi guys i am having some problems with my snapshot replication. It seems to be a network error(i check the sql agent error and indeed it was a network error) I apparently fix that part but just to make sure I would like to create a script to monitor the replication on my servers. I need to check a random record in my table tblDistributors in my serverA that was created 5 minutes ago(InsertDate) and check if it was replicated (exists in my tblDistributors) in my serverB. My table name is tblDistributors. If it does'nt exist in my serverB then send me an email. Is there a simple way of doing this? :ermm:
April 10, 2008 at 8:03 am
You should take some time to review and understand the replication monitoring tools you get with SQL Server. You can set latency and failure notifications with the built-in tools.
April 10, 2008 at 8:41 am
thank you!, i read there is a replication monitor i was not using. there is a job called reinitialize subscription havind data validation errors failures, this sounds like it is exactly what i need.
If i have transactional replication every 10 minutes, should i set this job to run every 10 minutes as well?
April 14, 2008 at 9:21 am
replication works great apart from user fiddling !
left alone, the replication Distribution Agent (DA) plods along happily and replays I/U/D statements [via synthetic sprocs] and all is wonderful.
But then a snake enters the garden and persuades Eve/Adam to do a local I/U/D at the repl sub [just because they can, and "because the DA will take too long so do it yourself now"]. Sometime later when the DA needs to do an I/U/D the row is not present and hell breaks loose.
there are 2 morals here
1. tie down permissions so that no renegate developer/user can do any local I/U/D at sub
2. periodically use the Replication Manager to validate the subs [to highlight any divergence]
you can elect to do just rowcounts but normally you want to do rowcount+checksum to be sure
- and if you want to delve into the engine room, the SMO (aka RMO) programming is awaiting your command
HTH
Dick
April 13, 2009 at 2:03 pm
hi, just a quick question with this replication jobs from replication monitor you can schedule to run anytime you want just as any other job?
April 14, 2009 at 11:43 am
DBA (4/13/2009)
hi, just a quick question with this replication jobs from replication monitor you can schedule to run anytime you want just as any other job?
Yes. But be careful not to set them too infrequent because the subscription(S) could get deactivated and you will have to re-snapshot it(them)
* Noel
April 14, 2009 at 1:37 pm
thanks, basically i wanted to do one as a checkup of only the rowcounts to send an alert, so i just wanted it to run like three times a day.
April 14, 2009 at 1:41 pm
DBA (4/14/2009)
thanks, basically i wanted to do one as a checkup of only the rowcounts to send an alert, so i just wanted it to run like three times a day.
That should be fine.
* Noel
April 14, 2009 at 1:49 pm
thank you!!
April 17, 2009 at 1:45 pm
hi again, just to confirm, i can schedule the predefined data validation for rowcounts only three times a day eventhough the transactional replication runs every 2 minutes correct? they are separate jobs in sql agent right that could be scheduled at different times?
April 17, 2009 at 2:37 pm
DBA (4/17/2009)
hi again, just to confirm, i can schedule the predefined data validation for rowcounts only three times a day eventhough the transactional replication runs every 2 minutes correct? they are separate jobs in sql agent right that could be scheduled at different times?
You can run rowcount validation as much as you want BUT keep in mind that if there are transactions pending you will get errors.
We for example do it on weekends at times where we don't expect data changes at the primary.
What we actually use is sp_publication_validation
* Noel
April 17, 2009 at 2:43 pm
thank you, can i use this instructions to set it up and schedule it only to run on weekends after the alert is made?
http://technet.microsoft.com/en-us/library/ms151251.aspx
or do i have to set up by creating a new job in sql agent and specify to exec sp_publication_validation (Transact-SQL). ?
April 17, 2009 at 3:46 pm
The "normal" way is that you call it on a created job in the primary and create an alert on the subscriber.
On the alert you specify your "notification" and you have a fully automated solution.
For how you set up the alerts: Take a look at "Replication: Subscriber has failed data validation"
* Noel
April 17, 2009 at 4:15 pm
i have created the alert in the subscriber , can you tell me how to call it on a created job in the primary? thanks for all your help!
April 20, 2009 at 9:35 am
Sorry for the delay.
just create a normal job with TSQL task and EXECUTE sp_.... in it.
If there is a failure on the subscriber just make sure that the alert sends an email to you.
* Noel
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply