How to Migrate a SQL Server Database to Azure

By Staff Contributor on May 27, 2022

For many organizations who traditionally had to adopt expensive and usually relatively inflexible on-site database solutions, the increasing adoption of database-as-a-service (DBaaS) providers is a welcome alternative, as DBaaS offers a flexible, cloud-based database option through subscription services.

Among DBaaS platforms, Microsoft Azure® SQL Database has become a go-to option for organizations hoping to move to the subscription model, likely because Azure’s solution is one of the most cost-effective while providing reliable security and disaster recovery.

One of the main factors still holding many businesses back is Azure database migration, which can be challenging for organizations worried about the cost and the time commitments necessary for successful cloud migration. However, Azure database migration doesn’t have to be daunting. The following tips can set you up for success. And remember, you can use an automated tool such as SolarWinds® SQL Sentry®, SolarWinds Database Performance Analyzer (DPA), or SolarWinds Database Performance Monitor (DPM) to streamline the process.

Top Tips for Database Migration

Here are some tips for database migration as you move a SQL Server® database to Azure.

Understand the Challenges

The cloud migration process begins with verifying if a cloud system is right for your business. Understanding the pitfalls and potential inconveniences of cloud migration can significantly reduce the pain points of the migration process. First and foremost, organizations need to be fully aware all migrations come with some amount of downtime—either all at once, as organizations switch all systems over to the cloud, or during phases. This downtime can be minimized if all of an organization’s data is copied to the cloud while teams are on-premises.

It’s also critical to understand precisely how the capabilities of a new cloud system differ from those of an on-premises system. Creating cloud-based code to account for these disparities might be necessary for transferring on-premises data into a cloud system. To understand both the cloud system and its differences from an on-premises system, work with a new cloud provider for a complete picture of what differences you may need to account for during the migration process.

Make a Plan

To further assist in planning for Azure database migration, building an in-depth understanding of your primary data systems will help create a detailed migration plan. Here are a few essential items to keep in mind during the discovery process:

  • Systems from which the migration target consumes data
  • Systems for which the migration target produces data
  • Current versions of software components
  • Resource utilization (e.g., CPU, storage, memory)
  • Performance baselines
  • Sample workloads
  • Recovery point objectives (RPO) and recovery time objectives (RTO)
  • Application owners

Automated tools, such as monitoring or documentation software, can assist teams in collecting this information. However, some of this crucial information also requires IT professionals to engage the knowledge of business leaders. This collaboration comes with the added benefit of ensuring executive buy-in for the migration. Earning this buy-in is an essential step in the migration process, as it means leaders fully understand the process and can take measures to support the Azure database migration.

Clean Up

Azure database migration is much easier when organizations don’t bring extra baggage. Sorting through data to cull unused data sources ultimately saves time and reduces the amount of clutter to sort out later—before moving to the cloud, cull data taking up valuable space, offering no discernable value to your organization. Starting fresh with a well-organized cloud, free from extraneous data, cuts down on costs while saving storage space and could mean your system will work faster.

Build a Data Dictionary

Cleaning up data is only one part of starting with a healthy, well-organized cloud. Kevin Kline from SentryOne recommends building a robust data dictionary before beginning your Azure database migration. According to his guidance, migration is a great time to look for “glaring problems” in your SQL code or your system design: “For example, you might have stored procedures causing a huge number of implicit conversions because the stored procedures use an incompatible data type compared to what is defined in the database table. Taking a look at those types of issues helps to build a good data dictionary.”

To build a data dictionary as you sort through databases before the migration, take note of every critical system document so they can be accounted for after the move. While this process is time-consuming, it’s an essential step to ensure the new system is fully functional and has everything your business needs.

A good data dictionary begins with careful data lineage tracing, including documenting each of the steps through which data has already passed, from inception through ETL processes, and perhaps including notes about production databases or data warehouses. Data lineage documentation helps an organization precisely understand where its data has been—if it has been reviewed in the past and whether it has been cleaned, altered, or moved previously. This process helps IT professionals decide if the data needs to be modified before it’s migrated to protect its integrity. Not all data should go directly into the cloud, but the data allowed to the cloud should come with a complete history so it may help organizations understand its new place in the cloud.

