How to decide the right Azure SQL service?

When we decide to migrate an existing on-premises Microsoft SQL Server to Azure SQL services, there are three options, whichever option we choose, there is a definite cost savings that we can realize, and it can be up to 64%. The report “ The Economic Value of Migrating On-Premises SQL Server Instances to Microsoft Azure SQL Solutions “ summarizes the savings delivered.  The savings that can be realized and the proportionate effort that we should put into the migration varies based on the on-premises SQL Server application design and integration with other systems.

When migrating on-premises Microsoft SQL Server to Azure, three Azure SQL options include:

1. Azure SQL Virtual Machines (IaaS) or SQL Server on Azure VM
2. Azure SQL Managed Instance (PaaS)
3. Azure SQL Database (PaaS)

There are two primary questions which can help decide on the options and help determine the migration efforts.

Q1: Is the SQL Server a Data Warehouse application or a Transaction application?

If it’s a DW application then leveraging PaaS will be a challenge because SSIS, SSRS, SSAS, DQS, MDS are not available. If we choose PaaS SQL Services, then following are the options to migrate

• SSIS – Run as an Integration Runtime in Azure Data Factory or migrate SSIS to ADF and Data Flow
• SSRS – Migrate to Power BI using RDL Migration Tool, there are few restrictions like Linked Reports or Rebuild the reports as Power BI Dashboards or Power BI Paginated Reports.
• SSAS – Migrate to Azure Analysis Services, it supports only Tabular model. If there are multidimensional model, then we need redesign it on AAS.
• MDS and DQS – Both are not available; we must use platform like Profisee addressing the capability of MDS and DQS
• ML Services – This can run on Azure SQL Managed Instance (PaaS) but not on Azure SQL Database (PaaS)

If we need to modernize the overall platform, then Azure SQL Database (PaaS) with the above suggested migrations is the recommended option. If we must migrate without much impact, then Azure SQL Virtual Machines (IaaS) is the best option.

Q2: Does the SQL Server have integration with linked SQL databases, cross database querying?

If it’s not a DW application, the next thing to check is whether the existing SQL Server environment has linked databases, cross database querying integrations. From the PaaS options Azure SQL MI supports linked and cross database querying, it’s not supported by Azure SQL Database. Azure SQL MI will be the right choice to consider. Azure SQL MI also supports other features like dbmail but cannot access file system, any file type data must be brought into Blobstore.

If the SQL Server is not a DW application and it doesn’t have linked, cross db queries, dbmail, windows authentication then most effectively Azure SQL Database (PaaS) can be adopted. There are few things that will be require efforts like changes to any file references, alternate mechanism for dbmail and auditing.

There are couple of interesting options that can make the migration bit easier, they are Compatibility level and Replication.

• Compatibility level

Setting the right compatibility can help save major SQL code changes that are embedded within an application. Right from SQL Server 2008 we have compatibility level support from the PaaS and IaaS versions. The compatibility table provides clear details on the support provided by the Azure SQL services.

• Replication

Replication ensures data is in sync between the on-premises SQL Server and Azure SQL services. There are multiple ways replication can be achieved, Link feature for Azure SQL MI is one of the ready to use feature. The Link feature enables near real-time data replication from SQL Server to Azure SQL MI. It’s supported from SQL Server 2016 version onwards. Replicating and keeping data in sync also helps to offload some of the read-only capabilities like reporting to run on the Azure SQL.

Once we decide on the option, a comprehensive assessment of the SQL Server application will help in defining a precision migration plan.

How WinWire Can Help?

At WinWire, we have defined an offering WinSQLPro that builds on the Microsoft toolsets capabilities, automates reconciliation process, and helps enterprises migrate from on-premises SQL Server to an appropriate Azure SQL service rapidly at a lesser cost. Also, check out the SQL Server migration specific offer WinSQLPro from the Azure Marketplace. Contact us to know more.