Wednesday, 11 September 2013

Encryption Decryption Routine

In this post we will look at a complete end to end routine for encrypting, storing, decrypting data in SQL Server and just how easy it is to set-up and maintain.  SQL Server encrypts data with a hierarchical encryption and key management infrastructure.  Each layer encrypts the layer below it by using a combination of certificates, asymmetric keys, and symmetric keys.  It is important to understand the different layers of protection, how they interact the performance overhead and best practices.  Books Online has a great visualisation of the SQL Server encryption hierarchy which I have included below.




Script

Below is the script used to create both the encryption / decryption routine and the equivalent routine without encryption.  You will need to Modify the below;

-- Change the database name below
USE SQLServer365;

GO

-- Change the path to the database master key backup
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'rZVb3DwZ8Vptc2#vm4wapspB';
BACKUP MASTER KEY TO FILE = 'C:\SQL\Backup\DatabaseMasterKeys\SQLServer365DatabaseMasterKey'
    ENCRYPTION BY PASSWORD = 'ratr7XgGGSJ5dM4QzAaXc8cj';

GO

-- Change the path to the certificate backup
BACKUP CERTIFICATE CertBankDetails TO FILE = 'C:\SQL\Backup\Certificates\CertBankDetails';
GO

/*
      -----------------------------------------------------------------
      Encryption / Decryption Routine
      -----------------------------------------------------------------
   
      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
    
      -----------------------------------------------------------------
*/
-- Set database context
USE SQLServer365
GO
-- Create table
IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   [object_id] = OBJECT_ID('dbo.BankDetails')
                        AND [type] = 'U' )
CREATE TABLE dbo.BankDetails(
      BankDetailsID INT IDENTITY(1,1) NOT NULL CONSTRAINT [PK_BankDetails:BankDetailsID] PRIMARY KEY,
      CustomerID INT NOT NULL,
      SortCode VARBINARY(128) NOT NULL,
      AccountNumber VARBINARY(128) NOT NULL,
      InsertDate DATETIME NOT NULL CONSTRAINT [DF_BankDetails:InsertDate] DEFAULT (GETDATE())
) ON [PRIMARY]
ELSE
      PRINT 'Error: Table "dbo.BankDetails" already exists, please modify the script to create a table name that does not already exist';
GO

-- Create database master key
/*
      This is the database master key that is used to encrypt all certificates when a password is not supplied
*/
IF NOT EXISTS ( SELECT 1
                        FROM sys.symmetric_keys
                        WHERE name = '##MS_DatabaseMasterKey##' )
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'rZVb3DwZ8Vptc2#vm4wapspB'
ELSE
      PRINT 'Error: Database master key already exists!'
GO

-- Backup master key
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'rZVb3DwZ8Vptc2#vm4wapspB';
BACKUP MASTER KEY TO FILE = 'C:\SQL\Backup\DatabaseMasterKeys\SQLServer365DatabaseMasterKey'
    ENCRYPTION BY PASSWORD = 'ratr7XgGGSJ5dM4QzAaXc8cj';
GO

-- Create certificate
/*
      This is the certificate used to protect the symmetric key
*/
IF NOT EXISTS ( SELECT  1
                FROM    sys.certificates
                WHERE   name = 'CertBankDetails' )
CREATE CERTIFICATE CertBankDetails
   WITH SUBJECT = 'Bank Details Certificate',
   EXPIRY_DATE = '02/25/2014';
ELSE
      PRINT 'Error: Certificate "CertBankDetails" already exists, please modify the script to create a certificate that does not already exist';
GO

-- Backup the certificate
BACKUP CERTIFICATE CertBankDetails TO FILE = 'C:\SQL\Backup\Certificates\CertBankDetails';
GO

-- Create symmetric key
/*
      This is the symmetric key used in conjunction with the certificate to encrypt / decrypt the data
*/
IF NOT EXISTS ( SELECT  1
                FROM    sys.symmetric_keys
                WHERE   name = 'SymKeyBankDetails' )
CREATE SYMMETRIC KEY SymKeyBankDetails
      WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE CertBankDetails;
