Azure SQL Database Migration Performance
TLDR -> If you are like me, this section will be all your read:
- Use the P11 Service Tier or higher when you Migrate your database to Azure SQL DB.
- Once you have completed your migration, drop down the database to the tier you want to run at if you aren't interested in running at the higher tier. Don't forget this step!
If you want to know more :
First we need to make certain our database is compatible with Azure SQL Database. If you haven't done this yet, click the link here and review the options for testing compatibility of your database: https://azure.microsoft.com/en-us/documentation/articles/sql-database-cloud-migrate/
Migration Steps:
Once you pass all of the compatibility tests, you are ready to migrate to Azure SQL Database. The tool I used in testing was SQL Server Management Studio 2016, which includes the "Deploy a Database to Azure SQL Database" feature. This feature is a very painless experience and only requires that:
- You have an Azure Subscription
- You have already setup an Azure SQL Database Server.
This process will fail if the database you are attempting to migrate doesn't meet the functional requirements of Azure SQL DB (remember you should have tested this already).
There are other methods to migrate data to Azure SQL DB, but the premise of this blog holds that the Service Tier you are using for the new Azure SQL DB matters:
SSMS 2016: https://msdn.microsoft.com/en-us/library/mt238290.aspx
Steps to Migrate the DB: https://azure.microsoft.com/en-us/documentation/articles/sql-database-cloud-migrate-compatible-using-ssms-migration-wizard/
Let's speed things up!
The above documentation does a great job of walking you through the steps of how to migrate your database, but it leaves out key details on making the migration take less time. In my testing, I used a homegrown 1GB database. I was running Windows Server 2012 R2, SQL Server 2016 behind a 1Gbps/1Gbps symmetrical connection, which took bandwidth out of the equation. I wanted this to be a test purely looking at if the tier the database is created in makes a difference in migration performance.
Note: If you are working over a low bandwidth connection, it is possible that you won't be able to realize the full performance of the premium tiers. Monitor DTU utilization in the Azure Portal to see if you are pushing the limits.
Below you will find a screen shot of the details that you can change when doing a migration, using the "Deploy a database to Azure SQL Database" wizard. By default you will see the Standard, S2 Tier is chosen. As I stated above, I normally choose the P11 Service Tier for the migration, and then drop this down immediately after the migration completes to a lower tier:
Azure SQL Database Service Tiers: https://azure.microsoft.com/en-us/documentation/articles/sql-database-service-tiers/
Overall Results of Migration tests, all performed from the same originating Server (results may vary): 
As you can see from the results above, choosing a Premium service tier will give you the lowest time for migration. Even if you are planning to run this database in a Basic or Standard Tier, you are better off migrating into a higher Premium Series SQL DB and then lower the Service Tier once the migration has completed.
Below, you can see the Resource Utilization comparison between a P1 and P11 Migration. As you will notice, the P1 Tier was pushed to the limit while the P11 Tier was only using 50% of resources. Precisely the reason the P1 Tier takes longer, it is much easier to max out the resources in that tier.
P1 Service Tier Migration: 
P11 Service Tier Migration: 
***One item to note in the resource Utilization Graphs above is that the times appear lower than the total time. This difference is because we take time to "stage" the data on the originating server before migrating the database.
Final Reminder:
If you don't want to stay in (pay for) one of the top Service Tiers, make CERTAIN to change this down to the proper tier as soon as the migration has completed. This will avoid your subscription being charged at the top tier rates. You've been warned, don't forget this step! :) Now get out there and test running in Azure SQL Database!