Let’s stop and think about the title of the article.  Perhaps I should have chosen “Does your SQL server send alerts” but I wanted to make a big point here: IF YOU ARE RUNNING MICROSOFT SQL SERVER YOU ARE HOSTING A BACK END TO YOUR APPLICATION AND IF YOU ARE RUNNING SYTELINE YOU ARE RUNNING A MISSION CRITICAL APPLICATION.   Who in the world would want their application to have a back end that is silent and doesn’t tell you when there are issues???

When you run an enterprise ERP application that potentially supports hundreds or thousands of concurrent users both internal to the company and external to customers and vendors the last thing you want to deal with is recovering from a failure after the fact when your users are in a panic that they cannot enter an order into the system or perform another tasks that keeps the company in business.   When a consultant is done setting everything up and puts the keys to the kingdom in the hands of the on-site IT delegate, they need to be equipped with as much knowledge as possible to keep everything running smoothly and that involves not only a maintenance plan to keep everything tidy but also an alert system set up so that SQL can tell you when there is a problem rather than simply putting the error in the SQL Server log file repository.  The basic ingredients to activating the alert system is as follows:

Open SSMS and connect to the database engine. Expand object explorer until you find Management /Database Mail and right click to set up

SQL will alert you that you are about to set up a new profile.   The wizard will guide you through the entire process and you will of course need to know the IP or Hostname of the mail server including the authentication method needed.

Lastly, it will ask whether you want it to be your default profile and whether or not it is public or private.  I would make it your default profile and choose public as my profile type.

Lastly, I would increase the retry attempts to 5 and change the maximum size to 10MB

After setting up database mail, go ahead and right click on database mail once more and test it.  Make sure to send one to both an internal address and an external one to ensure that mail is authorized to travel outside of the network

 

At this point, you have hopefully received test emails that arrived on your smartphone and your desktop/laptop/tablet.   Now its time to activate the alert system.  To do this, you scroll to the bottom of SSMS, and right click on the agent and go to properties

Activate the alert system by checking the box and ensuring database mail is the preferred choice

Now it’s time to define who gets the alerts.  In Microsoft SQL, a recipient is known as an “operator”.   The operator can be a user or a distribution group depending on whether or not you want the alert to go to multiple people.  You can also define more than one operator and separate out the alerts however most of the Infor Syteline customers tend to have only a small group of people that would be dedicated to keeping the server up so it’s not unreasonable for only one operator to be defined in a SMB.

 

In the below example, instead of adding an individual, I chose to make it a group.  Name can be anything you want it to be and email address will either be a person’s email address or the email address of a distribution list

All options regarding pager settings and on duty settings are optional.  I don’t know many people that still have pagers but I imagine there are a few out there where this is applicable.     Repeat adding operators if needed and save when done

The last step is to define the alerts that are applicable.   There are some alerts that everyone should have set up no matter what while there are other alerts that are more custom to how you run your business or specific issues that you are plagued with such as deadlocks or user limits exceeded.  For now, keeping this in scope of critical alerts is a good starting place.

I have created a generic script that you can download here

add_alerts

Before running this script in SSMS you must do a mass replace with the word OPERATORNAME to whatever you named your operator or the script will partially fail.   If you forget or fail to read this message before execution, all this means is that you will need to go back to each alert and manually specify the operator or run just the part of code that is needed for setting up the operator.

This is just the beginning of what alerts there should be on your system.  With a little creativity, you can find creative ways for SQL to be your eyes for your Syteline system when you are not around.

Over the last couple of years, a new generation of viruses and spyware have emerged that prove to be the most dangerous we have ever seen. In the 90’s I remember reading news articles about disgruntled programmers who sought revenge by wiping data, formatting a drive, deleting a partition, or even played musical chairs with all the files on the system. Today, the idea behind all this rogue software released to users tends to be financially driven and the creative techniques used to infect a computer make “sneaky” an understatement.

Have you ever found yourself surfing a website one minute only to find out that your web browser downloaded and installed something on your computer? Consider yourself lucky that you were fortunate enough that whatever software downloaded made itself “known”. Now you got a real mess on your hands. Common anti-virus products are sold in the stores (no names mentioned) but they are often not able to catch threats that have been out in the wild for a short bit and most antivirus engines unfortunately heuristically look at the file(s) after the fact when its much harder to remove the threat from the system.