ELSE
      PRINT 'Error: Symmetric key "SymKeyBankDetails" already exists, please modify the script to create a symmetric key that does not already exist';
GO

-- Create Encryption Proc
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   [object_id] = OBJECT_ID('dbo.spInsertBankDetails')
                    AND [type] IN ( 'P' ) )
    BEGIN
        DROP PROCEDURE dbo.spInsertBankDetails
    END
GO                   
CREATE PROCEDURE dbo.spInsertBankDetails
AS
BEGIN
-- Open Symetric Key
OPEN SYMMETRIC KEY SymKeyBankDetails
   DECRYPTION BY CERTIFICATE CertBankDetails;
-- Insert a record
INSERT INTO SQLServer365.dbo.BankDetails
           (CustomerID,
           SortCode,
           AccountNumber,
           InsertDate)
     VALUES
           (1,
           EncryptByKey(Key_GUID('SymKeyBankDetails'), '01-02-03'), -- Encrypt SortCode
           EncryptByKey(Key_GUID('SymKeyBankDetails'), '01234567'), -- Encrypt AccountNumber
           GETDATE());
END
GO

-- Create Decryption Proc
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   [object_id] = OBJECT_ID('dbo.spGetBankDetails')
                    AND [type] IN ( 'P' ) )
    BEGIN
        DROP PROCEDURE dbo.spGetBankDetails
    END
GO 
CREATE PROCEDURE dbo.spGetBankDetails
AS
BEGIN
-- Open Symetric Key
OPEN SYMMETRIC KEY SymKeyBankDetails
   DECRYPTION BY CERTIFICATE CertBankDetails;
  
-- Return decrypted record
SELECT BankDetailsID,
      CustomerID,
      CONVERT(VARCHAR, DecryptByKey(SortCode)) AS SortCode, -- Decrypt SortCode
      CONVERT(VARCHAR, DecryptByKey(AccountNumber)) AS AccountNumber, -- Decrypt AccountNumber
      InsertDate
FROM SQLServer365.dbo.BankDetails;
END
GO

-- Insert an encrypted record
EXEC SQLServer365.dbo.spInsertBankDetails;
GO

-- Return encrypted data
SELECT BankDetailsID,
      CustomerID,
      SortCode,
      AccountNumber,
      InsertDate
  FROM SQLServer365.dbo.BankDetails;
GO 
 
-- Return decrypted data
EXEC SQLServer365.dbo.spGetBankDetails;
GO


/*
      Unencrypted data for performance comparison
*/

-- Set database context
USE SQLServer365
GO
-- Create table
IF NOT EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   [object_id] = OBJECT_ID('dbo.BankDetailsNoEncryption')
                        AND [type] = 'U' )
CREATE TABLE dbo.BankDetailsNoEncryption(
      BankDetailsNoEncryptionID INT IDENTITY(1,1) NOT NULL CONSTRAINT [PK_BankDetailsNoEncryption:BankDetailsNoEncryptionID] PRIMARY KEY,
      CustomerID INT NOT NULL,
      SortCode VARCHAR(50) NOT NULL,
      AccountNumber VARCHAR(50) NOT NULL,
      InsertDate DATETIME NOT NULL CONSTRAINT [DF_BankDetailsNoEncryption:InsertDate] DEFAULT (GETDATE())
) ON [PRIMARY]
ELSE
      PRINT 'Error: Table "dbo.BankDetailsNoEncryption" already exists, please modify the script to create a table name that does not already exist';
GO

-- Create Insert Proc
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   [object_id] = OBJECT_ID('dbo.spInsertBankDetailsNoEncryption')
                    AND [type] IN ( 'P' ) )
    BEGIN
        DROP PROCEDURE dbo.spInsertBankDetailsNoEncryption
    END
GO                   
CREATE PROCEDURE dbo.spInsertBankDetailsNoEncryption
AS
BEGIN
-- Insert a record
INSERT INTO SQLServer365.dbo.BankDetailsNoEncryption
           (CustomerID,
           SortCode,
           AccountNumber,
           InsertDate)
     VALUES
           (1,
           '01-02-03',
           '01234567',
           GETDATE());
END
GO

