March 4, 2015 Leave a comment
How do you choose the best deployment options for your SQL Server environment running in Azure Virtual Machines? Here are some tips which should hopefully help those that have chosen this well trodden route.
This has been written to help with key decision points for implementation when considering using VMs running in Azure to host SQL Server.
Why Run SQL Server In Azure Virtual Machines At All?
There are many reasons you may want to run SQL Server using VMs in Azure. A handful of reasons many organisations are choosing this approach are provided here :-
- You need some compute capacity which is not available on premises or is not available in a timely manner.
- You need full control of the SQL Server instance or require custom add-on tools or applications running on the server.
- You need to spin up a SQL Server instance for test / development purposes in a short timescale.
- You need to spin up a production environment in a short timescale!
- You want to pay as you use, and for what you use rather than invest up front in hardware and its implementation.
- SQL Azure (PaaS) may not provide the control or features you require such as Windows Authentication for example.
- You may want to use Azure as a disaster recovery location for your on premises SQL Servers.
- Depending on your current environment you may want a more reliable, secure and scalable platform to host your infrastructure.
SQL Server Azure Virtual Machine Deployment Options
Here are some options available for running SQL Server in virtual machines in Azure IaaS.
Azure Virtual Machine SQL Server Gallery Options
We can see below some of the SQL Server template options provided in the gallery as well as the MSDN option on the bottom left.
Choosing the Right Deployment Option
The first thing to do is review the SQL Server Edition requirements and related costs followed by purpose and timescales.
SQL Server Edition and Licensing
A feature comparison of the different Editions of SQL Server can be found here :-
|SQL Server 2014||https://msdn.microsoft.com/en-us/library/cc645993.aspx|
|SQL Server 2012||https://msdn.microsoft.com/en-us/library/cc645993(v=sql.110).aspx|
For example if you need the advanced BI features, OR AlwaysOn high availability , OR SSRS scale out farm capability you will need Enterprise Edition. Also note that SQL Server Standard Edition (2014) is limited to 128GB of RAM so if you need more than this look at Enterprise. These are just some feature examples.
Look at the number of users that will need a license and calculate the costs of CALS if using Standard or BI Edition.
Duration of Usage for the SQL Server VM/s
Long Term Production Use
If the environment is to be for production and long term, review the costs of bringing your own license with your license reseller, compared to using the pre-built templates using the Azure Calculator which can be found here :- http://azure.microsoft.com/en-us/pricing/calculator/?scenario=full
Short Term Use
If the environment is to be used for the short term OR if there is a preference for pay as you go then you may choose to use the template virtual machines which include the SQL Server licenses and are quick to provision. You may also choose this option if you do not want your team or yourself to install SQL Server as well as prefer to have Azure provide the SQL licensing so all costs are bundled into one billing provider.
Ok so by now hopefully you know which license model, SQL Edition and gallery image (or VHD upload) you require. You now need to ensure you have the correct configuration for the VM and its supporting infrastructure such as storage and networking. I’ll provide high level details here only.
This includes things like what type of network, network IP addressing, subnets, IP address assignments, DNS, security options, load balancing, geographic location etc. You will also need to consider VPN types and options if required. Full Azure network documentation is provided here for reference – http://azure.microsoft.com/en-gb/documentation/services/virtual-network/
Where will the storage be located geographically ? Storage container names, local or geo-replicated, container structure. Microsoft recommend local storage rather than geo- replicated storage for SQL Server for best performance.
Does your VM need to be highly available? If so then you will need two in an availability set. You will also need to architect database high availability using either Mirroring or AlwaysOn Availability Groups. Failover Clustering for SQL Server is also not yet supported.
An example of an availability set is shown below where essentially the SQL Server VMs run on different racks (fault domains) and different update domains. This ensures Microsoft provide a 99.95% uptime SLA on machines in the availability set.
If you need a high performance virtual machine then use the D Series (or even G Series) virtual machines in order to make use of the higher spec processors and SSD storage, as well as larger VM CPU and RAM options.
Tip: When creating your SQL Server use a D Series VM size to start with and then drop back to an A series after creation if you need to.
This allows you to easily switch between all D and A Series VMs without having to recreate the VM. By recreate, I mean removing the virtual machine and recreating it using existing disks. I don’t mean rebuilding the entire server/s.
Here is a table of the D Series VMs as an example. You can see the amount of (temporary!) SSD available with each size in the last column “Disk Sizes”. Taken from – http://azure.microsoft.com/en-gb/pricing/details/virtual-machines/
Follow the performance guidelines here which I will also cover in the near future with scripts and screenshots of the elements mentioned within it :- https://msdn.microsoft.com/en-us/library/azure/dn133149.aspx
Standard Versus Basic VMs
Azure Virtual Machines can be either Standard or Basic and the configuration can be changed at any time through the GUI or PowerShell (although the machine will shutdown and restart if changed).
Basic VMs are about 20% lower in cost but do not have the option to autoscale or load balance and also have limited lower spec VM sizes compared to standard. The other main difference is that the basic machine OS disks are limited to 300 IOPS whereas standard VMs are 500 IOPS. Basic VMs are perfect for small servers that do not require these features or disk performance.
- Here is the cost of an A3 Standard VM per month using PAYG prices as of 4th March 2015. The screen shots also show the available VM sizes in with each option (Standard or Basic).
- Here is a Basic VM cost for the same size server.
As you can see there are many factors and decision points to consider even after you have decided to use Azure Virtual Machines for SQL Server. I hope this post helps provide a structured method and some useful information for those about to carry out an implementation of SQL Server in Azure. Put a comment below on your deployment options and reasoning as it would be interesting to see other peoples decisions. Also if you feel I have missed anything that I can add to help others do let me know and I can update, and give full credit of course !
The next post will look at performance guidelines for SQL Server running in Azure Virtual Machines to make sure your SQL Servers are optimally configured. Until then. Farewell !