I recently had the need for testing out a setup program which installs database components, integration services packages and reporting services reports. Setting up a machine like this would be really quick if you have Hyper-V installed and a VHD already pre-created with a SQL Server image. What if you do not have that handy and need to carry out your testing. This is what Microsoft Azure Virtual Machines comes to the rescue.

I used my Azure subscription to create a virtual machine for my testing. In this blog post, I will walk you through the steps for setting up a SQL Server virtual machine for testing purposes!

The first thing that you will need is to create a storage account (see in Screenshot 1) from the Azure Management portal. I am using a Locally Redundant storage as I do not need a higher grade storage for my testing purposes. As the replication type suggests redundancy, what you get for this replication type is: data that is replicated three times within the same data center.

A storage account provides access to the Windows Azure Blob, Table, and Queue services within a geographic region. A storage account created on or after June 8, 2012, can contain 200 TB of data. Storage accounts created before June 8, 2012, can contain up to 100 TB of data.

Now that you have a storage account, let’s create the Virtual Machine. Creating a storage account is not mandatory as the wizard for creating a VM will create a randomly named storage account for you. However, I do not like have entities named with weird alphanumeric sequences which is why I chose to pre-create the storage account. When you attempt to create a new Virtual Machine, the Wizard will offer you a choice of picking a virtual machine from a Gallery (see Screenshot 2). The gallery has a pre-created set of images which you can use to create your virtual machine and can save you time from additional post-setup configurations.

When you click on the gallery, you will be spoilt for options which allows you to pick the type of Operation System and SQL Server version along with the editions. Note that each of the combinations comes with their own pricing (see pricing link in reference section below).

Since I needed to perform my testing on a SQL Server 2012 instance, I picked SQL Server 2012 SP1 Enterprise on Windows Server 2012.

Once the image is selected, you will be asked to provide the user name and machine for the Virtual Machine. I picked a Standard A3 size machine which gives me 4 cores and 7GB of memory. The next page is additional configuration for your Virtual Machine which allows you to specify the following:

1. Cloud Service Name – I chose one that is already being used by other virtual machines. Cloud Service DNS Name is the global DNS name that becomes part of the URI that is used to contact the virtual machine.

2. Storage Account – This is the account which was created earlier to host the VHD of the virtual machine.

3. Availability Set – I did not create one as I didn’t need this feature for my testing. An availability set is a group of virtual machines that are deployed across fault domains and update domains. An availability set makes sure that your application is not affected by single points of failure, like the network switch or the power unit of a rack of servers.

4. Endpoint – By default, two endpoints are created for Remote Desktop and PowerShell. I added a MSSQL endpoint as well. As a safety precaution, it is always recommended to use two different port numbers for your public and private ports.

The screenshot below summarizes the UI inputs explained above.

In the next page, I left defaults as is which was the installation of the VM Agent. The VM agent is used to install and manage extensions that help you interact with the virtual machine. Once I hit OK, the Wizard did it’s magic and voila I had a VM with SQL Server pre-installed on it. So what did you get in the image:

• Database Engine
• Analysis Services
• Integration Services
• Reporting Services (configured in Native mode)
• AlwaysOn Availability Groups are available in SQL Server 2012 (or later) but need additional configuration before they can be used.
• Replication
• Full-Text and Semantic Extractions for Search (Semantic Extractions in SQL Server 2012 or later only)
• Data Quality Services (SQL Server 2012 or later only)
• Master Data Services (SQL Server 2012 or later only), but requires additional configuration and components. To run Master Data Services in a virtual machine in Azure, install the Web Server (Internet Information Services) and Silverlight, and also configure Master Data Services by using Master Data Services Configuration Tool (MDSConfigTool.exe).
• PowerPivot for SharePoint is available (SQL Server 2012 or later only), but requires additional configuration and components (including SharePoint).
• Distributed Replay Client is available (SQL Server 2012 or later only), but not installed.
• All tools, including SQL Server Management Studio, SQL Server Configuration Manager, the Business Intelligence Development Studio, SQL Server Setup, and upgrade and migration tools, such as Data-tier applications (DAC), backup, restore, attach, and detach.
• Client Tools Connectivity, Client Tools SDK, and SQL Client Connectivity SDK.
• SQL Server Books Online, but requires configuration by using Help Viewer.

Along with that I also get 412GB of disk space, pre-configured into two logical drives! Once the machine is up and running, I needed to configure Reporting Services using Reporting Services Configuration Manager. So with minimal effort, I have the base virtual machine which can be used for my setup.

Reference:

Azure Storage Accounts

SQL Server Azure Virtual Machine pricing

Getting started with SQL Server Azure Virtual Machine

Provisioning a SQL Server Azure VM