-- Create Get Proc
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   [object_id] = OBJECT_ID('dbo.spGetBankDetailsNoEncryption')
                    AND [type] IN ( 'P' ) )
    BEGIN
        DROP PROCEDURE dbo.spGetBankDetailsNoEncryption
    END
GO 
CREATE PROCEDURE dbo.spGetBankDetailsNoEncryption
AS
    BEGIN
        SELECT  BankDetailsNoEncryptionID ,
                CustomerID ,
                SortCode ,
                AccountNumber InsertDate
        FROM    SQLServer365.dbo.BankDetailsNoEncryption;
    END
GO

-- Insert unencrypted record
EXEC SQLServer365.dbo.spInsertBankDetailsNoEncryption;
GO

-- Return data
EXEC SQLServer365.dbo.spGetBankDetailsNoEncryption;

GO

Performance Comparison

I did some performance analysis comparing the insert encrypting the data / the select decrypting the data to the equivalent without encryption and decryption.  I used SQLQueryStress by Adam Machanic to execute the insert and select of both routines 100 times across 10 threads, the results of which I have to say might surprise a few of you;


UnencryptedInsert
EncryptedInsert
IncreasePercentage
Execution Time
0.73
2.5712
252.22
Client Seconds / Iteration (Avg)
0.0055
0.022
300.00
Logical Reads / Iteration (Avg)
2.034
2.114
3.93
CPU Seconds / Iteration (Avg)
0.0002
0.0033
1550.00
Actual Seconds / Iteration (Avg)
0.0076
0.0288
278.95
UnencryptedSelect
EncryptedSelect
IncreasePercentage
Execution Time
0.582
3.7593
545.93
Client Seconds / Iteration (Avg)
0.0032
0.0267
734.38
Logical Reads / Iteration (Avg)
10
26
160.00
CPU Seconds / Iteration (Avg)
0.0012
0.0103
758.33
Actual Seconds / Iteration (Avg)
0.0017
0.0319
1776.47

With this significant overhead, I recommend you make sure you have the capacity to make use of SQL Servers encryption hierarchy.  It is important to be selective, only encrypt data that you actually need to.  Investigate the use of an Hardware Security Module (HSM) as these add a layer of abstraction by keeping the encryption keys separate from the the encrypted data.  It is also possible to offload the encryption overhead from the SQL Server to the HSM for improved performance.


I will finish with 3 recommendations;

Backup the certificates and keys!

Simple really, make sure you backup all your database master keys and all your certificates, the usual precautions apply here as they do for all backups;
  • Back them up to a different drive 
  • Back them up to tape / different array
  • Get them off site
Be aware of the expiry date of the certificates!

Again this goes without saying but you don't want the be the person responsible when the applications are throwing errors as the certificate has expired :)

Scripts Save Life's!

As I have said many times before, I'm not a GUI fan.  Each to their own but you really should be scripting this stuff!  Scripts can be saved, backed up, recovered and the end result is achieved quicker than a GUI or Wizard if you have the scripts to hand.

Enjoy!

Chris

Thursday, 5 September 2013

Free Tools For The DBA

In this post we will look at 5 free tools that I use on a daily basis, I'll give a little bit of detail about them, why I find them useful and the links for you to download them.

1 - Maintenance Scripts - Ola Hallengren

What can I say about these maintenance scripts?  They just work, the only time I have ever seen a failure is when I have been a bit of a numpty!  They quite literally cater for every eventuality when backing up / consistency checking and performing index maintenance.  For example if you are taking native SQL Server backups on any version of SQL Server and are not using these scripts I would have a bit of a re-think.

http://ola.hallengren.com/

2 - SSMS Tools Pack - Mladen Prajdic

I have been using the SSMS Tools Pack for at least 3 years now.  There are some great features but my two favourites are Window Connection Colouring and Local SQL Query History.  Window Connection Colouring allows you to configure a different coloured strip for all instances which appears at the top of each Query Window in SSMS.  I use a traffic light scheme of Red for Live, Orange for QA and Reporting and Green for Dev and my local instance.  It is in the peripheral view so no need to reach for the mouse or check the bottom left of the Window for the instance I am working on before truncating a table or dropping a database.  Local SQL Query History has saved my ass on numerous occasions, each and every time I personally thank Mladen via Twitter.  I have been burned many times in the past when SSMS has crashed and the script(s) I was working on have not been saved and do not get recovered.  With Local SQL Query History, every time you run a query it gets saved.  Simple but extremely effective.

