If you have found yourself working with SQL Server or SQL Server Express for quite a while, you might have already noticed just how important the monitoring of such database instances can be. While there might be a dozen of database monitoring and management tools available for you to use (you may be using a SQL Server management studio or similar tools already), however, if you have tried one or a few of them, you might have noticed that only a couple of them are up to the task since monitoring also needs to be done in the right way.
Thankfully, there are a couple of monitoring tools that are up for the task – one of those tools is dbWatch. For example, launch dbWatch, and you will instantly be able to observe what’s happening in your SQL Server database instances:
Here you will be able to see all of the issues relevant to all of your database instances. Since the jobs of dbWatch can have multiple status codes (OK, an Alarm, and a Warning), these status codes are displayed on the main page of dbWatch as well – above the status codes, you will see all of the issues relevant to your database instances in dbWatch. You will also see the time the status was acquired and all of the details relevant to the status itself meaning, that the main page of dbWatch should provide you with good enough insight alone.
For monitoring your SQL server database instances hough, you might quickly find that it’s not enough: to properly monitor your database instances, you must first know what you need to monitor in the first place.
For that, start by importing your SQL Server (MSSQL) instances into dbWatch. Then, glance towards the left side of dbWatch and you will find all of the relevant database jobs – you will see that those database jobs are split across multiple categories, each category depicting a part of monitoring.
For example, if you expand the Availability category, you will see a couple of database jobs that are needed to keep your SQL server highly available, the Capacity category depicts the storage details of your hard disk drives and tells you how best to optimize SQL Server in that regard, the Cluster and Replication section of the database jobs checks some state information about all of the databases that are mirrored, keeping an eye out on the Maintenance section would be helpful to improve the maintenance speed and automate some SQL Server operations in that regard, and finally, the Performance section lists all of the database jobs that can help improve database performance sometimes by tens, but sometimes by even hundreds of times. Here’s how everything looks like:
Expand any of these categories, and you will see all of the database jobs relevant to that category – we will start from Availability:
As you can see, the Availability section of the SQL Server database jobs is pretty extensive – on the top you can see an illustration of how many database jobs have the status of OK, how many have the status of a WARNING, and how many do not have a status at all, letting you think what you should focus on first, and what might come afterwards.
As far as the Availability section is concerned, the most useful database jobs for monitoring would be the database backup and log backup database jobs (these jobs check the backup statistics from certain tables for certain databases or checks the transaction log backups), keeping an eye out on your deadlocks might also be very beneficial (dbWatch will trigger an alarm once a deadlock is found.) Of course, almost all of these database jobs can be beneficial in one way or another, but to find what jobs come into the right fit for you, you would, of course, need to experiment.
Once you expand the Capacity section however, you will see jobs like so: in this section, useful jobs include checking how capable your database disks are, how much space your databases use on the disk and database growth rate letting you decide when it might be time to replace your hard drives for those having more capacity.
Expand the Cluster and Replication section and you will see jobs that will monitor the mirroring state of your databases – the Database mirroring job, for example, will check what databases are being mirrored on your server, which might be really useful if you find yourself setting up replication:
The Internal part of the monitoring part of SQL server will return a warning or an alarm if it detects that there are several dbWatch engines running on the same instance.
The Maintenance and Performance sections though will perhaps be the most interesting to any SQL Server DBA that is monitoring the activity of their SQL Server instances. Expand the Maintenance section:
You will see that dbWatch is able to make your indexes performant again by rebuilding or reorganizing them, it can also provide you with some index statistics or check if your SQL Server data is fragmented – after knowing those things, you will be able to come up with better maintenance and performance plans for your SQL server instances. Oh, and did you see that? dbWatch is even able to back up all of your SQL Server databases for you! What’s not to like here? With dbWatch you are always in good hands!
However, there’s one more thing all DBAs and database-savvy developers are always keeping an eye out on, and that’s Performance. With dbWatch’s performance monitor, you will never have to worry about the performance of your SQL Server instances too: it will tell you whether your database instances are set up in the right way performance-wise, it will monitor your SQL Server database instances for abnormally high activity numbers and alert you once these numbers have been reached:
And it will also provide you with many other useful tidbits of information ranging from checking your SQL Server instances for internal fragmentation to showing you the overall transaction load over time.
As you can understand, with dbWatch you can never go wrong, and while monitoring the activity of SQL Server or SQL Server Express instances might seem different for every database administrator and database-savvy tech guy, it’s really not that hard – especially if you use tools developed by experts! Give dbWatch a try today or contact support if you are not sure about anything: the dbWatch wiki should also give you some pretty good insight into how the dbWatch Control Center operates, so be sure to keep an eye out on that as well.
About the Author:
Lukas Vileikis is an ethical hacker and a frequent conference speaker.
Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania.
He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.