MSSQL migration to Azure SQL managed instance TSQL differences

Question

Hi, We are planning to migrate SQL server 2008r2 running on Windows 2008r2 to Azure SQL managed instance. I have read Microsoft documentation some of TSQL like Always on availability group, CLR functions (with restriction) are not available in Azure SQL managed instance.
What options we can take to rectify this. We are also looking at considering hybrid deployments of Azure SQL (both IaaS and PaaS) on cloud.

Answer 1

Most of registry based applications are not supported PaaS SQL Database on Azure, you have to check the compatibility
Check this.
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15

Please do not forget to "Accept the answer" and Upvote on the post that helps you, this can be beneficial to other community members.
http://www.moamenhany.com

Answer 2

Hi @Sam - I want to follow up to you post with some additional information. Well, it's not necessarily additional in the context of what was previously provided as an answer. This information should have been provided to you as the answer and providing this information here.

The primary two docs you should be sourcing are the the following, as they cover the specific capabilities for Azure SQL Database Managed Instance:

• T-SQL differences between SQL Server & Azure SQL Managed Instance
• Azure SQL Managed Instance frequently asked questions (FAQ)

Because Managed Instance is essentially a bundled IaaS deployment (via ARM) provided as a PaaS service, it is helpful to understand the connectivity architecture with the VNET/Subnet and UDRs.

And finally, with regard to resource limits, the following document details the current resource limitations for the Managed Instance service and details the process for requesting a quota increase.

To address the specific concerns with regard to CLR and TDE functionality, here is that specific information:

• Functionalities include BULK INSERT/OPENROWSET, CLR, DBCC, distributed transactions, extended events, external libraries, filestream and FileTable, full-text Semantic Search, linked servers, PolyBase, Replication, RESTORE, Service Broker, stored procedures, functions, and triggers.
• LIMITATION of TDE: You can't execute BACKUP DATABASE ... WITH COPY_ONLY on a database that's encrypted with service-managed Transparent Data Encryption (TDE). Service-managed TDE forces backups to be encrypted with an internal TDE key. The key can't be exported, so you can't restore the backup. Use automatic backups and point-in-time restore, or use customer-managed (BYOK) TDE instead. You also can disable encryption on the database.

Managed Instance CLR Support

A SQL Managed Instance can't access file shares and Windows folders, so the following constraints apply:

Only CREATE ASSEMBLY FROM BINARY is supported. See CREATE ASSEMBLY FROM BINARY.
CREATE ASSEMBLY FROM FILE isn't supported. See CREATE ASSEMBLY FROM FILE.
ALTER ASSEMBLY can't reference files. See ALTER ASSEMBLY.

#Always On Availability Groups

• Always On availability groups: a high-availability and disaster-recovery solution