Easier Deployment of Azure SQL VMs

It’s not that hard to install SQL Server, but rarely does a SQL Server deployment consist purely of an installation. There are challenges, such as designing storage, deploying networking, and configuring backup. Have you ever been in the situation where a developer asks you to restore a database, and it’s the first time that you’ve ever heard of that database, so there’s no backup to restore? I sure have, and it’s not fun! Microsoft is making it much easier for you to deploy SQL Server inside of Azure VMs in the Azure Portal. Read on to learn more.

A New Experience

When you choose to deploy a virtual machine with SQL Server 2014 and a Resource Manager deployment model, you’ll see the new SQL Server deployment and configuration experience. A new child blade, SQL Server Settings, is added to the Create Virtual Machine Blade, which lets you easily pre-configure SQL Server before the virtual machine is even deployed.
The goal here is to simplify common tasks and speed up the configuration. The result should be that non-DBAs, such as developers and IT pros, produce better installations of SQL Server.

Clicking SQL Server Settings will open up a new blade with new options to customize the SQL Server installation:

• SQL networking
• SQL authentication
• Storage configurations
• Automated patching of Windows Server and SQL Server
• Automated backup of SQL Server
• Azure Key Vault integration

SQL Connectivity

Microsoft says that:

In the past, configuring SQL Server connectivity was not trivial.

It’s true; you had to log into the machine, know where and how in SQL Server to configure the required port, and reconfigure the Windows firewall to allow SQL clients to connect to the database server.
If public access was required, then you would also need to configure an endpoint, which is fun with ARM load balancers right now because it is both complex and only supports PowerShell at the time of writing. The new experience makes things quite easy. You can choose a TCP port (1433 by default), configure authentication, and set what kinds of connections will be possible:

• Private: From within the virtual network, including private routed connections.
• Local: Only allowing connections from within the virtual machine.
• Public: Configure an endpoint to allow connections from the Internet. Make sure you secure this or every bot on the planet will own your database within hours! See SQL Server security and Azure Network Security Groups.

Storage Configurations

I’ve discussed how you can configure storage using multiple data disks to meet the requirements of heavy workloads, such as SQL Server. The new SQL experience makes this easier; you specify your requirements and workload, and Azure will configure the required data disks for you.

The workloads types are:

• General (default): Good for most workloads.
• Transactional: Storage is optimized for online transaction processing (OLTP).
• Data Warehousing: Optimized for reporting and analytics.

Note that your virtual machine specification will limit the performance and scalability profiles of the data disks.

Automated Patching

You can configure automated patching of your machine’s guest OS and SQL Server:

• Enable or disable
• Day of week
• Time of day
• When patching can be done

Note that Azure will use the virtual machine’s locale for determining the time.

Automated Backup

You can opt into automated backup of all of your SQL databases. This feature backs up databases to a normal Azure storage account — this is not performed by Azure Backup. You can retain up to 30 days of data to a storage account of your choice. You can choose to encrypt the data; a password to protect an Azure-generated and managed certificate, and this certificate is used to encrypt the backups.

You might also want to protect the entire virtual machine with the option of long-term retention using Azure Backup.

Azure Key Vault Integration

Azure Key Vault provides secure key management in the cloud using FIPS-validated hardware security modules (HSMs). Using this feature, you configure SQL Server to store encryption keys in Azure Key Vault.

Summary

You are presented with a summary before the virtual machine is created. For the below example, I selected a DS3 virtual machine that required 12800 IOPS and 6 TBS of storage, with a transactional processing (OLTP) workload. Note how the virtual machine is going to be created with 6 Premium Storage data disks.