One thing I wanted to touch on for a while now is the Azure SQL PaaS offerings. If you have SQL on premise then at present you manage the server, the database, and the application, but Microsoft want to be helpful and so they have offered to take on the management of the server and SQL and just let you make use of their database as service… ain’t that lovely.
Microsoft have even got a pretty picture to go with this – its all BLACK!
They do have some lovely graphics.
Azure SQL PaaS Offering
If you do decide to try Azure SQL there are quite a few different options to choose… firstly you need to decide whether you are going for single database, elastic pool, or the top of the range managed instance. These are pretty much what they sound like – a single database is well a single database, elastic pool is a fixed (but scalable) resource onto which you can place up to 500 databases, managed instance is a managed virtual machine running SQL dedicated to you. For most of the these you have a plethora of additional options, do you want to use standard tier or business critical, do you want to select the skew based on vCore or DTU, etc etc.
Feature Parity SQL Server to Azure SQL?
But critically – there is not full feature parity at this point in time with SQL server. Microsoft readily admit this and provide detailed information on which features are available where here – in essence single database and elastic pool have less feature parity than managed instance, but even managed instance does not have full feature parity.
Now – as I have said before I am no SQL guru and my purpose here isn’t to reveal my SQL ninja skills to the world… because I don’t have any. So I won’t be going into detail on the differences at all. My point is that for a large portion of on-premise Microsoft SQL databases the database structure, features used, and data types etc are defined by the supplier of the application that uses the database. You aren’t at liberty say to stop using a feature when you want to migrate an application to Azure because Azure doesn’t support the feature because then the application using the database might start misbehaving or at worst break and lose your data.
Microsoft’s Data Migration Assistant
What we need is something to check whether our databases will migrate to Azure without problems or not – enter Microsoft’s “Data Migration Assistant”… this nifty tool installs on a PC or Server and then scans all SQL server databases you point it at. Microsoft say that the tool will:
Assess on-premises SQL Server instance(s) migrating to Azure SQL database(s). The assessment workflow helps you to detect the following issues that can affect Azure SQL database migration and provides detailed guidance on how to resolve them.
· Migration blocking issues: DMA discovers the compatibility issues that block migrating on-prem SQL Server database(s)s to Azure SQL Database(s). It then provides recommendations to help customers remediate those issues.
· Partially or unsupported features: DMA detects partially or unsupported features that are currently in use at the source SQL Server. It then provides comprehensive set of recommendations, alternative approaches available in Azure and mitigating steps so that customers can plan ahead this effort into their migration projects.
So to me this is kind of a critical tool to run prior to considering migrations to Azure as it will enable you to make an informed decision on whether you will be able to use Azure SQL PaaS or will need an SQL server solution in Azure.
But what kind of things will this show when all an organisation’s MS SQL databases are scanned?
Firstly – it doesn’t record there being a problem per database but rather per record in the database so if you have 23k records and each one uses the same feature that is not supported in Azure then you will have 23k records of there being a “migration blocker” or “unsupported feature” that will need resolving before you can migrate that database into Azure.
“Hope for the best, plan for the worst”
Secondly – it would be great for the DMA tool to detect no migration blockers or unsupported features but you should plan on the assumption that if i) you have a reasonable number of databases containing thousands of records and ii) most of your databases are used by third party applications where you do not control the source code, you will receive a list containing many thousands of migration blockers and unsupported features and in all probably there won’t be a whole lot you can do about it.
Mitigating DMA declared “Migration Blockers” and “Unsupported Features”
But what specifically will DMA say is wrong and what does it suggest you do about it? Good question, in reports I have seen from DMA the below Migration Blockers came up thousands of times:
Problem: Azure SQL Database supports two types of authentication: i) SQL Authentication, which uses a username and password ii) Azure Active Directory Authentication, which uses identities managed by Azure Active Directory and is supported for managed and integrated domains.
What Action MS suggest is taken: Windows authentication (integrated security) is not supported in Azure SQL Database. Database users mapped to Windows logins not supported. Remove the reported unsupported users before migration and start using either SQL Authentication or Azure Active Directory Authentication after migrating to Azure SQL Database. Logins mapped to either certificate or asymmetric key are also not supported.
Problem: Constant expressions are allowed (and ignored) in the ORDER BY clause when the database compatibility mode is set to 80 or earlier. However, these expressions in the ORDER BY clause will cause the statement to fail when the database compatibility mode is set to 90 or later.
What Action MS suggest is taken: Before you change the database compatibility mode to 90 or later, modify statements that use constant expressions in the ORDER BY clause to use a column name or column alias, or a nonnegative integer representing the position of the name or alias in the select list.
Problem: Global temp objects are not supported in Azure SQL Database. If your database contains objects that reference global temp tables (which have names beginning with “##”), your migration will be blocked.
What Action MS suggest is taken: Consider using local temp tables or table variables, which are supported in Azure SQL Database, instead of global temp tables. It may require some re-engineering to achieve the same functionality.
Problem: One or more objects were found that contain unresolved references, which may block migration to Azure SQL Database.
What Action MS suggest is taken: Address the unresolved references reported in “Object details” section.
Problem: Queries or references using three- or four-part names not supported in Azure SQL Database. Three-part name format, [database_name].[schema_name].[object_name], is supported only when the database_name is the current database or the database_name is tempdb and the object_name starts with #.
What Action MS suggest is taken: Move the dependent datasets from other databases into the database that is being migrated. Migrate the dependent database(s) to Azure and use ‘Elastic Database Query’ functionality to query across Azure SQL databases.
Problem: SQL Server Service Broker provides native support for messaging and queuing applications in the SQL Server Database Engine.
What Action MS suggest is taken: “Service Broker feature is not supported in Azure SQL Database. You need to disable the Service Broker feature before migrating this database to Azure. In addition, you may also need to remove or stop the Service Broker endpoint in order to prevent messages from arriving in the SQL instance. Once the database has been migrated to Azure, you can look into Azure Service Bus functionality to implement a generic, cloud-based messaging system instead of Service Broker.”
There is one further category of migration blocker which comes up a lot but the recommendation is object specific:
Problem: These unsupported features may block migration to Azure SQL Database.
What Action MS suggest is taken:These unsupported features may block migration to Azure SQL Database platform. Review the “Impacted Objects” and “Object Details” sections for the specific object type, object and error details, fix the object and re-execute the assessment.
Unsupported object types listed include Broker Priority, Certificate, Contract, Message Type, Service, and Signatures etc.
Clearly the problem with a lot of these blockers is that the database structure, use of features or object types need to be changed in order to fix the issue and remove the blocker to the database migrating to Azure or working properly once in Azure but making these changes may be difficult and may cause the application using the database to not work properly.
So what then?
In conclusion then – if you are migrating from on-premise to Azure, Azure SQL may not be a viable option for your on-premise databases as it does not have feature parity with MS SQL server. This is especially true if you don’t own the source code and therefore can’t modify how the application queries its associated SQL database.