At Redgate, we regularly chat to IT leaders and experts about the challenges of managing and monitoring their server estates. This series of ‘DBAs at work’ insights is based on some webinars hosted by Grant Fritchey, Microsoft Data Platform MVP, which talked about intensifying data demands and how effective estate monitoring enables DBAs to manage continually evolving environments. This episode features Kevin Davis, Manager of Database Administration at Tower Loan, a consumer finance company with 250 branches across the US.
Why were you looking for a monitoring tool?
Our 250 branches aren’t always communication-enabled and we lose communication regularly, maybe for an hour, maybe a few days. Those branches can’t go without a database during this time, so I need to monitor all databases across our estate daily.
We have a lot of data movement, data traffic and data sources, and as a one-man DBA team no matter how many program scripts you put out there, if you’re trying to look at what returns from 250 locations, plus different servers, and clusters, that’s too much data to just try to browse through and keep up with every day. I needed a visual solution, something that was going to do the task of four or five people, and SQL Monitor does that for me.
Why did you choose SQL monitor?
It’s a unified product that provides everything you need between doing query analysis, finding weak points, bad indexes and more. You get all the features that you want and it also gives an easy to see, and absorb, overview of your entire estate. Other products try to give you that same overview but overcomplicate things, and it’s time-consuming to just see where the problem is.
The color-coded overview that SQL monitor uses makes it so easy to filter down. I don’t even look at what’s green, I’m just looking at what’s giving me warnings, or errors. I break down my different groups into states and production clusters for a simple overview and I literally keep it up on a TV screen in front of me all day long so I can see those different placards come and go.
SQL Monitor is a great product. I looked around at lots of other ones and for me, not making things overly complicated and telling me what I need to know, SQL monitor does the best job of all of them.
Was it hard to get SQL Monitor set up?
Not so much the setup but configuring the system to create meaningful alerts. That’s where you spend your time, and that’s where you as a DBA can get in there to tweak, tune, and turn things the way you want to ensure you’re getting the information you need.
When you set up SQL Monitor, did it have the PowerShell feature that was added a while ago?
Some of the PowerShell pieces were there but they weren’t as advanced as they are now. I’m getting to play with them now though, because I’m downgrading a database originally set up on a little temporary server that wasn’t really intended to last for three years, and I can absolutely say that they have evolved and improved dramatically over the past three years.
Have you had to build any custom metrics for monitoring your servers?
I’ve built different custom metrics that helped me not only with database stuff, but also some of our application processes. I love custom alerts because if you can query anything that you can turn into an integer result, you can make a custom alert with the different zones of low, medium, or high. One of my favorites tells me if one of my Express servers is getting close to its ten-gigabyte limit, which is essential for our estate planning. Another informs me if we’ve got a hole in our application, where we leave a foreign key out when we do an insert with a record in our workflow.
What tips would you give to somebody getting started with SQL Monitor?
Read through the manuals to understand all that the product can do, so you can take advantage of what you need out of it. For example, I don’t do a lot of SQL analysis inside of SQL monitor, but every now and then I get alerted that there’s something running a little slower, or it’s causing a couple of waits, or it’s got a deadlock going on. At this point I get the opportunity to go and see what the developers have added that may need another index, or maybe the index just needs to include another column, usually simple stuff. But SQL monitor tells me about it without me having to wait until I’ve added a database that was completely locked up. Or even worse, 200 branches that were locked up, because we put out a piece of code that turned into something bad.
We’re lucky because with SQL Monitor, we can cut off all those problems, but they are realistic issues for us that we must be concerned about every day. We have branches on the coast, and when hurricanes come through, we must act quickly. We may have to move branches back to VMs for weeks or months, the buildings may get destroyed and we need to find new real estate, and I must be able to move quickly with those changes to be able to monitor a different environment. I can suspend monitoring over here, and I can set up another machine in seconds, and be monitoring it and ready to go again. It helps keep an eye on what may be a very fluid environment every day.
Where do you find that you’re spending more of your efforts?
I still spend time doing the daily business operations type questions and problems that arise every day, but thanks to SQL Monitor I spend a very small amount of time doing these DBA type of duties. I can come in in the mornings and solve any problems within the first 20 minutes. I’ve usually got the entre estate backed up and running, and then rest of the day I just kind of glance up and see if everything’s still doing well.
What benefits have you seen from monitoring the estate?
It helps me keep ahead of the issues that come along in the day that you would otherwise not catch until it’s too late. It improves the entire business workflow, from keeping SQL Servers managed and running a lot smoother, to being more proactive, rather than reactive, and it makes for a lot less stress. I just don’t have as many of those ‘Oh, no, not again days’ because I can see the issue come in and take steps to avoid it becoming a problem.
Redgate SQL Monitor makes it all easy for me. Because of the size and configuration of my estate, and the different databases that I’m running, without SQL Monitor I would need a four- or five-person team to keep up with everything daily.
How does monitoring help with your distributed setup?
It allows me to visualize a large-scale, geographically dispersed, estate and manage that estate with minimal down-time with the ability be alerted to impending issues. That’s either through SQL Monitors ‘out-of-the-box’ alerts or my own custom alerts as well as all of the tuning and performance metrics that a DBA needs to keep an estate efficiently managed. With the number of servers and clusters that I manage a daily basis, it would be nearly impossible to do that without monitoring software that worked at a large scale.
Have you looked at cloud technology?
Everything we do is sensitive data. We’ve got social security numbers, places of employment, phone numbers, and as a result our company is adamant that we’re not going to do anything in the cloud. Until that sentiment and mindset with the executives changes, we’ll probably continue to stay on-premises.
How do you cope with changing business needs?
It’s always an ever-evolving world out there. The application that we’re running was modelled after the previous application that the company used, which was designed in a COBOL environment, and as times are changing, we’re evolving with it.
We’ve almost completed setting up dual line communications to all 250 branches and once we’ve completed that we’ll start bringing those databases centrally back into the home office. We’ll keep 250 of them because we must migrate the application to go along with the database changes, before we can move to a more centralized application. But it’s an ever-moving target, and that’s what my good days are, just keeping things advancing to be better than they were yesterday.
Look out for other episodes in this series, featuring Grant Fritchey talking to:
- Dennis Heitmann, Database Administrator at Atruvia
- Deborah Thompson, Database Administrator at WestJet
If you’re new to SQL Monitor and would like to see how it can help you monitor database performance issues, you can download a fully-functional 14-day free trial, or try our live online demo environment.
Was this article helpful?