Blog Post

SQL- SP_PROCOPTION – AutoNotification to App Owners – When SQL Instance Restarts

,

We had a situation where Application owners automatically notified whenever server restarts.  The SP_PROCOPTION do have a facility to perform this activity. The SP_PROCOPTION built-in stored procedure being used to invoke the user defined stored proc automatically.

The details of this implementation is as follows

  • User defined meta data table to hold all Application Owner Details
  • Create user defined Stored procedure Named “Notification” under MASTER database(Mandatory).The below automation developed with an intellegence to concatenate all Application Owner’s email-ids.
  • Modify the stored procedure “Notification” – ProfileName and BlindCopyReceipents
  • Add this stored procedure for automatic execution
  • Once done Restart the SQL Instance .

Pre-requisites

  •  Mail Profile – Make sure that you have DB Mail Configured.

Download the complete code here SQL- AutoNotification to App Owners – When SQL Instance Restarts

Table Creation to store Meta Data about the Server

USE MASTER

GO

CREATE TABLE AutoAlert

(

id INT IDENTITY(1,1),

DbName VARCHAR(100),

DbOwner VARCHAR(100) DEFAULT ‘DBA Team’,

AppOwner VARCHAR(100),

AppOwnerID VARCHAR(100))

/* Insert AppOwners Details*/

INSERT INTO AutoAlert(dbname,AppOwner,AppOwnerID) VALUES(‘PROD_OLAP’,'Dave H’,'dheim@PowerSQL.com’)

INSERT INTO AutoAlert(dbname,AppOwner,AppOwnerID) VALUES(‘Crystal_App’,'Ana V’,'vana@PowerSQL.com’)

–SELECT * FROM dbo.AutoAlert

Stored Procedure Creation – Change Profile and BCC receipients

/* Create a new Stored Proc NOTIFICATION Under Master Database*/

CREATE PROCEDURE NOTIFICAION

AS

DECLARE @tableHTML NVARCHAR(MAX),

@td1 CHAR(15),

@td2 CHAR(15) ,

@td3 CHAR(20) ,

@td4 CHAR(15),

@td5 CHAR(15),

@td6 CHAR(15),

@td7 CHAR(15),

@Loopstatus2 INT,

@RowId2 INT,

@dmll NVARCHAR(max),

@dml2 NVARCHAR(max),

@Loopstatus1 INT,

@RowId1 INT,

@ProfileName VARCHAR(100),

@blind_copy_recipients VARCHAR(100),

@receipients VARCHAR(200),

@subject VARCHAR(500),

@email1 VARCHAR(MAX),

@email2 VARCHAR(MAX),

@email varCHAR(100)

/* Change Profile and Receipients details*/

SET @ProfileName =’PowerSQL’
SET @blind_copy_recipients=’PowerSQL@PowerSQL.com’

CREATE TABLE #mail

(

id INT IDENTITY(1,1),

email VARCHAR(100))

INSERT INTO #mail(email)

SELECT DISTINCT AppOwnerID FROM dbo.AutoAlert

SET @LoopStatus2=1

SET @RowId2=1

SET @email1=”

SET @email2=”

WHILE @LoopStatus2>0

BEGIN

SELECT @email=email FROM #mail WHERE id=@RowId2

IF @@ROWCOUNT = 0

BEGIN

SET @LoopStatus2=0

END

ELSE

BEGIN

SET @email1=@email1+@email+’;’

SET @email2=@email2+@email1

PRINT @email2

SET @email1=”

END

SET @RowId2=@RowId2+1

END

PRINT @email2

SET @email2=SUBSTRING(@email2,0,len(@email2))

PRINT @email2

SET @dmll=”

SET @dml2=”

SET @Loopstatus1=1

SET @RowId1=1

SET @tableHTML =

N’<H1>Server Restart Notification </H1>’ +

N’<table border=”1″ cellpadding=”1″><tr>

<th BGCOLOR=”RED”>[Server]</th>

<th BGCOLOR=”RED”>[DB Name]</th>

<th BGCOLOR=”RED”>[App Owner]</th>

</tr>’

WHILE @Loopstatus1<>0

BEGIN

SELECT

@td1 =@@servername,

@td2 =DBName,

@td3 =AppOwner

FROM AutoAlert WHERE id=@RowId1

IF @@ROWCOUNT=0

BEGIN

SET @Loopstatus1=0

END

ELSE

BEGIN

SET @dmll= @dml2+N’<tr><td>’+@td1+N’</td><td>’+@td2+’</td><td>’+@td3+’</td></tr>’

SET @dml2=@dmll

SET @dmll=”

END

SET @RowId1=@RowId1+1

END

SET @tableHTML=@tableHTML+@dml2+’</table>’+CHAR(13)+’Auto generated Email, Do not reply’+CHAR(13)+’Appvion DBA Team’

–PRINT @tableHTML

SET @Subject=’DB Server ‘+ @@servername +’ Restarted, Please Check all your dependent App Services’

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @ProfileName,

@recipients=@email2,

@blind_copy_recipients=@blind_copy_recipients,

@subject = @subject,

@body = @tableHTML,

@body_format = ‘HTML’;

DROP TABLE #mail

The following SQL sets a procedure for automatic execution

EXEC sp_procoption @ProcName = ‘NOTIFICAION’

, @OptionName = ‘startup’

, @OptionValue = ‘on’;

The following SQL stops a procedure from automatic execution

EXEC sp_procoption @ProcName = ‘NOTIFICAION’

, @OptionValue = ‘off’;

Restart SQL Instance

Output :-

ServerRestart

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating