Transactional Replication – A Much Better Approach For Alert Messages


As  DBAs, we are often required to set up alert notification emails for various tasks. For example, there might be a requirement to set up notification alerts if a particular backup job fails. A while back, I  came across the situation where I was asked to write a script which sent an email alert to a particular group of users if there was an issue with the Transactional Replication. The users were not happy with the alert message generated by the pre-defined alerts introduced by Microsoft in SQL Server Management Studio since they were not able to understand the description of the message – since the body as well as the heading of the email were not easily interpreted. After gathering the requirements, I decided to implement a more functional email alerts system.

Prerequisites:

  • SQL Server 2005(Except Express Edition) or Above
  • A valid database mail profile.

Approach:

I identified that  a table named sysreplicationalerts which is present in the msdb database and  contains the entire replication failure alert message. There are around 14 types of alert messages generated for Replication:

  • Replication Warning: long merge over dial up connection
  • Replication Warning: long merge over LAN connection
  • Replication Warning: Slow merge over dial up connection
  • Replication Warning: Slow merge over LAN connection
  • Replication Warning: Subscription expiration
  • Replication Warning: Transactional Replication Latency
  • Replication: agent custom shutdown
  • Replication: agent failure
  • Replication: agent retried
  • Replication: agent success
  • Replication: expired subscription dropped
  • Replication: Subscriber has failed data validation
  • Replication: Subscriber has passed data validation
  • Replication: Subscription reinitialized after validation failure

STEP 1

Create a table named notification_subscribers which contains all the subscriber information i.e subscriber ip address, server name, and general name if there is any for that server.

Script for the same is as shown below:

create table notification_plants
(
row_id int,
plant_server_ip varchar(100),
plant_server_name varchar(100),
general_name varchar(1000)
)

Then populate the table with the respective server ip, server name of the respective subscribers as well as if there is any general name. By general name,  consider for example if a subscriber has been created for Newyork location, then in the general name we shall include Newyork.

STEP 2

This step involves creation of a stored procedure named usp_send_replication_alerts which contains the required logic.

First, I created a table named replicationalerts and subscriber_alert_message. I then populated the temporary table named replication_alerts with the data from sysreplication table. This table is present in the msdb database of the SQL Server.

create table #replicationalerts

(
replication_alert_id int,
[time] datetime,
subscriber varchar(100),
alert_error_message varchar(1000)
)

Insert #replicationalerts
select row_number() over(order by alert_id),[time],subscriber,alert_error_text

from sysreplicationalerts

where datepart(yyyy,[time]) = datepart(yyyy,getdate())
and datepart(mm,[time]) = datepart(mm,getdate())
and datepart(dd,[time]) = datepart(dd,getdate())

When populating the data, bear in mind that only the current date’s data should be populated. i.e If the script is executed on 23 September 2010 then only those alerts encountered on 23 September 2010 should be inserted into the temporary table.

set @i = 1
select @max_count = max(replication_alert_id) from #replicationalerts

while(@i<=@max_count)
begin

select
@subscribername=subscriber from #replicationalerts where replication_alert_id = @i

select
@patindexname=patindex(‘%%’,@subscribername)

select
@count = @patindexname-1

select
@left_subscriber_name = left(@subscribername,@count) from #replicationalerts

select
@alerterrormessage = alert_error_message from #replicationalerts where replication_alert_id = @i

select
@alert_message_patindexname = patindex(‘%.%’,@alerterrormessage)

select
@count_alert_message = @alert_message_patindexname -1

select
@length_alert_message = len(alert_error_message) from #replicationalerts where replication_alert_id = @i

select
@final_count = @length_alert_message-@count_alert_message

select
@left_alert_error_message = right(@alerterrormessage,@final_count-1) from #replicationalerts

select
@time = [time] from #replicationalerts where replication_alert_id = @i

insert #subscriber_alert_message

select @left_subscriber_name,NULL,@time,@left_alert_error_message

update #subscriber_alert_message

set general_name = a.general_name from notification_plants a inner join #subscriber_alert_message b

on a.plant_server_name = b.subscriber_name
set @i = @i+1

end

First the value of the variable @i is initialized as 1. Then we determined the max value of the replication_alert_id column present in the temporary table named replicationalerts. While the value of @i is less than or equal to @max_count  the WHILE loop continues and fetches the required information as shown above.

Then a temporary table named #alert_message is created which fetches the data from the temporary table named subscriber_alert_message. It fetches only those data which is between 1 hour.

create table #alert_message

(
row_id int,
subscriber_name varchar(1000),
general_name varchar(1000),
alert_message varchar(1000)
)

insert #alert_message

select
rank() over(order by subscriber_name,alert_message),
subscriber_name,
general_name,
alert_message

from
#subscriber_alert_message

where
datediff(mi,[time],getdate())<=60

The initialized the value of the variable @j as 1 and the WHILE loop continues till the value of the variable @j is less than or equal to the value of @max_count.

set @j =1
select @max_count = max(row_id) from #alert_message
while(@j<=@max_count)

begin
select @mail_subject = general_name+space(1)+’Replication Has Failed’ from #alert_message where row_id = @j
select @message = alert_message from #alert_message where row_id = @j

select @mail_body =

Hi Team,<br><br>
The reason for failure is:<br><br>
‘+convert(varchar(100),@message)+’<br><br>
Regards,<br>

Administrator

Then send the email to the desired recipients. In order to do this  we need to create a Database Mail Profile. More details on How to create a Database Mail Profile can be found in the Books Online.

exec msdb..sp_send_dbmail @profile_name = ‘Profile Name’
,@recipients =’Email Address’
,@subject = @mail_subject
,@body = @mail_body
,@body_format = ‘HTML’
,@importance = ‘High’
set @j = @j+1
end

drop table #alert_message
drop table #replicationalerts
drop table #subscriber_alert_message

That’s it, now you will have a much more better email alerts notification system.

The entire code is as below:

create procedure usp_send_replication_alerts
as
begin
set nocount on
declare @subscriber_ip_address varchar(1000)
declare @subscriber_ip_name varchar(1000)
declare @alerterrormessage varchar(1000)
declare @alerterrormessagecount int
declare @general_name varchar(100)
declare @i int
declare @max_count int
declare @subscribername varchar(1000)
declare @patindexname int
declare @count int
declare @final_subscriber_name varchar(1000)
declare @left_subscriber_name varchar(100)
declare @alert_message_patindexname int
declare @count_alert_message int
declare @left_alert_error_message varchar(1000)
declare @length_alert_message int
declare @final_count int
declare @time datetime
declare @j int
declare @mail_body varchar(1000)
declare @mail_subject varchar(1000)
declare @message varchar(1000)

create table #replicationalerts
(
replication_alert_id int,
[time] datetime,
subscriber varchar(100),
alert_error_message varchar(1000)
)
create table #subscriber_alert_message
(
subscriber_name varchar(1000),
general_name varchar(100),
[time] datetime,
alert_message varchar(1000)
)
insert #replicationalerts
select row_number() over(order by alert_id),[time],subscriber,alert_error_text
from sysreplicationalerts
where datepart(yyyy,[time]) = datepart(yyyy,getdate())
and datepart(mm,[time]) = datepart(mm,getdate())
and datepart(dd,[time]) = datepart(dd,getdate())
set @i = 1
select @max_count = max(replication_alert_id) from #replicationalerts
while(@i<=@max_count)
begin
select
@subscribername=subscriber from #replicationalerts where replication_alert_id = @i
select
@patindexname=patindex('%\%',@subscribername)
select
@count = @patindexname-1
select
@left_subscriber_name = left(@subscribername,@count) from #replicationalerts
select
@alerterrormessage = alert_error_message from #replicationalerts where replication_alert_id = @i
select
@alert_message_patindexname = patindex('%.%',@alerterrormessage)
select
@count_alert_message = @alert_message_patindexname -1
select
@length_alert_message = len(alert_error_message) from #replicationalerts where replication_alert_id = @i
select
@final_count = @length_alert_message-@count_alert_message
select
@left_alert_error_message = right(@alerterrormessage,@final_count-1) from #replicationalerts
select
@time = [time] from #replicationalerts where replication_alert_id = @i
insert #subscriber_alert_message
select @left_subscriber_name,NULL,@time,@left_alert_error_message
update #subscriber_alert_message
set general_name = a.general_name from notification_plants a inner join #subscriber_alert_message b
on a.plant_server_name = b.subscriber_name
set @i = @i+1
end
create table #alert_message
(
row_id int,
subscriber_name varchar(1000),
general_name varchar(1000),
alert_message varchar(1000)
)
insert #alert_message
select
rank() over(order by subscriber_name,alert_message),
subscriber_name,
general_name,
alert_message
from
#subscriber_alert_message
where
datediff(mi,[time],getdate())<=60
set @j =1
select @max_count = max(row_id) from #alert_message
while(@j<=@max_count)
begin
select @mail_subject = general_name+space(1)+'Replication Has Failed' from #alert_message where row_id = @j
select @message = alert_message from #alert_message where row_id = @j
select @mail_body =
'
Hi Team,<br><br>
The reason for failure is:<br><br>
'+convert(varchar(100),@message)+'<br><br>
Regards,<br>
BCP Administrator
'
exec msdb..sp_send_dbmail @profile_name = 'Profile Name'
,@recipients ='Email Address'
,@subject = @mail_subject
,@body = @mail_body
,@body_format = 'HTML'
,@importance = 'High'
set @j = @j+1
end
drop table #alert_message
drop table #replicationalerts
drop table #subscriber_alert_message
end

I hope your’l have enjoyed reading this. Any suggestions will be highly appreciated.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: