As developers, we love things that make our lives easier. We’d rather spend time developing, than we would maintaining servers and tending to our database and we want to be able to deploy our apps with the click of a button. That’s why we love Azure SQL Database – we can use all of the tools we’re already comfortable with and get the added benefit of Azure’s built-in intelligence services.
We’ve used Azure SQL Database on new apps, when starting from scratch, but hadn’t yet migrated an existing client to the platform. The goal was to essentially move an existing database from a dedicated VM with full SQL Server up to the cloud to be fully managed by Azure. But how can you be sure that Azure SQL Database is right for you?
THE PROS AND CONS OF MANAGED SQL
Azure SQL Database is the intelligent, fully-managed relational cloud database service built for developers. Accelerate app development and make maintenance easy and productive using the SQL tools you love to use. Take advantage of built-in intelligence that learns app patterns and adapts to maximize performance, reliability, and data protection.
From our experience, this is true. Right from the Azure dashboard we’re able to see Resource Health, change the Database size, see a performance overview, get performance recommendations and even turn on automatic tuning so recommendations are applied as necessary. An exhaustive list can be found here.
While there are tons of things to get excited about when evaluating Azure SQL Database, there are a few things to be aware of as well. First, only a subset of T-SQL is implemented. For example, features such as CLR, trace flags, and system tables are currently not supported (full list of differences). There’s also a cost involved that will vary depending on how much power you need. The pricing tiers are based on Database Transaction Units (DTUs) which are a measure of resources used (combination of CPU, Memory, I/O). In some cases you might find that you need to optimize your existing schema/queries before it is feasible to make the transition. More on that below.
THE MIGRATION PROCESS
We knew what needed to be done, but “how do we get there?” is the question. We could create our new Azure SQL Database and deploy our schema right from our existing database project, and then write a script to copy over all the data— but that sounds tedious and error-prone. Luckily, Microsoft provides a tool to do just that: the Data Migration Assistant.
Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server and Azure SQL Database. It recommends performance and reliability improvements for your target environment. It allows you to not only move your schema and data, but also uncontained objects from your source server to your target server.
—MSDN
ASSESSMENT
Using the DMA tool, you can hook up to and assess your existing database. This is where you’ll be made aware of any unsupported feature you might be using. It’ll also point out unresolved references as well. These are things you’ll want to address (you can always re-run your assessment to make sure everything passes) before moving onto the Migration phase.
MIGRATION
Now we’re ready to migrate our data. Make sure your new Azure SQL Database is created by this point. This is where you’ll use the DMA tool to specify the source & target database, select the objects/tables to create, deploy the new schema, and then migrate the actual data into the new database. Depending on the size of your tables and internet speed, this could take a few hours to run.
Once this is done, you should be able to point your application to the new database for a clean switchover! Now is a good time to run load tests against your new database so you can scale it appropriately and choose the best power tier for your app.
FINE TUNING
When testing your Azure SQL Database, you’ll want to pay attention to the performance recommendations. Finding & applying new indexes is a breeze. There’s also a Query Performance Insight dashboard that’ll show you which queries are consuming the most resources and which queries are running the longest. While Azure doesn’t offer to fix these for you automatically, it does show you where at in the script is your bottleneck. From there you can optimize – rinse and repeat.
FINAL THOUGHTS
Azure SQL Database provides a great way to optimize, scale, and manage your database. Worry less about uptime and deploy easier – you can spend more time developing.