November 2011 - Posts

A follow up to yesterday

As I have been asked,  here to tidy up yesterdays post is the procedure my startup procedure calls along with the logging table deployed in the DBA database.

Just to muddy the water further I have routines for remotely calling the DBAMessages table through a remote server to send out email from a central server!!

Just to explain that I have been ( previously ) limited to only using one Server to send email alerts for multiple Servers so I attempt to code to deal with all possible circumstances.

I have two DBA databases on each server which hold code and data I use to manage the server.
I try to make sure everything is generic rather than specific as carrying a different version of a procedure or table on every server can quickly become a nightmare, not counting anything  specific to the SQL Server version or edition.

Here’s the procedure which handles messages and the table that messages are written to ( optionally ).

/****** Object:  StoredProcedure [dbo].[dbasp_SendMessage]    Script Date: 11/21/2011 19:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[dbasp_SendMessage]
@subject varchar(100),
@message varchar(1000),
@to varchar(255)
-- =============================================================  
--  Stored Procedure:     dbo.dbasp_SendMessage                          
--  Written by:         Colin Leversuch-Roberts
--                      www.kelemconsulting.co.uk     
--                        (c) 2005                            
--                                                                
--  Purpose:               simplifies sending a message to email or file   
--                                                                
--  System:                DBA maintenance - ServerAdmin
--
--  Input Paramters:        @subject   -  the email/message subject
--                                     @message   -  email/message body
--                                     @to        -  recipients
--             
--  Output Parameters:     None                                      
--  Return Status:         @@error
--                                                                
--  Usage:                 EXEC dbo.dbasp_SendMessage 'Warning','message','fred@somedomain.co.uk'
--                                                                
--  Called By:                                                           
--  Calls:                 DBADatabase.dbo.dbasp_SendSMTPmail
--                        DBADatabase.dbo.ServerParameters ( table )
--                      DBADatabase.dbo.DbaMessages ( table )
--
--  Notes:        
--            This is to simplify the direction of messages if a mail server
--            is unavailable. Sends mail or writes to message table
--
--                                                                
--  VERSION HISTORY
--  Version No        Date            Description
--  1            19th May 2005    Initial Release
--  2                            sql 2008
-- =============================================================  
--
as
set nocount on
declare @from varchar(100) = @@servername+'@regus.com';
--
IF (Select ParmValue from DBADatabase.dbo.ServerParameters where ParmName = 'MessageMail')=1
    BEGIN
        exec DBADatabase.dbo.dbasp_SendSMTPmail @from, @to, @subject, @message;
        print  @subject;
    END
--endif
IF (Select ParmValue from DBADatabase.dbo.ServerParameters where ParmName = 'MessageLog')=1
    BEGIN
        Insert into DBADatabase.dbo.DbaMessages(Alert,[Notification]) values (@subject,@message);
        print  @subject;
    END
--endif
return(@@error);
GO

And this is the table definition

/****** Object:  Table [dbo].[DbaMessages]    Script Date: 11/21/2011 20:01:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DbaMessages](
    [Numkey] [int] IDENTITY(1,1) NOT NULL,
    [TheDate] [datetime] NOT NULL,
    [Alert] [varchar](250) NOT NULL,
    [Notification] [varchar](5000) NOT NULL,
    [Actioned] [tinyint] NOT NULL,
 CONSTRAINT [PK_DbaMessages] PRIMARY KEY CLUSTERED 
(
    [Numkey] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[DbaMessages] ADD  CONSTRAINT [DF_DbaMessages_TheDate]  DEFAULT (getdate()) FOR [TheDate]
GO
ALTER TABLE [dbo].[DbaMessages] ADD  CONSTRAINT [DF_DbaMessages_Actioned]  DEFAULT ((0)) FOR [Actioned]
GO

Hello it’s your server calling

This is nothing exciting but I've always found this startup procedure  very useful.
All this simple procedure does is send you an email if the SQL Service Starts.
If your Server is a cluster it will tell you which node you're on.
--
On it's own this procedure can't actually be used as I route the output through another procedure, dbasp_SendMessage, this procedure routes a passed message to either a smtp email or a log table or both, the destination is set in a server config table which allows me to still log dba messages even if the mail server is unavailable or if I'm in a environment which doesn't have email.
I'm assuming that most have a favourite stored procedure which will send an smtp email so all you need to do is substitute the procedure.
For SQL 2005 you'll have to define and assign the variables on seperate lines.
--
All my SQL Jobs have customised failure/success alerting which is routed through dbasp_SendMessage, the dbasp_ prefix was accepted practice at one client and I have not chosen to change it.
--
I set all my alerts with the format of three exclamation marks either side of a key word, this assists with rules in Outlook, by default the sender address of the email is set to @@ServerName, this avoids any confusion with multiple environments raising alerts.

 

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_Mail_DBA_on_Start]    Script Date: 11/21/2011 13:53:26 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Mail_DBA_on_Start]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Mail_DBA_on_Start]
GO

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_Mail_DBA_on_Start]    Script Date: 11/21/2011 13:53:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp_Mail_DBA_on_Start]
-- ============================================================================
--  Stored Procedure:     sp_Mail_DBA_on_Start                          
--                       
--  Written by:     Colin Leversuch-Roberts
--                     www.kelemconsulting.co.uk 
--                    (c) january 2005                            
--                                                                
--  Purpose:          Nothing very complicated, uses  SMTP proc to mail the DBA group
--                    when the sql server restarts
--                    Master procs can be set to run on start-up, this is supported by Microsoft
--                    and is a valid implementation.
--                    set to autorun using exec sys.sp_procoption '[dbo].[sp_Mail_DBA_on_Start]','startup','true';
--                    check status using select OBJECTPROPERTYEX(  object_id('[dbo].[sp_Mail_DBA_on_Start]'),'ExecIsStartup');                            
--                                                                
--  System:            master database
--                    does not need to be marked as a system object
--
--  Input Paramters:       none
--                       
--  Output Parameters:     None                                      
--                                                                
--  Usage:                 runs on sql server service restart                                          
--                  
--  Calls:                 dbo.dbasp_SendMessage
--                                                           
--  Data Modifications: None                             
--                                                                
--  VERSION HISTORY
--  Version No        Date            Description
--  1            28-January-2005        Initial Release 
--    2           5 dec 2008            now sends message through  dbasp_SendMessage
--    3            jan 2011            changed to pick up physical name so names cluster node in message
-- ============================================================================
as
set nocount on;
declare @subject varchar(100) = '!!! ALERT !!! The SQL Service has just started';
declare @message varchar(1000) = 'The SQL Service has started on '+convert(varchar(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))+' - Time of event '+convert(varchar(30),getdate())+''+char(10)+char(10)+' If this was not planned then there may have been a server os failure or unauthorised reboot';
declare @to varchar(255) = 'DBAGroup@yourdomain.com';
exec ServerAdmin.dbo.dbasp_SendMessage @subject,@message,@to;
GO

EXEC sp_procoption N'[dbo].[sp_Mail_DBA_on_Start]', 'startup', '1'
GO
Posted by GrumpyOldDBA with no comments