SQL Server configuration in Azure

You may have come across Performance Considerations for SQL Server in Azure Virtual Machines and wonder what the recommendations would look like in practice.
 
Below walks through most of the recommendations.
 
First, connect and log into the Azure portal.
image1
 
Create a storage location.
image2
 
Performance Check: Disable Azure geo-replication on the storage account.
Note: From a security perspective, it would be best not to use your company name.
image3
 
image4
 

Create a cloud service if you don’t have one already.

 
Performance Check: Keep the storage account and SQL Server VM in the same region.
image5
 
image6
 
Both the Storage and Cloud Service will be located in Southeast Asia.
image7
 
Create the SQL Server VM.
image8
 
image9
 
image10
 
Performance Check: Use minimum Standard Tier A2 for SQL Server VMs.
 
If you have purchased SQL Server core licences, you should typically use a 4 core VM as this is the minimum licences you can have per VM.
 
Note: By adding extra CPUs, the base virtual machine running costs increases.
 
image11
 
image12
 
Note: The Antimalware is still in preview at the time of this blog. This application may impact SQL Server performance if exclusions for the SQL Server files are not in place. If you find that the application impacts the server, it can easily be removed via the control panel in the VM, if required.
 
image13
The virtual machine will be provisioned and started.
 
Notice that the below VM is located in Southeast Asia as selected during the install.
image14
 

 

 

 

 

 

 

At the time of this blog, the A2 VM supports up to 4 data disks.
image15
Performance Check: Avoid using operating system or temporary disks for database storage or logging.
 
Note: Microsoft has stated ‘Locally redundant storage (LRS) is replicated three times within a single data center. When you write data to a blob, queue, or table, the write operation is performed synchronously across all three replicas. LRS protects your data from normal hardware failures.’, with this in mind, RAID level protection is not required.
image16
 
Performance Check: Avoid using Azure data disk caching options (caching policy = None).
image17
 

 

 

 

 

 

 

 

 

 

 

 

 

Repeat the above and add another 3 disks.
 
Note: If you try add more than 4 disks to the A2 VM, you’ll receive the following error:
image18
Below shows all 4 disks added.
 
Notice that the C drive uses Read/Write cache which is not recommended for SQL data/log files.
image19
 
Connect to the new VM.
image20

 

 

 

Performance Check: Stripe multiple Azure data disks to get increased IO throughput.
image21
 
 

 

 

image22
 
image23
 
 

 

 

 

 

 

 

 
image24

 

 

 

 

 

 

 

 

 

 

Microsoft notes: ‘For Standard tier A2, we recommend a configuration of three data disks for data files and tempdb, and one data disk for log files. Depending on whether your workload is data or log sensitive, you can adjust the number of disks dedicated to data or log storage pool accordingly.’
 
‘VMs allowing more than 4 disks to be attached, place the data and log files on separate disks or storage pools’.
 
For Production VMs with core licences, you would most likely use at least 4 CPU’s as that is the minimum licences allowed per VM and therefore would be able to assign more disks to the VM.
image25

 

 

 

 

 

 

 

 

 
image26

 

 

 

 

 

 

 

 

 

 

Performance Check: Format with documented allocation sizes.
 
Microsoft notes: ‘NTFS allocation unit size: When formatting the data disk, it is recommended that you use a 64-KB allocation unit size for data and log files as well as tempdb.’
image27

 

 

 

 

 

 

 

 

 
image28

 

 

 

 

 

 

 

 

 
image29

 

 

 

 

The F drive will now show.
 
Performance Check: Separate data and log file I/O paths to obtain dedicated IOPs for data and log.
image30

 

 

 

 

 

 

 

 

 

 
image31

 

 

 

 

 

 

 

 

 
image32
 
 

 

 

 

 

 

 

 

 
image33

 

 

 

 

 

 

 

 

 
image34

 

 

 

 

 

 

 

 

 
image35
 
 

 

 

 

 

 

 

 

 
image36
 
 

 

For this demo, I’ll use a local account called SQLService for the SQL Server service and SQLAgent for the SQL Server Agent service account. You’ll likely use a domain account instead for your environment.
 
Neither of these accounts are a member of the local Administrators group on the server.
 
Performance Check: Enable instant file initialization for data files.
 
Performance Check:  Enable locked pages.
 
Note: The SQL Server Agent service does not require these privileges.
image37
 
 

 

 

 
image38
 
I’ve downloaded SQL Server 2014 evaluation edition (http://technet.microsoft.com/en-US/evalcenter/dn205290) to go through the remaining steps.
 
Note: Internet Explorer is heavily locked down by default and you may need to disable ‘IE Enhanced Security Configuration’ to use it successfully.
image39
 
image40

 

 

 

 

 

 

 

 

 

 

 

During the install of SQL Server the following will be addressed:
 
Performance Check:  Setup default locations.
 
Performance Check:  Move all databases to data disks, including system databases.
 
Performance Check:  Move SQL Server error log and trace file directories to data disks.
 
As shown below the Shared features can remain on the C drive, but the Instance root directory should move the new Data drives.
image41
 
Notice the F drive below. This takes care of the SQL Server error log and trace file directories.
image42
 
As shown below only the log files will use the G drive:
 
Note: The Backup location should reflect your backup strategy.
image43
Once the install is finished:
 
Performance Check:  Apply SQL Server performance fixes.
 
The latest patch info with recommended patch levels can be found here
 
The following SQL database settings complete the check list:
 
Performance Check:  Limit or disable autogrow on the database.
image44
 
Performance Check:  Disable autoshrink on the database.
image45
 
 

 

 

 

 

 

 

 

Performance Check:  Consider enabling database page compression (Note: Available on Enterprise Edition only.)
image46
 
Or
 
image47

 

 

 

 

I would also suggest:
 
Review and set a minimum and maximum memory threshold (also for Reporting Services and Analysis services, if used).
 
The following shows the memory settings for an A2 instance which has 3.5GB and no Reporting/Analysis services.
image48

 

 

 

 

 

 

Enable backup compression by default.
image49
 
That should be it for now.
 

Anonymous

Hi,

Thanks for writing this up. Using the above we managed to move from a single disk to a stripe and have dramatically improved our SQL Server.

Thanks

Leave a Reply to Anonymous Cancel reply