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.
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.
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 Case
|Migration sources other than SQL Server or Azure SQL
|SQL Server Migration Assistant (SSMA)
|Oracle Database, MySQL, SAP ASE, DB2 or Access
|SQL Server 2012 or later, Azure SQL Database and Managed Instance
|Complete, multi-step migrations with schema mappings - Desktop application - Automatic, but allows T-SQL based customization during data migration too
|Flexible data transformation
|Azure Data Factory
|Arbitrary, but including e.g. MySQL, PostgreSQL, MongoDB, DB, Google Big Query and many more
|SQL Server, Azure SQL Database and Managed Instance
|General data pipeline and integration product - Cloud replacement option for SQL Server Integration Services (SSIS) - Web based pipeline configuration
|Upgrades from (outdated) SQL Server and optimization
|Data Migration Assistant (DMA)
|SQL Server 2008 or later
|SQL 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 databases
|Azure Data Migration Service (DMS)
|Azure SQL Database and Managed Instance, SQL Server on Azure VM
|Web 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 Database
|SQL Data Sync
|Azure 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 VM
|Synchronization 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 versions
|Managed Instance Link, Alternative: Log Replay Service or Native Backup/Restore
|SQL Server 2019 (on-premise or Azure VM), Azure SQL Managed Instance
|Azure SQL Managed Instance
|Disaster 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 Replication
|SQL 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, DB2
|Replacement 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.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.
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!