Mirror Data

While it’s not possible to merely copy and paste all data into the cloud, the mirroring process does help ensure nothing is lost during Azure database migration. To aid migration, consider the Extract, Transform, and Load (ETL) process. It allows teams to copy data and applications into the cloud with end users still on-premises while also permitting data and applications to remain parallel to the cloud for the entirety of your migration. This is where the first step of the migration process comes back into play. Code written or reconfigured for cloud compatibility should ideally be identified before Azure database migration begins.

Test, Then Test Again

Once database migration is complete, it’s crucial to test the cloud thoroughly to identify and prepare for any issues affecting the migration process in the future. For starters, compare your previous system to your new cloud to identify any missing data, spot differences between the new system and the old, and look for pain points or places for cloud improvement.

After completing these assessments, build a baseline for reports by providing an initial document outlining exactly how the cloud runs, continue to monitor the system while updating the baseline reports, and test for potential future issues instead of simply assessing the cloud as-is. Your cloud should adapt as your organization grows, functioning correctly months or years in the future as it does during those initial stages. Be sure to hold off on an organization-wide launch of the new cloud until it’s thoroughly tested and its capabilities are well documented.

Tools to Streamline Your Azure Database Migration

Luckily, the Azure database migration process doesn’t have to be undertaken alone. There are several tools available to help make migration as painless as possible. Here are a few picks for some of the best Azure database migration tools.

SolarWinds® Database Performance Monitor (DPM)

This tool assists with your Azure database migration by collecting, analyzing, and visualizing the metrics needed to help pinpoint Azure SQL performance issues in real time. DPM provides in-depth insight into database performance by monitoring latency, throughput, errors, warnings, index usage, execution plans, and more for every query executed. Designed by IT professionals for IT professionals, DPM is built to be intuitive and easy to use, allowing you to drill down from a global view to examine specific problem queries on individual servers with a few clicks.

SolarWinds® Database Performance Analyzer (DPA)

The migration and testing process is all about identifying problems before they arise. The Azure SQL database monitoring tool in SolarWinds DPA can help you find the most significant Azure SQL server performance issues in your environment—then quickly drill into the analysis DPA provides to determine what’s causing the issues. Use correlated resource metrics for deeper insight into utilization issues.

The Azure SQL performance monitoring tool in DPA offers detailed blocking and deadlock analysis to quantify the impact blocking has on end users while visualizing the blocking tree to expose the sessions and queries involved—allowing you to see what caused the issue and who the victims were. Query-level performance analysis brings available data into one view to help you answer, “Why is this query slow now?” and much more.

SolarWinds SQL Sentry®

Choosing the correct SQL database for your organization requires careful planning for cost efficiency. Whether you’re using Microsoft Azure SQL Database for your software-as-a-service (SaaS) business, enterprise production, or development and testing, under- or over-provisioning resources can mean a poor user experience and wasted budget.

SQL Sentry helps monitor performance metrics, including database transaction unit (DTU) usage, and provides notification of events occurring on your system. SQL Sentry allows you to distinguish consistent performance patterns from anomalies, which is critical for ensuring your data platform delivers optimal performance to end users.

Moving SQL Servers from on-premises to the cloud can bring significant benefits and challenges, especially during the transition. As a longtime technology Partner with Microsoft, SolarWinds provides expert guidance as you’re migrating SQL Server workloads to Azure SQL Database or an Azure SQL Database Managed Instance.

SolarWinds solutions pave the way for a smoother migration and ensure your Azure workloads are optimized for peak performance.

Getting Started With Migrating Your Database to Azure

To get started today, consider a free trial to help you streamline the process. SolarWinds DPM offers a free 14-day trial for gaining insight into critical metrics for an informed migration process. SolarWinds DPA 14-day free trial provides you with cross-platform database performance analysis for both on-premises and cloud environments. Meanwhile, SolarWinds SQL Sentry provides a 14-day free trial for at-a-glance insights into your Microsoft database environments.

Related Posts