Grant Fritchey

7 December 2020

Tackling the Challenges of Hybrid, Cloud, and On-Premises SQL Server Estates

More and more organizations are managing some part of their data in the cloud. Gartner and similar organizations are showing massive growth in cloud adoption in 2020. Redgate’s own State of Database Monitoring survey shows cloud adoption rising rapidly. Everyone, it seems, is moving to the cloud.

Yet, there is data out there that’s not moving to the cloud. Some organizations have either not yet started the migration, or, they have data that is better served through traditional means with big hardware managing their data locally. In fact, the majority of organizations are likely to end up like this, with some data being managed through a cloud provider and some being managed locally. This style of cloud adoption is known as the hybrid model.

The hybrid model leads to some unique issues, especially around monitoring your servers and services. If your cloud adoption is simply to implement virtual machines in a different location than you traditionally do, your challenges are probably less. There’s a lot of knowledge available on monitoring VMs. However, as you move to the cloud, you also start to adopt cloud-specific services such as Azure SQL Database and AWS RDS. These services have different monitoring requirements than traditional hardware or virtual machines. In short, the hybrid model of data management leads to hybrid monitoring.

So, while you may still be an organization with a focus on Microsoft SQL Server as your database managing system, the chances are high that you’re going to need more. You’re going to need to monitor SQL Server on the cloud-based servers. You’re also going to need to monitor Azure SQL Database and monitor AWS RDS. After all, all these various locations and services are just SQL Server under the covers.

However, most of all, you’re going to want a way to monitor your SQL Servers so that you can see everything, from local big iron and VMs to cloud instances to services like AWS RDS. That single pane of glass into your hybrid environments and SQL Server monitoring is important, so you’re either going to have to go through a not insignificant set of tasks in order to build it or pick it up from a third party monitoring tool.

Why the Cloud?

Even though the year is 2020 and cloud technologies have been around for a good fifteen years at this point, there’s still a lot of questions about why any organization would move to the cloud. There’s a rough agreement that, of course, startups should be using cloud technologies. The cloud provides a way to move rapidly without incurring the overhead of building a data center when it’s unclear what you need. This just makes sense for startups. However, it makes as much sense for other organizations as well.

Older organizations may have significant investment in their existing data centers. Can they also benefit from the cloud? The short answer is yes. Organizations are generally not single-minded behemoths. Different aspects of a given organization may be moving at a different pace, with different needs and requirements. These differences lead directly to hybrid data management. This is why you’ll see large, old-school, firms that are also using cutting edge cloud services for data management. In fact, this is why you see so many different articles touting the hybrid model as the trend for the year. It’s only going to continue.

Many organizations are seeing additional benefits in using the cloud, in whole or in part, as a fundamental aspect of their data management. By using cloud technologies for management, the organization can focus on it’s core needs. Building a global data center requires a lot of technical expertise and massive infrastructure. Whereas, implementing databases across multiple data centers through AWS requires less technical expertise, and no infrastructure expenditures at all.

Moreover, organizations are seeing the need to expand, and contract their systems on demand. A retail management system will likely need a lot more resources around Black Friday than they will on some random Friday in August. The cloud, in general, offers that ability to expand and contract, as a fundamental aspect of the design.

You’re also seeing that lots of organizations have a hard time securing their systems. Getting an appropriate upgrade and patching service to ensure the most fundamental protections on the system is a lot of work and does require technical knowledge beyond what many organizations possess. Add to that fundamental security and encryption and many organizations just can’t meet the needs. Yet, all the major cloud services are built and designed with security in mind. Further, they build in patching, upgrades, and some maintenance, such as database backups, as a fundamental part of the service.

Cloud service offerings continue to expand. It’s not just a question of where your data lives. It’s also a question of how your data is consumed. As we see cloud-based analytics, data mining and even machine intelligence expand, we’re seeing the utility of storing the data closer to the analysis site growing. Having the data nearby where it’s getting analyzed increases the speed of the analysis and just makes it easier. This means many more people are moving data into the cloud.

All of these things taken together are why the majority of organizations are working with cloud services.

Why On Premises?

If the cloud offers so many positive aspects, why then do organizations retain, or even expand, their on-premises data management? There are two fundamental answers to this question. The first is simple: already sunk investment. If you’re storing your data successfully in a local server, or even in an entire data center, already, moving to the cloud doesn’t have much attraction. The second is also simple, your organization needs more than the cloud offers. You’ve got special functional needs that keep the business online or beat the competition.

Very small organizations may only need a single database and instance of SQL Server to get the job done. If they’ve purchased that and are happily maintaining it, moving to the cloud doesn’t make sense from a business standpoint. The same thing applies to an organization that has built out a large-scale data center and may have already built out off-site secondary systems and mechanisms to support global scale. While either of these scenarios might benefit from a move to the cloud, it simply doesn’t make financial sense.

I’ve worked with organizations that are doing very unique types of data processing. Either they have a transaction volume that would swamp a cloud-based system, or they have a need for processing power that would be difficult to easily replicate in the cloud. What’s most interesting about these types of systems is the competitive edge that the company derives from it. So, unlike a lot of organizations, they’re not simply investing in servers and disks. These organizations invest in their people to build superior systems so that they stay ahead of the competition.

This is very different from, for example, an embroidery company whose real differentiator is the embroidery they do, not where their customer data is stored. My imaginary embroidery company wouldn’t derive benefits from hardware and people investments. Take a Wall Street analytics firm, and the same questions would reach wildly different answers.

