The cloud has become an important option for businesses of all sizes today. Even server applications like SQL Server,...
that many organizations once considered on-premises only, have gradually been moving to the cloud. However, there are a couple of different options for implementing SQL Server cloud databases. For instance, if you're using Microsoft Azure, you can choose between running SQL Server in an Azure infrastructure as a service virtual machine or you can choose to use the platform as a service Azure SQL Database offering. Both of these different cloud offerings can provide enterprise level database support, but their characteristics, capabilities and costs are quite different.
Running SQL Server in an Azure IaaS VM is very similar to running SQL Server in an on-premises VM. The main difference is that Microsoft Azure provides the underlying infrastructure and hardware support. Creating and managing the VM, the Windows Server operating system, and the SQL Server instance are entirely your responsibilities. In essence, you are responsible for the database functionality. The PaaS Azure SQL Database service also provides relational database services that are based on the SQL Server relational database engine. Here Microsoft Azure provides the infrastructure as well as the database services. You don't need to manage any VMs, operating systems or even the SQL Server instance. Let's take a more in-depth look at each option.
SQL Server in an IaaS virtual machine
The SQL Server IaaS VM option is best suited for migrating existing applications and for setting up development and test scenarios. It provides full compatibility with on-premises SQL Server installations. In addition, you have full control of the configuration and management of the VM and Windows Server operating system, as well as the SQL Server configuration and management.
Azure IaaS VMs can support up to 32 cores and 448 GB for RAM. System Center Virtual Machine Manager can provide a single management interface that allows you to manage both your on-premises and cloud-based VMs. The migration wizard built into SQL Server Management Studio (SSMS) in Server 2014 makes it easy to move your databases from on-premises to your IaaS VM. The wizard will help you size the VM as well as move your schema and data to the Azure VM. High availability and disaster recovery for SQL Server IaaS VMs are supported using AlwaysOn Failover Clustering Instances, AlwaysOn Availability Groups, Database Mirroring and Log Shipping. In all these cases it's your responsibility to set up and configure the availability solution.
Like on-premises VMs, Azure IaaS VMs require licensing. You can use either a SQL Server image from the Azure VM marketplace and pay the per-minute rate for the SQL Server image, or you can upload your own SQL Server image using the license mobility benefits of software assurance.
Azure SQL Database
SQL Azure Database is often best for all new cloud-based applications. Azure SQL Database can take advantage of cloud scale, and it does not run inside a VM. Instead, it is a cloud service that can scale dynamically across multiple nodes during peak workloads as well as scaling down during periods of reduced demands.
Unlike SQL Server, running in an IaaS VM Azure SQL Database can provide built-in high availability without the need for building Failover Clustering Instances or AlwaysOn Availability Groups. High availability and geo-replication are part of the Azure SQL Database service and can be purchased as opt-in features.
Since Azure SQL Database runs as a PaaS, there's no need for creating VMs or installing the Windows Server operating system or SQL Server itself. The PaaS provides everything required to support the database services. There's also no patching, software updates or system maintenance that's required. However, it is important to realize that Azure SQL Database doesn't have full-feature parity with the latest versions of SQL Server like SQL Server 2014. Some of the notable omissions include Database Mail, the FILESTREAM data type and CLR integration with SQL Server. You can learn more about the implementation differences between Azure SQL Database and SQL Server 2014 at Azure SQL Database Transact-SQL differences.
You can use the SSMS Migration Wizard to move on-premises databases to Azure SQL Database. Before you do so, you need to ensure that the database is compatible with Azure SQL Database by using the Export Data-tier Application option from SSMS. When preparing to implement Azure SQL Database, it is a good idea to plan for some reserved capacity.
The Azure SQL Database is a shared service, and you don’t know when other database applications might need to consume additional resources from the Azure pool, which may result in unpredictable performance. To compensate for this possibility, Microsoft provides Basic, Standard and Premium subscription levels for Azure SQL Database. The Premium offer will deliver up to 1000 eDTUs per database and up to four active Geo-Replicas. EDTUs, or elastic Database Transaction Units, are the unit of measure the Microsoft uses to represent the performance of the database. A Basic database with 5 eDTUs is capable of five transactions per second. EDTUs can dynamically scale up and down according to demand, and they enable you to provision resources for the pool rather than for single database. This simplifies the management of multiple databases and provides a predictable budget for what might be an otherwise unpredictable workload. Azure SQL Database bills on an hourly rate based on the service tier and eDTUs.
IaaS vs PaaS
The choice boils down to compatibility and control verses flexibility and ease of management. The SQL Server Azure IaaS options give you full control and 100% compatibility with an on-premises SQL Server database. In contrast, the Azure SQL database PaaS option removes all of the management responsibilities and provides easy flexibility
How to price out Windows Azure SQL Database
Adopting Azure on the rise with challenges
Microsoft's IoT Hub takes on Azure Suite