Moving your MS SQL databases to Azure SQL might look daunting at first, especially the concerns around compatibility with your applications and security and to a greater extend the fear of anything new or unknown. To help you get started I’m going to give a quick overview of Azure SQL and share some of my experience using it thus far.
NOTE: Not all your applications will be able to run on SQL Azure it is not 100% compatible with on-site/boxed SQL server so you have to determine which of the incompatible features will be a blocker for your applications. That said the applications I moved to the cloud so far didn’t have any problems. The information in this post is from my own experience. There are so many factors impacting your specific database and application requirements that you’ll have to do a thorough analysis of azure SQL for your own applications and run some tests first, especially for very complex, data sensitive applications. For the remainder of the post, Azure SQL refers to Azure SQL Database V12 which is very close to SQL 2014 compatibility. Here is a link that lists the features that are not supported. Azure SQL is only the SQL database engine not SSRS, Analytics and other tools, you can still use SSRS from a boxed SQL instance and target your Azure database and there are other Azure products that like Data Factory to fill the gaps.
Firstly, why would you move to the cloud
- In most cases it is cheaper to use Azure SQL rather than running a VM with SQL server, take a look at the pricing.
- High availability, the SLA is 99,99% up time, if you feel brave go find out what you have to go through to get this with boxed SQL server. Azure keeps 3 copies of your data.
- Disaster recovery is much easier with standard or active geo-replication.
- It is easy to scale up, you just move to a better service tier and you can still scale out/shard using elastic scale, currently in preview only.
Service Tiers
Azure SQL comes in 3 service tiers Basic, Standard and Premium, the differences are in the transactions per second (DTU), backups and geo-replication options you get. To differentiate the performance levels the transactions per second allowed is expressed in DTUs. It is a synthetic benchmark taking into account disk I/O, CPU load etc and here is a tool to calculate the number of DTUs you need based on your current database load. When you reach your maximum DTUs you won’t get exceptions back from SQL but your queries will take longer and longer until they start to timeout.
- Basic tier is for very small apps < 2 GB, 5 DTUs (1 level) and daily retained backups for a week.
- Standard tier is for small/medium apps < 250GB, 10~100 DTUs (4 different levels) and point in time backups retained for 14 days.
- Premium tier is for apps < 500GB but there is one Premium tier for 1TB databases, 125~1750 DTUs (5 levels) and point in time backups retained for 35 days.
The point in time backups you get with all the tiers allow you to restore the database to any point in time covered by the backups. I didn’t experience it myself but I’ve seen some posts about very long restore times, so test first. If you are concerned about availability and the time it will take to recover rather use geo-replication instead of relying on backups alone.
- Basic tier does not offer geo-replication.
- Standard tier offers standard geo-replication, you have only one secondary database in a different datacentre and it is non-readable, charged at a discount to the primary since it is off-line. You have to initiate failover manually in the portal or via a script.
- Premium tier offers active geo-replication, you can have up to four readable secondaries which means you can run queries against them and use them as a means to scale our your read operations. You have to initiate failover manually in the portal or via a script.
Compatibility
- Azure SQL is binary compatible over the wire, you can connect to it from SQL Server Management Studio or your application just like you do for boxed SQL.
- You can use Visual Studio Database tools and Data Projects to connect to Azure SQL. This is handy to do a schema compare to make sure your database in Azure is identical to the local SQL Server one.
- You can export your Azure database as a DACPAC and import it into a local SQL Server and vice versa. Using SQL 2014 SSMS you can migrate your local database to Azure SQL.
In my next post I’ll show a quick demo on creating and connecting to an Azure SQL database and highlight some of the differences or surprises you might run into.
