Strategy for SQL-Server Migration to Cloud
One of the leading enterprise of their domain were looking for strategies around the migration of on-premise SQL-Servers (multiple versions: 2010, 2012, 2014) to Azure. The customer’s SQL server infrastructure was scattered at the data-centers as well as individual site levels, so it was a spaghetti of license management and policy enforcement, hence they were eager to consolidate the infrastructure along with the cloud transition. Microsoft has published links around the migration of SQL-Server to Azure:
- https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/database/sql-server-to-sql-database-guide?view=azuresql
- https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison?view=azuresql
Comparing feature by feature it was evident that Azure SQL – Managed Instance (MI) is the closest fit to replace the on-premise SQL Servers (compatibility assessment was done using Azure Data Studio). However there were other considerations that we needed to account for, the teams were using multiple add-on features/components like SSIS (SQL Server Integration Services), SSAS (SQL Server Analysis Services), and SSRS (SQL Server Reporting Services) are not natively supported on Azure SQL (SSIS requires Azure Data Factory managed integration services, similarly SSRS requires separate VM deployments).
The teams were looking for a best-fit solution and recommendations around it. The recommended architecture offered maximum flexibility that met the customer's requirements with minimal management. Of course, the SQL- servers deployed on VMs required oversight by the DB teams for patching/upgrades, etc. (we intentionally kept automated patching turned off), but by default most of the workload was compatible with Azure-SQL-MI so the majority of apps now don’t need to worry about DB patching/version upgrades going forward, Azure SQL ensures that.
Few of the benefits that we could enumerate were:
- Consolidation of diverse SQL-Server infrastructure into 2 topologies, One full PaaS (Azure SQL -MI) and another self-managed (SQL-Server on VM).
- Ability to support the additional SQL Server configurations involving SSRS, SSIS, and SSAS.
- Less moving parts (eg No Az. Data Factory required for supporting the SQL Server integrations)
- Cloud enablement for applications.
- Allowed teams to gradually migrate from SSRS to PowerBI (strategic BI tool).
- Allowed teams to meet certain compliance requirements.
During discussions we also enumerated the known issues with the Azure SQL-MI as described here:
Overall, Azure SQL – MI seemed a good fit for customer requirements and just in case we later realize that a particular application is not compatible with Managed Instance we always have SQL-Servers on VM to fall back to. The above-recommended architecture ensured just that.