Texting Alerts from SQL Server Database Mail

The Issue:

You need to get alerts from my SQL Server databases 24/7, but you don’t always carry your laptop to check email and when you do, you may not be near an internet connection. You may always have your cell phone with you, however. Can you get your SQL Servers to send you a text message when there is an important alert without setting up your own SMS gateway or cellular modem? Learn more…

A Solution:

Most cellular providers who offer texting (SMS or Short Message Service) also provide a free email to SMS gateway that costs the sender nothing and the receiver pays for messages based on their text plan. This tip will show how to configure an operator that will send an email to one of these gateways.

Some time ago it was announced that you could send an email to a cell phone using a free service provided by your cellular provider. When developing an on-call rotation for a DBA team it quickly became apparent that for off-hour alerts they needed a way to reach team members when they were not online. Those with smart phones found it easier to attach a custom notification to an incoming text message than to an email.

You setup two operators on each server, one for non-critical alerts and one for alerts that need immediate attention. The non-critical alerts only go to email and can sit in your inbox until someone checks. The critical alerts use an operator setup to send email to the SMS gateway of our cellular provider so they can be received by the on-call DBA even when they are offline.

Example:

Let’s assume that you have already setup Database Mail on your server, if not there are instructions in the “Database” Mail  category on MSDN2.com that will walk you through the process.

This is a table listing the largest cellular providers here in the US and the domain name of the SMS gateway that will convert an email to a text message. It is a free service from most providers, but a few may charge a fee or require sign-up. Keep in mind that even though sending the message is free, the recipient is liable for charges, if you have a run-away alert it can get very expensive! Check with your provider before you rack up a ton of text charges — be warned!

Provider

Gateway Domain

Alltel PhoneNumber@message.alltel.com
AT&T PhoneNumber@txt.att.net
Sprint PhoneNumber@messaging.sprintpcs.com
T-Mobile PhoneNumber@tmomail.net
US Cellular PhoneNumber@email.uscc.net
Verizon PhoneNumber@vtext.com
Virgin Mobile PhoneNumber@vmobl.com

If your provider is not in this list or you are outside the US, then search Wikipedia for “SMS Gateway” for a more extensive list or contact your provider directly for information.

To ensure Database Mail can reach the gateway let’s send a text email/text message. Open SQL Server Management Studio and connect to an instance with Database Mail configured. In Object Explorer expand the instance, then the Management tab. Right-click on Database Mail and select “Send a test E-mail”. Enter your e-mail address in the To: field and click Send Test E-Mail, use your cell number and the gateway domain name from the table above to find your own cell phones email address. If you’ve got the right address and Database Mail is set up correctly you should get a text message in just a minute or two.

A Test Script:

You can also enter the appropriate email in the script below to send a quick test.

USE [msdb]

GO

EXEC msdb.dbo.sp_send_dbmail

@recipients = “1235550000@vtext.com”,

@subject = “Test SMS”,

@body = “This is only a test”

GO

For this to be useful for alerts we will need to configure an operator. In the Object Explorer pane of SQL Server Management Studio expand the instance, then expand the SQL Server Agent listing, right-click the “Operators” option and select “New Operator…” For the name of the operator enter “New On-Call Operator” and in the Email name you should enter your cell number and the appropriate gateway from the table above or from Wikipedia.

For more Microsoft Technical Training information visit www.directionstraining.com or call 1-855-575-8900.

*Adapted from an article on MSSQLTips.com