An SQL Server Agent is a component of the Microsoft SQL Server, which can run pre-set tasks or “jobs” on a schedule, in response to a specific request.
Here’s an example scenario: you’re tasked to back up all your company servers daily after working hours, and to do so is very tedious (repeat again: after working hours). Instead of sacrificing your work-life balance, you can automate recurring jobs with an SQL Server Agent, scheduling the backup to run Mondays through Fridays, at exactly 12:01am, where you know there’s not likely to be any new activity you’ll miss out (save for your overzealous colleague’s unexpected bursts of energy at 3:00am). Automation – Complete!
But stopping there means you don’t have a clue if a backup doesn’t actually happen due to some strange unforeseen forces. What then?
The answer, my friend, is written in the… email alert setup in your SQL Server Agent. But alas, you have not set it up – the horrors, like a bad Marvel comic-to-screen adaptation with twists and turns at every corner! Fret not, mi compadre, this article explains exactly how to create SQL Server Agent email alerts that will record the event and automatically notify you through your inbox.
First things first, access your SQL Server Management Studio (SSMS) and connect to the Database Engine. Expand Management, and right-click Database Mail. In this menu, select Configure Database Mail.
When this appears on your screen, click Next.
Leave default option Set up Database Mail by performing the following tasks and click Next.
Fill in your Profile Name then click Add to input your SMTP account.
Fill in the fields with your details, then click OK.
Great! You’ve just created the SMTP account record.
To make the profile public, check the box to the left of the Profile Name. Note that permissions will need to be assigned properly if the profile is not set to public.
Leave the System Parameters as default values, then click Next.
Click Finish.
While the system is running the configuration, check that each action status lists as “Success”. Otherwise, go back to the previous step. When all the actions status are successful, you can now close this window and move on to the next step.
First, open your SQL Server Management Studio (SSMS) and connect to the Database Engine. Once there, expand Management, right-click Database Mail, and click Send Test E-Mail.
Fill in the “Database Mail Profile” and “To” information fields, then click Send Test E-mail.
If you’ve done the setup successfully, the recipient in the “To” field earlier will receive the email.
Once again, open your SQL Server Management Studio (SSMS) and connect to the Database Engine. This time, right-click SQL Server Agent, and select Properties.
In the Alert System page, do the following: i. Under Mail session, tick the option Enable mail profile. Ensure you select the correct mail profile. ii. Under Token replacement, tick Replace tokens for all job responses to alerts. Then click OK. iii. Restart the SQL Server Agent service.
Stay on the SQL Server Agent, and right-click Operators to expand the menu. Select New Operator.
Insert the recipient email address in the Notification options section and click OK.
Now, expand the SQL Server Agent menu and click on Jobs. Right-click on the job that requires automation, and select Properties.
From here, go to Notifications on the left navigation pane, and enable E-mail. Then select the Operator created earlier. Lastly, select the condition When the job fails from the drop-down list next to the E-mail Operator field.
And you’re done! Now your appointed user (a.k.a. Operator) will receive an email similar to this one below if a job fails to execute. Cheers to peaceful after hours!
Generate P&L Reports with Account Schedules in Dynamics 365 Business Central in Minutes!
System Notification Emails on Business Central: Identify with Different Sender Addresses in Just 5 Minutes!
This article was written by our in-house experts.
Need any help on your Microsoft Dynamics365 Business Central? Drop us a note at sales@crt-insights.com!
WhatsApp us
