A migration from on-premises SQL Server to Azure SQL offers many customers a number of advantages. It can enable scalability, reduce costs, enhance security, ensure high availability, and simplifies maintenance. Many organizations are looking to equivalent cloud services to move on-prem workloads such as SQL databases to the cloud, freeing themselves from the overheads of purchasing, configuring and maintaining physical hardware and infrastructure. Moreover, many fully managed cloud services remove the need to maintain and patch software as is needed with on-prem SQL Server.

Azure SQL also facilitates global reach, integrates with other Azure services, supports advanced analytics, streamlines DevOps, and provides robust backup and disaster recovery options. The Azure SQL service also complies with various industry standards, making it an appealing choice for businesses seeking to modernize their database infrastructure.

Today, I’ll cover the key steps in planning and executing a migration project from on-premises SQL Server to Azure SQL:

  • Choosing the Azure SQL option that suits your needs
  • Baselining your current Microsoft Server SQL usage
  • Choosing a monitoring tool to baseline your Azure SQL migration projects
  • Evaluating the performance of Azure SQL to quantify success
  • Proactive monitoring and troubleshooting of Azure SQL in production
  • Other considerations when migrating to Azure SQL

Choosing the Azure SQL option that suits your needs

Microsoft Azure Cloud offers broadly 3 different options to deploy Microsoft SQL server on Azure:

  • SQL Server on Azure VM (IaaS),
  • Azure SQL Database (PaaS) and
  • Azure SQL Managed Instance (PaaS).

We’ve put together an overview of the differences between these options, together with lots of links to Microsoft and other resources that will help you understand and make a decision between the options available, see: Azure SQL Deployment Options: Making the Right Choice.

In short, if you need direct OS access or rely on a specific version of SQL Server, then migrating to SQL Server on Azure VMs is probably the natural choice. Whereas Azure SQL Managed Instance is usually the best choice for current SQL Server users who are migrating legacy databases but don’t need OS-level access. For those starting afresh who are looking to deploy new cloud databases Azure SQL Database is often the right choice. There are a few features available in on-prem Microsoft SQL Server that are unavailable in the PaaS options so it’s worth reviewing your usage for those, FILESTREAM is one example.

Baselining your current Microsoft Server SQL usage

Once you have made the choice of which Azure SQL option to use, you will probably want to evaluate your current on-prem SQL Server usage. The pay as you go and for the size of resources you use nature of cloud billing models means that you will want to rightsize your Azure SQL deployment to optimize price to performance but to also capacity plan for expected peak usage and growth.

If you are running on-premises servers, the chances are that you are utilizing only a fraction of your overall server capacity most of the time but are over-provisioned to handle peak loads. Moving those workloads to the cloud can greatly reduce cost by “rightsizing” server capacity as needed. See: Rightsize to maximize your cloud investment with Microsoft Azure | Azure Blog | Microsoft Azure. Benchmarking and baselining should therefore cover both normal usage and the surge/peak usage.

Given that a migration to the cloud is moving your SQL Server workloads from running on on-prem infrastructure to running on cloud infrastructure, it is essential to understand and baseline the infrastructure resources utilized by your SQL workloads. Key resource metrics such as server CPU, I/O metrics and memory usage will help you configure and size the Azure SQL solution.

If your needs demand that you will be using SQL Server on Azure VM (the IaaS option), your infrastructure resource baselines will be critical to selecting an appropriate Azure VM instance to rightsize your deployment. Microsoft provide a helpful checklist and guidance on choosing VMs for SQL Server on Azure VMs, here: Checklist: Best practices & guidelines – SQL Server on Azure VMs | Microsoft Learn. Storage performance is of course also critical for SQL Server performance, and you will need to go through a similar selection process for storage, advice is given here: Storage: Performance best practices & guidelines – SQL Server on Azure VMs | Microsoft Learn.