http://www.ssmstoolspack.com/

3 - SQL Sentry Plan Explorer - SQL Sentry

SQL Sentry plan explorer is a must for me.  If you spend any time analysing execution plans then download this tool!  You will save so much time especially in larger more complex plans as all the information in the execution plan is more easily visible / accessible.  The traffic light scheme for the most expensive operators in the graphical plan view alone makes this tool worth a look. 

http://www.sqlsentry.net/plan-explorer/sql-server-query-view.asp

4 - Internals Viewer

Internals viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.  I am new to internals viewer and it is fantastic, If like me you are a geek and enjoy learning about how and why SQL Server does what it does, then this is a must!

http://intview2.codeplex.com/

5 - SQL Fragmentation Analyzer  - Idera

Apart from the adverts, which can be found just about everywhere (but hey it is free!) this is a fantastic little application.  It allows you to quickly and easily check the fragmentation of table, there are quite a few options for filtering but you can just as easily choose a table or entire database.

http://www.idera.com/productssolutions/freetools/sqlfragmentationanalyzer

Enjoy!

Chris

Monday, 26 August 2013

Bank Holiday Fun - DDL Trigger

Following on from my previous post on triggers, I had made a mental note to make sure I did a post about a DDL trigger I use.  As a DBA I am a gatekeeper to the companies SQL Server estate, I would love to say that every instance is locked down tighter than Fort Knox but this is not true.  The reality is that there are inevitably permissions nested that give a level of access that exceeds what the person actually requires to fulfill their role.  Sure I could just cart blanch everything I deem as being excessive and go on a REVOKE spree, but there would be a whole host of political repercussions as a result.  Rightly or Wrongly, the cold harsh reality is that some of these permissions will remain.

As a safety net I use a DDL Trigger in every single production database to notify me if someone has abused the privilege of having elevated permission.  It is worth highlighting that while I was creating the script used in this post I ran into the 2000 line limit of the undocumented sp_msforeachdb stored procedure and was just about to revert to using a while loop when @sqlchicken pointed out I should look at using an improved version created by @AaronBertrand.  You can find the sp_foreachdb procedure here. It is so much more flexible than the original Microsoft system procedure and one that I have now rolled out to our SQL Estate.  I will also be going back and updating all the routines I have that use sp_msforeachdb and replacing them with sp_foreachdb.

The below script will create a DDL Trigger trgDDLTableModification in every user database which will fire for the below statements;

ALTER_TABLE
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER

You will need to change the below two lines accordingly

@profile_name = 'DBA',
@recipients   = 'Chris@SQLServer365.com',


