There are two flavors of SQL Server in Azure: one is the traditional SQL Server platform hosted in Azure virtual machines, also described as Infrastructure as a Service (IaaS). The other is called Azure SQL Database, database-as-a-service or Platform as a Service (PaaS). there are several important differences in functionality and features, so let’s look at them:
|SQL Server (SaaS)||SQL Database (PaaS)|
|Hosted:||Virtual Machine||Azure Service|
|Functionality:||Full SQL Server functionality||Nearly Complete Database Engine, but no SSIS, SSRS, SSAS|
|Authentication:||Windows and SQL||SQL only|
|Number of Databases:||Unlimited||1|
|Size of Databases:||up to 16 TB||500 GB|
|HA/DR:||Managed by User||Automatic by default|
|RPO/RTO:||Depends on HA/DR strategy||Depending on service tier, can be in seconds!|
|Scalabilty:||Managed by User||automatic|
Compared with the traditional SQL Server IaaS, the SQL PaaS seems like limited in features: 1 database, max. 500 GB, etc. However, some of this is intentional and requires a change in the mindset to understand why these limitations exist in first place and what the benefit are.
The number of databases is limited to 1 simply because we are not building our own server but merely creating and using as much databases as we need. Each database can be assigned different level of resources – CPUs and RAM, depending on workload. Each database can be scaled up and down separately at any time, or they can be stopped when they are not needed, so that users pay only for what they really use. This is very different in IaaS where we need to provision a virtual machine powerful enough to support the most demanding databases even when others are small and rarely used.
The size of each database is currently limited to 500 GB but keeping them relatively small allows them to be easily replicated so that Azure can provide automatic High Availability (HA) and Data Recovery (DR) by default. For each of our SQL Databases, there are two replicas created behind the scenes and kept in sync at all times. In a case that the master database becomes unavailable for any reason, the platform redirects all connections to the next available replica and immediately creates another copy of the database so that we again have 2 replicas ready for automatic failover. In order to provide this in IaaS, we need to provision multiple VMs and implement and maintain complex HA/DR strategies based on AlwaysOn Availability Groups. We also need to pay for multiple Enterprise Edition licenses, Windows licenses and so on.
Backups in PaaS are executed automatically behind the scenes and user can request a point-in-time restore going back 7 to 35 days, depending on service tier. Users don’t have any influence on backup strategies and in fact they don’t need to worry about it at all. All they need to know is how to initiate a restore when they need it and that can be done either through GUI or with PowerShell.
Another interesting difference is in installing updates and service packs. In PaaS, users don’t do that at all, the system does that for them so they always work with the latest and greatest version of SQL. In future, new features will be made available in Azure even before official releases of new SQL Server versions.
In terms of functionality, one of important features that were missing in the SQL Database until a few days ago was Full-text search. This is now publicly available as preview and brings almost the full feature set that’s available in SQL 2014. When we say “Available as a preview”, it means that it’s still being worked on, may change any time and may be different from the version that will be “released” soon. Here’s one interesting characteristic of PaaS: new features can be released even before the development is completed, release cycles are very short so that user feedback is incorporated in the backlog, patches and improvements can be deployed at any time, etc.
If you are new to SQL Server in Azure, probably the most fascinating feature is that you can register and try it for free. You can create virtual machines and database, look around, change configuration, run queries, and then just delete them when you’re done. All that in your browser, there’s no waiting for anyone to build a server for you. Go and try it.