Azure SQL Database offers the following purchasing models:

  • The vCore-based purchasing model lets you choose the number of vCores, the amount of memory, and the amount and speed of storage. The vCore-based purchasing model also allows you to use Azure Hybrid Benefit for SQL Server to gain cost savings by leveraging your existing SQL Server licenses.
  • The DTU-based purchasing model offers a blend of compute, memory, and I/O resources in three service tiers, to support light to heavy database workloads. Compute sizes within each tier provide a different mix of these resources, to which you can add additional storage resources.

Whilst Azure SQL Managed Instance offers vCore-based pricing, see: vCore purchasing model – Azure SQL Managed Instance | Microsoft Learn. Beyond hardware configurations, Azure pricing also factors in service tiers. The resource limits of service tiers vary and so you will need to benchmark resource usage such as CPU to determine the appropriate combination especially if your workloads are intensive. See: Resource limits – Azure SQL Managed Instance | Microsoft Learn.

Migration projects usually aim to result in a situation where performance is similar or better post-migration. By measuring and recording baseline performance values on the original system and then comparing them to the post-migration environment, success can be quantified. A performance baseline should usually be a set of parameters that define your average workload on your original system.

Microsoft suggest that you select a set of queries that are important to, and representative of your business workload. Measure and document the min/average/max duration and CPU usage for these queries, as well as performance metrics on the source server, such as average/max CPU usage, average/max disk IO latency, throughput, IOPS, average / max page life expectancy, and average max size of tempdb.

Of course, any performance issues on the source SQL Server should be addressed prior to migration. Migrating known issues to any new system might cause unexpected results and invalidate any performance comparison.

Choosing a monitoring tool to baseline your Azure SQL migration projects

When choosing a monitoring tool for an Azure SQL migration project you will want to choose a tool such as eG Enterprise that provides features such as:

  • Infrastructure monitoring capabilities
  • Azure service monitoring capabilities, including billing monitoring. Azure storage performance can have a significant on database performance and should be monitored too
  • Auto-deploy and discovery to capture your entire SQL landscape
  • Out-of-the box metric, log and event capture without tedious manual configuration
  • Real-time monitoring and live and historical reporting over longer timeframes for capacity planning and trend analysis
  • AIOps capabilities to determine granular baselines automatically and will automatically raise alerts on anomalous database behavior or performance
  • Support for application delivery stacks, application servers and web servers e.g., Java used with JBoss or Microsoft IIS
  • Single pane of glass for monitoring your SQL Server databases in the cloud and on-premises with one monitoring tool

Details on how eG Enterprise monitors SQL Server can be found, here: Microsoft SQL Server Database Monitoring | eG Innovations and details on how eG Enterprise monitors Azure SQL are covered on: Azure SQL Monitoring & Azure SQL Performance | eG (eginnovations.com). Key metrics monitored include:

User Experience Transaction Workload & throughput Database Memory
SQL sessions by status – Running/ blocked /Suspended Top queries by CPU Memory grants pending
Session leaks monitoring Top queries by IO Utilization Buffer Hit ratio
Resource overheads by user connections for memory, CPU and IO Top queries by Memory Page life expectancy
Sessions waiting for what type of resources Long running queries SQL Query waits by Memory
Root blocker and blocked sessions Commits Memory usage indicating memory pressure
Connection Response time Rollback Full table scans
Request Response time Activity Monitoring for resource waits Temporary table space usage
Network Response time Transaction Log monitoring for space usage and IO latency
Database Engine Database Services & Concurrency Database Optimization
CPU Utilization by SQL Engine Always On Status Index Fragmentation
CPU Usage for IO% Replication Status Data block corruption
IO Utilization RPO lagging Missing Indexes
Throughput Locks Unused Indexes
Locks waits Full Table Scans
Root blocker Database Error Analysis
Deadlocks
Database Space usage and IO

If you are migrating to a DTU-based purchasing model for Azure SQL Database, support for DTU metrics is of course essential.

Evaluating the performance of Azure SQL to quantify success

