In my previous article we have discussed what is TDE, how it works, its pros and cons. In this article I’m going to cover how to implement transparent data encryption to your server. It’s very simple to implement TDE, below are the steps that needs to be completed to enable TDE.
Introduction
Securing each part and data’s in a database is now an important task for DBA’s to accomplish else the data’s might be hacked. Keeping in mind Microsoft has started encryption features from SQL 2005 and it’s greatly improved it’s feature in SQL Server 2008. Lets recall the encryption features in previous versions of SQL Server.
In my previous article we have covered installing reporting service. The next step in reporting service is to configure them according to your environment.
There are many options are available in reporting service configuration. You need to configure all these values with respect to your environment. There are two ways to configure reporting service a.) Using Reporting service configuration GUI and b.) Reporting service configuration command line utility (RSConfig.exe located @ C:\Program Files\Microsoft SQL Server\90\Tools\Binn directory). The reporting service GUI configuration tool is very simple to use and many prefer this. Lets configure reporting service using this GUI too.
I was trying to find a alert which could fire immediately upon any of the job failure in the server to send an email. Unfortunately I couldn’t find any the alert related to this. So I thought of writing a DML trigger which should fire immediately when an entry is inserted in sysjobhistory table on msdb database. The next step is that the trigger should send a mail when there is failure in the job, hence I’ve used run_status column in the table, as all the DBA’s know when this column has a value 0 then it states the job has been failed. I’ve tried this script in SQL Server greater than version 2000 however it’s not working on it. This script works only on SQL Server 2000, still some of them are still working on SQL 2000 so I thought this script has still value. This DML trigger will send an email upon any of the job failure.
Applies To
/*
Written By : Vidhya Sagar
www.sql-articles.com
*/
USE MSDB
GO
CREATE TRIGGER jobfailurenotify
ON sysjobhistory
FOR INSERT
AS
DECLARE @failed TINYINT, @subject VARCHAR(300), @body VARCHAR(500)
SELECT @failed = COUNT(*) from inserted where run_status=0
IF @failed > 0
BEGIN
SELECT @subject=name from msdb.dbo.sysjobs where job_id in (select distinct(job_id) from inserted where run_status=0)
SET @subject=@subject + ' job failed on ' + CONVERT(VARCHAR(25), GETDATE())
SET @body=@subject + '. Please look in to this. Server Name : ' + CONVERT(VARCHAR(25),SERVERPROPERTY('SERVERNAME'))
EXEC master.dbo.xp_sendmail
@recipients = N'kvs1983@gmail.com',
@subject = @subject,
@message = @body
END
ELSE
BEGIN
PRINT 'No job failures found'
END
Sample Mail Output
From: testing@gmail.com
Sent: Monday, March 02, 2009 6:50 PM
To: kvs1983@gmail.com
Subject: TESTING job failed on Mar 2 2009 6:50PM
TESTING job failed on Mar 2 2009 6:50PM. Please look in to this. Server Name : SAGARSYS
If you have any concerns or queries regarding this script. Just post it out in our Forums Section.
Microsoft SQL Server Reporting Services enables organizations to transform valuable enterprise data into shared information for insightful, timely decisions at a lower total cost of ownership. In this article I’m going to install SQL Server 2005 Reporting Services on Windows 2003 machine. Let’s discuss about installation of reporting services.
Pre-requisites of SQL Server Reporting Service