Recommendation for Hosting SQL Server Databases in Azure
Question
You have 100 servers that run Windows Server 2012 R2 and host Microsoft SQL Server 2014 instances. The instances host databases that have the following characteristics:
-> Stored procedures are implemented by using CLR.
-> The largest database is currently 3 TB. None of the databases will ever exceed 4 TB.
You plan to move all the data from SQL Server to Azure.
You need to recommend a service to host the databases. The solution must meet the following requirements:
-> Whenever possible, minimize management overhead for the migrated databases.
-> Ensure that users can authenticate by using Azure Active Directory (Azure AD) credentials.
-> Minimize the number of database changes required to facilitate the migration.
What should you include in the recommendation?
Answers
Explanations
Click on the arrows to vote for the correct answer
A. B. C. D.B
SQL Managed Instance allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes. At the same time, SQL Managed Instance preserves all PaaS capabilities (automatic patching and version updates, automated backups, high availability) that drastically reduce management overhead and TCO.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instanceBased on the given requirements, the best option for hosting the databases would be Azure SQL Managed Instance (Option B).
Here are the reasons why:
Minimize management overhead - With Azure SQL Managed Instance, Microsoft takes care of the infrastructure, patching, and management of the database engine. This minimizes management overhead for the migrated databases.
Azure AD authentication - Azure SQL Managed Instance supports Azure AD authentication, which means users can authenticate using Azure AD credentials. This fulfills the requirement of allowing users to authenticate with Azure AD.
Minimize database changes - Azure SQL Managed Instance supports most of the features that are available in on-premises SQL Server, including CLR stored procedures. This means that minimal database changes would be required to facilitate the migration.
Size of the largest database - Azure SQL Managed Instance can support databases up to 8 TB in size, which is more than enough for the largest database of 3 TB in this scenario.
Azure SQL Database elastic pools (Option A) are a good option for hosting multiple databases with predictable usage patterns. However, it doesn't support CLR stored procedures, which are being used in this scenario. This would require significant changes to the database schema, which goes against the requirement of minimizing database changes.
Azure SQL Database single databases (Option C) are similar to Azure SQL Managed Instance, but they have some limitations such as a maximum database size of 4 TB. This means that the largest database in this scenario would not fit in a single database. Also, it does not support CLR stored procedures.
SQL Server 2016 on Azure virtual machines (Option D) is a viable option for hosting the databases, but it would require more management overhead than Azure SQL Managed Instance. Additionally, you would need to handle the infrastructure and patching of the database engine. This goes against the requirement of minimizing management overhead.
