This week is a shorter post than last week, but it answers a very important question:
Should I move my on-premises database to Azure SQL Database?
The short answer is:
It depends.
Firstly, why do you want to move your database to the cloud? Is it a business requirement? A better way to ask this is, does it help your business to move to Azure?
One of my clients runs a huge custom application, supported by SQL Server, running off a single 4GB database. They’ve been talking for years about moving to Azure or Amazon Web Services. Their current server is more than five years old, and the SQL Server version is 2008 Web Edition.
Moving to Azure SQL Database makes sense for them because they would benefit from new features and not have to worry about purchasing new hardware or new licences for Windows Server and SQL Server. Their developers can focus on improving the application and not worry about a database server going down.
This is probably the first main reason why people want to migrate.
Something to keep in mind, though, is that it’s a single database you’re migrating at a time. If you have a whole lot of databases that support different parts of your business, and you don’t want to run on-premises anymore, it makes more sense to investigate moving to Azure (or even Amazon or Rackspace) Virtual Machines instead. There, the cost of upgrading hardware is removed, but you can use the latest version of SQL Server as required, and everything else is the same.
Another main reason for moving to Azure SQL Database is when your database supports a website or web application.
Say you run a website with a very basic database structure, but you have a lot of users and need to store persistent data for a hugely popular mobile app.
It makes a lot of sense to move this kind of database to Azure SQL Database because it lets you forget about the administrative costs of maintaining a server, licences, operating system patches, SQL Server updates, backups, maintenance tasks, and all the rest that goes with it, and concentrate on your mobile app.
Another consideration is cost. You can save money in the short- to medium-term by moving to Azure SQL Database. Remember though, that after a certain number of years, you would be paying the same (and more) as purchasing new hardware and licences, and getting a DBA on speed dial. So for startups, Azure SQL Database make a lot of sense.
One final thing, that I cover in my new talk on Migrating to Azure SQL Database (insert plug here), is compatibility.
While Azure SQL Database supports most of the same features as SQL Server 2016, there are some things that could scuttle your plans from the get-go. It is very important to investigate those before jumping in and writing code.
For example, because we don’t have access to instance-level functionality, including master and tempdb databases, database collation is critically important to get right. Azure SQL Database, by default, uses SQL_Latin1_General_CP1_CI_AS collation. For starters, a difference in your database will cause performance issues and bad query plans because tempdb will be on a different collation. It goes downhill from there. Some applications require a specific collation to be able to work correctly.
Another limitation is that you are required to use Azure Active Directory or SQL authentication. Certain usernames are forbidden, which might require changes in your applications.
A short list of features not supported by Azure SQL Database can be found by visiting this link.
I hope this has been an informative read. Now that I’ve scared you away, next week we will finally get into the “how” of “how to migrate to Azure SQL Database”. Stay tuned.
If you would like to share your horror stories about failed migrations, find me on Twitter at @bornsql .