Txture Blog

Cloud Migration

All articles
Cloud Comparison Series

Migration Tool Options for Azure SQL Offerings

5min read
2022-05-11
Share this article:
Comparing different cloud options visualized by clouds on a scale

Txture Cloud Transformation serves as the central communication, assessment and recommendation platform in many cloud transformation initiatives across the globe. Oftentimes we see a planned transition towards Azure's SQL database offerings, coming from an on-premises Microsoft SQL Server installation.

The current Azure SQL offering consists of Azure SQL Database and Azure SQL Managed Instance and both are DBaaS platform products, where Azure takes care of running and maintaining all underlying compute infrastructure and required software packages. Both always run the latest stable versions of Microsoft's SQL Server database management system. As Azure is performing the patching for us automatically, but at any time, all we have to account for is to embed a little tolerance in our custom applications to retry requests on transient faults.

For compatibility reasons and quick lift-and-shift options for on-premises SQL databases, Azure also offers regular Azure VMs - SQL Server on Azure VMs - that come with prepared images, but allow full OS level access. This offering is especially useful to take e.g. earlier database software versions to the cloud or customized ones that might leverage unsupported features.

It is obvious that you need to take care of maintaining the system and software packages there. Still many features that are natural to DBaaS offerings can be enabled for SQL Server on Azure VMs as well. Azure's "SQL Server IaaS Agent" helps to accomplish e.g. central portal management and monitoring of SQL Server VMs and SQL features, backups, patching, key management or license management.

So, from a general migration perspective, when to choose what?

Use Azure SQL Database if you start from scratch. It should also be your default migration target, if you want a fully managed, elastic database and you don't need instance/OS level access.

Use Azure SQL Managed Instance if you want a fully managed database, but you rely on features e.g. of SQL Server Agent, like jobs, alerts, notifications, etc. or Database Mail. It offers a good amount of compatibility to SQL Server installations and hence can be a bridge migration target for your on-premise SQL Server.

Use SQL Server on Azure VMs for lifting and shifting SQL Server installations, i.e. migrating them as-is to a cloud environment. This allows full instance level customization, in case needed to replicate an older SQL Server version or otherwise unsupported extensions.

Now, what tools to use to achieve your migration?

Migration to Azure SQL products happens either online or offline. Online means your database instances keep running, whereas in an offline migration you stop the workload and hence no modifications will occur while migrating. Online migrations may still require a source or target database to put in read-only state.

There are many different tools and migration methods available to move from an existing on-premise or cloud database to Azure SQL products. The following table is a general guideline to pick the right migration approach, although it does not take into account particular limitations or benefits out of brevity:

Main Use CaseMigration ToolSourcesTargetsAdditional Considerations
Migration sources other than SQL Server or Azure SQLSQL Server Migration Assistant (SSMA)Oracle Database, MySQL, SAP ASE, DB2 or AccessSQL Server 2012 or later, Azure SQL Database and Managed InstanceComplete, multi-step migrations with schema mappings - Desktop application - Automatic, but allows T-SQL based customization during data migration too
Flexible data transformationAzure Data FactoryArbitrary, but including e.g. MySQL, PostgreSQL, MongoDB, DB, Google Big Query and many moreSQL Server, Azure SQL Database and Managed InstanceGeneral data pipeline and integration product - Cloud replacement option for SQL Server Integration Services (SSIS) - Web based pipeline configuration
Upgrades from (outdated) SQL Server and optimizationData Migration Assistant (DMA)SQL Server 2008 or laterSQL Server 2012 or later Azure SQL Database and Managed Instance, SQL Server on Azure VM (if system access)Assessment for migration issues, e.g. unsupported features - Optimizations based on migration target features - SSIS assessment - Desktop application
Reliable migration of large, compatible databasesAzure Data Migration Service (DMS)SQL ServerAzure SQL Database and Managed Instance, SQL Server on Azure VMWeb based - Online and offline migration - Requires feature parity between source and target (use DMA) - SSIS migration not directly included
Hybrid cloud or distributed setups for Azure SQL DatabaseSQL Data SyncAzure SQL Database (at least as central hub and metadata database), otherwise also SQL Server (member databases)SQL Server, Azure SQL Database and Managed Instance, SQL Server on Azure VMSynchronization across databases without transactional consistency - Good for (geographically) distributed applications - Not necessarily for disaster recovery or load balancing setups
Near-real time data replication for Azure SQL Managed Instance and newer SQL Server versionsManaged Instance Link, Alternative: Log Replay Service or Native Backup/RestoreSQL Server 2019 (on-premise or Azure VM), Azure SQL Managed InstanceAzure SQL Managed InstanceDisaster recovery scenarios - Data offloading for read scalability - Helpful for initial Azure SQL setups
Low latency data distribution with an option to leverage existing infrastructure(Bidirectional) Transactional ReplicationSQL Server, Azure SQL Managed Instance and Oracle Database (towards a compatible SQL Server version)SQL Server, Azure SQL Database and Managed Instance, SQL Server for Azure VM, Oracle Database, DB2Replacement for SQL Data Sync if Azure SQL Database is not involved, e.g. supports Azure SQL Managed Instance - Transactional consistency and low latency - Change tracking support

For a detailed description of the individual tools and methods, look up the corresponding and extensive documentation from Microsoft Azure. Good starting points can be found in the references section below.

Beyond the recommended migration approaches there exist basic migration approaches (e.g. exporting/importing BACPAC files, the Log Replay Service for Azure SQL Managed Instance and used in DMS, etc.) and moreover many service providers offer dedicated utilities and knowhow to support migrations to Azure SQL.

Also check out Txture's Cloud Insider to see feature comparisons for Azure SQL offerings and many more cloud products across various cloud service providers.

Screenshot from Txture Cloud Insider, providing feature comparisons for Azure SQL and many other cloud service providers and cloud products.Txture Cloud Insider, providing feature comparisons for Azure SQL and many other cloud service providers and cloud products.

Give us a shout on your experiences in cloud transformation engagements or if you want to learn how Txture software solutions help you assess and plan cloud transformations with confidence.

References


Keen to learn more?

Do you already want to know more about Txture's Cloud Transformation Platform and want to discuss how Txture can facilitate the cloud journey of your organization?
Feel free to get in touch and book your own demo session!

Related posts

16.11.2022 |Cloud AssessmentReady, set, cloud: What is a cloud readiness assessment?2.11.2022 |Cloud KnowledgeFaces of Cloud Transformation22.10.2022 |Cloud MigrationUnderstanding your client's IT landscape before moving to the cloud17.10.2022 |Cloud MigrationCloud before cloud: a deep dive into the history of cloud computing24.6.2022 |Cloud KnowledgeAll you need to know: The 6 R’s of Cloud Migration
Txture Newsletter
Get practical cloud knowledge directly delivered to your inbox.
Sign me up
Thomas Trojer
Author
Thomas Trojer
Thomas is responsible for the overall product vision at Txture. Joining Txtures enablement teams regularly, he is strongly connected to customers and partners alike. And there is one primary topic he is eager about: building software to solve the crucial problems during a cloud transformation journey.

Contact us

thomas.trojer@txture.io
+43 676 7218402
LinkedIn
Marian Vladoi
Co-Author
Marian Vladoi
Marian works as a Cloud Engineer Consultant at Txture. He is passionate about programming and cloud services, in addition to his regular tasks of pushing clients Cloud Transformation ahead.