Fall of 2013 we saw a re-emergence of the cryptovirus that spread via social media and unlike most other malware, this one truly holds you ransom by encrypting your most precious files and asking the user to pony up approximately $300 to get it back.  In cases like these, antivirus may be able to clean the cryptovirus off but the files remain encrypted.  The awesome folks at Malwarebytes spent some time writing about this new virus here and reiterate that the only real recovery is restoring from backup or snapshot.

The good news is that much (if not all) malware and viruses are preventable.  Simply take the ethernet jack out of the back of your pc and disable all USB ports and any other I/O ports on your computer and the problem is solved.    Not ready to go off grid yet?    Well in that case there are still plenty of ways to stay safe.   The following list is a good start for preventative measures:

1. Keep windows up to date by automatically applying critical and recommended updates.  This includes adding Microsoft Update to Windows Update.
2. If you have Java installed, make sure it gets updated.  While Java provides a lot of functionality it can also run malicious code in privileged mode which means that malware can get installed without you clicking anything.  More information on how to set up and configure updates can be found here
3. Keep the User Account Control (UAC) settings set to a sufficiently high level where you can still function without getting buried alive in warning messages.  Note: some applications like Infor Syteline tend to perform much better when UAC is turned off so check compatibility with any enterprise software release notes on best practices
4. Run internet based applications such as mail and web browsing in a sandbox.   I have tried various flavors of sandboxing from Faronics Deep Freeze, Microsoft Steady State (retired), VM images, and one product called Sandboxie.   While I liked the ability to roll back a VM to a snapshot, I did realize that I would get complacent and not always run internet applications on it so I ended up purchasing a license for Sandboxie.  They offer a free version and a paid version and while both will protect you sufficiently, I would highly recommend buying a license because it unlocks many great features that are not in the free version such as forcing all web browsing to be sandboxed and secure erasing of the sandbox when the browser is closed.   Here is a list of other differences. Again I cannot speak highly enough about this software.  It has matured over the years into a robust architecture capable of shielding the user from pretty much any unknown threat by not allowing applications to reach out to real files.   When I have a customer who habitually gets infected and tells me a sob story about how they were just on amazon looking for a replacement print cartridge I invitably roll my eyes and put on Sandboxie so the next time they were supposedly on Amazon (yeah right!) they will be able to escape the punishment of another infection
5. Keep a close eye on freeware that you download and make sure it has a good online reputation.  Also make sure you are grabbing it from a website that is more or less considered legitimate.  I don’t mind grabbing software from sites like cnet, tucows, filehippo, majorgeeks, etc but if I land on a site I don’t recognize or looks to be filled with loads of spam advertisements I simply keep looking elsewhere or choose another piece of software.  Once downloaded, I right click on the file and look at the properties.  I want to see a digital signature that is not expired.  This doesn’t guarantee it’s legit but it does tell me that an author was willing to go through the process to digitally sign their work through a trusted certificate authority which validates various aspects of them
6. When installing software, always choose custom or advanced option and ensure that you aren’t installing more than what you want.  Some folks might want the free ask toolbar, askjeeves plugin, or ebay quick search bar however I would opt to download anything not part of the core package from the manufacturer directly and not choose to include it.
7. In some cases, either a user forgets to uncheck the nagware or other fluff that comes with legitimate software and a software that specializes in adware/malware/grayware is needed after the fact.  I personally have used MalwareBytes over the last 5 years and find it to be quite useful to run either monthly or quarterly.   The good news is that they offer a free version and it will run a deep scan of your entire system and remove anything that happen to sneak its way past your watchful eye.     Antivirus packages often tout their ability to remove malware but I have found that a straight-up anti malware software is still needed even with the best antivirus package out there.   Some folks (myself included) would also argue that having 2-5 antimalware applications is really the way to go because there is somewhat decent chance that one author will catch a handful of threats that another author missed and therefore the first scan removes 98% and the other scanners hopefuly find and remove the last 2%