The ability to use the same tools to evaluate and baseline your SQL environments both pre and post migration is essential. To assist our customers undertaking cloud migration products our eG Enterprise licensing is transferable between all of the 400+ technology stacks that we support. This means that as you deprecate hardware and on-prem SQL Server deployments you can transfer those licenses to monitoring your Azure services including Azure SQL, Azure Load Balancers, Azure Storage and other dependencies of your new environment. To learn more on eG Enterprise license mobility see: How eG Enterprise IT Monitoring Licensing is Cost-Effective and Flexible | eG Innovations.

Proactive monitoring and troubleshooting of Azure SQL in production

eG Enterprise allows you to use a combination of synthetic and RUM (Real User Monitoring) to assess the end-to-end performance of applications and services supported by your SQL landscape, prior to migration, during migration and post-migration. With synthetic monitoring robot users continually access services and applications in a repeatable manner simulating common workflows and user behavior patterns which determine standardized baselines. If services slowdown or experience performance degradation or outages automatic alerts are triggered. Synthetic monitoring can detect issues even when real users are not accessing services allowing many issues to be resolved before real users experience them. RUM on the other hand monitors the performance of real users and workloads in real time. A good understanding of proactive monitoring is helpful, see: What is Proactive Monitoring and Why it is Important (eginnovations.com).

eG Enterprise application performance monitoring (APM) allows database performance to be monitored in the context of the applications using the database server.

  • Without requiring any agents on the database servers, eG Enterprise APM traces all application accesses and reports on slow queries and exceptions during database processing.
  • When a specific web page or URL is slow, you don’t have to wonder any more as to what is causing the slowdown. eG Enterprise APM gives you the answers at your fingertips.

This eliminates finger-pointing between application development, application operations and database admin teams. A detailed case study of how SQL problems can manifest in a Java stack is covered in: End-to-End Application Monitoring to Troubleshoot Slowness (eginnovations.com), which gives full details of how end-to-end monitoring can be leveraged particularly by IT Ops teams rather than database specialists.

I’ve previously written a guide to monitoring and troubleshooting Azure SQL, available here: Troubleshoot Azure SQL Database Performance | eG Innovations which covers key metrics and ways in which to ensure Azure SQL performance and health, including:

eG Enterprise includes out-of-the-box one-click dashboards that provide a high-level overview of the metrics needed to understand the performance of SQL Server workloads whether they are on-premises or hosted on Azure.

Other considerations when migrating to Azure SQL

When moving applications and services to Azure you may want to evaluate your organizational strategy to handle Azure service outages, some considerations are covered in: Is Azure Down? – Proactive Alerting for Azure Outages | (eginnovations.com) and beyond this we’ve put together a guide on how you can ensure your IT Ops teams preserve full observability on cloud outages, here: How to Protect your IT Ops from Cloud Outages (eginnovations.com).

Migrating to Azure services means that you will probably also want to adopt a strategy to identify and reduce wastage and unused resources which can lead to unnecessary Azure billing costs, see: Reduce Azure costs by finding unused and wasted resources (eginnovations.com) for some useful tips.

Moreover, if you approach the storage limits of Azure SQL Managed Instance, query performance is likely to degrade. It’s a good idea to have monitoring and alerting in place for storage and similar Azure infrastructure resources and services and for metrics such as storage limits on services.

If you are concerned about cloud-vendor lock-in and future Azure pricing or availability, you probably will want to consider monitoring and management tools such as eG Enterprise which support other vendors’ cloud services such as AWS RDS. By keeping the user-experience consistent, wherever servers and instances are hosted, eG Enterprise database monitoring allows DBAs to focus on responsiveness, improving performance and supporting business-critical areas, rather than trying to understand the differences and complexities between different database platforms.

eG Enterprise is an Observability solution for Modern IT. Monitor digital workspaces,
web applications, SaaS services, cloud and containers from a single pane of glass.

Free Trial  See the platform

Learn more