Happy New Year to all of my blog readers.
2016 is going to be an exciting year as we wait for the newest releases of Azure Stack, Windows Server & System Center from Microsoft. In my new year post I’m going to share some happy news for all Azure Ninjas out there working on IaaS.
If someone asks me what Microsoft Product makes my Azure deployment most complex I’d answer SQL server. The reason is SQL server being a awesome product needs an extra DBA care. When you are provisioning a SQL server VM in Azure you need to think about IOPs, Connectivity, Backups, Security first hand and how to provide the same level of experience as of a on-premises data center.
Microsoft Azure team understood the pain us system administrators face when it comes to SQL server configuration and came up with a set of new configuration options for SQL VMs in Azure Resource Manager Deployment Model. In order to use the new configuration experience you need to create a VM in new Resource Manager deployment model and it supports any version of SQL server that Azure marketplace offers.
In the classic model in order to configure SQL server connectivity from on-premises using SQL Server Management Studio (SSMS) you had to first Remote Desktop to the VM, open the SQL Server port in Windows firewall, enable SQL Server Authentication, and to allow inbound connectivity had to create a Public Azure endpoint for the VM. The new experience allows you to do all of that in the portal itself during the provisioning time and you can select whether this SQL server can only be contact from the VM itself or within the Virtual Network.
Automated Patching & Backup
Another pain that IT Pros encounter with SQL server is patching. The new automated patching capability allows the administrators to define a maintenance window at their convenience during the VM provisioning itself. So if your customers need to take off the burden of patches for their SQL VM instances in Azure this is a life saver.
What about backup then? The new Automated backup feature allows administrators to automatically backup all databases in SQL Server and it is not enabled by default as different workloads can have different backup requirements. You can retain these backups up to 30 days and even encrypt them.
Be it on-premises or cloud the most important thing in SQL server instance is storage. Previously in Azure classic deployment model we had to attach the required number of data disks to provide the IOPs and throughput manually and stripe the SQL files across the disks or create a Storage Pool to divide the IOPs or throughput across them. The new deployment model has all of these included in to provisioning by enabling us to specify the required IOPs, throughput and VHD size within the allowable limit of the VM instance size. The cool thing is when you tweak these settings Azure automatically changes the number of data disks using Windows storage spaces. So you no longer have to worry about calculations.
Also you can select between any of the below three storage optimization method for your SQL VM depending on your workloads.
- General is the default setting and supports most workloads.
- Transactional processing optimizes the storage for traditional database OLTP workloads.
- Data warehousing optimizes the storage for analytic and reporting workloads.
For the automation geeks you can use the Azure Resource Manager templates to make it even more automated for larger deployments. Considering the amount of effort and time taken previously for SQL IaaS VM configuration in Azure the new deployment experience offers much more hassle free one time setup for SQL workloads.