Home | Blog | SQL Server Migration to Azure SQL part 2

SQL Server Migration to Azure SQL part 2

Welcome to the second post from the blog series about SQL Server migration to Azure SQL.

In the previous post, we covered the following topics:

Why migrate SQL Server to Azure SQL?
Migration strategy
Azure SQL family
Migration paths

This post focus on overall migration process step-by-step i.e.: Discover, Asses, Migrate, Cutover, Optimize. Let’s start!

Migration process

Overall SQL Server Migration process can be split into several phases and steps. In our case we divided it into five phases.

Migration to Azure SQL - Adam Jonson 2 -1

Discover

The first step in migrating a SQL Server to Azure SQL is to discover the existing database environment. This involves identifying the server instances and databases to be migrated. Discovery is done by dedicated Azure service like Azure Migrate that can automate the process. In case of large infrastructure hosted on VMware or Hyper-V it is recommended to install dedicated software to scan on-premises infrastructure. The goal of this step is to ensure that all critical SQL Servers instances and databases are identified and included in the migration plan.

All the results of discovery phase can be collected and presented on Azure Migrate service. Service gather and consolidate all required information about all SQL Server instances, number of databases and their volume, operating system and SQL Server versions, hardware specification but also about all machines and their dependencies (not only SQL Server) existing in scanned infrastructure.

Azure migrate is not dedicated only for SQL Server migrations. Azure Migrate is a Microsoft tool that helps assess and migrate on-premises workloads to Azure. It provides a central hub for tracking the progress of migrations, and offers sizing and cost estimates to help organizations plan their migrations.

Assess
In the second step, we evaluate which of our resources are ready for migration. Of course, we don’t make this assessment with the by eye, but rather we use dedicated tools to assist us. The evaluation is performed from both a hardware and an application perspective.

The assess step involves analyzing the existing database environment to determine the best migration strategy. This involves assessing the complexity of the server and database environment, identifying potential challenges (i.e. blockers and compatibility issues), and determining the best migration method. The assessment results contain information about:

Azure SQL deployment model i.e. Azure SQL VM, Azure SQL Managed Instance or Azure SQL Database
Recommended configuration and sizing
Service costs estimation

To conduct assessment following tools can be used:

Azure Database Migration Service (ADMS) [https://learn.microsoft.com/en-us/azure/dms/]
Azure SQL Migration extension for Azure Data Studio [https://learn.microsoft.com/en-us/sql/azure-data-studio/extensions/azure-sql-migration-extension?view=sql-server-ver16]
PowerShell [https://learn.microsoft.com/en-us/powershell/module/az.migrate/?view=azps-9.4.0]

Migrate

Once the assess step is complete, the next stage is Migrate. This is the actual process of moving the data from the source SQL Server to Azure SQL. This step can be done online or offline, depending on the needs of the organization.

Migration mode

Online migration involves creating a replica of the source database and then synchronizing it with the target Azure SQL database. This allows for minimal downtime during the migration process, but it requires a stable network connection with sufficient bandwidth.

Offline migration involves taking a backup of the source database and restoring it to the target Azure SQL database. This method requires more downtime, but it is suitable for large volumes of data or when the source database is not available during the migration process.

Supported migration scenarios status from SQL Server to Azure SQL (valid for 2023-09-21)

Source	Target	Online	Offline
SQL Server	Azure SQL Database	NO	YES
SQL Server	Azure SQL Database ManagedInstance	YES	YES
SQL Server	Azure SQL VM	YES	YES

The actual migration scenario status can be tracked here: https://learn.microsoft.com/en-us/azure/dms/resource-scenario-status

Migration tools and services
To assist with the migration process, Microsoft provides several tools and services. Azure Database Migration Service (ADMS) is a fully managed service that simplifies the migration of on-premises databases to Azure SQL. It provides a variety of migration methods, including online and offline migration, and supports a range of source databases, including SQL Server, MySQL, and Oracle.

The Azure SQL migration extension for Azure Data Studio is a free extension that provides a graphical interface for migrating databases from SQL Server to Azure SQL. It allows for online and offline migration and provides options for customization and optimization. It also integrates with Azure Migrate and Azure Database Migration Service.

The Database Migration Assistant (DMA) is another tool provided by Microsoft that helps assess the compatibility of on-premises databases with Azure SQL. It also provides guidance for resolving any issues that may arise during the migration process. DMA allow to conduct migration process.

Important factors

When migrating to Azure SQL, it’s important to consider factors such as:

the volume of data to be migrated number of servers, databases etc.
system availability during the migration process
the performance of both the source and target databases.
the network performance

All mentioned factors should be taken into account to ensure that the migration is completed within a reasonable and available time frame.

Additionally, it’s important to plan the migration process during a time window that allows for minimal disruption to the organization. This may involve conducting the migration during off-peak hours or using a phased migration approach to minimize downtime.

To sum up
Overall, the „Migrate” stage of the SQL Server to Azure SQL migration process is a critical step that requires careful planning and consideration. With the right tools and services, and proper attention to important factors such as data volume and system performance, organizations can successfully migrate their databases to Azure SQL and reap the benefits of a modern, cloud-based database solution.

Cutover
The cutover phase of SQL Server migration to Azure SQL is a critical step to ensure all data is migrated and available in the new environment. It requires careful planning and execution to minimize downtime and avoid data loss. To begin, you should ensure that all data is migrated and in place before proceeding with the cutover procedure. This involves checking the status of all the migrated data and validating that it is accessible in the new environment.

Before final switch to new system the cutover procedure should be prepared. This involves identifying the systems and applications that need to be updated to point to the new Azure SQL instances. For example, if you are migrating a data warehouse, reports and analytical models need to be updated to point to the new server. The same applies to any applications that were previously connected to the SQL Server.

Before the cutover, it’s essential to prepare the right communication within the organization. This means informing stakeholders and end-users of the impending switch, specifying the time window when the migration will occur, and highlighting any potential system unavailability. In addition, you should have a clear plan for the old system cutoff, including whether or not it should be kept for a specified time for reference or archival purposes.

By following these steps, you can ensure that the cutover phase of the SQL Server migration to Azure SQL is successful, and the new environment is fully operational with all data accessible. It’s essential to plan and execute this phase with utmost care to minimize downtime and avoid data loss.

Nevertheless, our migration journey does not end here.

Optimize
After the migration process, it’s important to focus on optimizing the new Azure SQL environment to ensure it’s running efficiently and effectively. This can be done through monitoring and analyzing the database behavior and adjusting the hardware configuration to meet the demands of the database workload.

One of the first things to do is to monitor the metrics of the new Azure SQL environment, such as CPU, memory, and I/O. This will give insights into how the database is performing and where bottlenecks might be occurring.

Next, it’s important to analyze the behavior of the database after the migration. This includes reviewing the indexes and statistics and rebuilding them if necessary. This can help to improve query performance and overall database efficiency. It’s also important to consider adjusting the hardware configuration, such as scaling up or down based on the workload demands. This can be done manually or by implementing an auto-scaling, which will adjust the hardware during less intensive periods.

After a longer time period, it’s a good idea to review the recommendations provided by Azure Advisor service. This service can provide valuable insights and recommendations for improving the Azure SQL environment’s costs.

Overall, the optimization process is crucial for ensuring that the new Azure SQL environment is running efficiently and effectively, which can ultimately lead to improved application performance and better overall business outcomes. By carefully conducting optimization, stakeholders can see the benefits of the migration, such as reduced maintenance costs, improved scalability, and greater agility in responding to changing business needs.

Conclusion

The SQL Server to Azure migration process is a logical sequence of steps that includes recognizing, assessing the situation i.e.: discover SQL Server instances and databases, potential target configuration. Output from first two steps is simultaneously an input for migration process, which is supported by available Azure services and tools. The migration does not end with switching to Azure SQL, after that the optimization takes place By carrying out the migration according to a defined process, we maintain control over its course, while minimizing potential risks of unforeseen situations.

The last post from series will focus more on costs optimization i.e. Azure Hybrid Benefit, Reserved Instance. It will also contain summary about tools and services which supports migration process.