Between specific technical requirements and those sunk investments, you’re going to be seeing on-premises servers continue to be a factor for a very long time to come.

Hybrid Monitoring

The majority of organizations, estimations vary, but somewhere around 60% seems to be agreed on, have moved some part of their data to the cloud. However, simply saying “the cloud” does not really say anything. We have to start to talk about what that means. It could mean virtual machines in Azure, AWS, Google, or any other cloud provider. Since we’ve been managing servers remotely for more than 30 years, and virtual machines remotely, for almost 20, this is a well-established problem space. Things get really interesting when we talk about the Platform as a Services (PaaS) offerings.

Implementing PaaS for your databases requires a number of changes in how you get things done, how you consume data, and overall how your data is managed and monitored. While moving your data into services like Amazon RDS or Azure SQL Database has a ton of benefits, there are also costs. The biggest of these costs, which shows up in a lot of surveys including ours, is the need for additional training and knowledge in your staff. One of the key places where more knowledge is needed is in dealing with monitoring your databases. While some fundamental aspects of monitoring go away, like whether or not a given machine is online, a lot more remain, but are changed, like measuring CPU in a PaaS environment.

Add to this the fact, as we’ve already stated, you’re going to have a lot of on-premises data to worry about. You’re in a hybrid environment now, and you’re going to need a solution that can deal with it.

Azure SQL Database and SQL Monitor

According to the 2020 Redgate State of Database Monitoring survey, Azure usage grew 15% at the time of the survey. All indications suggest that adoption has accelerated in the short time since the survey report was released. We also know that more than half of the respondents are using Azure in whole or in part:

With all this mind, it just makes sense to have Azure SQL Database monitoring built into SQL Monitor. Happily we do.

You can connect up to individual Azure SQL Database instances with SQL Monitor. There you’ll get the great database internals that you get when you monitor a standard SQL Server instance. However, we also capture a number of metrics unique to Azure. These include database wait metrics, DTUs, and others. Further, if you’re managing your Azure SQL Database through Pools, we also monitor for those. You can easily spot the Azure databases being monitored in the SQL Monitor overview screen:

Each of these is a different instance of Azure SQL Database.

While all the added metrics are important, vital even, for understanding the behavior of your Azure SQL Database instances, all the traditional information has to be included as well. Through SQL Monitor we can show you top waits, top queries, the associated execution plans, compiles, and all the other sorts of information you need. Further, all of these metrics are available for analysis. You can set up alerts on the metrics. You can add them all to reports. In short, we’ve added functionality for Azure SQL Database, but kept all the functionality necessary for monitoring SQL Server.

AWS RDS and SQL Monitor

As we know from the State of Database Monitoring survey, many people also have SQL Server databases running on Amazon. Yes, many of these may be in traditional Infrastructure as a Service virtual machines, but some are in AWS RDS. Other surveys have found that many organizations have more than one cloud vendor for managing their systems. With all this in mind, this year we introduced AWS RDS to SQL Monitor.

Similar to the Azure offerings, the monitoring of RDS does two things. First, we’re capturing metrics that are unique to RDS. These metrics are grouped together to show Amazon RDS SQL Server metrics, RDS machine metrics, and SQL Database metrics. Second, we’re putting all these metrics into the same, single pane of glass, in which you see all your other metrics. For example, this is a test system where I’m monitoring my local instance, and an RDS database at the same time:

Drilling down into the RDS instance, I have all the same level of details I have when looking at a regular instance of SQL Server. For example, here’s the list of databases on my test RDS instance:

While this looks identical to a list of databases on my local instance of SQL Server, you can see the rdsadmin database that’s part of every RDS instance. The behaviors and information of this part of the tool though is identical to the on-premises server.

SQL Server and SQL Monitor

If you have a small footprint in the cloud, or you’re not there at all, you still have to deal with your on-premises servers. Further, as with everything else, the data under management there, demands on the system, and more, are all still an issue. Even as SQL Monitor has expanded what it can do with Amazon RDS and Azure SQL Database, we haven’t left the on-premises systems behind. In fact, we continue to expand on what you can with your servers.

For example, one of the newest pieces of functionality is enhanced monitoring of tempdb:

We’re now capturing metrics to allow you to understand how your tempdb is being used. The chart above shows a breakdown of the tempdb on a server by the types of objects in use. You can see a large spike in the amount of tempdb space for user objects right around 10:30AM. Then, you could look at tempdb by Session, Login, Program or even Database in order to identify where that spike originated as a way to troubleshoot the issue.

There are a number of other examples, but the core concept is simple. We’re expanding the capabilities of SQL Monitor in order to support you, regardless of where your SQL Server data lives.

Conclusion

As a technologist, I love the challenges that the hybrid cloud-based/on-premises environments we’re dealing with present. However, many more of us simply want to get the job done and not sweat every nuance of the differences our hybrid systems present. When it comes to monitoring, I’d say that’s one of the biggest areas where, it would be a lot easier if you could just get a single tool to do the job. Since SQL Monitor supports everything you’re doing today, and will support the things you do in the future, as you move to the cloud, it makes for a great solution to the hybrid monitoring problem.

The State of Database Monitoring Survey, Insights Report

Why cloud and hybrid estates are important — but bring complexity

Download your free copy today

Download