/*
      -----------------------------------------------------------------
      DDL Trigger
      -----------------------------------------------------------------
    
      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
    
      -----------------------------------------------------------------

*/
-- Set database context
USE master;
GO
-- Drop trigger in all user databases
EXEC sp_foreachdb
@command = 'USE ?;
IF EXISTS (SELECT 1 FROM sys.triggers WHERE parent_class_desc = ''DATABASE'' AND name = ''trgDDLTableModification'')
DROP TRIGGER trgDDLTableModification ON DATABASE;
GO',
@user_only = 1,
@print_command_only = 1;
GO
-- Print the create trigger command for all user databases
EXEC sp_foreachdb
@command =
'USE ?;
GO
CREATE TRIGGER trgDDLTableModification ON DATABASE
FOR ALTER_TABLE, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER
AS
BEGIN
    -- SET options
    SET NOCOUNT ON;
     
      -- Declare variables
    DECLARE @xmlEventData XML;
    DECLARE @DDLStatement VARCHAR(MAX);
    DECLARE @Msg VARCHAR(MAX);
    DECLARE @MailSubject  VARCHAR(255);
    DECLARE @DatabaseName VARCHAR(255);
    DECLARE @User NVARCHAR(50);

    -- Build variables from event data
    SELECT @xmlEventData = EVENTDATA();
    SET @DDLStatement = @xmlEventData.value( ''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'', ''nvarchar(max)'' );
    SET @DatabaseName = CONVERT(VARCHAR(150), @xmlEventData.query(''data(/EVENT_INSTANCE/DatabaseName)''));
    SET @User = CONVERT(VARCHAR(150), @xmlEventData.query(''data(/EVENT_INSTANCE/LoginName)''));

    IF CHARINDEX(''DROP TRIGGER trgDDLTableModification'',@DDLStatement) != 1
        AND CHARINDEX(''ALTER TRIGGER trgDDLTableModification'',@DDLStatement) != 1
    BEGIN
        SELECT @Msg                 = ''ALERT FIRED AS A RESULT OF A DDL TABLE LEVEL EVENT IN DATABASE: '' + @DatabaseName + CHAR(13) + CHAR(13)
                            + ''*** Start DDL Statement ***'' + CHAR(13) + CHAR(13)
                            + @DDLStatement + CHAR(13) + CHAR(13)
                            + ''*** End DDL Statement ***'' + CHAR(13) + CHAR(13)
                            + ''*** Start Event Data ***'' + CHAR(13) + CHAR(13)
                            + CAST(@xmlEventData AS NVARCHAR(MAX)) + CHAR(13) + CHAR(13)
                            + ''*** End Event Data ***'' + CHAR(13) + CHAR(13) ;                         
        SELECT @MailSubject = ''DDL TABLE LEVEL EVENT MODIFICATION DETECTED ON: '' + @@SERVERNAME;
           
            -- Send mail alert
        EXEC msdb.dbo.sp_send_dbmail
                  @profile_name     = ''DBA'',
            @recipients   = ''Chris@SQLServer365.com'',
            @subject      = @MailSubject,
            @body         = @Msg ,
            @importance   = ''high'';
    END
END;
GO',
@user_only = 1,
@print_command_only = 1;

GO

I have used the @print_command_only = 1 for both the drop and create statements for consistency because 'CREATE TRIGGER' must be the first statement in a query batch, so the second execution of sp_foreachdb would fail otherwise.  All you need to do once run is copy the results from the messages window paste them in a new query window and execute.

Enjoy!

Chris

Tuesday, 20 August 2013

Trigger Status

Have you ever spent hours looking at an issue only to have your investigation hindered by a trigger?  I know I have and on more than one occasion!  This little script can be added to a SQL Agent Job and scheduled as you require to email an operator with a list of all triggers for all user databases, the table they are on and the status.


/*
      -----------------------------------------------------------------
      Trigger Status
      -----------------------------------------------------------------
    
      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
    
      -----------------------------------------------------------------

*/
-- Set database context
USE master
GO

-- Declare variables
DECLARE @EmailProfile VARCHAR(255)
DECLARE @EmailRecipient VARCHAR(255)
DECLARE @EmailSubject VARCHAR(255)

-- Set variables
SET @EmailProfile = 'SQLReports';
SET @EmailRecipient = 'Chris@SQLServer365.co.uk';

--Drop temporary table if exists
IF OBJECT_ID('tempDB.dbo.#TriggerStatus') IS NOT NULL
    DROP TABLE #TriggerStatus ;
   
-- Create temporary table   
CREATE TABLE #TriggerStatus
(
      DatabaseName SYSNAME,
      TableName VARCHAR(255),
      TriggerName VARCHAR(255),
      TriggerStatus VARCHAR(8)
);
-- Insert triggers
INSERT INTO #TriggerStatus
EXEC sp_msforeachdb
'
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''reportserver'', ''reportservertempdb'')
BEGIN
USE [?];
SELECT  DB_NAME() AS DatabaseName,
        OBJECT_NAME(parent_id) AS TableName,
        name AS TriggerName,
        CASE is_disabled
          WHEN 0 THEN ''Enabled''
          ELSE ''Disabled''
        END AS TriggerStatus
FROM    sys.triggers WITH ( NOLOCK )
WHERE   is_ms_shipped = 0
        AND parent_class = 1;
END'

