Deploy SQL Server Business Intelligence in Windows Azure Virtual Machines
SQL Server Technical Article
Writer:Chuck Heinzelman |Senior Program Manager | Microsoft Azure SQLCAT
Contributors: Omer Boker
Technical Reviewers:Craig Guyer, Kay Unkroth, Lara Rubbulke, Alexei Khalyako, Mike Plumley, Yorihito Tada, Mark Perry, Buck Woody, Xin Jin, Rajinder Singh, Madhan Arumugam Ramakrishnan, Beth Inghram
Revision: 1.03
Published:August 2013
Applies to: SQL Server 2012 SP1 and Windows Azure
Summary:This document describes and walks you through the creation of a multiserver deployment of SQL Server Business Intelligence features, in a Windows Azure Virtual Machines environment. The document focuses on the use of Windows PowerShell scripts for each step of the configuration and deployment process.
Copyright
This document is provided “as-is”. Information and views expressed in this document, including URL and other Internet website references, may change without notice. You bear the risk of using it.
Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred.
This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes.
© 2013 Microsoft. All rights reserved.
1.Contents
1.Contents
2.Introduction
3.What Is Infrastructure as a Service (IaaS)?
4.Why Infrastructure as a Service (IaaS)?
5.Recommended Scenarios for BI on IaaS
6.Document Conventions
7.Getting Started with IaaS
7.1.Affinity Groups
7.2.Virtual Networks
7.2.1.Subnets
7.2.2.DNS
7.2.3.On-Premises Connectivity
7.3.Storage
7.3.1.Containers
7.3.2.Access Keys
7.4.Cloud Service
7.4.1.Deployments
7.4.2.Virtual IP Address
7.5.Endpoints
7.6.Virtual Machines
7.7.Availability Sets
7.8.Disks
7.9.Images
8.Prerequisites and Assumptions
9.Windows Azure PowerShell Commands
10.Non-Windows Azure PowerShell Commands
11.The Overall Environment
12.Overview of the Deployment Steps
13.Step 1: Configure the Windows Azure Environment
13.1.Create the Affinity Group
13.1.1.Validation
13.2.Network
13.2.1.Create the Virtual Network
13.2.2.Validation
13.2.3.Point-to-Site VPN
13.3.Cloud Service
13.3.1.Create the Cloud Service
13.3.2.Validation
13.4.Storage Account
13.4.1.Create the Storage Account
13.4.2.Validation
14.Step2: Deploy Active Directory Domain Services
14.1.First Domain Controller
14.1.1.Provision VM
14.1.2.Validation
14.1.3.Format Disks
14.1.4.Create Domain
14.1.5.Create Sites and Subnets
14.1.6.Remove Forwarder
14.2.Second Domain Controller
14.2.1.Provision VM
14.2.2.Format Disks
14.2.3.Create Domain Controller
14.2.4.Create Share
14.3.Service User Accounts
14.3.1.Create Service User Accounts
15.Step 3: Configure SQL Server Database Servers (SharePoint Back End)
15.1.First SQL Server Instance
15.1.1.Provision VM
15.2.Format Disks
15.3.Enable Clustering
15.4.Install SQL Server
15.5.Second SQL Server Instance
16.Step 4: Configure SQL Server PowerPivot Servers
16.1.First PowerPivot Server
16.1.1.Provision VM
16.1.2.Install SQL Server
16.2.Second PowerPivot Server
17.Step 5: Deploy the first SharePoint Application/Central Administration Server
17.1.SharePoint Image
17.1.1.Provision VM
17.1.2.Install SharePoint Prerequisites
17.1.3.Install SharePoint
17.1.4.Install Add-Ins
17.1.5.Install Updates
17.1.6.Sysprep
17.1.7.Capture Image
17.2.First SharePoint Server
17.2.1.Provision VM
17.2.2.Create New Farm
17.2.3.Add PowerPivot Solutions
17.2.4.Install PowerPivot Features
17.2.5.Configure Service Instance
17.2.6.Create PowerPivot Service Application
17.2.7.Create Default Web Application
17.2.8.Deploy Web Application Solution
17.2.9.Create Site Collection
17.2.10.Activate PowerPivot Feature
17.2.11.Start the Claims to Windows Token Service
17.2.12.Configure Secure Store Service
17.2.13.Configure Alternate Access Mappings
17.2.14.Install Reporting Services
17.2.15.Install Reporting Services Bits
17.2.16.Enable Reporting Services
17.2.17.Create Reporting Services Shared Service Application
17.2.18.Grant Reporting Services permissions
17.3.Section Validation
18.Step 6: Configure AlwaysOn Availability Groups
18.1.Create Cluster
18.1.1.Validation
18.2.Enable AlwaysOn Availability Groups
18.2.1.Validation
18.3.Create Availability Group
18.3.1.Validation
18.4.Enable High Availability in SharePoint
18.4.1.Validation
19.Step 7: Deploy SharePoint Web Front End Servers
19.1.Provision VM
19.1.1.Validation
19.2.Join SharePoint Farm
19.2.1.Validation
19.3.Import Certificate
19.3.1.Validation
19.4.Deploy PowerPivot Solutions
19.4.1.Validation
19.5.Configure Second SharePoint Web Front End
20.Step 8: Deploy Additional SharePoint Application/Central Administration Servers
20.1.Provision VM
20.1.1.Validation
20.2.Join SharePoint Farm
20.2.1.Validation
20.3.Configure Local Service Instances
20.3.1.Validation
20.4.Start the SharePoint Services
20.4.1.Validation
20.5.Deploy PowerPivot Solutions
20.5.1.Validation
20.6.Install Reporting Services Bits
20.6.1.Validation
20.7.Enable Reporting Services
20.7.1.Validation
21.Conclusion
21.1.For more information:
21.2.Feedback
2.Introduction
We’ve been getting more and more requests for guidance on running Business Intelligence (BI) workloads in Windows Azure Virtual Machines.This paper is a joint effort between the Microsoft SQL Server BI portion of the Windows Azure Customer Advisory Team and Microsoft's Israel Development Center. The deployment guidance in this document is based on customer experiences, customer feedback, and user research.
The environment outlined in this document works as a stand-alone environment that does not need to connect to an on-premises Active Directory domain. It emphasizes BI deployment techniques for Windows Azure Virtual Machines without going too deeply into individual BI technologies. The paper assumes that you already understand how to build BI environments in general and you now want to deploy a Microsoft SharePoint based BI environment in Windows Azure Virtual Machines. This document should serve as a starting point to build such a Windows Azure-based BI environment.
Although this paper describes the use of Windows PowerShell to build the environment, most of these tasks can also be accomplished through other tools, including the Windows Azure Management Portal, SQL Server Management Studio, and SharePoint Central Administration. The Windows PowerShell approach does not require the use of multiple tools and can easily be automated and repeated as needed.
3.What Is Infrastructure as a Service (IaaS)?
IaaS is any environment that enablesyou to host virtual machines (VMs) without having to build and maintain the hosting infrastructure yourself.Many corporate IT departments take advantage ofvirtualization environments to run theirworkloads.IaaSeliminates the need to install or maintain host servers,enablingyou to focus on your VMs rather than the infrastructure.In Windows Azure, IaaS is provided through the Windows Azure Virtual Machines service.
4.Why Infrastructure as a Service (IaaS)?
There are several reasons to run BI workloads on IaaS. Some reasons are related to the general benefits of an IaaS environment. Other reasons are derived from the combination of IaaS advantages with the characteristics of BI solutions:
- Seamless migration to the cloud. IaaS is the most accessible alternative for migrating BI workloads to the cloud, becauseIaaS is very similar to existing on-premises architectures.
- No physical infrastructure maintenance.Windows Azure takes care of physical infrastructure deployment and maintenance for you.
- IaaS solutions are flexible.Additional VMs can be created from predefined Windows Azure gallery images or from custom images. You can deploy additional servers to increase capacity dynamically.
- Reduced total cost of ownership (TCO). By accruing costsonlyfor consumed resources, required software SKUs, and actual resource usage time, IaaS can help to lower TCO. This is especially significant for short-lived projects, such as demos or proof-of-concept (POC) deployments.
5.Recommended Scenarios for BI on IaaS
IaaS is a good choice for the following scenarios:
- Demonstrations –IaaS is a convenient option whenyou need to demonstrate the new Microsoft BI stack. No hardware is required, and the deployed solution is available from everywhere. Also, cost is associated only with the time during which the solution is deployed.
- Proof of concept (POC) – IaaS can be used for creating a POC of a BI solution. Running the POC on IaaS enables trial and error of various architectures while avoiding the high costs associated with buying hardware. After the BI solution architecture is clear, it can be decided which hardware to buy, or even continue using IaaS for the production solution. Also, the Windows Azure gallery provides some building blocks that can save some work in comparison to the on-premises alternative, for example VM images that contain Operating Systems and so on.
- Development/Test/Lab/Training – Development and test environments commonly require an iterative build-and-try workflow. Training environmentsfrequently require several machines configured in a specific way for several days, and lab environments can be used for various explorationsand scenario testing. For all these cases, using IaaS is convenient because it provides maximum flexibility—different environments can be created quickly, and IaaS is easy to scale as needed. Also, as in previous scenarios, cost is minimized to the actual required resources.
6.Document Conventions
This section describes documentation conventions used in this paper.
Each implementation section starts with a description of the desired end result of that section.If you feel comfortable implementing the particular section without using the sample scripts included in the document, feel free to skip the scripted guidance and implement that section on your own.
Important: The implementation sections are progressively dependent on each other; later sections build on previous sections.You cannot complete sections later in the document if the previous sections have not been completed by either the scripts provided or manually by you.
Each implementation section concludes with steps and recommendations for how to verify that the steps for that section completed successfully.You should verify the results of each section before you proceed to the next section.
- Scripting Conventions – This document makes wide use ofWindows PowerShell script fragments.If the fragment contains a single command, the text leading up to it provides information on what the script does.If the fragment contains several commands, the text leading up to it provides information on what the fragment as a whole does, and comments (Windows PowerShell comments start with #) embedded in the fragment describe what is happening in more detail.
7.Getting Started with IaaS
This section goes over the topics and terminology specific to Windows Azure. The content in the section is not specific to BI, but if you are new to Windows Azure, it helps you understand the overall Windows Azure IaaS environment. If you are comfortable with these topics already, feel free to skip this section and move on to the architectural guidance.
Note that this information is believed to be accurate at the time of the paper’s publication.The Windows Azure environment is a live service that receives updates and improvements. For the most up-to-date information about Windows Azure, see the following:
- Windows Azure –
- Virtual Machines –
7.1.Affinity Groups
In Windows Azure, an affinity groupis a logical grouping of resources defined by the user. When storage and virtual machines are grouped into affinity groups, Windows Azure does all it can to locate these resources physically close together in the data center, which helps minimize latency. It is important to locate your virtual machines and the storage close together physically, because the virtual hard disks (VHDs) used by the virtual machines are stored as blobs in Windows Azure storage.
7.2.Virtual Networks
In Windows Azure, you can define one or more virtual networks within your subscription.A virtual network is private to you and the services that you deploy to it—VMs that are not a part of the virtual network cannot see into the virtual network.You can define a single address space or multiple address spaces within your virtual network, and you can divide those address spaces up into multiple subnets if you want—the flexibility is yours.
It is worth mentioning at this point that it is important to plan your virtual network carefully before you create resources. After resources (virtual machines in this case) are deployed to a network, most of the configuration settings on that network cannot be changed. The only way to change configuration settings after deployment is to deprovision the resources from the network, make your changes, and then add the resources back to the network. This is time-consuming, and it requires downtime of your applications and services while the work is being performed.
All IP addresses within Windows Azure Virtual Machines are assigned through Dynamic Host Configuration Protocol (DHCP) and remain assigned to the VM until the VM is deallocated. It is important that you do not change the IP address in your virtual machines. If you manually change the IP address, you may lose connectivity to the VM. Let Windows Azure provide the network settings for all of the machines that you deploy. For more information, see
7.2.1.Subnets
A virtual network in Windows Azure supports address space divided into multiple subnets. Routing between the subnets is automatically handled through Windows Azure, so VMs in one subnet on your virtual network are automatically able to see VMs in other subnets within the same virtual network. You can add more subnets to a virtual network after machines are deployed, but you cannot change the settings of existing subnets without removing all of the deployed resources.
Windows Azure uses some of the addresses in each subnet defined for its own internal purposes.Currently Windows Azure consumes three addresses from every subnet defined (which is why a /29 subnet is the smallest that you can define).When you planyour network, make sure that you take this information into account.You might need to use a larger subnet than you think to accommodate for the addresses that Windows Azure consumes.
Avoid making assumptions about IP address assignments. It is common for administrators to make the assumption that the first IP address that they receive in a subnet will be the fourth possible IP address. For example, for a 10.10.10.0/29 subnet, you might assume that the first IP address handed out is 10.10.10.4. Although that assumption is currently correct, the pattern of IP address assignment can change at any time without warning. The best bet here is to refrain from making assumptions about the IP address that your VM will receive.
7.2.2.DNS
Windows Azure provides a Domain Name System (DNS) server that allows you to access the Internet from your virtual machines. You can also provide your own DNS servers on your virtual network to handle name resolution within your own network. In the example deployment that we build in this paper, two DNS servers are defined, and they provide name resolution within the virtual network.
You can define the DNS servers in two places, at the virtual network level and at the Cloud Service level. DNS settings that are defined at the Cloud Service level apply to every machine deployed into that Cloud Service, regardless of what the virtual network-level settings are. DNS settings that are defined at the virtual network level apply to every machine deployed to that network, unless that machine is part of a Cloud Service that has custom DNS settings.
7.2.3.On-Premises Connectivity
Windows Azure Virtual Networks can be set up for on-premises connectivity in two different ways —point-to-site and site-to-site.Each method has its own purpose, and both can be used in the same virtual network.
Point-to-Site On-Premises Connectivity
Point-to-site connectivity is essentially a virtual private network (VPN) connection into your virtual network. Your machine gets an IP address on the virtual network, and you can then communicate with the machines via IP address or via names resolved by a DNS server, if you have configured one. This best way to think about point-to-site connectivity is like a corporate VPN. When you are off-site, you can use VPN software provided by your employer to connect to network resources at the office. This is the same basic technology that corporate VPNs use. The communication channel between your machine and the virtual network in Windows Azure is handled through certificates that you create and load rather than user names and passwords.
Site-to-Site On-Premises Connectivity
Site-to-site connectivity is different from point-to-site connectivity. Site-to-site connectivity is meant to be a permanent tunnel between your corporate environment and your virtual network in Windows Azure. If you set up site-to-site connectivity, your cloud-based virtual machines can actually be a part of your corporate domain structure.
7.3.Storage
Windows Azure Virtual Machines make extensive use of .vhd files, similar to what is used in on-premises Windows Server Hyper-V environment. You can have multiple distinct storage accounts within a single Windows Azure subscription, and you can spread files from VMs across multiple storage accounts. The .vhd files used by Windows Azure Virtual Machines are stored in Windows Azure Blob Storage as page blobs. This is an important distinction because there are two types of blobs in blob storage: page blobs and block blobs. For .vhd files, you use page blobs.
If you have existing machines that you want to host and run in Windows Azure Virtual Machines, you can upload the .vhd files to Windows Azure Blob Storage and create a virtual machine based on them. You can also upload a .vhd that includes a sysprepped virtual machine that can be used as an image to create multiple virtual machines. You can also create new .vhd by creating a new virtual machine ‘Disk’ in the management portal (or Windows PowerShell Add-AzureDatadisk).
7.3.1.Containers
Rather than folders, blob storage uses containers to store blobs.When you are working with virtual machines, a “vhds” container is automatically created for you.Containers have three different levels of security: Private, Public Container, and Public Blob.The “vhds” container that is created when you create virtual machines is created as Private.