Introduction
Migrating an on-premises SQL Server database is an important task and should be planned to perfection. An ideal migration methodology should be like the one shown in the following diagram:
Figure 2.1: Migration methodology
Finding the Migration Benefits
You should first analyze and find out the benefits of migrating an on-premises SQL database to an Azure SQL database. Migration involves a lot of time, effort, and cost, and it shouldn't be done just for the sake of having a cloud database.
Finding the Blockers
The next step is to find out the compatibility issues that may stop you from migrating to Azure SQL Database.
Selecting a Service Model
The next step is to find out whether the database will be deployed individually, in an elastic pool, or as a SQL managed database instance. This is important as the service model will affect the overall pricing, service tier, performance, and management of the Azure SQL Database.
Selecting a Service Tier
The next step is to find an appropriate service tier and performance level for Azure SQL Database. This is important as it will directly affect the performance of an Azure SQL Database. A too-low service tier will result in bad performance, and a too-high service tier will result in unnecessary cost.
Selecting the Main Region and Disaster Recovery Region
The next step is to find the main region and the disaster recovery region for your Azure SQL Database. It's advisable to have the database in a region that would provide fast connectivity to your users.
Selecting a Migration Tool
Microsoft provides various tools to automate database migration. You can also write PowerShell or C# scripts to automate the database migration process. Tool selection largely depends on the database's size and the downtime SLA.
Choosing Between Azure SQL Database and SQL Database Managed Instance
Azure SQL Database and SQL Database managed instance both offer the benefits of the Software-as-a-Service (SaaS) model, in which the user doesn't manage the underlying hardware, software upgrades, and operating system configuration. The user therefore saves on the administrative cost of managing the platform.
These two deployments (Azure SQL Database and SQL managed instance) provide additional services such as automated backups, Query Performance Insight, advanced data security, high availability, and disaster recovery. Each of the two deployments, therefore, provides a ready-to-use database for new or existing applications.
The two deployment options have common performance tiers, with Azure SQL Database now supporting the vCore pricing tiers.
With the two options each having similar sets of features, consider the following aspects when choosing between an Azure SQL database and a SQL managed database instance.
Features
As mentioned earlier in Lesson 1, Microsoft Azure SQL Database Primer, in the Introduction to Managed Instance section, SQL database managed instances provide near 100% surface area compatibility and support almost all of the on-premises SQL Server features.
On the other hand, Azure SQL Database doesn't support some of important on-premises features, such as Common Language Runtime (SQL CLR), global temporary tables, SQL Server Agent, cross-database queries, and log shipping (for a complete list, see the Unsupported Features section in Lesson 1, Microsoft Azure SQL Database Primer).
Therefore, if you are looking to use any of the features not supported in Azure SQL Database, you can opt for a SQL database managed instance.
An especially important feature to consider is cross-database queries. If you have an application with two or more databases that performs cross-database queries, it's better to opt for a SQL database managed instance.
Note
For a list of features not supported by SQL managed instance, please visit https://feedback.azure.com/forums/915676-sql-managed-instance.
Migration
A SQL database managed instance provides speedy migration with little to no downtime, as it's almost 100% compatible with on-premises SQL Server features.
As you prepare to migrate and work finding the migration issues, with a SQL database managed instance, there will be zero or minimal migration constraints compared to migration constraints with an Azure SQL Database.
Time to Develop and Market
Azure SQL Database provides fast database deployment for a team with limited database expertise and development and deployment time constraints. With DTU-based pricing, a team can easily provision an Azure SQL Database and start the application development. As the application takes shape and the database and scalability requirements become clearer, the Azure SQL Database can be easily scaled to a higher DTU-based pricing tier or a vCore pricing tier.
On the other hand, if a team migrates an existing application from on-premises SQL Server, a SQL database managed instance provides fast and easy cloud migration with minimal application changes being required.
When opting for a SQL database managed instance for new applications, you need to choose the compute and storage resources in the vCore pricing tier. If a team doesn't have database expertise or clear compute and storage requirements, a DTU-based pricing model proves to be the best fit.
Azure SQL Database Service Tiers
Azure SQL Database has two types of pricing models: DTU-based pricing tiers and the new vCore-based pricing tiers. A pricing model defines the size, performance, features, and most importantly, the cost of your solution.