Database monitoring in complex networks

Monitoring databases in large, distributed or hybrid environments

Monitoring database or SQL servers can in itself be a complex task – as we discuss in SQL Monitoring – 5 steps to full control. But what if you have a really complex environment, with multiple locations, restricted networks behind firewalls, high number of servers or a hybrid solution with some on-premise and some in a cloud? What are the issues, and how can you resolve them?

If you have your database servers distributed in multiple locations or networks they will often be behind separate firewalls. In addition you often have limited bandwidth or slower response time (round-trip time). Opening ports in the firewall for each and every instance you want to monitor is cumbersome and should be avoided from a security perspective.
If you have a large number of servers to monitor you run into scalability issues. There is an upper limit on the number of simultaneous connections you can have from a single monitoring server, it will have a finite number of cores and memory and too many monitoring connections can create network contention and a storage bottleneck if you try to collect and store monitoring data from too many servers at the same time
Lack of redundancy. The bigger and more complex the environment, probably the bigger the need for redundancy in monitoring servers as well as the database servers themselves

In many enterprise networks you are likely to encounter several, sometimes all of the above challenges. We find these kind of challenges common, especially in larger managed service providers (MSP) and hospital/healthcare groups. So we started to work on resolving these issues about 8 years ago, delivered the first solutions 6 years ago and have been refining and extending these ever since.

But lets be honest – we have not been good at telling our customers about this, it has been a well kept secret for all but a small group of our larger customers.

In the dbWatch database monitoring solution we have the possibility to deploy any number of dbWatch servers. Each of these servers have the following properties:

On windows, we can monitor 250 instances, on a linux platform 500 instances
The server will handle all status checks
The server triggers scheduled maintenance and statistics collection tasks
The server will cache the most recent status and stats for all the local servers

The dbWatch monitor (this is the dbWatch UI application) can connect to one or more dbWatch servers simultaneously.

In most smaller installations there will be only one dbWatch server. This server connects to all the instances that are being monitored. The dbWatch keeps an open connection to all the servers.

The dbWatch monitor connects to the dbWatch server.

When we get to more complex environments, where you have multiple locations, or subnets with database servers hidden behind firewalls, we can add more dbWatch servers. We put these additional dbWatch servers behind the firewall or in location where the instances to be monitored are located.

By using multiple dbWatch servers we get a number of benefits:

The monitor has a single connection through the firewall to the dbWatch server. This keeps number of open ports in the firewall at a minimum.
The local dbWatch server maintains the connections to all the local instances, and triggers the monitoring and maintenance tasks locally.
Network traffic between the dbWatch monitor and the local dbWatch server will only take place when the status of a local server changes, or when you run dashboards or reports on the local servers from the dbWatch monitor. This will keep network traffic to a minimum.
We can scale up to monitor almost any number of instances by adding more dbWatch servers
In the dbWatch monitor you always get the complete overview or can drill down into any instance, regardless of which dbWatch server it is connected to.
If the connection to any dbWatch server is lost, the other servers and connected instances are still available in the monitor. This adds to overall redundancy and system robustness.
when autoscanning for new instances, we can do this inside the local network or location from the local dbWatch server. Scanning for instances from outside the firewall is not desirable, and often impossible.

Over the last 10 years we have developed a range of technologies and methods to safely and efficiently monitor sql instances in distributed locations and in diverse networks behind firewalls. This is a common scenario in healthcare, managed service providers and large enterprise in general. Today we consider this a proven and common mode of operation and is frequently used by many customers.

Get started with dbWatch - 30 day free trial

Vision for the Future: dbWatch Enterprise Manager 12

dbWatch Enterprise Manager 12

At dbWatch we believe that DBAs should be able to manage their databases using a single solution. We understand your pains because we experience them, we work through our own bugs before going into beta-testing. Because we use our own software to monitor databases, we have learned firsthand the improvements needed.

With Enterprise Manager 12, we have developed a package that gives DBAs a complete overview and platform to manage their workflow using one source. Seventeen years of building and programming database software has laid the foundation for this version.

Enterprise Manager 12 increases functionality and allows trouble-free monitoring, maintenance, performance and planning. Four consoles offer you all the tools needed to smoothly manage databases. Within most consoles, the main screen gives an overview and tabs along the top screen allow you to dig deeper into details and help you stop possible problems more efficiently. All four consoles will be available in the web server view.

Monitoring Console

Caption: Live Monitoring Console with the ‘stop light’.

For the last three years, the “stoplight” has adorned the monitoring screen, measuring the pulse of all the data bases. A stoplight indicates: green – everything good; yellow – possible problems; red – alert.

By keeping on top of the yellow notifications, DBAs can move from putting out fires to working directly on prevention and fine tuning.

Maintenance Console

Data bases are similar to cars: They both run better when they are optimised. The Maintenance Group Console keeps an eye on the car’s oil levels, tire pressure and brake fluid. This console advises you when maintenance is needed.

“We call it bullet-proof maintenance,” says Marek Jablonski, CTO. It monitors everything: re-indexing, statistics, changing routines, and adjustments. The Issues are highlighted in yellow and red, making them easy to identify.

The following five tabs aid in the Maintenance Group Console: DBCC, Analyse statistics, Reorganise indexes, Rebuild indexes, and Cleanup msdb.

Maintenance Tasks

This new solution came out of the demand for more information on maintenance routines, it allows DBAs to deploy routines and monitor how they are working. “Imagine if you are installing instances yourself – you have to manually deploy them, upgrade and collect information.

With dbWatch, you can deploy hundreds of instances and then monitor them,” says Marek. “This allows you to see if these are reporting without errors, note if there are changes in the data, and give DBAs full control beyond the implementation.”

Performance Console

Going back to the car metaphor: while the maintenance console monitors the fluid levels of a car, performance console keeps an eye on how the chauffer drives.

Here, all instances are monitored and new instances can be found. The system’s level of use is recorded and instances can be found. Alarms and warnings show the system performance and alert of potential problems.

The following three tabs support the performance console:
– File IO Latency, Waits, and DML test

Capacity Console

As its name suggests, the Capacity Console gives DBAs the information they need to plan ahead. Four tabs: Disk, CPU, Memory, and Data Cache; give DBAs optimal capacity measures.

Marek explains, “Many DBAs don’t have a system to track exactly what they have done or how well their systems have been working.” By knowing what has worked well in the past, DBAs can better plan the future.

The Capacity Console also gives DBAs important information they need to plan with a client, for example, knowing how much disk space is needed. This console covers a wide range of activities – tracking resources, growth rate, and the size of the environment. Additionally, it shows how the maintenance routes are being followed and finds patterns.

Knowing what has happened in the past is vital for planning the future. Having this information on hand allows you to have strategic meetings with clients, allowing you to show them their past history and possibilities for the future.

The New Evolution

All of these consoles with their multiple views help streamline DBAs’ workflow. They aid at many different levels, from checking that everything is running smoothly, to maintenance tasks, performance analysis or checking capacity. Those currently using updated software already have access to some of these new features.

dbWatch Enterprise Manager is released early 2019. Experience the difference of handling all your workflow from one program – find out first when we release: follow us on TwitterLinkedIn or on our blog.
Start your free trial today! download here