Why run SQL Server on premise when you can run it in the cloud?
Azure provides a convenient way to move an on premise SQL Server workload and applications to the cloud.
There are loads of reasons why you might want to run SQL Server in Azure instead of on premise. For one, organisations have access to many datacentres dotted around the world. Data held in Azure is secure and reliable. As well as this, SQL Server 2016 offers what Microsoft claim is a seamless transition between databases that are on premise, in a private cloud, and on Azure.
SQL VMs in the cloud
SQL Server 2016 can be run as a virtual machine (VM) on Azure. This means that a VM can be spun up quickly, stopped when it is not being used, and deleted when it is no longer needed.
There are a few ways that SQL Server 2016 can be run on Azure. First, Azure can be used to test and develop SQL Server applications before running them in a production environment. These development images come with a free license for testing and are only available on Azure. These also host light workloads of less than 1GB of memory and 10GB of storage. However, it is still priced per hour of usage.
It can also be run as a flexible, reliable and limitless off-site backup storage for SQL Server applications. This allows full backup with point-in-time restore directly through SQL Server Management Studio. Either on premise or Azure based VM instances of SQL databases can be backed up and can also be restored to an on premise datacentre or Azure VM.
But if running completely on the cloud is not possible, SQL Server 2016 also features a hybrid approach. One such feature is the SQL Server Stretch Database. This new Azure companion service enables customers to dynamically extend data from an on premise SQL Server infrastructure to Azure.
The Stretch Database service makes remote query processing possible by providing compute and storage in a way that’s completely transparent to the application. This will also work with Always Encrypted technology, meaning the database gets encrypted before it goes to Azure with the encryption key staying on premise.
The hybrid capabilities of SQL Server 2016 also support Transactional Replication to Azure SQL Database. This allows replication of data directly to Azure SQL Database and gives organisations a backup of their data in the cloud, should the worst happen.
Best practices for SQL Server in Azure VMs
To get the best performance from an SQL Server workload on Azure, it pays to optimise as fully as possible. This is because there are a number of factors that will determine how well such workloads run in the public cloud. These include virtual machine sizes and data disk configuration.
When provisioning VMs in the Azure portal, SQL Server VMs provisioned there with Resource Manager implement all best practices. These best practices can be summarised thus.
If you are running SQL Server Enterprise Edition, you will need a DS3 or higher VM size. For SQL Server Standard and Web Editions, the VM size can be DS2 or higher.
Microsoft also recommends Premium Storage for all production workloads as Standard Storage has varying latencies and bandwidth and is only recommended for dev/test workloads.
As far as disks are concerned, data disks should be used instead of operating system disks for performance sensitive applications. Also, user database files or user transaction log files should not be stored on temporary storage drives.
Microsoft also recommends using data disks for data and log files. At a minimum, two Premium Storage P30 disks should be used where one disk contains the log file(s) and the other contains the data file(s) and TempDB.
Database page compression should be used as it can help improve performance of I/O intensive workloads. But this can also increase CPU overhead on the database server.