Generating Replication alerts using SMTP.

I cannot use SQL Mail due to the requirement of outlook client installation
on the server (that I cannot do). The other option is to use SMTP.
I have tried to use xp_smtp_sendmail by adding tasks in the system generated
jobs but it doesn't seem to work.
Anyone any ideas?
Regards,
MZeeshan
Ive used it just fine. I made these procs because I needed to add a job step
named "Alert" to every job on every server that I have. Keep in mind this
will change the way your job steps are run, and may not be applicable to all
jobs. (Say if they have 5 steps, and steps 1 - 4 are Quit with Failure when
the step fails. )
Use msdb
go
ALTER procedure admin_AddAlertJobStep
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.command varchar(256)
set @.command = 'exec admin_AlertFailedJob '''+ @.JobName
+''','''+@.Recipient+''''
exec sp_add_jobstep @.job_name = @.JobName
, @.step_name = 'Alert'
, @.command = @.Command
, @.on_success_action = 2
, @.on_fail_action = 2
declare @.StepId int
set @.StepId = (select max(step_id) - 1 from SysJobSteps sjs
inner join SysJobs sj on sjs.job_id = sj.job_id
where sj.Name = @.JobName)
exec sp_update_jobstep
@.job_name = @.JobName
, @.step_id = @.StepId
, @.on_fail_action = 3
use master
go
ALTER procedure admin_AlertFailedJob
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.rc int
declare @.ServerName varchar(256)
set @.ServerName = (select @.@.ServerName)
set @.JobName = @.JobName + ' failed!'
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.ServerName
,@.TO = @.Recipient
,@.Subject = @.JobName
,@.server = N'address'
,@.Port = 25
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:50D1D59D-BA02-46A3-96FE-1770B00E15D8@.microsoft.com...
>I cannot use SQL Mail due to the requirement of outlook client installation
> on the server (that I cannot do). The other option is to use SMTP.
> I have tried to use xp_smtp_sendmail by adding tasks in the system
> generated
> jobs but it doesn't seem to work.
> Anyone any ideas?
> --
> Regards,
> MZeeshan

Generating Replication alerts using SMTP.

I cannot use SQL Mail due to the requirement of outlook client installation
on the server (that I cannot do). The other option is to use SMTP.
I have tried to use xp_smtp_sendmail by adding tasks in the system generated
jobs but it doesn't seem to work.
Anyone any ideas?
--
Regards,
MZeeshanIve used it just fine. I made these procs because I needed to add a job step
named "Alert" to every job on every server that I have. Keep in mind this
will change the way your job steps are run, and may not be applicable to all
jobs. (Say if they have 5 steps, and steps 1 - 4 are Quit with Failure when
the step fails. )
Use msdb
go
ALTER procedure admin_AddAlertJobStep
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.command varchar(256)
set @.command = 'exec admin_AlertFailedJob '''+ @.JobName
+''','''+@.Recipient+''''
exec sp_add_jobstep @.job_name = @.JobName
, @.step_name = 'Alert'
, @.command = @.Command
, @.on_success_action = 2
, @.on_fail_action = 2
declare @.StepId int
set @.StepId = (select max(step_id) - 1 from SysJobSteps sjs
inner join SysJobs sj on sjs.job_id = sj.job_id
where sj.Name = @.JobName)
exec sp_update_jobstep
@.job_name = @.JobName
, @.step_id = @.StepId
, @.on_fail_action = 3
use master
go
ALTER procedure admin_AlertFailedJob
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.rc int
declare @.ServerName varchar(256)
set @.ServerName = (select @.@.ServerName)
set @.JobName = @.JobName + ' failed!'
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.ServerName
,@.TO = @.Recipient
,@.Subject = @.JobName
,@.server = N'address'
,@.Port = 25
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:50D1D59D-BA02-46A3-96FE-1770B00E15D8@.microsoft.com...
>I cannot use SQL Mail due to the requirement of outlook client installation
> on the server (that I cannot do). The other option is to use SMTP.
> I have tried to use xp_smtp_sendmail by adding tasks in the system
> generated
> jobs but it doesn't seem to work.
> Anyone any ideas?
> --
> Regards,
> MZeeshan

Generating Replication alerts using SMTP.

I cannot use SQL Mail due to the requirement of outlook client installation
on the server (that I cannot do). The other option is to use SMTP.
I have tried to use xp_smtp_sendmail by adding tasks in the system generated
jobs but it doesn't seem to work.
Anyone any ideas?
Regards,
MZeeshanIve used it just fine. I made these procs because I needed to add a job step
named "Alert" to every job on every server that I have. Keep in mind this
will change the way your job steps are run, and may not be applicable to all
jobs. (Say if they have 5 steps, and steps 1 - 4 are Quit with Failure when
the step fails. )
Use msdb
go
ALTER procedure admin_AddAlertJobStep
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.command varchar(256)
set @.command = 'exec admin_AlertFailedJob '''+ @.JobName
+''','''+@.Recipient+''''
exec sp_add_jobstep @.job_name = @.JobName
, @.step_name = 'Alert'
, @.command = @.Command
, @.on_success_action = 2
, @.on_fail_action = 2
declare @.StepId int
set @.StepId = (select max(step_id) - 1 from SysJobSteps sjs
inner join SysJobs sj on sjs.job_id = sj.job_id
where sj.Name = @.JobName)
exec sp_update_jobstep
@.job_name = @.JobName
, @.step_id = @.StepId
, @.on_fail_action = 3
use master
go
ALTER procedure admin_AlertFailedJob
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.rc int
declare @.ServerName varchar(256)
set @.ServerName = (select @.@.ServerName)
set @.JobName = @.JobName + ' failed!'
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.ServerName
,@.TO = @.Recipient
,@.Subject = @.JobName
,@.server = N'address'
,@.Port = 25
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:50D1D59D-BA02-46A3-96FE-1770B00E15D8@.microsoft.com...
>I cannot use SQL Mail due to the requirement of outlook client installation
> on the server (that I cannot do). The other option is to use SMTP.
> I have tried to use xp_smtp_sendmail by adding tasks in the system
> generated
> jobs but it doesn't seem to work.
> Anyone any ideas?
> --
> Regards,
> MZeeshan

Generating Replication Alerts through Perfmon monitoring.

We use a third party monitoring system that polls perfmon statistics on
different servers and generates alerts if a threshold is reached. For general
SQL tasks like backup, I programmatically set/reset User Settable counters
(10 in all) through system stored procedures sp_user_counter[1...10] in the
Scheduled task command.
But, this is not working in the case of replication. However, there are
other perfmon monitors that are specifically related to replication.
Would someone be able to identify what is the best parameter to monitor?
Thanks!!!
MZeeshan
Hi MZeeshan,
From your descriptions, I understand you would like to select a better
performance counter for your monitoring. However, I am not sure what you
are going to monitor and improve. Would you please show me more detailed
information, which I believe will make us closer to the resolution
You could find detailed introduction of all replication performance
counters in BOL topic "Using SQL Server Objects"
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||use replication alerts for this. Connect to your sql server using EM, expand
your server, expand the replication monitor node, and then expand the
replication alerts section.
You could monitor conflicts in SQLServer:Replication Merge, monitor the
latency of SQLServer:Replication LogReader and SQLServer:Replication Dist.
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
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:F2BF344D-36CE-47D8-8D76-DDD8A0150B71@.microsoft.com...
> We use a third party monitoring system that polls perfmon statistics on
> different servers and generates alerts if a threshold is reached. For
general
> SQL tasks like backup, I programmatically set/reset User Settable counters
> (10 in all) through system stored procedures sp_user_counter[1...10] in
the
> Scheduled task command.
> But, this is not working in the case of replication. However, there are
> other perfmon monitors that are specifically related to replication.
> Would someone be able to identify what is the best parameter to monitor?
> Thanks!!!
> MZeeshan
|||Let me give you an example of how I am monitoring other tasks now. For
instance, I put the T-SQL below as a Schedule task:
BACKUP DATABASE AMA TO DISK='D:\DailyBackups\Full\AMA.bak' WITH INIT
IF (@.@.error<>0)
BEGIN
EXEC sp_user_counter1 900150
WAITFOR DELAY '00:01:10'
EXEC sp_user_counter1 0
END
Now, this keeps the counter1 with given value for 70 seconds. This is enough
time for the manager program to poll and pick up the value. This is then send
as a page or e-mail.
I tried to implement similar approach on system generated jobs (in SQL
Agent|Scheduler) once transactional replication was created. However, it
didn't work out.
What I basically need is to get information on some perfmon counter(s) that
shows "a change in state" if there is an error in any replication process
(logreader, distributer). The third party vendor we are using has ability to
monitor perfmon counters and provide information as alerts or graphs. FYI...
our internal standards do not allow us to use any of the SQL Server provided
alerting mechanisms (like SQL mail or smtp mail).
Is this clarification enough for you to understand what I am trying to get?
"Michael Cheng [MSFT]" wrote:

> Hi MZeeshan,
> From your descriptions, I understand you would like to select a better
> performance counter for your monitoring. However, I am not sure what you
> are going to monitor and improve. Would you please show me more detailed
> information, which I believe will make us closer to the resolution
> You could find detailed introduction of all replication performance
> counters in BOL topic "Using SQL Server Objects"
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Thanks for the information. I'll definitely check the counter information.
"Hilary Cotter" wrote:

> use replication alerts for this. Connect to your sql server using EM, expand
> your server, expand the replication monitor node, and then expand the
> replication alerts section.
> You could monitor conflicts in SQLServer:Replication Merge, monitor the
> latency of SQLServer:Replication LogReader and SQLServer:Replication Dist.
> --
> 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
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:F2BF344D-36CE-47D8-8D76-DDD8A0150B71@.microsoft.com...
> general
> the
>
>
|||Hi MZeeshan,

>What I basically need is to get information on some perfmon counter(s)
that
>shows "a change in state" if there is an error in any replication process
>(logreader, distributer).
Based on my experience, you may perform:
1. SQL Server: Replication Agents Object. It represents the number of
instances of a given replication agent running currently.
2. Since Replication agents (logreader, distributer) correlate to some SQL
Agent jobs, an agent's failing is equivalent to a job's failing. They
should be able to add a step and run something like:
EXEC sp_user_counter2 nnnnnn
WAITFOR DELAY '00:01:10'
EXEC sp_user_counter2 0
Once the step in the replication agent job fails, the custom step can be
run.
However, please note that you will have to test it in your development
scenario before deploy it to the production server.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||I'll definitely check #1. However, with #2, I have already tested that and it
doesn't work. Every time there is a failure, the task will fail and a system
error is generated resulting in non-execution of the remaining portion of the
task.
I had similar problem with DBCC monitoring but I end up creating a DTS
package utilizing ON FAILURE and ON SUCCESS workflow options to identify next
command execution (in other words... generation of alerts through them).
However, I don't have that luxury of making such changes in system setup
replication tasks.
"Michael Cheng [MSFT]" wrote:

> Hi MZeeshan,
> that
> Based on my experience, you may perform:
> 1. SQL Server: Replication Agents Object. It represents the number of
> instances of a given replication agent running currently.
> 2. Since Replication agents (logreader, distributer) correlate to some SQL
> Agent jobs, an agent's failing is equivalent to a job's failing. They
> should be able to add a step and run something like:
> EXEC sp_user_counter2 nnnnnn
> WAITFOR DELAY '00:01:10'
> EXEC sp_user_counter2 0
> Once the step in the replication agent job fails, the custom step can be
> run.
> However, please note that you will have to test it in your development
> scenario before deploy it to the production server.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||I checked different Replication counters and the two below as
SQL Server: Replication Logreader: Delivery Latency
Normal Working Range: 2-7 seconds
Error Threshold: 60 sec
Monitoring Duration: 2 minutus
SQL Server: Replication Distribution: Delivery Latency
Normal Working Range: 8-9 seconds
Error Threshold: 90 sec
Monitoring Duration: 2 minutus
In words, if the vendor Manager application finds out that the counters have
value equal to or higher than given in Error Threshold and finds this for two
cycles (2 minutes), then it will generate an error alert.
How that sounds?
"MZeeshan" wrote:
[vbcol=seagreen]
> I'll definitely check #1. However, with #2, I have already tested that and it
> doesn't work. Every time there is a failure, the task will fail and a system
> error is generated resulting in non-execution of the remaining portion of the
> task.
> I had similar problem with DBCC monitoring but I end up creating a DTS
> package utilizing ON FAILURE and ON SUCCESS workflow options to identify next
> command execution (in other words... generation of alerts through them).
> However, I don't have that luxury of making such changes in system setup
> replication tasks.
> "Michael Cheng [MSFT]" wrote:
|||Hi MZeeshan,
It's OK and if you have any questions or experience during your monitoring,
don't hesitate to let us know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.