Is your SQL Server Code Ready for Azure?

I recently had a discussion with a client that turned to the question of SQL Server code compatibility with Azure SQL Database. We were designing a new pipeline for their on-premises SQL environment, and they mentioned their abandoned cloud migration effort from a few years earlier. The business ended up pausing this effort because of code incompatibilities that would have required an unexpected amount of re-engineering. After that experience, they wanted to ensure that any new pipeline development was done while being mindful of a possible future migration.

The risks of delaying code analysis

Delaying an analysis of code compatibility is surprisingly common for businesses undertaking a migration to the cloud. Early cost-benefit analyses often address the hardware and infrastructure changes involved but can sometimes neglect to consider the impact of required code changes.

Infrastructure concerns are critical considerations when evaluating a move to the cloud. However, limiting our analysis to these considerations may hide the costs and risks associated with any necessary re-engineering. Unfortunately, it is very possible for these hidden risks and costs to turn out to be deal-breakers. To avoid a sticky situation, learn about these factors up front before investing time and energy into migration preparations.

Azure options: SQL VM vs. Managed Instance vs. SQL Database

It is important to note that a “move to the cloud” can come in many forms. Some examples are migrations to a hybrid environment, “lift and shift” moves to Azure-hosted VMs or SQL Managed Instances (SQL MI), or full or partial migrations to multi-tenant Azure SQL Databases (SQL DB). You will need to know what type of migration is being considered before evaluating code changes since the different options have different levels of compatibility with SQL Server.

Here’s a quick breakdown of the options in Azure with SQL Server compatibility.

IaaS Option

SQL Server on an Azure VM
Since this option constitutes a full installation of SQL Server on a dedicated Azure-hosted virtual machine, there are no code or feature incompatibilities to be concerned about. Azure SQL VMs achieve complete feature parity with on-premises SQL environments. With this option, the primary difference between the Azure implementation and an on-premises installation is the management of the underlying server.

PaaS Options

Azure SQL Managed Instance
SQL MI boasts “near 100% compatibility” with the latest Enterprise Edition of the SQL Server database engine, while still including automated backups, patching, and high availability of the SQL environment. This option uses a single-tenant database engine intended to enable the least disruptive migration from an on-premises or Azure-hosted SQL Server instance to a full PaaS environment. This means that many of the incompatibilities that exist with Azure SQL Database are minimized or eliminated with Azure SQL MI. However, functionality that requires access to the file system or OS is still impacted.

Azure SQL Database
Azure SQL DB is another fully managed PaaS option consisting of a multi-tenant database engine that is optimized for cloud-native applications. While this option is generally less expensive than Azure SQL MI, there is less overlap with SQL Server and greater potential for code or data flow incompatibilities.

10 Common Incompatibilities

There are several very helpful tools (see the “Resources and tools” section below) that can help you identify data flow issues prior to migration. As you go through your analysis, keep in mind that Microsoft has established workarounds for a good number of these incompatibilities, so their presence in your code does not necessarily mean they are a barrier to migration.

For a high-level overview, here are some of the most common sticking points we see for migrating SQL code. The differences between SQL MI and SQL DB are included where applicable, as well as some potential workarounds.

1. Uses linked servers

Linked servers can be used in SQL MI to access SQL Server and Azure SQL Databases without distributed transactions. SQL DB requires the use of elastic queries instead.

2. Performs cross-database queries or transactions

These are supported with SQL MI, but not with SQL DB. In SQL DB, cross-database queries may be able to be converted to elastic queries.

3. Uses Database Mail

This is available for SQL MI but not SQL DB. There are workarounds available for sending email in the Azure platform, but they will require some re-engineering.

4. Uses system tables, views, functions, or stored procedures

Some system objects are available in both SQL MI and SQL DB but not all. Consult Microsoft’s documentation for a full comparison of what is available.

One important note is that the amount of space available to tempdb is provisioned in both SQL MI and SQL DB based on the number of cores available and the service tier licensed. Consult the documentation of each for details.

5. Accesses Windows command line or file system

Neither SQL MI nor SQL DB supports direct access to the file system or the Windows command line.

One workaround is to migrate files to Azure Blob Storage or Azure Files. For SQL MI, with the appropriate security and firewall configurations, it is also possible to establish connectivity between your Managed Instance’s VNet and the location of an on-premises file share.

SQL MI also supports SSISDB configuration and the Integration Services Catalog, allowing SSIS packages to be used for file manipulation. Azure Data Factory can also be leveraged to load and transform files for both SQL MI and SQL DB. An Azure-SSIS Integration Runtime (IR) can be installed and configured, and SSIS packages can be run directly from Azure Data Factory.

6. Uses change data capture (CDC)

Change data capture is supported for SQL MI. It is also supported for SQL DB, but only in the S3 service tier and above.

7. Uses BULK INSERT or OPENROWSET

BULK INSERT and OPENROWSET are only supported from a supported Azure file source (e.g.: Azure Blob Storage or Azure Files).

8. Uses .NET Framework: common language runtime (CLR)

CLR support is not available for SQL DB, but it is available in SQL MI with some important differences.

9. SQL Server Agent

SQL Server Agent is not available in SQL DB, and elastic jobs should be used instead. In SQL MI, SQL Server Agent is supported with important differences.

10. Uses semantic search

Full-text semantic search is not available in either SQL MI or SQL DB.

Other important things to remember when migrating to a PaaS environment

• High availability: Since high availability is included in the PaaS offerings, SQL Server functionality and syntax connected with Always On Availability Groups is not supported.
• Maintenance: Updates, patches, backups, and restores are likewise managed automatically in the PaaS offerings. Therefore, associated T-SQL syntax will not work in SQL DB and will be different for SQL MI.
• Credential management: Windows authentication is not supported in SQL DB, and is replaced by Windows Authentication for Microsoft Entra in SQL MI.
• Collation: Catalog collation is set when an instance (SQL MI) or a database (SQL DB) is created, and it cannot be changed afterwards.

Resources and tools

I hope this provided you with a jump-start for thinking about whether your SQL Server code is Azure-ready.

Here are a few more resources and tools that can help you take the next steps toward a full compatibility analysis:

• Data Migration Assistant – Microsoft’s robust tool for enabling database compatibility assessments, recommendations, and migration assistance.
• Azure Migrate – this service can be used as a start-to-finish hub for planning and facilitating a cloud migration.
• Pro Database Migration to Azure – An excellent and comprehensive book covering the best practices for successful on-premises migrations to the Azure cloud platform.

Want to learn more?

Want to learn more about how The SERO Group helps organizations prepare for a SQL Server cloud migration? Schedule a call and let’s talk.