-- Check for unused indexes
IF EXISTS ( SELECT  1
            FROM #TriggerStatus)
    BEGIN
        DECLARE @tableHTML NVARCHAR(MAX); 
        SET @tableHTML = N'<style type="text/css">'
            + N'.h1 {font-family: Arial, verdana;font-size:16px;border:0px;background-color:white;} '
            + N'.h2 {font-family: Arial, verdana;font-size:12px;border:0px;background-color:white;} '
            + N'body {font-family: Arial, verdana;} '
            + N'table{font-size:12px; border-collapse:collapse;border:1px solid black; padding:3px;} '
            + N'td{background-color:#F1F1F1; border:1px solid black; padding:3px;} '
            + N'th{background-color:#99CCFF; border:1px solid black; padding:3px;}'
            + N'</style>' + N'<table border="1">' + N'<tr>'
            + N'<th>DatabaseName</th>'
            + N'<th>TableName</th>'
            + N'<th>TriggerName</th>'
            + N'<th>TriggerStatus</th>'
            + N'</tr>'
            + CAST(( SELECT td = DatabaseName,
                            '',
                            td = TableName,
                            '',
                            td = TriggerName,
                            '',
                            td = TriggerStatus,
                            ''                       
                     FROM   #TriggerStatus
                   FOR
                     XML PATH('tr') ,
                         TYPE
                   ) AS NVARCHAR(MAX)) + N'</table>'; 
     
            -- Set subject
            SET @EmailSubject = 'Trigger Status Report For ' + @@SERVERNAME
           
            -- Email results 
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,
            @recipients = @EmailRecipient, @subject = @EmailSubject,
            @body = @tableHTML, @body_format = 'HTML'; 
    END

    GO

Remember, fully understanding your environment, the features you use and what is the norm is something that pays dividends when things go bad.

Enjoy!

Chris

Friday, 9 August 2013

Windows Failover Cluster Monitor

Windows Failover Clusters are fantastic, they provide High Availability for mission critical SQL Server instances and make my life as a DBA better in so many ways.  Can they though, sometimes be too good?  I have had SQL Server instances failover between cluster nodes in the past that none of our alerting has picked up on!  I've only noticed the failover by stumbling across it days or even weeks later.  One way to prevent these "Ghost" failovers going unnoticed for prolonged periods of time that I use is to have a startup procedure on the instance to email an operator when a failover occurs.

Below is the script to create a startup procedure to achieve this.  You will need to update the @profile_name = 'SQLErrors' and @recipients = 'Chris@SQLServer365.co.uk' accordingly

I have played about with the WAITFOR DELAY a bit and found that 15 seconds is sufficient after the SQL Server Service has started and executed the startup procedure for the database mail engine to be ready and successfully send the email.


-- Set database context
USE master;
GO

-- Check if procedure exists
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   [object_id] = OBJECT_ID('dbo.spEmailSQLServerRestart')
                    AND [type] IN ( 'P' ) )
      -- Drop procedure                    
    DROP PROCEDURE dbo.spEmailSQLServerRestart
GO
-- Create procedure                   
CREATE PROCEDURE dbo.spEmailSQLServerRestart
AS
    BEGIN
            -- Declare Variables
        DECLARE @strServer VARCHAR(128) = CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
        DECLARE @strMailSubject VARCHAR(128) = 'SQL Server '
            + UPPER(@@SERVERNAME) + ' restarted!'
        DECLARE @strMailBody VARCHAR(1000) = 'SQL Server '
            + UPPER(@@SERVERNAME) + ' restarted at '
            + CONVERT(VARCHAR(12), GETDATE(), 108) + ' on '
            + CONVERT(VARCHAR(12), GETDATE(), 103)
            + ' Now running on server: ' + @strServer

            -- Wait for the database mail engine to start
        WAITFOR DELAY '00:00:15'

            -- Send Email
        EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLErrors',
            @recipients = 'Chris@SQLServer365.co.uk',
            @subject = @strMailSubject, @body = @strMailBody,
            @body_format = 'HTML';
    END      
GO

-- Set procedure as startup procedure   
EXEC sp_procoption @ProcName = 'spEmailSQLServerRestart',
    @OptionName = 'STARTUP', @OptionValue = 'ON';

GO

Enjoy!

Chris