SALES: 1-800-867-1380

Use PowerShell to Create an Azure VM With SQL Server BI and SharePoint 2010

Updated: October 8, 2013

This topic and the supporting Windows PowerShell Scripts walk you through creating an Azure Virtual Machine that contains Microsoft SQL Server Business Intelligence features and Microsoft SharePoint. The procedures and scripts assume that you have an Azure subscription, installation files for SQL Server 2012, and installation files for SharePoint 2010. The document includes a procedure to create virtual hard disks (.VHDs) to contain your installation files. For more information on requirements, see the Prerequisites section of this topic.

For similar information and scripts that use SharePoint 2013 and SQL Server 2012 SP1, see the following:

For similar content related to multiple Azure virtual machines and SharePoint 2013, see the whitepaper Deploy SQL Server Business Intelligence in Azure Virtual Machines (http://msdn.microsoft.com/en-us/library/dn321998.aspx).

Recommended Experience: The topic and scripts assume that you have some familiarly working with Windows PowerShell scripts and Azure subscriptions.

The following diagram illustrates the workflow of preparing your computer and running the Windows PowerShell Scripts:

worklfow of powershell scripts

 

In this topic:

Download Windows PowerShell Scripts from CodePlex

The Windows PowerShell Scripts and supporting text files can be downloaded from CodePlex. The script project does not include SQL Server or SharePoint installation files. See the Prerequisites section for other requirements.

PowerShell To Download:

  1. Browse to Use PowerShell to Create an Azure VM with SQL Server BI (http://go.microsoft.com/fwlink/p/?LinkId=301316).

  2. Download WA_BI_VM.zip.

  3. Extract all the files to C:\WA_BI_VM.

For related projects, see the main site page Azure SQL

Arrow icon used with Back to Top link Top

Prerequisites

 

Prerequisite Description

SQL Server and SharePoint Installation files

The scripts require you to supply installation files for the following:

The scripts assume the installation files you provide are uncompressed. The scripts do not function correctly with .iso image files.

Visual Studio 2012

The procedures require a management certificate. The Makecert.exe utility creates certificates and it is installed with Visual Studio 2012 and Visual Studio 2010. If you already have an X.509 certificate to upload to the Azure subscription, then you do not need Visual Studio.

Azure PowerShell

Version 0.6.13 or later is required to use the scripts in this project.

Install Azure PowerShell cmdlets from Command the line tools section of the following download page: Microsoft Azure Downloads (http://azure.microsoft.com/en-us/downloads/?fb=en-us).

Azure Subscription Core Quota

Azure subscriptions have a computing core quota. The CreateVM.ps1 script in this project creates a large virtual machine, which requires 4 cores. Before you begin using the scripts, verify that your subscription quota can support the additional 4 cores.

If your subscription does not have enough cores to run the scripts, the CreateVM.ps1 script fails with a message similar to the following:

  • New-AzureVM: Failed: The subscription policy limit for resource type 'cores count' was exceeded. The limit for resource type 'cores count' is 20 per subscription, the current count is 18, and the requested increment is 4.

For more information on virtual machine sizes, see the following:

Azure Subscription is enabled for the Virtual Machine service.

Verify which of the Azure locations available to your subscription, have access to the Azure Virtual Machine service. The location is a required parameter for the CreateVM.ps1 script. For more information on how to use Windows PowerShell to verify available services, see the section “Azure Geographical Location” in this topic.

If none of the locations show “PersistentVMRole” as an available service, see the “Geographic availability” section of Virtual Machines Pricing Details (http://azure.microsoft.com/en-us/pricing/details/virtual-machines/).

Azure Virtual Network

The scripts assume that you are using a subscription that does not already contain a virtual network. If The subscription you are using contains a virtual network that you cannot delete, see the Virtual Network Conflicts section in this topic.

Licensing

Arrow icon used with Back to Top link Top

Prepare the environment

The steps in this section prepare your local computer with the settings and files needed to complete the scripts.

In this section:

Management Certificate

This section walks you through the creation of a management certificate and uploading the certificate to your Azure subscription. If you do not have makecert.exe, see the section Create a certificate from Azure .publishsettings File

The following are points of interest when using Azure subscriptions and management certificates.

  1. You can upload more than one certificate for each subscription.

  2. You can use a single certificate on more than one subscription. Users should consider the security implications and potential surface area for security threats.

  3. A best practice is to create and use one certificate for each subscription, so a one to one relationship.

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Create Certificate

Create a X.509 certificate with the makecert.exe utility and upload the certificate to your Azure Subscription. Makecert.exe is installed with Visual Studio and with the Windows SDK. The following paths are the common locations to find Makecert.exe:

  • C:\Program Files\Windows Kits\8.0\bin\x86

    C:\Program Files\Windows Kits\8.0\bin\x64

To Create a Certificate:

  1. Create folder C:\Temp if it does not exist.

  2. Open a Visual Studio command prompt with administrative privileges, using one of the following procedures:

    • Windows 8 or Windows Server 2012

      1. From the Windows 8 Start screen, type Developer.

      2. Right-click the Developer Command Prompt for VS 2012 and click Run as administrator

    Or

    • Windows 7 or Windows Server 2008 R2

      1. Click Start and then click All Programs.

      2. Click Visual Studio and then click Visual Studio Tools.

      3. Click Visual Studio Command Prompt.

  3. Navigate to a directory where you want the certificate created. For example cd C:\temp

  4. To create a certificate with the name cloudbi, in a certificate store with the name My:

    makecert -r -pe -a sha1 -n "CN=cloudbi" –ss my-len 2048 -sp "Microsoft Enhanced RSA and AES Cryptographic Provider" -sy 24 cloudbi.cer
    
    If you see an error message regarding “Too many parameters”, verify the “-“ character pasted as the ‘minus’ sign. For more information, see the forum post MakeCert Error: Too many parameters(http://social.msdn.microsoft.com/Forums/vstudio/en-US/1e41910f-2f89-439c-93a6-57e5c391d7ca/makecert-error-too-many-parameters).

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Upload the Certificate

  1. Sign into the Azure Management Portal: Management portal (http://manage.windowsazure.com).

  2. In the SUBSCRIPTION dropdown list, ensure that the proper subscription is selected.

  3. In the left pane, click SETTINGS. settings

  4. In the top navigation menu, click MANAGEMENT CERTIFICATES.

  5. In the bottom navigation menu, click UPLOAD. upload to windows azure

  6. Click the FILE text box and then browse to C:\temp\cloudbi.cer, and then click Open. For example C:\temp\cloudbi.cer

  7. Click the OK checkbox. ok

  8. To see the certificate in the list, refresh the browser window.

Note: For information on how to use the certificate on another computer, see the section Use the Management Certificate on another computer.

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Create a certificate from Azure .publishsettings File

If you do not have access to the makecert.exe utility, you can use Azure PowerShell cmdlets to generate a certificate from your Azure subscription. For more information, see the following:

securitySecurity Note
The .publishsettings file contains your credentials (unencoded) that are used to administer your Azure subscriptions and services. A security best practice is to store the file temporarily outside your source directories (for example in the Libraries\Documents folder). Then,delete the file once the import is completed. A malicious user that gains access to the publishsettings file can edit, create, and delete your Azure services.

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Azure PowerShell

Location of Csupload.exe

The CSupload.exe utility transfers files to Azure Storage. The utility is installed with the Azure PowerShell environment or the Azure SDK. Verify the folder location for Csupload.exe, the default is the following: C:\Program Files\Microsoft SDKs\Azure\.NET SDK\2012-10\bin

If Csupload.exe is located in a different folder, update the path in the C:\WA_BI_VM\offbox\CreateVM.ps1 script.

For more information, see Command line tools.

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

File Location of the Azure module for Windows PowerShell Azure.psd1

Verify the location of Azure.psd1 on your local computer. Verify the path is correct in the Import-Module section of C:\WA_BI_VM\offbox\CreateVM.ps1 and C:\WA_BI_VM\offbox\Unmountdata.ps1. The following are typical paths for Azure.psd1:

C:\Program Files\Microsoft SDKs\Azure\PowerShell\Azure\Azure.psd1

Or

C:\Program Files (x86)\Microsoft SDKs\ Azure\PowerShell\Azure\Azure.psd1

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Execution Policy

The first time you run the Azure PowerShell command shell, run the following command to set the execution policy to RemoteSigned.

Set-ExecutionPolicy RemoteSigned

When you run Set-ExecutionPolicy, if you see an error message similar to the following, close the Azure PowerShell window. Reopen Azure PowerShell with administrative privileges.

  • Set-executionpolicy: Access to the registry key ‘Hkey_Local_Machine\Software\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell’ is denied.

To verify the current execution policy, run the following command:

Get-ExecutionPolicy

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Verify the Azure Geographical locations

The CreateVM.ps1 script uses the $Azurelocation parameter to supply the name of the Azure Location that hosts the Azure objects. For example the virtual machine and the virtual network. The location needs to support the PersistentVMRole as an AvailableService. For example, East Asia. If you are not sure what Azure location to use, you can edit and then run the Azure PowerShell script GetGalleryImageList_andLocationList.ps1. The script returns a list of locations and the supported services that are available to the current Azure subscription. Edit the script with your Azure subscription information.

For example, the GetGalleryImageList_andLocationList.ps1 script returns the following list. In this example, you update CreateVM.ps1 to use either “East Asia” or “Southeast Asia”.

Name             DisplayName      AvailableServices                   
----             -----------      -----------------                   
South Central US South Central US {Compute, Storage}                  
East Asia        East Asia        {Compute, Storage, PersistentVMRole}
Southeast Asia   Southeast Asia   {Compute, Storage, PersistentVMRole}

 

If none of the locations show PersistentVMRole as an available service, see the “What you Can Do” section of Azure Purchase Options (http://www.windowsazure.com/en-us/pricing/purchase-options/).

TipTip
For information on preparing the script GetGalleryImageList_andLocationList.ps1, see the Update Script Parameters section.

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Verify the Name of the Gallery Image to Use as the VM Base

The Windows PowerShell scripts in this project build an Azure virtual machine that runs Microsoft Windows 2008 R2 image. The image is in the Azure Virtual Machine gallery. The script CreateVM.ps1 builds the virtual machine based on a specific image name. The images available in the gallery change over time so verify the current image names available and update the CreateVM.ps1 script as needed. Run the Windows PowerShell script GetGalleryImageList_andLocationList.ps1 to get a list of the current Azure Virtual Machine Gallery images. Verify the ‘ImageName’ property of the Windows Server 2008 R2 image and update the $vmImageName parameter of the CreateVM.ps1 script.

For example, the GetGalleryImageList_andLocationList.ps1 script returns the following:

 

Label ImageName

Windows Server 2008 R2 SP1, March 2013

a699494373c04fc0bc8f2bb1389d6106__Win2K8R2SP1-Datacenter-201303.01-en.us-127GB.vhd

Therefore Update the CreateVM.ps1 script:

$vmImageName ="a699494373c04fc0bc8f2bb1389d6106__Win2K8R2SP1-Datacenter-201303.01-en.us-127GB.vhd"

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Unique Service Names

  1. Storage Account: The Azure Storage Account name must be unique within Azure. The name is the prefix of the storage DNS name, which can be used to access objects in the storage account.Update the CreateVM.ps1 Script with a storage account name that is unique.$storageAccountName = "azurebistorage"

    There is an Azure Service Management REST API you can use to verify the availability of a storage account name. For more information, see Check Storage Account Name Availability (http://msdn.microsoft.com/en-us/library/windowsazure/jj154125.aspx).

  2. Virtual Machine Name: The virtual machine service same must be unique within Azure. Update the CreateVM.ps1 script with a unique service name. Modify $vmServiceName = "IaaS-BI-onsharepoint"

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Virtual Network Conflicts

The scripts assume an azure subscription that contains zero Azure virtual networks. If the subscription you want to use already contains an Azure network, the existing Networkconfig.netcfg could create a conflict. There can be only one Networkconfig.netcfg for each Azure subscription. If you upload the Networkconfig.netcfg file per the steps in this document, Azure will first try to delete the existing virtual network. Therefore the CreateVM.ps1 script may modify the network environment of your subscription based on the following behavior:

  1. If your Azure subscription does not contain an existing virtual network, the CreateVM.ps1 script creates a new Virtual network that uses the NetworkConfig.netcfg file provided in the scripts project in the offbox folder.

  2. If a virtual network exists but the network is NOT associated with any Azure objects, CreateVM.ps1 will successfully delete the existing virtual network and then creates a network based on the configuration file NetworkConfig.netcfg.

  3. If a virtual network exists and it is associated with objects, CreateVM.ps1 will FAIL to delete the existing virtual network and the script will FAIL to create a network based on the configuration file NetworkConfig.netcfg.

    • If you want to preserve existing network information, you need to download the NetworkConfig.netcfg from your Azure subscription and then merge the <DnsServers> and <VirtualNetworkSite> sections with the NetworkConfig.netcfg provided with this project.

    • Copy the modified NetworkConfig.netcfg to C:\Temp.If you want to use a different folder, update the following entry in CreateVM.ps1.

      -ConfigurationPath "C:\Temp\NetworkConfig.netcfg"

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Update Script Parameters

Modify the following script parameters for your environment. Some of the parameters in the table below you modified as part of the other preparation steps. Modify the Scripts at C:\WA_BI_VM.

 

Script Parameter Description

GetGalleryImageList_andLocationList.ps1

$subscriptionID

This script is optional and retrieves information from your Azure subscription to help you populate parameters in the other scripts.

The subscription ID, subscription name, and thumbprint are listed on the Settings, Certificate page of Azure Management Portal.

GetGalleryImageList_andLocationList.ps1

$subscriptionName

Provide your subscription name.

GetGalleryImageList_andLocationList.ps1

$thumbPrint

Provide your certificate thumbprint. See the Settings, Certificate page of Azure Management Portal.

GetGalleryImageList_andLocationList.ps1

$certificate

The Script uses the “MY” store, that was specified with the -ss parameter on the MakeCert prepare step. If you use a different store, update the script.

------------

------------

------------

CreateVM.ps1

$subscriptionID

Provide your subscription ID

CreateVM.ps1

$subscriptionName

Provide your subscription name

CreateVM.ps1

$thumbPrint

Provide your certificate thumbprint

CreateVM.ps1

$Azurelocation

The geographic data center location where the virtual machine will be created.

CreateVM.ps1

$storageAccountName

This must be a unique name within Azure. To retrieve a list of locations available to your subscription, see script GetGalleryImageList_andLocationList.ps1.

CreateVM.ps1

$csuploadLocation

Validate the location of CSUpload.exe

CreateVM.ps1

$vmImageName

Required: Name of the VM gallery image to use for the base of the virtual machine. To retrieve a list of image names, see script GetGalleryImageList_andLocationList.ps1.

CreateVM.ps1

$vmServiceName

This must be a unique name.

CreateVM.ps1

$certificate

This script uses the “MY” certificate store, which is created with the -ss parameter on the MakeCert prepare step. If you use a different store, update the script.

------------

--------------

------------

OnBoxScript7.ps1

Update the three references to the cloud service name used in CreateVM.ps1. OnBoxScript7.ps1 configures an IIS certificate based on the cloud service name.

------------

--------------

------------

UnmountData.ps1

$subscriptionID

Use the same value that is used in CreateVM.ps1 script

UnmountData.ps1

$subscriptionName

Use the same value that is used in the CreateVM.ps1 script.

UnmountData.ps1

$thumbPrint

Use the same value as the one used in the CreateVM.ps1 script.

UnmountData.ps1

$certificate

Use the same value as the one used in the CreateVM.ps1 script.

UnmountData.ps1

$vmServiceName

Use the same value as the one used in the CreateVM.ps1 script.

UnmountData.ps1

$vmName

Use the same value as the one used in the CreateVM.ps1 script.

Arrow icon used with Back to Top link.Prepare the environment

Arrow icon used with Back to Top link Top

Build .VHDs that contain your SQL Server and SharePoint installation files

This section walks you through creating virtual hard disks that contain your SQL Server and SharePoint 2010 installation files.

The following steps work on Windows 7, Windows 8, Windows Server 2008 R2, Windows Server 2012.

Create and Mount .VHD as X Drive

To create a drive X, open a Command Prompt with administrative privileges and then run each of the following commands:

diskpart
create vdisk file=C:\BitsForCloud.vhd type=expandable maximum=130048
select vdisk file=C:\BitsForCloud.vhd
attach vdisk
create partition primary
format fs=ntfs label=”Bits for Cloud” quick
assign letter=X
exit

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Create folders on the X drive

Create the following folders on drive X:

  • X:\scripts

  • X:\bits

  • X:\bits\sharepoint2010

  • X:\bits\SharePoint2010SP1

      Note: If your SharePoint 2010 installation files include SharePoint 2010 Service Pack 1 (SP1), you do not need to create this folder.

  • X:\bits\sqlserver2012

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Copy installation and Script Files to the .VHD

Copy the installation files for SQL Server 2012 and SharePoint Server 2010 to the VHD file.

  1. Copy SharePoint 2010 server with SP1 to X:\Bits\SharePoint2010.

  2. If you have separate installation files for SharePoint 2010 SP1, copy the files to X:\bits\SharePoint2010SP1.

  3. SQL Server 2012 to X:\Bits\SQLServer2012.

  4. Copy the scripts folder C:\WA_BI_VM\OnBox to X:\Scripts, the resulting folder is X:\Scripts\onbox.

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Update Scripts with your SQL Server License Key

Add your SQL Server License key to the /PID parameter in the following scripts that install instances of SQL Sever. Update each instance of the /PID parameter. Add your key inside the double quotes. /PID Specifies the product key for the edition of SQL Server. If this parameter is not specified, Evaluation is used and the Evaluation license is not allowed on Azure Virtual Machines:

  • OnBoxScript_3.ps1: One instance.

  • OnBoxScript_4.ps1: Three instances.

For more information on using /PID, see Install SQL Server 2012 from the Command Prompt (http://msdn.microsoft.com/en-us/library/ms144259.aspx#Install).

Update the SharePoint config.xml Installation File with Your SharePoint License

  1. Edit the SharePoint silent installation file with your SharePoint license key: x:\bits\SharePoint2010\Files\SetupFarmSilent\config.xml

  2. Add your license key and remove the comment characters:

    <!--<PIDKEY Value="Enter Product Key Here" />-->

    To looks like the following

    <PIDKEY Value="your license key”/>

For more information on SharePoint command-line setup and the confg.xml file, see the following:

  1. Config.xml reference (SharePoint Server 2010) (http://technet.microsoft.com/en-us/library/cc261668(v=office.14).aspx).

  2. Setup command-line reference (SharePoint Server 2010) (http://technet.microsoft.com/en-us/library/cc262897(v=office.14).aspx).

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Update OnBoxScript_2.ps1 with SharePoint 2010 sp1 installation files

SQL Server 2012 requires SharePoint 2010 Service Pack1 (sp1). If your SharePoint 2010 installation files have not been slip streamed with SharePoint 2010 SP1, update the script OnBoxScript_2.ps1 to include the SP1 filename. The script includes a section at the bottom to run an SP1 install file. Remove the comment character “#” from the beginning of the line and verify the file name and path.

For example:officeserver2010sp1-kb2460045-x64-fullfile-en-us.exe

For more information on Slipstream installation of SharePoint SP1, see Slipstream SharePoint 2010 SP1 and Language Packs SP1 into RTM Builds (http://blogs.msdn.com/b/ronalg/archive/2011/07/11/slipstream-sharepoint-2010-sp1-and-language-packs-w-sp1-into-rtm.aspx).

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Detach the .vhd

The .VHD cannot be copied while it is attached. To detach the .VHD, open a command prompt with administrative privileges and run each of the following commands individually:

  • Diskpart
    Select vdisk file=C:\BitsForCloud.vhd
    detach vdisk
    Exit
    

Arrow icon used with Back to Top link Prepare the environment

Arrow icon used with Back to Top link Top

Step through the PowerShell Scripts to Build an Azure Virtual Machine running SQL Server BI features

This section walks you through the execution and verification of each Windows PowerShell script as well as recommended documentation.

Script Summary

The following table summarizes the scripts included with this project. The scripts are run from either your local computer and Azure PowerShell or from the Azure virtual machine.

 

Run from Script Name Description of script tasks

Local, Windows Azure PowerShell

GetGalleryImageList_andLocationList.ps1

This script is optional and it retrieves information from your Azure subscription to help you populate parameters in the other scripts. The script returns the following:

  • A list of locations and services available to your Azure subscription.

  • A current list of the Azure virtual machine gallery images.

Local, Windows Azure PowerShell Local

CreateVM.ps1

  • Creates an Azure Affinity group.

  • Creates an Azure virtual Network.

  • Creates an Azure Storage Account.

  • Uploads the .vhd files you created in the preparation steps.

  • Creates an Azure Virtual Machine

  • Attaches a data disk for a domain controller.

  • Attaches a data disk for SQL Server.

  • Creates an SSL EndPoint.

  • Secures the RDP Endpoint by changing the port# used for the public port.

On VM, Windows PowerShell

OnBoxScript_1.ps1

  • Partitions and formats the disks.

  • Formats Disk 2 and assigns drive letter Z.

  • Formats Disk 3 and assigns drive letter S.

  • Configures the virtual machine as a domain controller (DC).

  • Restarts the Virtual machine.

On VM, Windows PowerShell

OnBoxScript_2.ps1

  • Runs the SharePoint perequisite installer twice.

  • Installs SharePoint 2010, using the installation files you copied to the .VHD.

On VM, Windows PowerShell On VM

OnBoxScript_3.ps1

  • Creates Active Directory users.

  • Installs SQL Server Features: Database Engine, Reporting Services SharePoint mode, Reporting Services add-in for SharePoint, SQL Server Data Tools, Management Studio.

  • Creates several accounts for the SQL Server services and SharePoint.

On VM, Windows PowerShell

OnBoxScript_4.ps1

  • Installs three instances of SQL Server Analysis Services. The script runs SQL Server setup three times to install the following:

  • Analysis Services – Multi-Dimensional

  • Analysis Services – Tabular

  • PowerPivot

On VM, Windows PowerShell On VM

OnBoxScript_5.ps1

  • Core SharePoint Farm configuration, including SharePoint Central Administration.

  • Deploys the two PowerPivot solutions.

  • Installs the three PowerPivot SharePoint features.

On VM, Windows PowerShell

OnBoxScript_6.ps1

  • Creates PowertPivot Service application, creates web application, and deploys solution.

  • Creates a site and enables features.

  • Configures Secure Store Service.

  • Configures Excel Services.

On VM, Windows PowerShell

OnBoxScript_7.ps1

  • Creates an IIS certificate.

  • Binds the certificate to the site.

  • Creates Alternate Access Mapping for https.

On VM, Windows PowerShell On VM

OnBoxScript_8.ps1

  • Configures Reporting Services in SharePoint mode

Local, Windows Azure PowerShell

UnmountData.ps1

  • Unmounts the .VHD that contained the SQL Server and SharePoint installation files.

Arrow icon used with Back to Top link Script Summary

Arrow icon used with Back to Top link Top

CreateVM.ps1

This script completes the following tasks:

  1. Creates an Azure Affinity group.

  2. Creates an Azure virtual Network.

  3. Creates an Azure Storage Account.

  4. Uploads the .vhd files.

  5. Creates an Azure Virtual Machine

  6. Attaches a data disk for a Domain Controller.

  7. Attaches data disk for SQL Server.

  8. Creates an SSL EndPoint.

  9. Secures RDP Endpoint by changing the port# used for the public port.

WarningWarning
This script assumes that you are using a subscription that does not already contain an Azure virtual network. If your subscription does contain an existing virtual network that you cannot delete, see the section Virtual Network Conflicts in this topic.

Steps:

  1. Create folder c:\Temp if it does not exist.

  2. Copy C:\WA_BI_VM\OffBox\NetworkConfig.netcfg to C:\Temp. If you want to use a folder other than C:\Temp, update the -ConfigurationPath parameter in the CreateVM.ps1 script.

  3. Open Windows Azure PowerShell and run the following command:

    C:\WA_BI_VM\OffBox\CreateVM.ps1
    
  4. The script runs 5-15 minutes, depending on the speed of your internet connection. A majority of the script run time is due to copying the .VHD file to Azure.

    • Bits Disk: Does Not Exist...Creating

      Windows(R) Azure(TM) Upload Tool version 1.8.0.0

      for Microsoft(R) .NET Framework 3.5

      Copyright (c) Microsoft Corporation. All rights reserved.

      Using the saved connection string...

      MD5 hash is being calculated for the file 'C:\BitsForCloud.vhd'.

      Progressing: 45.4% complete; Remaining Time: 00:08:49; Throughput: 1073.4Mbps

    The script checks for the existence of Azure objects that have the same name as the objects created by the scripts. If the objects exist, the script skips the creation step and it writes a verification message to the Windows PowerShell window similar to Affinity Group Exists….Moving On.

    If your subscription does not have enough cores to run the scripts, the CreateVM.ps1 script fails with a message similar to the following:

    • New-AzureVM : Failed: The subscription policy limit for resource type 'cores count' was exceeded. The limit for resource type 'cores count' is 20 per subscription, the current count is 18, and the requested increment is 4.

Verification:

  1. Refresh the Azure Management portal and click All Items. The new virtual machine, storage account, and virtual network are listed.

    all items
  2. Connect to the new Virtual machine: In the Azure Management Portal, click the Virtual Machines icon.

    windows azure virtual machines
    1. Select the new Virtual machine Iaas-BI-Full and click the Connect icon on the bottom of the screen.

      connect to azure virtual machine
    2. Login as the testuser account and use the password "Testword!1" which is one of the parameters of the CreateVM.ps1 script.

    3. Verify the drive F:\ is attached and contains the F:\scripts folder and the F:\Bits folder.

  3. ImportantImportant
    It is recommended you run windows Update before running more scripts.

    1. In the Azure Management portal, connect to the virtual machine as the user Testuser with password Testword!1.

    2. Open Windows update in control panel.

    3. Click Check for update.

    4. After installing updates, you may need to restart the virtual machine and reconnect from the Azure Management portal.

For more information on Azure PowerShell cmdlets used by the CreateVM.ps1 script, see the following:

  • New-AzureVM (http://msdn.microsoft.com/en-us/library/windowsazure/jj152815.aspx).

  • New-AzureVMConfig (http://msdn.microsoft.com/en-us/library/windowsazure/jj152883.aspx).

Arrow icon used with Back to Top link Script Summary

Arrow icon used with Back to Top link Top

OnBoxScript_1.ps1

This script completes the following tasks:

  • Partitions and formats the disks.

  • Disk 2 is formatted and assigned drive Z.

  • Disk 3 is formatted and assigned drive letter S.

  • Configures the virtual machine as a domain controller (DC).

  • Restarts the virtual machine.

Steps:

  1. Browse to the Azure Management Portal and Connect to the Virtual Machine Iaas-BI-Full.

  2. Verify the following files are available on the virtual machine in the folder f:\Scripts\onbox.

    • DCPromoAnswer.txt. Note: this file includes the administrator password SafeModeAdminPassword=Testword!1.

    • DiskpartUnattended.txt

  3. On the virtual machine, open Windows PowerShell with the System Modules. For more information, see the section Import System Modules in PowerShell.

  4. To verify the current execution policy, run the following command:

    Get-ExecutionPolicy
    
  5. If the returned value is Restricted, run the following command to change the configuration:

    Set-ExecutionPolicy RemoteSigned
    
  6. Type Y to confirm the execution policy change.

  7. Run the following command

    F:\scripts\OnBox\onboxScript_1.ps1
    

When the script completes, the virtual machine restarts and therefore the remote desktop window closes.

Verification: To verify the script succeeded:

  1. In the Azure Management portal, connect to the virtual machine and login as the user Testuser with password Testword!1.

  2. To verify the Domain controller, run the System application in Windows Control Panel and verify the Domain is IaaS-BI.local. The domain name was supplied by f:\scripts\OnBoxf\DCPromoAnswer.txt.

  3. For a more detailed method to verify the domain controller, open a command prompt with administrator privileges and run dcdiag.

  4. ImportantImportant
    It is recommended you run windows Update before running more scripts. Windows update may require the virtual machine to reboot after updates are installed.

    1. In the Azure Management portal, connect to the virtual machine as the user Testuser with password Testword!1.

    2. Open Windows update in control panel.

    3. Click Check for update.

    4. After installing updates, you may need to restart the virtual machine and reconnect from the Azure Management portal.

For more information on unattended DCpromo, see How to use unattended mode to install and remove Active Directory Domain Services on Windows Server 2008-based domain controllers (http://support.microsoft.com/kb/947034).

Arrow icon used with Back to Top link Script Summary

Arrow icon used with Back to Top link Top

OnBoxScript_2.ps1

This script completes the following tasks:

  • Runs the SharePoint prequisite installer, twice.

  • Installs SharePoint 2010, using the installation files you copied to the .VHD.

  • Installs SharePoint 2010 SP1, if you edited the script in the preparation steps.

  • Other scripts in this series configure the SharePoint farm and service applications.

Steps:

  1. In the Azure Management portal, connect to the virtual machine and login as the user Testuser with password Testword!1.

  2. WarningWarning
    Verify your SharePoint license key is in the SharePoint f:\bits\SharePoint2010\Files\SetupFarmSilent\config.xml configuration file. For more information, see the section Update the SharePoint config.xml Installation File with Your SharePoint License.

  3. On the virtual machine, open Windows PowerShell with the System Modules.

  4. Run the following command:

    F:\scripts\onbox\OnBoxScript_2.ps1
    
  5. The SharePoint Products Preparation tool opens and begins running. The SharePoint product Preparation tool runs twice and the script writes to the PowerShell console as it progresses:

    • PS C:\Users\testuser> F:\scripts\onbox\OnBoxScript_2.ps1

      Run SharePoint PrerequisiteInstaller - first

      >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

      Run SharePoint PrerequisiteInstaller - second

      >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  6. After the Prerequisite installer completes, the script installs SharePoint. The SharePoint installation is a silent installation and there are no dialogs visible. The SharePoint installation process takes 10-15 minutes. The PowerShell script writes a message to the PowerShell console when the installation is complete

    • Install SharePoint files

      >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

      4/24/2013 11:05:45 PM

      SharePoint file are installed but not yet configured

      4/24/2013 11:13:06 PM

  7. If you updated OnBoxScript_2.ps1 to install SharePoint 2010 SP1, it will install in silent mode after the SharePoint installation is complete. For more information, see the section Update OnBoxScript_2.ps1 with SharePoint 2010 sp1 installation files.

  8. Note: Restart the virtual Machine before running additional scripts.

Verification: To verify the script succeeded:

  1. Verify in Start, All programs you see a Microsoft SharePoint Products group.

  2. To trouble shoot SharePoint installation issues, see the SharePoint installation logs files. The location of the log files depends on the Logging Type setting in the following configuration file:

    • F:\bits\SharePoint2010\Files\SetupFarmSilent\config.xml

    The default value for Logging Type is the following:

    • <Logging Type="verbose" Path="%temp%" Template="SharePoint Server Setup(*).log"/>

    Which resolves to the path of: C:\Users\<User Name>\AppData\Local\Temp\2.

    Note:AppData is a hidden folder by default.

Arrow icon used with Back to Top link Script Summary

Arrow icon used with Back to Top link Top

OnBoxScript_3.ps1

Your SQL Server license needs to be in the script file. Editing the script was a step in the preparation section. If have not added your SQL Server key to the /PID=, see Update Scripts with your SQL Server License Key. This script completes the following tasks:

  • Creates Active Directory (AD) users. Creates the following accounts for the SQL Services and SharePoint. The accounts are all created with the same password of Testword!1.

    • SQL_Engine

    • SQL_Agent

    • SQL_Reporting

    • SQL_AS_MD

    • SQL_AS_Tabular

    • SQL_PowerPivot

    • SP_Farm

    • SP_Root

  • Installs the following SQL Server Features:

    • Database Engine

    • Reporting Services SharePoint mode

    • Reporting Services add-in for SharePoint

    • SQL Server Data Tools

    • SQL Server Management Studio

Steps:

  1. In the Azure Management portal, connect to the virtual machine and login as the user Testuser with password Testword!1.

  2. On the virtual machine, open Windows PowerShell with the System Modules.

  3. Run the following command

    F:\scripts\onbox\OnBoxScript_3.ps1
    
  4. The Script will run 10-15 minutes.

    For information on where to find and view SQL Server Setup log files, see View and Read SQL Server Setup Log Files (http://msdn.microsoft.com/en-us/library/ms143702.aspx).

  5. ImportantImportant
    Note: Restart the virtual machine and run Windows update before running additional scripts.

Verification:

SQL Server: To verify the script successfully installed the SQL Server components, complete the following.

  1. Click Start and then click All Programs.

  2. Click Microsoft SQL Server 2012 and then click Configuration Tools.

  3. Click SQL Server Configuration Manager.

  4. In the SQL Server Configuration Manager, in the left pane, click SQL Server Services. Note the SQL Server (SharePoint) and SQL Server Agent (SharePoint) instances are installed and running.

Accounts: To verify the script successfully created the accounts, complete the following.

  1. On the virtual machine, open a command prompt with administrative privileges.

  2. Run the following command to see a list of logins:

    Net User
    
    • C:\Users\testuser>Net User

      User accounts for \\IAAS-BI-FULL

      ----------------------------------------------------------------

      Guest krbtgt SP_Farm

      SP_Root SQL_Agent SQL_AS_MD

      SQL_AS_Tabular SQL_Engine SQL_PowerPivot

      SQL_Reporting testuser

  3. Or on the virtual machine, from a Windows PowerShell window opened with system modules, run the following:

    Get-Aduser –filter *
    

Arrow icon used with Back to Top link Script Summary

Arrow icon used with Back to Top link Top

OnBoxScript_4.ps1

Your SQL Server license needs to be in the script file. Editing the script was a step in the preparation section. If you have not added your SQL Server key to the /PID=, see Update Scripts with your SQL Server License Key. This script completes the following tasks:

  • Runs SQL setup three times to install the following three instances of Analysis Services:

    • Analysis Services – Multi Dimensional

    • Analysis Services – Tabular

    • PowerPivot

If you do not plan to use Analysis Services Multi-Dimensional mode or Analysis Services Tabular mode, you can comment out the appropriate lines by adding # to the beginning of the lines (.\setup.exe) in the OnBoxScript_4.ps1 script. PowerPivot is required for later scripts that configure the farm, PowerPivot, and Reporting Services SharePoint mode.

 

Note: The SQL Server setup command lines used by the OnBoxScript_4.ps1 script, disables error reporting with the parameter /ERRORREPORTING=0. Modify the values if you need to trouble shoot installation. Supported values for /ERRORREPORTING=0 1=enabled or 0=disabled. You may also find the parameter /INDICATEPROGRESS useful. /INDICATEPROGRESS configures setup to pipe the verbose setup log file to the console. For more information, see Install SQL Server 2012 from the Command Prompt (http://technet.microsoft.com/en-us/library/ms144259.aspx).

 

Steps:

  1. In the Azure Management portal, connect to the virtual machine and login as the user Testuser with password Testword!1.

    On the virtual machine, open Windows PowerShell with the System Modules.

  2. Run the following command:

    F:\scripts\onbox\OnBoxScript_4.ps1
    
  3. noteNote
    Before you run the next script, close and reopen the PowerShell window. The PowerPivot cmdlets added by the OnBoxScript_4.ps1 script are not available until the PowerShell environment is restarted.

Verification: To verify the script succeeded:

  1. Click Start and then click All Programs.

  2. Click Microsoft SQL Server 2012 and then click Configuration Tools.

  3. Click SQL Server Configuration Manager.

  4. In the SQL Server Configuration Manager, in the left pane, click SQL Server Services. Note the three instances of Analysis Services are installed and running.

    sql services installed

Arrow icon used with Back to Top link Script Summary

Arrow icon used with Back to Top link Top

OnBoxScript_5.ps1

This script completes the following tasks:

  • Core SharePoint Farm configuration including the SharePoint Central Administration site.

  • Deploys the PowerPivot farm solution and the PowerPivot application solution.

  • Installs the three PowerPivot SharePoint features.

Steps:

  1. On the virtual machine, open Windows PowerShell with the System Modules.

  2. Run the following command:

    F:\scripts\onbox\OnBoxScript_5.ps1
    
  3. Close and reopen the PowerShell window before running the next script.

Verification: To verify the script succeeded:

  1. On the virtual machine, verify you can browse to SharePoint Central administration. The default url is http://iaas-bi-full:8080. Use the testuser login and Testword!1 password. Note: you may need to disable IE SEC. For more information, see Internet Explorer: Enhanced Security Configuration (http://technet.microsoft.com/en-us/library/dd883248(v=WS.10).aspx).

    Click Start, All Programs, and then click Microsoft SharePoint Products.

  2. Verify the three PowerPivot features are installed. On the virtual machine, open the SharePoint management Shell:

    1. In All Programs click Microsoft SharePoint Products.

    2. Click SharePoint 2010 Management Shell.

    3. Run the following command:

    Get-spfeature | where {$_.displayname –like “PowerP*”}
    
    DisplayName Id Scop

    ----------- -- ----

    PowerPivotSite 1a33a234-b4a4-4fc6-96c2-8bdb56388bd5 Site

    PowerPivotAdmin e9c4784b-d453-46f5-8559-3c891d7159dd Web

    PowerPivot f8c51e81-0b46-4535-a3d5-244f63e1cab9 Farm

Arrow icon used with Back to Top link Script Summary

Arrow icon used with Back to Top link Top

OnBoxScript_6.ps1

This script completes the following tasks:

  • Creates a PowertPivot Service application and creates a web application

  • Creates a new site and enables features.

  • Configures Secure Store Service.

  • Configures Excel Services.

Steps:

  1. On the virtual machine, open Windows PowerShell with the System Modules.

  2. Run the following command:

    F:\scripts\onbox\OnBoxScript_6.ps1
    

Verification: To verify the script succeeded:

  1. Use of one the following methods to verify the Excel Services service application:

    1. In SharePoint Central Administration, click Manage Service Applications. If you are prompted for a login, use the “testuser” login. Verify the Excel Service Application ExcelServiceApp1 is Started.

    2. Run the following PowerShell command from the SharePoint Management shell:

      Get-spserviceapplication | format-table typename, displayname
      
  2. Use one of the following methods to verify the site:

    1. Browse to the PowerPivot site, http://iaas-bi-full/SitePages/Home.aspx.

    2. In SharePoint Central Administration, click Application Management, and then click View all site collections.

    3. Run the following PowerShell command from the SharePoint Management shell:

      Get-spsite
      

Arrow icon used with Back to Top link Script Summary

Arrow icon used with Back to Top link Top

OnBoxScript_7.ps1

This script completes the following tasks:

  • Create an IIS certificate

  • Binds the certificate to the site

  • Creates and alternate access mapping (AAM) for https

Steps:

  1. On the virtual machine, open Windows PowerShell with the System Modules.

  2. Run the following command:

    F:\scripts\onbox\OnBoxScript_7.ps1
    

Verification: To verify the script succeeded:

  1. Run the following PowerShell command from the SharePoint Management shell to verify the <your cloud service name>.cloudapp.net mapping exists:

    Get-Spalternateurl
    

Arrow icon used with Back to Top link Script Summary

Arrow icon used with Back to Top link Top

OnBoxScript_8.ps1

This script completes the following tasks:

  • Installs and starts the Reporting Services Service.

  • Creates a Reporting Services service application, application pool, and proxy.

  • Associates the Reporting Services service application proxy to the default website.

  • Grants the web application rights to the Reporting Services application pool.

Steps:

  1. On the virtual machine, open Windows PowerShell with the System Modules.

  2. Run the following command:

    F:\scripts\onbox\OnBoxScript_8.ps1
    
    noteNote
    To create a managed service account, the script retrieves the credentials for the IaaS-BI\SQL_Reporting service account. This step will cause a credential verification dialog to appear. Type the password Testword!1, unless you changed the password in OnBoxScript_3.ps1

credential request

Verification: To verify the script succeeded:

  1. In SharePoint Central Administration, click Manage Service Applications.

  2. If you are prompted for a login, use the testuser login.

  3. Verify the Reporting Services Application and Reporting Services Application Proxy are installed and Started.

    OR

  4. Run the following command:

    Get-spserviceapplication | where {$_.displayname –like “Report*”}
    
    The output list includes the name and a GUID. The GUID will be different than the one shown in this example.

    Name Id UEAccountName

    ---- -- -------------

    Reporting Services ... 001c9a73-04e8-4e8f-823d-0e2454c4ce20

Arrow icon used with Back to Top link Script Summary

Arrow icon used with Back to Top link Top

UnmountData.ps1

This script completes the following tasks:

  • Unmounts the F: drive, the .VHD that contains the SQL Server and SharePoint installation files. Removing this drive will reduce the storage used by your Azure subscription.

Steps:

  1. On your local computer, open Azure PowerShell.

  2. Run the following command:

    C:\WA_BI_VM\offbox\UnmountData.ps1
    

Verification: To verify the script succeeded:

  1. In the Azure Management portal, connect to the VM and login as the user Testuser with password Testword!1.

  2. Open Windows File Explorer and verify the F: drive is not listed.

Arrow icon used with Back to Top link Script Summary

Arrow icon used with Back to Top link Top

Additional Configuration and installation

Arrow icon used with Back to Top link Top

Reporting Services Content Types

Reporting Services provides content types that are used to manage shared data source (.rsds) files and report definition (.rdl) files. Adding the content types to a library enables the Reporting Services options in the New menu. For more information, see Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode) (http://msdn.microsoft.com/en-us/library/bb326289.aspx).

Silverlight

The first time you click PowerPivot Gallery in the SharePoint left navigation pane, you will see a message indicating the need to install Silverlight. Before you download Silverlight. Complete the following steps to configure Internet Explorer on the virtual machine to allow downloads.

  1. Open Internet Explorer browser.

  2. Go to the Tools menu.

  3. Select Internet Options.

  4. Go to the Security tab.

  5. Select Internet zone.

  6. Click Custom level.

  7. Under the Downloads category, select enable for File download.

  8. Click OK, and then click OK again.

  9. In the PowerPivot Gallery, click the Install Microsoft Silverlight.

  10. When the Silverlight installation is complete, refresh the browser to see the PowerPivot Gallery view.

Arrow icon used with Back to Top link Script Summary

Arrow icon used with Back to Top link Top

More Resources

Use the Management Certificate on another computer

For information on how to export a certificate with the private key and import the certificate to another computer so that computer can also manage your Azure Subscription, see How to: View Certificates with the MMC Snap-in (http://msdn.microsoft.com/en-us/library/ms788967(v=vs.110).aspx).

You can also export and import certificates from Internet Explorer. For example to Export:

  1. Click Internet options.

  2. Click the Content tab.

  3. Click Certificates.

  4. Select the certificate you want to export, and click Export

  5. In the Certificate Export Wizard, select the option to export the private key.

Arrow icon used with Back to Top link More Resources

Arrow icon used with Back to Top link Top

Windows PowerShell Integrated Scripting Environment (ISE)

Windows PowerShell Integrated Scripting Environment (ISE) is an optional feature in Windows Server 2008 R2. To enable the PowerShell ISE Complete one of the following procedures:

  1. In Windows Server Manager, click features.

  2. Click Add Features.

  3. Select Windows PowerShell Integrated Scripting Environment (ISE) and then click Next.

  4. Click Install.

OR

  • Import-module servermanager
    Add-WindowsFeature as-net-framework
    Add-WindowsFeature PowerShell-ISE
    

Arrow icon used with Back to Top link More Resources

Arrow icon used with Back to Top link Top

Azure PowerShell Help

Run the following command from an Azure PowerShell window to see a list of Azure cmdlets:

'help azure'

To get command line help for a specific command, use the –full switch, for example:

get-help New-AzureVMConfig  -full

Arrow icon used with Back to Top link More Resources

Arrow icon used with Back to Top link Top

Cleanup Your Subscription to Re-run the Scripts

This section describes steps to ‘clean up’ the subscription in case you want to re-run the scripts starting with createvm.ps1. In Azure Management Portal, click All Items, and delete the following in the order listed: all items

  1. Click Virtual Machines, and then click the virtual machine Iaas-BI-Full. Click Delete.

  2. Click Networks, and then click the network Iaas-BI-network. Click Delete.

  3. Click Virtual Machines and then in the top menu, click Disks. Click Delete, and then click Delete the associated VHD. Delete all the disks.

  4. Click Storage, and then click the iaasbistorage. Click Delete.

  5. Click Settings and then click Affinity Groups. settings

  6. Click Iaas BI Affinity Group and then click Delete.

Arrow icon used with Back to Top link More Resources

Arrow icon used with Back to Top link Top

Import System Modules in PowerShell

The PowerShell system modules are required for most of the scripts in this project. Right-click the PowerShell icon to open PowerShell with the system modules.

Arrow icon used with Back to Top link More Resources

Arrow icon used with Back to Top link Top

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft