Alerting capabilities in SQL Server 2005

Native alerts were valuable in SQL Server 2000, but at the same time they were unreliable in many cases due to SQL Server Mail's dependency on MAPI with Outlook. Many 'work-arounds' were created to improve the inherent functionality based on individual needs. SQL Server 2005 has alleviated this issue with the introduction of Database Mail. Thus far, this solution seems to meet industry needs for an SMTP based email system and has enabled the native SQL Server alerting capabilities to shine. In this tip we will outline the prerequisites for alerts, setup options and then highlight valuable alerts you'll want.

SQL Server 2005 alerts prerequisites

SQL Server 2005 Alerts are dependent on a few core pieces of infrastructure to operate properly. These items include:

[TABLE]

SQL Server 2005 alerts setup

SQL Server has two means to setup native alerts. The first is with SQL Server 2005 Management Studio and the second is via a set of system stored procedures. Below outlines examples of each option:

[TABLE]

Valuable SQL Server 2005 Alerts

Below are three basic approaches to building a set of needed SQL Server 2005 alerts, since none are created by

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Database Modeling and Design Managing the development lifecycle with Visual Studio Team System 2008 A first look at Visual Studio Team System 2008 Database Edition Testing transaction log autogrowth behavior in SQL Server Top 10 SQL Server Tips of 2008 Tutorial: SQL Server indexing tips to improve performance Tutorial: Learn SQL Server basics from A-Z SQL Server database design disasters: How it all starts Can you shrink your SQL Server database to death? Physical data storage in SQL Server 2005 and 2008 SQL Server 2008 data types: Datetime, string, user-defined and more Microsoft SQL Server Installation Creating fault-tolerant SQL Server installations SQL Server consolidation: Why it's an optimization technique SSIS error message due to installation problem on SQL Server 2005 Get SQL Server log shipping functionality without Enterprise Edition How to create a SQL Server linked server to DB2 Tutorial: Migrating to SANs from local SQL Server disk storage How to restore SQL Server database to transition server during upgrade SQL Server 2005 log shipping setup using the wizard SQL Server tools don't appear in menu after SQL Server 2005 install Troubleshoot SQL Server 2005 SP2 installation error Microsoft SQL Server Installation Research Microsoft SQL Server Performance Monitoring and Tuning Performance implications of transaction log autogrowth in SQL Server The short course on how SQL Server really works Determining the source of full transaction logs in SQL Server Improving SQL Server full-text search performance New GROUP BY option provides better data control in SQL Server 2008 Microsoft SQL Server 2008 Resource Governor primer Examining data files when SQL Server tempdb is full Testing transaction log autogrowth behavior in SQL Server Meeting business needs with SQL Server full-text search Using dynamic management views to improve SQL Server index effectiveness

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary binary tree  (SearchSQLServer.com) block  (SearchSQLServer.com) data structure  (SearchSQLServer.com) DDBMS  (SearchSQLServer.com) entity-relationship model  (SearchSQLServer.com) initial extent  (SearchSQLServer.com) primary key  (SearchSQLServer.com) segment  (SearchSQLServer.com) tablespace  (SearchSQLServer.com) view  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

default:

[TABLE]

Duplicating the alerts

Once you have all of the alerts that you want on one specific SQL Server, they can be scripted out and created on another SQL Server. This can be accomplished by the following steps:

SQL Server 2005's alerting capabilities are expected to meet the much desired features of the SQL Server community. Based on your application needs configure your alerts to be the first line of defense in if SQL Server has an issue. Do not overlook this simple yet valuable means to obtain near real time alerts.

About the author: Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.