Using machine learning to tune your SQL database in Azure

I’m presently working on posting my insight in moving a recent app of mine from an on-premise (colocated server) server to the Azure cloud.  My app is a pretty typical (and OLD) ASP.NET app with a SQL Server database backend.  There was some interesting things I learned in moving the web app portion to Azure App Service, but I’ll save that for a later post…this one is about Azure SQL Server.

My database was actually a SQLExpress database that has been humming along for a while.  It’s also an older schema and a typical relational database system.  The first step for me was to ensure I could move my data before I moved the site…I wanted a full move to a cloud platform.  There are a few ways of migrating databases to Azure as noted in this blog post Differentiating Microsoft’s Database Migration Tools and Services.  Recently one of our Cloud Developer Advocates, Scott Cate, demonstrated the newest full migration strategy, Data Migration Service (DMS), at the Azure Red Shirt Dev Tour.  Because this isn’t generally available I didn’t want to use it and as well my database didn’t warrant the need for managed instance features.  So I went with the Data Migration Assistant tool.

First was to get the tool and install it on my source server.  Because this is an on-prem server I just logged in remotely (RDP) as an admin.  You can choose to first run an assessment, but for me I went crazy and just wanted to migrate (don’t worry, that actually runs an assessment first as well):

After connecting to my SQL db instance I select the database I want to migrate.

NOTE: Use the “trust server certificate” checkbox when doing this migration from local db or you will see some failures in trying to connect to Azure.

After this I need to choose the destination and I can either select an already-created Azure SQL database or create one within my Azure subscription.  This link will launch instructions on how to create a new Azure SQL database on your subscription using the Azure Portal.  You will want to select your server size, etc. based on your needs.  There is some pricing guidance on the selections to help you understand your cost.  After this, return to the tool, enter the server you just created (or already had) and authenticate using your credentials for the server.  Then choose which database is the target for the migration:

Then the next step will show you the assessment and flag things that may need attention.  You need to examine these to assess whether they will be impactful to your app and either accept the script or not.  Once done you have 2 more steps: Deploy Schema and then (assuming that was successful) Migrate Data.  For me, this was rather quick and it was done.  I verified the data and was good to go!

Post-migration Tuning

After deploying the database and site I made sure that on the database I turned on the Automatic Tuning feature provided to me as a service for hosting in Azure:

And then I went away.  Immediately after a few days I returned to see some automatic tuning being done and analyzed.  Azure had analyzed my database under real conditions and made recommendations to actually alter the database to improve performance.  This is then automatically applied if Azure determines it will benefit my performance.  Here were the recommendations:

And notice the determination of impact for one of them:

You’ll see that Azure’s machine learning was smart enough to realize that one of the recommendations wasn’t going to improve (in fact it assessed it would actually regress a query) and decided not to apply the initial tuning recommendation.  Pretty awesome.  Taking a look at my performance profile of the database you can tell very quickly when these recommendations were applied:

This is awesome.  I’ve got some tuning still to go, but thankfully Azure did all the hard work of helping me identify the performance bottlenecks of my database, suggest and analyze some automatic tuning it could do, but also still give me all the data I need to further analyze troublesome queries.

You can learn more about this feature in a recent Channel 9 video talking about this:

It really is an amazing feature and combined with the easy migration, I’m really excited about moving this app to Azure App Service + Azure SQL!

Hope this helps!