Migrating an on-prem database to a public cloud comes with a number of benefits, such as no longer needing to manage and maintain physical infrastructure, dynamic scaling, disaster recovery, and overall cost reduction. However, migrating to the cloud can often be a complex and daunting task. For instance, if an organization is a Microsoft shop with teams that rely on SQL Server databases, Azure is a natural fit for its needs. But, as with any cloud platform, Azure comes with its own challenges, such as creating a migration strategy and determining what KPIs to monitor throughout the process to ensure an application continues to perform at a high level.
Last year, Datadog partnered with Microsoft to help organizations address those challenges and navigate cloud adoption through Microsoft’s Cloud Adoption Framework (CAF). But it’s up to you to determine which Azure-managed SQL solution is best suited for your workloads. You also need to employ a monitoring strategy that maintains the performance of your workloads and identifies any emergent issues that need troubleshooting.
In this post, we will cover a few factors to consider as you migrate SQL workloads into the Azure cloud—and how an observability platform like Datadog can aid this process—so that you can:
- Adopt the solution that best meets your needs
- Establish benchmarks for your current SQL Server performance
- Get visibility into your Azure-managed SQL service of choice to ensure your migration is successful
- Monitor your Azure-hosted SQL workloads with Datadog
Adopt the Azure solution that best suits your use case
As outlined in Microsoft’s Cloud Adoption Framework, the first steps toward a successful migration include creating a strong adoption strategy and plan. These initial phases involve assessing your goals and current workloads to determine which Azure solution is right for you. If you are migrating on-prem SQL Server workloads to Azure, you have three solutions to choose from:
SQL Server on Azure Virtual Machines (VMs): an infrastructure-as-a-service (IaaS) option that enables you to deploy/install SQL Server instances to Azure-hosted VMs while maintaining operating system-level access
Azure SQL Managed Instance: a database deployment option with the same fully managed benefits as Azure SQL Database but with additional instance-scoped features.
Azure SQL Database: a fully managed database-as-a-service (DBaaS) solution that removes the need to manage the maintenance, security, and reliability of your database. SQL Database allows you to configure elastic pools, a collection of databases that run separate workloads but share resources from a single server.
The solution you adopt depends on your needs, particular use case, and what level of access to your workloads’ operating system (OS) you need. For example, if you need direct OS access or you rely on a specific version of SQL Server, then migrating to SQL Server on Azure VMs is most likely to be the best fit. Running SQL Server on Azure VMs is also useful if your application relies heavily on SQL Server features that aren’t currently available in other Azure SQL solutions such as FileStream and PolyBase.
Alternatively, Azure SQL Managed Instance is the best choice for current SQL Server users who are migrating legacy databases but don’t need OS-level access. SQL Managed Instance enables teams to move their workloads to Azure in a few clicks, providing a frictionless way to quickly perform lift and shift migrations while gaining the benefits of a managed platform–as-a-service (PaaS) solution. These benefits include access to features such as automated backups and software patches, high availability, and more.
Finally, if you want to spin up a completely new scalable cloud-native database, then a fully managed solution such as Azure SQL Database is your best option. If you’ve chosen to host workloads on SQL Database, you should be aware of certain trade-offs. For instance, while SQL Database makes it easy to manage workloads with a full suite of PaaS features, it doesn’t include some of the SQL Server features that are available on-prem or through managed instances such as the SQL CLR integration, SQL Server Agent, and cross-database querying.
Benchmark your current SQL Server performance
After choosing the Azure solution that is right for you, you need to take stock of how your on-prem SQL workloads are performing and understand their current capacity. By doing so, you can establish behavioral baselines and create an effective cloud storage plan before your migration. This includes gauging how your database behaves under moderate levels of traffic so that you can identify a “normal” performance baseline, as well as benchmarking it during peak periods of traffic to understand how it behaves under pressure.
Benchmarking requires that you establish baselines at all layers of your database, which means you not only need visibility into your SQL Server workloads but also the underlying infrastructure that they run on. Tracking host-level infrastructure metrics such as CPU and memory usage can help inform how you configure the Azure solution you’re migrating to. This process will help you rightsize your cloud resources and create a cost-effective migration strategy depending on the Azure solution you have adopted. For example, if you have decided to host your SQL Server workloads on Azure VMs, benchmarking your current memory usage can help you determine whether it is high enough that you need to adopt VMs that are memory optimized, which is likely the case if you are running large in-memory databases or performing memory intensive analytics.
Similarly, if you’re migrating to Azure SQL Managed Instance, benchmarking the CPU usage of your on-prem SQL Server instances can help you decide on the hardware configuration and service tier settings of the managed instances that will host your workloads. If benchmarking reveals that your workloads are regularly utilizing high levels of CPU, then you should consider opting for the Business Critical service tier with premium-series hardware, which allows you to configure managed instances with a higher number of virtual cores.
And if you are migrating to Azure SQL Database, benchmarking can help you discover the appropriate compute tier to select. For instance, your benchmark testing may reveal that your database’s traffic patterns are irregular and difficult to predict, which results in inconsistent CPU usage. In this scenario, you might be tempted to over-provision CPU resources to account for unpredictable traffic spikes, which risks paying for cloud resources you don’t need. Instead, you should consider using SQL Database’s serverless compute tier plan, which scales your database’s CPU resources up and down automatically based on workload demand.
Monitoring tools like Datadog, which visualize key health and performance metrics and enable you to easily identify trends, are essential for effective benchmarking. These tools allow you to quickly compare performance baselines with real workload performance across every layer of your database to ensure workloads remain consistent or improve as you migrate. Visualizing health and performance metrics as you form benchmarks also makes it easier to make informed decisions when establishing SLOs by providing visibility into key performance indicators—such as the average latency of your SQL Server queries—that can be used to verify your workloads are performing as expected after migration.
In the next section, we’ll look at what is important to monitor after you have migrated to Azure to ensure that your database is healthy and performant.
Get visibility into your Azure-managed SQL service of choice
Once you’ve benchmarked your workloads, visibility into the Azure SQL solution you’ve adopted will enable you to compare the performance of your cloud-hosted workloads against the benchmarks you’ve established to verify that performance has either remained consistent or improved. Visibility into Azure SQL solutions will also enable you to monitor the health of your cloud environment so that you can confirm that your workloads have sufficient resources to perform as expected.
Depending on the solution you’ve chosen, there are different monitoring considerations to keep in mind that are covered below.
Monitoring Azure VMs
If you’ve opted to host your SQL Server workloads on Azure VMs, it’s important to keep track of the health and performance of the VMs your workloads rely on because issues with your VMs will translate to degraded workload performance. Monitoring key resource metrics such as CPU and memory utilization can help you determine whether VMs are being overutilized and require reconfiguration. You should also set alerts on disk I/O metrics to notify you when throughput becomes higher than expected so that you can avoid throttling by either enabling caching or resizing your VMs.
Monitoring Azure SQL Managed Instances
Similar to SQL Server on Azure VMs, when you are monitoring Azure SQL Managed Instances it is important to keep track of the CPU and memory usage of your managed instances. If either is too high, you risk degrading the performance of your workloads. You should also closely monitor the reserved storage space for managed instances and compare that to the storage space currently being used. If current storage usage approaches the storage space your managed instances have reserved, query latency will increase. You can optimize storage usage by removing unnecessary data such as old backups and unused tables. You might also consider scaling up your managed instances or, if you haven’t already, switching to a service tier that provides higher storage limits.
Monitoring Azure SQL Database
If you have chosen to host workloads on the fully managed Azure SQL Database, monitoring metrics such as CPU and DTU percentages and deadlocks can provide you with an overview of how well your Azure SQL Database is performing and whether you need to scale it up or optimize your queries so that they run more efficiently. You should also monitor and alert on connectivity metrics such as the number of failed connections and the number of connections blocked by a firewall, which can help you determine if database traffic is being directed as expected. If there’s a sudden spike in the number of blocked connections, for instance, it could be a sign of a misconfigured firewall that needs to be investigated.
Monitor your Azure-hosted SQL workloads with Datadog
Whichever Azure-managed SQL solution you adopt, Datadog can provide you with the visibility you need to ensure your migration is successful and that your workloads are performant. Our SQL Server integration includes an out-of-the-box dashboard that provides you with a high-level overview of the metrics you need to understand the performance of your SQL Server workloads whether they are on-prem or hosted on Azure.
And Datadog’s integration with Microsoft Azure VMs gives you visibility into the Azure VMs that host your SQL workloads so that you can monitor the performance of your newly migrated SQL Server workloads alongside the health of your Azure VMs. If you’ve been notified that your database isn’t performing as expected, you can use the integration to get a complete birds-eye view of all your Azure VMs to check on their status and view resource utilization metrics.
You can also gain visibility into Azure SQL Managed Instances and Azure SQL Database through a combination of our Azure and SQL Server integrations, as well as Datadog Database Monitoring (DBM). You can use DBM to benchmark and identify historical trends in query performance so you can set expectations about how queries will perform after you’ve migrated to the cloud. Once you have enabled the Azure integration, you can quickly start collecting and visualizing DBM telemetry from your managed instances in just a few clicks. Datadog DBM also includes support for SQL Server Always On availability groups, so that you can collect telemetry from each database within a configured Availability Group. You can also rely on our Azure SQL Managed Instances integration to ensure your managed instances have sufficient resources to perform as expected.
Strategize your SQL workload migration with Datadog today
In this post, we looked at the three Azure solutions for hosting SQL workloads and how each provides unique benefits based on your database needs. We also looked at how you can use Datadog for complete visibility into these solutions, as well as to benchmark your current SQL workloads to help you better understand your cloud resource capacity needs and maintain consistent performance throughout your migration.
Learn more about best practices for monitoring a cloud migration and how Datadog can help support you.
You can also request a demo with a Datadog engineer to illustrate how you can monitor your entire Azure infrastructure more effectively.
If you aren’t already using Datadog, sign up today for a 14-day free trial.
