Monitoring Replication Status/Restarting

Occasionally, our replication between our offices stops, almost always due
to a general network failure/pesky WAN link.
Is there a table or sp that I can periodically query to detect if
replication is still running, or if replication has stopped and why?
I would ideally like to call this every 1, 5, 10 minutes directly from our
front-end program, so I can display an error/attention indicator if it has
stopped.
Part two, is if it has failed, and I am able to tell that it is a General
Network failure type error, can I exec something to jump start it?
Sorry if this is a FAQ. I looked around a bit but didn't find anything...
You could use tis type of code to see if the merge agent is running:
http://www.replicationanswers.com/Do...unningJobs.txt
As a simple solution to your problem, you could schedule the merge agent to
run every 1 minute rather than continuously.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Query distribution.dbo.MSdistribution_agents.
You can also use the replication alerts to notify you on failure.
You can't do much about general network failures programmatically. Generally
these are transient, when they last longer you have to run tracert to find
out exactly where the network failure is and hunt down your network admins
or call your ISP, or their ISP.
I normally take my 3rd job step and loop it around to the first job step on
failure or have the jobs restart every 5 minutes and run them continuously,.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Art Vandelay" <artvandelay92k@.hotmail.com> wrote in message
news:11ph8lnhs9dpg6a@.corp.supernews.com...
> Occasionally, our replication between our offices stops, almost always due
> to a general network failure/pesky WAN link.
> Is there a table or sp that I can periodically query to detect if
> replication is still running, or if replication has stopped and why?
> I would ideally like to call this every 1, 5, 10 minutes directly from our
> front-end program, so I can display an error/attention indicator if it has
> stopped.
> Part two, is if it has failed, and I am able to tell that it is a General
> Network failure type error, can I exec something to jump start it?
> Sorry if this is a FAQ. I looked around a bit but didn't find anything...
>
|||Thanks.
To set it up to loop back to step 1, do I just edit the Agent properties,
then edit the Job Step "On Failure Action" to Goto Step:1?
Or, just go to the Schedules tab and add another Schedule to just restart
every 5 minutes.
Is one way better than another? I kind of like the delay in there, so it
will wait a bit to restart if down, so hopefully the network has fixed
itself by the next time it retries.
Any downsides? Does it mess anything to keep restarting, say even if the
service is already up, or in the middle of a replication, to have it just
start up again? I would assume it would not, but I am just maybe being
overparaoid here...
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:unY4anM$FHA.140@.TK2MSFTNGP12.phx.gbl...
> Query distribution.dbo.MSdistribution_agents.
> You can also use the replication alerts to notify you on failure.
> You can't do much about general network failures programmatically.
> Generally these are transient, when they last longer you have to run
> tracert to find out exactly where the network failure is and hunt down
> your network admins or call your ISP, or their ISP.
> I normally take my 3rd job step and loop it around to the first job step
> on failure or have the jobs restart every 5 minutes and run them
> continuously,.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Art Vandelay" <artvandelay92k@.hotmail.com> wrote in message
> news:11ph8lnhs9dpg6a@.corp.supernews.com...
>
|||The overhead with having it loop is negligible. I prefer to schedule it as
typically I have many subscribers and I want to avoid having too many agents
running concurrently.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Art Vandelay" <artvandelay92k@.hotmail.com> wrote in message
news:11s8mvrf7e6rq82@.corp.supernews.com...
> Thanks.
> To set it up to loop back to step 1, do I just edit the Agent properties,
> then edit the Job Step "On Failure Action" to Goto Step:1?
> Or, just go to the Schedules tab and add another Schedule to just restart
> every 5 minutes.
> Is one way better than another? I kind of like the delay in there, so it
> will wait a bit to restart if down, so hopefully the network has fixed
> itself by the next time it retries.
> Any downsides? Does it mess anything to keep restarting, say even if the
> service is already up, or in the middle of a replication, to have it just
> start up again? I would assume it would not, but I am just maybe being
> overparaoid here...
> Thanks.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:unY4anM$FHA.140@.TK2MSFTNGP12.phx.gbl...
>

Monitoring replication status via SNMP

I'm trying to find an SNMP MIB that will let me monitor the replication status of a Microsoft SQL2K database. I know you can send Email alerts from MSSQL, as long as the DB is running under a domain account, but in case that is not an option. Any help w
ould be GREATLY appreciated.
You can configure alerts to execute jobs. Use these jobs to raise SNMP alerts.
AFAIK there are no replication specific MIBs but there is a SQL Server specific one.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Derek Small" wrote:

> I'm trying to find an SNMP MIB that will let me monitor the replication status of a Microsoft SQL2K database. I know you can send Email alerts from MSSQL, as long as the DB is running under a domain account, but in case that is not an option. Any help
would be GREATLY appreciated.
>

Monitoring Queue status

Hi,

I'm new to the service broker service. All I want to do is to monitor the queue status. If the queue is disabled, send me an email alert.

Can you let me know what's the best way to accomplish it?

Thanks,

Jia

if ((select is_receive_enabled from sys.service_queues where name = 'PublisherQueue ') = 0)

begin

execute msdb.dbo.sp_send_dbmail 'MyDBMail', 'me@.abc.com', @.subject = 'queue is down'

end

|||

You could use SQL Trace Events to do this for you. Here are the steps... you can lookup Books Online for exact syntax on what statements you need to use to achieve this:

1. Create a Service that will get notified whenever your queue gets disabled. Make sure that the service implements the PostEventNotificationContract (the actual name is a big URL I haven't memorized... look at sys.service_message_types).

2. Write a service program (as a stored proc) for the above service that accepts event notification messages from the service queue, parses the required information and sends e-mail via DBMail.

3. Create an event notification for QUEUE_DISABLED_EVENT for your user queue.

When your user queue gets disabled, the event service will receive an event message. Activation will launch your service program which will send you an e-mail.

|||Thanks Rushi!

Monitoring agent status

All -
I'm trying to setup a way to monitor the agent status for replication. I
tried using the built in "Alerts" and they are not working. I made sure that
they are enabled, but after enabling the Alerts, it does not show that the
event had ever happened when I check the history. I've made sure that the
alerts are enabled on the distributor and no where else.
So my next question would be is there a way programatically monitor agents?
I see the system view sys.dm_qn_subscriptions, but when I select * from it,
nothing is returned.
I am trying to monitor the failures and retries.
"Paul Ibison" wrote:

> Which event re you monitoring. I recall from another poster that we couldn't
> get the on success alert running as the event couldn't get raised to the
> windows log (couldn't change the message attributes, unlike in SQL 2000) ,
> but I am not aware of any others.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||This should be ok. Are you using SQL 2000 or 2005? I'd l'll take a look later
on, but for now as a stop-gap you could use a notification on the job itself.
Cheers,
Paul
|||We are using 2005. I'll try and give the notification on the specific job a
try and see if that works.
"Paul Ibison" wrote:

> This should be ok. Are you using SQL 2000 or 2005? I'd l'll take a look later
> on, but for now as a stop-gap you could use a notification on the job itself.
> Cheers,
> Paul
>

Monitor transactional replication.

Hi,
I want via email to monitor transactional replication status such as still
running, have some problem during retry, or re-initialize, etc. instead of
login to Microsoft SQL Server Management Studio in SQL Server 2005 (SP1). I
think those information should get from msdb, does anyone done that before?
Regards,
Chen
Hi Paul,
Thanks a lot for reply.
Can you take about details how to do those alerts?
Regards,
Chen
"Paul Ibison" wrote:

> Shouldn't have to do anything unusual here - you can use the replication
> alerts for this:
> a.. Replication: agent success
> a.. Replication: agent failure
> a.. Replication: agent retry
> and have them send emails.
> HTH,
> Paul Ibison
>
>

Monitor SQL Server status through Notification Services

Hello,

I am new to Notification Services. My question is can notification services be used to monitor the status of sql server 2005 (stopped/started) and databases (online/offline).

Thanks

I probably wouldn't use NS for this. Use the Microsoft Operations Manager with the SQL Server MOM pack. The MOM pack is available at http://www.microsoft.com/downloads/details.aspx?familyid=79F151C7-4D98-4C2B-BF72-EC2B4AE69191&displaylang=en.

Or, you can use SQL Trace and event notifications to receive notifications of SQL Server events. See the Montioring Events section of SQL Server Books Online for more information. See http://msdn2.microsoft.com/en-us/library/ms190378.aspx.

-Diane Larsen
SQL Server User Education

Monitor Database Mirroring

Does database mirroring have any monitoring capabilities like Log shipping
to let the DBAs know the current status of the mirrored server i.e. how far
behind the principal it is,etc. ?
See the DMV dm_db_mirroring_connections. See details from below URL:-
http://msdn2.microsoft.com/en-us/library/ms189796.aspx
Also see:-
http://msdn2.microsoft.com/en-us/library/ms365781.aspx
Thanks
Hari
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23IQSavNeHHA.2332@.TK2MSFTNGP04.phx.gbl...
> Does database mirroring have any monitoring capabilities like Log shipping
> to let the DBAs know the current status of the mirrored server i.e. how
> far behind the principal it is,etc. ?
>

Monitor Database Mirroring

Does database mirroring have any monitoring capabilities like Log shipping
to let the DBAs know the current status of the mirrored server i.e. how far
behind the principal it is,etc. ?See the DMV dm_db_mirroring_connections. See details from below URL:-
http://msdn2.microsoft.com/en-us/library/ms189796.aspx
Also see:-
http://msdn2.microsoft.com/en-us/library/ms365781.aspx
Thanks
Hari
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23IQSavNeHHA.2332@.TK2MSFTNGP04.phx.gbl...
> Does database mirroring have any monitoring capabilities like Log shipping
> to let the DBAs know the current status of the mirrored server i.e. how
> far behind the principal it is,etc. ?
>

Monitor Database Mirroring

Does database mirroring have any monitoring capabilities like Log shipping
to let the DBAs know the current status of the mirrored server i.e. how far
behind the principal it is,etc. ?See the DMV dm_db_mirroring_connections. See details from below URL:-
http://msdn2.microsoft.com/en-us/library/ms189796.aspx
Also see:-
http://msdn2.microsoft.com/en-us/library/ms365781.aspx
Thanks
Hari
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23IQSavNeHHA.2332@.TK2MSFTNGP04.phx.gbl...
> Does database mirroring have any monitoring capabilities like Log shipping
> to let the DBAs know the current status of the mirrored server i.e. how
> far behind the principal it is,etc. ?
>