Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

SQL Server Data Warehousing and Transactional Workloads in Azure Virtual Machines

Updated: June 29, 2015

To use SQL Server for data warehousing or transactional workloads in an Azure Virtual Machine, we recommend using one of the pre-configured virtual machine images in the Azure Virtual Machines Gallery. These images have been optimized based on the recommendations in Performance Best Practices for SQL Server in Azure Virtual Machines.

This article focusses on running these workloads on Azure Virtual Machines (this is also known as Infrastructure-as-a-Service or IaaS). You can also run data warehousing and transactional workloads as a service in Azure. For more information, see Azure SQL Data Warehouse Preview and Azure SQL Database.

The following pre-configured VM images are available in the Azure VM Gallery:

Currently we support these images on VM instances that allow up to 16 data disks attached to provide the highest throughput (or aggregate bandwidth).These instances are Standard Tier A4, A7, A8, A9, D4, D13, D14, F3, G4, and G5 and Basic tier A4. Please refer to Virtual Machine Sizes in Azure for further details on the sizes and options.

Prior to September 2014, previous transactional and DW gallery images were available. However, those images required you to attach data disks to be usable. It is recommended to use the newer images above, because they are ready for use upon provisioning.

  1. Sign in to the Azure Management Portal.

  2. Click VIRTUAL MACHINE in the Azure menu items in the left pane.

  3. Click NEW in the bottom left corner, and then click COMPUTE, VIRTUAL MACHINE, and FROM GALLERY.

  4. On the Virtual machine image selection page, select one of the SQL Server for transactional or Data Warehousing images.

    Azure VM Gallery
  5. On the Virtual machine configuration page, in the SIZE option, choose from the supported sizes.

    Azure VM Gallery Configuration
    Only the Standard tier A4, A7, A8, A9, D4, D13, D14, G3, G4, and G5 and Basic Tier A4 are currently supported. Attempts to provision unsupported VM sizes will fail.

  6. Wait for the provisioning to finish. While waiting, you can see the provisioning status on the virtual machines page (as in the picture below). When the provisioning is finished, the status will be Running with a checkmark.

    Azure VM Gallery Status

You can also use the PowerShell Commandlet New-AzureQuickVM to create the VM. You must pass your cloud service name, VM name, image name, Admin user name and password, and similar information as parameters. A simple way is to obtain the image name is to use Get-AzureVMImage to list all the available VM images.

For example, the following PowerShell command returns the latest image that matches the image label SQL Server 2012 SP2 Enterprise Optimized for DataWarehousing Workloads on Windows Server 2012 R2 from the list in the previous section.

(Get-AzureVMImage | where {$_.Label -like "SQL Server 2012 SP2 Enterprise Optimized for DataWarehousing Workloads on Windows Server 2012 R2"} | sort PublishedDate -Descending)[0].ImageName

For more information about creating images with PowerShell, see Use Azure PowerShell to create and preconfigure Windows-based Virtual Machines.

The optimizations included in the images are based on the Performance Best Practices for SQL Server in Azure Virtual Machines. Specifically, the configuration for these images include the following optimizations.

If you are bringing your own license and creating a Data Warehousing or Transactional virtual machine from scratch, you can base your optimizations on the performance article and the example of the optimizations in the preconfigured gallery images below.


Number of data disks attached


Storage spaces

Two storage pools:

- 1 data pool with 12 data disks; fixed size 12 TB; Column = 12

- 1 log pool with 3 data disks; fixed size 3 TB; Column = 3

One data disk remaining for the user to attach and determine the usage.

DW: Stripe size = 256 KB

Transactional: Stripe size = 64 KB

Disk sizes, caching, allocation size

1 TB each, HostCache=None, NTFS Allocation Unit Size = 64KB


Startup Parameters

-T1117 to help keep data files the same size in case the database needs to autogrow

-T1118 to assist in tempdb scalability (For more information, see SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage.)

Recovery model

Transactional: No change

DW: Set to SIMPLE for model database using ALTER DATABASE

Setup default locations

Move the SQL Server error log and trace file directories to data disks

Default locations for databases

System databases moved to data disks.

The location for creating user databases changed to data disks.

Instant File Initialization


Lock pages in memory

Enabled (For more information, see Enable the Lock Pages in Memory Option (Windows).

  • Is there any price difference between the optimized images and the non-optimized ones?

    No. The optimized images follow the same pricing model (details here) with no additional cost. Note that a higher cost is associated with the larger VM instance sizes.

  • Any other performance fixes I should consider?

    Yes, consider applying relevant performance fixes for SQL Server:

  • How can I find more information on Storage Spaces?

    For further details on Storage Spaces, please refer to Storage Spaces Frequently Asked Questions (FAQ)

  • What is the difference between the new DW image and the previous one?

    The previous DW image requires customers to perform additional steps such as attaching the data disks after creating the VM while the new DW image is ready for use upon creation so it is available more quickly and has less chance of errors.

  • What if I need to use the previous DW image? Is there any way I can access it?

    The previous VM images are still available, just not directly accessible from the gallery. Instead, you can continue using Powershell commandlets. For instance, you can use Get-AzureVMImage to list out all the images and when you locate the previous DW image based on the description and publish date, you can use New-AzureVM to provision.

See Also

© 2015 Microsoft