By: Eric Blinn | Updated: 2022-08-15 | Comments | Related: > Tools
Problem
I need to administer a large number of databases and database servers with a relatively small team. We need to be able to manage rollouts, patching, maintenance plans, best practice settings, scheduled tasks, monitoring, alerting and performance issues. Is there a software solution to help me automate some of these tasks and improve the efficiency of my DBA group?
Solution
It is important to be able to roll out a new DBMS server and have all the proper settings and maintenance plans put into place. Without that, the server may not perform well or may not be properly backed up.
- A server must remain properly patched or it could be vulnerable to malware or hacking attacks.
- Scheduled tasks sometimes fail and need to be handled in a timely fashion to keep workflows moving.
- When a server is having performance issues that can have a very direct and negative impact on business the DBA needs to have visibility into performance metrics to detect and solve such problems.
A good DBA knows all of this and can take action for each item mentioned above. The problem is, how to complete these tasks in the most effective and efficient way. Many tasks need to be completed by the DBA, but there are only so many hours in the day.
SQL Server is delivered with a few tools that can help in these areas, but they fall short pretty quickly in most environments. The SQL Server Agent can send an email when a job fails, but its ability to alert is limited to that one method. There are no built-in warnings for settings that don't meet best practices, backups that aren't happening, or missing patches.
SQL Server Management Studio (SSMS) offers Activity Monitor to see current database activity but it doesn't offer any activity history, let that be an hour ago, a day ago, or a week ago. If the DBA can't catch a performance issue right away, the data needed to solve the issue may be lost forever.
This is where a tool like dbWatch becomes so beneficial.
Overview
dbWatch will make you a much more efficient DBA. With its automation capabilities, you'll be able to roll out new instances to meet best practices in just a few clicks. You'll be able to find and fix performance issues or failed tasks in a matter of moments. Keeping up with patching will no longer be a challenge.
dbWatch Control Center is the only tool on the market that can truly bring a DBA's entire estate into a very responsive and easy-to-use "single pane of glass". In fact, dbWatch boasts of one customer that has 270,000 databases across 1,300 DBMS instances that is managed by a team of only 2 DBAs -- a feat only possible due to the efficiencies and automation opportunities delivered by dbWatch Control Center.
All my DBMS platforms in one tool
dbWatch Control Center allows the DBA the ability to monitor all their DBMS instances from a single dashboard. It can monitor SQL Server, Oracle, MySQL, Postgres, MariaDB, and other database servers simultaneously. These servers can be on-premises, in the cloud, physical or virtual. dbWatch is also cluster aware.
Check out this dbWatch Control Center dashboard that shows a single dashboard with 19 instances of SQL Server and Oracle databases which are a combination of on-premises, Azure and AWS.
Putting all those servers on one screen can seem very daunting, but dbWatch has a plan for that too! The target servers can be grouped, sorted, and filtered to make it easy to find just the ones you want to see.
All my locations, clouds, and customers in one tool
For many, their DBMS estate encompasses different data centers or cloud providers. If that estate is being monitored by a central DBA group, then the security of the data that is passed around becomes paramount. With dbWatch Control Center, these different server locations can be combined into a single tool. The connections between dbWatch Control Center and those target instances are protected using advanced encryption to ensure security. It is important to note that the data is never shared between the target instances. This makes dbWatch Control Center a fantastic solution for managed service providers (MSPs) that need to monitor many target DBMS instances that can span several clouds, locations, or domains at one time.
This screenshot shows how different domains can be accessed within the dbWatch framework.
Imagine having one dashboard created automatically right out of the box, to alert any SQL Server maintenance problem across the entire estate no matter the location, version, or platform.
Automate tasks to save time
dbWatch Control Center has a feature called automated setting adjustment. With this feature, you can modify settings like max memory to match historical trends and current usage patterns. In larger environments, this can be very useful in reducing the effort needed by the DBA group to keep up with constantly changing workloads.
Another way to save time is with templates. dbWatch Control Center is delivered with several customizable templates that can be applied to a target instance. This feature allows for a very quick rollout of new instances. Just point dbWatch Control Center at a new instance, choose a template and relax as dbWatch Control Center sets all the best practice settings and maintenance plans (backups, statistics updates, index defragmentation, consistency checks) that match your needs. This feature is very useful when a new group of servers is installed for a project as it can be applied to many servers at once.
As a DBA who specializes in Microsoft SQL Server, I really love this feature. Every once in a while, I get asked to look at a different DBMS for one reason or another, but I know so little about Postgres or Oracle. With dbWatch Control Center, I can just choose a default template for that type of instance -- written by an expert in that DBMS -- and know that I'm meeting best practices.
Find new instances and get them enrolled!
Speaking of new instances, other products require an additional product license to help keep your DBMS instance inventory up to date. Not so with dbWatch Control Center as this feature is included at no additional charge. Let it scan the network to look for new instances of which the DBA might not yet be aware. If you find one, apply a template!
Make this product work for you with customization
The dbWatch Control Center application is written using a custom language called Farm Data Language (FDL), called so because it can query your entire farm. FDL allows the application to run code written in the native language for any database it monitors and put it all together on one screen, dashboard, or report. It is also open and available to be customized or copied as a base for a new screen, dashboards, reports, or monitoring points.
This is a default counter as defined by dbWatch Control Center for SQL Server instances. It's incredibly simple to understand and implement.
If you don't like it, you can change it. Maybe you want to exclude a specific database from a check. That's not a problem, go ahead and change it in this settings file and FDL will call your customized performance counter. Keeping with the efficiency theme, this can be done directly from dbWatch Control Center. You don't have to leave dbWatch Control Center and open this file in a separate text editor.
If you need a new monitoring point, go ahead and make it. You could see how active a particular installation of OLTP software is by counting a metric such as invoices created today or how busy a DMS is by counting the number of documents checked out.
To implement, just copy and paste an existing metric, provide the query you need dbWatch Control Center to run, and you're on your way. That metric will now be available to be included on screens within the applications or on reports. If it is something you think other dbWatch Control Center users might want as well, let the development team know. They just might include it in a future release!
If XML isn't your thing there is a designer that will allow you to modify the existing metrics or save them as a brand-new metric.
While this example was for SQL Servers and utilized the SQL language, these counters are not limited to SQL Server or a SQL language. That code block can be SSH, JavaScript, or other languages.
Find and fix your scheduled tasks
One tremendous efficiency feature in dbWatch Control Center is how it handles the SQL Server Agent. Gone are the days when every job needs to email an operator on failure. dbWatch Control Center allows the DBA to see all their failed jobs from all their servers in one place. While this can seem daunting, remember that the sorting, grouping, and filtering options work here too! The DBA only needs to see the job failures that matter to them.
What makes this feature so great is that it allows jobs to be viewed and restarted right from the tool. There is no need to leave the dbWatch Control Center application to go to SSMS to read more about the failure or to restart the job.
If the failed job needs to gather the attention of someone other than the DBA watching the instance, you can even let dbWatch Control Center automatically create a helpdesk ticket. That feature isn't limited to failed scheduled tasks. Any alert in dbWatch Control Center can be escalated via integration to another workflow system.
Run queries right from the monitoring and maintenance platform
Continuing the theme of efficiency, how many times has a job failed for a reason such as a string or binary data would be truncated? The DBA must now figure out who entered something like a name or an address that is too long. With most tools that means that the DBA has to leave their management tool to investigate. Not so with dbWatch Control Center. There is a built-in query tool to quickly do a diagnosis. This feature is a huge time saver.
Similar to the Registered Server / Central Management Server feature in SSMS, dbWatch can also run queries against multiple target servers at once. But with dbWatch, the feature is even more powerful as it can execute a query even if the target servers are totally different platforms! There are several output options so there is sure to be one that works for you.
This screenshot shows the feature in action. The query being run is incredibly simplistic so the real excitement is everywhere else on the screen. Where SSMS will add the server name to the output as the first column of a multi-server query, dbWatch Control Center can add that optionally -- and place it anywhere in the result set -- along with an optional row number or group name. This is seen on the top left of the screenshot. The bottom left shows how target instances can be selected. On the top right is the query being executed and on the bottom right is the output.
Find slow queries and correlate them to performance moments
dbWatch Control Center offers a timeline to view performance metrics over time. If you discover or get a report of a trouble area, highlight that section of time to zoom in on it. You'll get even more details including the sessions and queries that were executed during that window.
If a problem query is identified, use the saved information including the query text, statistics, and plan detail to make code improvements.
dbWatch Control Center will also include its own queries in the results and highlight them as part of these screens so you'll know if metric gathering is causing a problem. Not every DBMS performance tool does that.
Keep up to date on patches
Patching SQL Servers is a very important step in keeping your environment secure. For SQL Server, Microsoft releases new patches on a regular basis. Since every firm has different policies on when these patches should be applied it can be challenging to keep track of each new update and when you are ready to install. This gets exponentially harder as the environment grows to include many versions of SQL Server and other DBMS. Luckily, dbWatch Control Center is prepared for this. It keeps a list of patches along with the date of their release. Simply set the timeline for when you are expected to install the patches (2 weeks after release, 6 weeks after release, etc.) and have dbWatch warn you as the expected install date approaches -- or if the date gets missed.
Dedicated support staff
dbWatch Control Center is a tool written by data professionals and for data professionals. When you use the tool and talk to the staff it shows. They want to make you more efficient.
The management team at dbWatch believes that if a DBA needs assistance with their product that they probably want to talk to someone that knows the problems a DBA faces, someone that speaks the DBA vernacular. If you reach out to dbWatch for support, your request is not going to a call center, but rather it will be answered by a DBA with 10 or more years of experience in the field.
In addition, their support team is geographically dispersed meaning support can be reached at most hours of the day for most parts of the world rather than being limited to the working hours of one or two time zones.
Their development staff is equally as impressive. That group is fanatic about their product. They boast that they can turn around a bugfix or feature request in a matter of a few weeks -- a timeline unheard of in the industry.
Free Trial and Pricing
You truly have to see dbWatch Control Center in action to understand how responsive the intuitive the screens are or how well it works right out of the box. To help with this, dbWatch offers a full version trial for six months to monitor five database instances for free. A credit card is NOT required to download, install, and try the software.
Next Steps
What this article describes is just scratching the surface of what dbWatch Control Center can do. Give it a try and see what dbWatch can do for you.
MSSQLTips.com Product Spotlight sponsored by dbWatch makers of dbWatch Control Center.
About the author
Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2022-08-15
