Category Archives: Cool Features

Have you configured Database Mail to utilize multiple SMTP Accounts ?

Posted on by

Lets start discussing about this subject by closely looking at the term Redundancy.  What is redundancy all about ?

Wikipedia defines redundancy as “In engineering, redundancy is the duplication of critical components or functions of a system with the intention of increasing reliability of the system, usually in the case of a backup or fail-safe.”

Technically this is redundancy too  !

Redundency

That’s a very nice and to the point definition isn’t? Well, very true.

What’s this hype all about redundancy in database mail ? Lets closely look database mail for more details.

When you create a new database mail profile, you have an option to add multiple SMTP accounts. If an account fails when sending an email, the profile uses the next account in the priority list.

Yes, this is some level of redundancy for database mail.

SMTP Red

You can set the priority of accounts by moving them up and down.

Adding a new account is a simple process and can be done easily with the GUI.

SMTP Red2

The ability to configure multiple SMTP accounts comes really handy when your enterprise infrastructure is having multiple SMTP servers as part of redundancy plan.

Conclusion

Incase you do have an opportunity to add multiple SMTP accounts for database mail, then dont think twice. Its always good to have a backup account which will ensure that you will never miss that important alert/email.

Thanks for reading and keep watching this space for more.

Quick alert on servers which are not compliant for policies set using PBM

Posted on by

Last month I wrote about leveraging policy based management for regular DBA compliance checks. You can read that post here.

Policy based management(PBM) is indeed one of my favorite features. I normally like to play around exploring more and more about it, and eventually end up learning something new(Yay!).

Today’s post is based on that surprise learning. Small but very useful feature.

PBM currently lacks flexible reporting capabilities like sending an automated email to the DBA every week stating compliance status or even a reporting dash-board for the DBA to constantly monitor the details. I hope Microsoft is working on these enhancements, and we can expect some good news when next major bits of SQLServer is out.

Note – Enterprise policy management framework has great enterprise wide policy compliance reporting capabilities, and I’m still exploring this great tool. This is a codeplex project and you can explore the same by following http://epmframework.codeplex.com/

So here is my little story – When I walk in to office on a Monday morning I wanted to know which servers are having compliance issues. To start of I ‘m interested to check my super critical business servers. I used to connect to those servers and do a View History(Under Management->Right click Policy Management)  to get the details of compliance issues.

Note – I evaluate my policies on a schedule.

Recently I observed a quick alert when connected to a  server from SSMS which was telling me that there was a policy compliance issue, and I need to act on it.

The alert looks like – PBM Visual Alert There was indeed a policy violation which was picked by a schedule which ran on the weekend. Monday morning when I connected to that box,SSMS reported me that there is a policy violation via the above described small(but very helpful) graphical alert.

Once we fix the compliance issues, the alert is removed. PBM Visual Alert2 Conclusion

These simple but useful features is indeed helpful for DBA’s to have better control over compliance issues and I’m really looking forward for more and more features and enhancements for PBM from Microsoft SQLServer team.

Thanks for reading and keep watching this space for more.

SQLAgent Job related reports and out of box real time job monitoring tool !

Posted on by

Lets start New Year with a very simple yet useful topic.

I am a big fan of out of box reporting capabilities of SSMS. Some of the reports are really useful.

One of the reports which I really like is the Top Jobs report. This one can be pulled by traversing through –

Job Monitoring 1

The report comes in really neat with a graph and 3 major information related to SQL agent jobs.

Job Monitoring 2

The ones which I’m really interested all the time are 20 Most Frequently Failing Jobs and the 20 Slowest jobs.

This information is very important for me as I can trend the job information over a period of time(Even though its for a weeks time) for the most business critical servers.

Job Monitoring 3

The reports provide lot of useful information such as Avg Run Duration,Total Executions etc which will help you to observe how jobs are behaving under normal circumstances and can even use this as a baseline. Pretty neat stuff out there.

We also have the Job steps execution history report which will provide us with information regarding various steps within the job. Again a useful one if you are having lot of steps which are configured for the same job.

Now lets look at a scenario  – Your server is hitting 100% CPU and you believe that a heavy SQL job running at that point of time might be a reason for this spike.

How do you check which SQL job is running at that very point of time? Well, there are multiple ways to accomplish this. A TSQL code which joins couple of job related MSDB tables can quickly pull this information for you.

What if there is a light weight tool sitting right inside SSMS which can pull that information for you, lets look at that right away

Job Activity Monitor option will help you to monitor SQL jobs real-time.

Job Monitoring 4

If you need to know about jobs which are executing right now, then you will need to use the filter option and apply the required status.

Job Monitoring 5

Job Monitoring 6

Conclusion

There are multiple ways to monitor your jobs and its statistics, however the above mentioned methods are simple and efficient to get a quick view of SQLAgent jobs and related information.

Installed SQL Server features discovery report

Posted on by

Have you ever faced a situation where in you had to double check what all features you deployed for your SQL Server environment ? Oh! did I selected Full-Text search feature during installation?

There are many number of ways you can double check the features which are available

1. Check services running under SQL Server Configuration Manager

2. Check the install folders for logs(Example – C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log) which will give details about features.

Think about getting a report of this! Wow,that’s a cool thing isn’t? This is what exactly ‘Installed SQL Server features discovery report’ wizard does for you.

You need to run SQL Server setup to launch this wizard and this is rightly available under the tool section of the page which we have seen many times –

We just need to run this option and it will provide us with a very structured report which talks about all the features which were installed for the server.

I was amazed to know couple of  features(Example – LocalDB) were present in my machine.

The contents of the report is actually getting pulled from the summary.txt file which is created after the install is completed (C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\summary.txt)

So,if you just need the data then its easy to look at the file rather than running the setup.exe.However the report looks neat and structured.

Thanks for reading.

Template Explorer

Posted on by

This is one of the awesome feature I learned today.I was aware of the availability of Template Explorer,but never knew that there is a short cut for it.

Ctrl+Alt+T will bring this up for you.The A-B button in the tool bar will allow you to enter the parameters too.

This is indeed a cool feature.