Configure Database Mail, Operator and Notifications in SQL Server

hello friends so today what I want to share is an interesting feature of sequel server first let's discuss how we log I mean get the informations whenever there is an error happening in our sequel server systems sequel server runs various jobs like sss packages or replication jobs and once any error happens it's really important that we come to know in time so that we can actually troubleshoot the issue is as soon as possible to do so the to do so the most important and industry-wide a way of knowing it is via mail that is a mail pops up on our smartphones or our laptops where we get the message that XYZ job job is down and this is the issue because of which it is down so the mail once we have the mail configured for the job it's it really it's it's helpful as a DBA for me to understand that this job is gone down and I need to look into it ASAP so what I want to share today is configuring the database mail option which provides provided by the sequel server so once this is configured first step will be configured this secondly what I will share is how we can use the setup database mail option in one of the sequel jobs to get notified whenever a job complete successfully or an error happens so we will do that so first let me configure my database mail so this is the default window that knobs and start configuring my table so the first step is to configure a profile so and the next is to configure an account and associate to the profile we can have multiple accounts so let me just create a profile first of admin admin profile and we will add SMTP accounts to this profile so let's add one I'll create a new account the counts name let me keep admin account and email address I for this demo session let me configure it to my gmail account so you will have your organization's SMTP which can be used so I am just configuring to my personal mail ID this is your SMTP server name so you need to know the SMTP server name and the port by which you will be configuring so since I'm using the Google fridge SMTP server so I'm providing the same TP of Gmail and the port number is 587 the next is you can use the SSL for encrypting your messages that you are sending so I will be using that next is to create an authentication so we are done with configuring an account so you have configured an account and added it to a profile admin profile so almost yes this window I will like to show what are the different options like you can prohibit certain extension file extensions to be associated I mean to be attached to the mail by the while being sent you can do that you can restrict the sizes you can restrict the delays in the second attempts and retry number of retries to be taken place you want the retry to be taken place ten times you say ten so that's how you can configure these options and we are done so yes we have configured an account so and and so that is done now we will try and test we will say the test email will use admin profile and send it to my hotmail MSN account so we have sent a test email to check let's just take our hotmail yes as you can see we received an email ID sorry an email this is a this is the main that we received from and if you go in my gmail account and check for the sent mail you will find that there is a mail sent yes you can see that there is a male's mail sent in the details as you can see that the male's was from and the two so that's how we have configured this successfully next what we do is how we can what also for checking if the mail details you can check it in the MS DB of your server you will find entries for every male that is being sent and for the status of the mail you can check in this mail items they will the status for all the leopard mail will be one so that is how the way you can check and fall all the field send an unsent items you will find entries in these tables so let me run through all these here we are finding there is there is no entry in failed and unsent items we have one item that has being sent this for sent items table will have entry and there will be the complete message that we have sent will be logged in here we can see the complete message that that is there in this table and we have the other details so you'll have complete details in this next I want what I want to show you is how I can use this for any of my jobs I mean notification so first for that to be done what I do is I open my sequel server agent properties and in the alert system I have to enable the profile so you'll find the database email and the profile so I have few profiles I'll be using admin profile and you say ok you have once this is done please restart your sequel server agent once this is restarted we'll what we will do is we will set up an operator which will use this database meal facility so this is step one the next step is to create an operator what I do is I create a new operator with the name admin operator and this operator sends mail from my database mail account configured account to for my hotmail account I mean this will be this is the destination right this is done so we have created an operator next I have a few jobs I have this job parent 2 which is nothing but it is it is running an SSI package so I can use this job for the dynamo session what I want to do this is I'll just show you this is actually simply running an SSI package so as you can see this is running an SSI package from server this is the package that it is running so I wanted to show you that and so for this package what I want to do is I want to not eat notify so I'll use the operator that we just configured and get notified admin operator and we will say we want to get notified when the job succeeds or rather we will save in the job completes so we will do this what we have again let me repeat we have created an operator admin operator and next in this equal job parent two we are trying to configure an of this the operator and get notified once this job completes so we have done this and we say ok the next is I'll run this job okay the job is succeeded so let's just go and check if we get and getting notified or not yep we are so we got the message from sequel server job system saying parent to job completed on the server this is the server name we can each of these things can be customized for for Navi are simply key which I just showed you so the job duration it is giving you all the details when the job completed what job it invokes so it's it's really wonderful if we can see the sent mail you will have the database mail has sent this right so again we have this so this message is again sent from my gmail account to my hotmail account and this is the message that was sent so that was what I wanted to really share it it will be really helpful for getting notified and time and you can if I see all of these tables about the mains that went across you will find entries in each of these that each of these tables unsent and field items will not have any entries in them right they are not there so the next few tables which are important for you are these you will have entries in each of them so that was about this session friends I hope this session is really helpful to you and getting to know how you can get notified for any of the problems occurring so one last thing what I want to say is you can create sequels alerts to happen so for each of the alerts again you can create a notification say my this replication agent fails I want to create notification I get and go to properties and in the response section I can notify operators I can use this admin operator again and get notified so and whenever this fails so that is again away for each of the alerts so for alerts for jobs for each of these things we can get notified whenever there is a problem or an issue so thanks a lot friends thank you

Channel: Keshav Singh

Views: 59,285

Rating: 4.9212599 out of 5

Keywords: Microsoft SQL Server, Database, database mail, send mail, SQL Server

Id: TxvjqQ9RdtU

Length: 13min 18sec (798 seconds)

Published: Sat Dec 24 2011