This is the fifth post for the mini-series entitled “Hosting SQL Server in Window Azure IaaS Fundamentals”. In this post I will discuss the option of doing a manual install of SQL Serve on a previously provisioned Azure VM running Windows Server.
So why would you want to do a manual install of SQL Server on Azure IaaS when the Gallery VMs with pre-installed versions of SQL Server exist? Well, you might want to do a manual install of SQL Server if you require a different version of SQL Server than what is available within the Windows Azure VM Gallery. Or maybe you want to install SQL Server on your own due to the licensing approach you are taking where you want to bring your own license for SQL Server into Azure. Your company policy may dictate that you pay less per month in Azure costs and more $ up front using your own license.
Let’s discuss the process of creating an Azure IaaS environment where you can install SQL Server. Since will create the VM in an identical way as we did in previous examples in this course I will just quickly review the process here on this slide since we have already gone through it a few times so far.
Provision a VM with a Windows OS from the Azure Gallery. Choose a core Windows OS VM, one that is compatible with your version of SQL Server.
a. Once you provision the VM you may want to create an affinity group if one does not already exist. You may also want to create a named storage account rather than taking the garbled system-given storage account if you do not create one explicitly.
b. Create a new Cloud service, join the affinity group
c. Create the UID/PWD as you will need to RDP into the Azure VM using those credentials to install SQL Server
d. Expose TCP Port 1433 for SQL Server to be able to manage it remotely from SQL Server Management Studio (SSMS).
Now let’s talk about the best practice of creating non-OS disks for your database and error log files to support your SQL Server installation. You would do this to improve disk performance such that the main disk that runs SQL Server should not contest with logging and data for IOPS cycles. It makes sense to create independent disks to isolate that I/O performance and improve it independently.
Note this is a process that is not limited to the manual installation of SQL Server and we are choosing to demonstrate it in the module. You can, and should, use the separate disks for data and error files regardless of how you get SQL Server up and running in the cloud.
Once the VM is provisioned you will remote desktop into the Virtual machine and manually install SQL Server. The nice feature of this manual installation option is you can install whatever version of SQL Server you want to install and are not limited by what comes in the Gallery. Here are the most common options for installing SQL Server on the Azure VM. Your options for installing greatly increase if the VM to run SQL Server in part of a virtual network and connected to your on premises severs.
• Install from internet location or VNET UNC path
• Install free SQL Server trial version
• Download from MSDN subscription
• If on VNET install from connecting to UNC path and running SQL Setup
Installing SQL Server on Azure IaaS Is just a normal installation of SQL Server with a few minor exceptions that we will discuss.
Once you get SQL Server installed you will want to enable the TCP protocol for that database server via SQL Server Configuration Manager. You also will want to configure the error and data log files to point to the non-OS disks we will attach to the VM in the upcoming demo to maximize disk performance.
A key point when optimizing disk configuration for SQL Server on an IaaS VM is you should not use D: for tempdb. The tempdb is a temporary workspace for storing temporary tables, worktables that hold intermediate results during the sorting or query processing and materialized static cursors, which correspondingly increases the performance for SQL Server. Microsoft recommend that you place tempDB on the operating system disk or the data disk instead of the temporary disk (D:) for the following reasons.
1. Performance variance: You can get the same level of performance you get on D:, if not more IOPS from the operating system or a single data disk. However, the performance of D: drive is not guaranteed to be as predictable as the operating system or data disk. This is because the size of the D: drive and the performance you get from it depends on the size of the virtual machine you use.
2. Configuration upon VM downtime situation: If the virtual machine gets shutdown down (due to planned or unplanned reasons), in order for SQL Server to recreate the tempDB under the D: drive, the service account under which SQL Server service is started needs to have local administrator privileges. In addition, the common practice with on-premises SQL deployments is to keep database and log files (including tempDB) in a separate folder, in which case the folder needs to be created before SQL Server starts. For most customers, this extra re-configuration overhead is not worth the return.
3. Performance bottleneck: If you place tempdb on D: drive and your application workloads use tempDB heavily, this can cause performance bottleneck because the D: drive can introduce constraints in terms of IOPS throughput. Instead, place tempDB on the operating system or data disks to gain more flexibility.
A summary of the steps involved in creating and configuring non-OS disks to the SQL Server installation running on Azure IaaS are as follows.
1. Attach disks in Azure portal to our existing VM that has SQL Server already manually installed
2. RDP into the Azure SQL Server VM and Initialize them
3. Configure SQL Server to use those disks using SQL Server Manager
In this post I showed you how to configure an Azure IaaS VM for a manual install of SQL Server. We talked about provision first the core Windows Base OS from the Gallery without SQL Server. We then install SQL Server and attach disks to the Azure VM to hold the SQL Server data and log files. The D: drive is transient and its size and performance vary so SQL Server should not use that for tempDB.
Note that the best practice we showed in this post of creating alternative data/log disks for the SQL Server IaaS installation is not particular to the process of installing SQL Server on an Azure IaaS VM. Most of the best practices that apply to SQL Server VM on premises apply to SQL Server in the Azure IaaS VM Cloud as well.