Automatically Shut Down an Azure VM

Here is a self-documenting (I hope) PowerShell script. Windows Azure PowerShell is required.

Import-Module "C:\Program Files (x86)\Microsoft SDKs\Windows Azure\PowerShell\Azure\Azure.psd1"
# Importing publish settings only has to be done once.
# Download from <a href="https://windows.azure.com/download/publishprofile.aspx">https://windows.azure.com/download/publishprofile.aspx</a>
# Then, run the following line:
# Import-AzurePublishSettingsFile "Path\File.publishsettings"
# Test:
Get-AzureVM -ServiceName "MyService" > C:\Users\user\Documents\StopLog.txt 2>&1
# Stop the VM:
Stop-AzureVM -Name "MyName" -ServiceName "MyService" -Force >> C:\Users\user\Documents\StopLog.txt 2>&1
# Schedule a task (using Task Scheduler) with
# Action: Start a program
# Program/script: powershell.exe
# Parameters: C:\Users\user\Documents\StopVM.ps1

Deploying to SQL Server Analysis Services (SSAS) using PowerShell

My previous post was about configuring http access to SSAS. Here is a way to deploy a SSAS project using script (PowerShell) via http.

First, create a project configuration. I called mine “Test”. Then, edit the “.deploymenttargets” file located in the project configuration folder in the bin folder (MyProject\bin\Test in my case). Edit the server parameter to e.g.

https://myserver.cloudapp.net/OLAP/msmdpump.dll

Then, create a deployment script using the deployment utility:

“C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment” MyProject\bin\Test\Model.asdatabase /s:ScriptLog.txt /o:Model.xmla /d

Then, deploy using the new PowerShell commandlets:

$credential = Get-Credential domain\user
Invoke-ASCmd -InputFile $fspath\Model.xmla -Server https://myserver.cloudapp.net/OLAP/msmdpump.dll -Credential $credential

Here is the entire script:

$fspath = (Get-Location).Path
Write-Host "Generating deployment script..."
& "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment" MyProjct\bin\Test\Model.asdatabase /s:ScriptLog.txt /o:Model.xmla /d
Start-Process $fspath\ScriptLog.txt
Write-Host "Importing SQL modules..."
Import-Module SQLPS -disablenamechecking
Import-Module sqlascmdlets
Write-Host "Invoking deployment script... This may take several minutes or even hours."
$credential = Get-Credential domain\user
Invoke-ASCmd -InputFile $fspath\Model.xmla -Server https://myserver.cloudapp.net/OLAP/msmdpump.dll -Credential $credential | Out-File $fspath\Result.xml
Start-Process $fspath\Result.xml
Write-Host "Done."

Accessing SQL Server Analysis Services via HTTP

I have set up HTTP access to SQL Server Analysis Server a couple of times. It is well documented here: http://technet.microsoft.com/en-us/library/gg492140.aspx

But each time, I seem to encounter the following error when I try to add the script map: “The specified module required by this handler is not in the modules list. If you are adding a script map handler mapping, the IsapiModule or the CgiModule must be in the modules list.”

image

Here is how to solve this: In Windows Server 2012, add the ISAPI Extension feature using the Add Roles and Features Wizard (which is accessible from Server Manager):

image

Here is a PowerShell script that automates the steps 1-2 outlined in the Technet article above. (I ran into problems adding the script map using PowerShell.)

# Prerequisites: Windows PowerShell Snap-In for IIS 7.0

$filepath="C:\inetpub\wwwroot\OLAP"
$olapisapipath="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\bin\isapi"

New-Item -Path $filepath -ItemType directory
Copy-Item -Path "$olapisapipath\*" -Destination $filepath -Recurse
Import-Module WebAdministration
New-Item IIS:\AppPools\OLAP
Set-ItemProperty IIS:\AppPools\OLAP -Name managedPipelineMode -value Classic
Set-ItemProperty IIS:\AppPools\OLAP -Name managedRuntimeVersion -value v2.0
New-Item "IIS:\Sites\Default Web Site\OLAP" -type Application -physicalPath $filepath
Set-ItemProperty "IIS:\Sites\Default Web Site\OLAP" -Name applicationPool -Value OLAP

Access Permissions to SSAS

IIS must be granted access to query the SSAS database. If you’re using authentication, it is the IIS AppPool account that should be granted access. If you’re using anonymous, you can check the account by selecting Anonymous Authentication in IIS Manager and clicking Edit. If your IIS server is separate from your SSAS server, you must change this to a domain account.

I have put an XMLA script to create an appropriate role in SSAS on OneDrive: https://onedrive.live.com/redir?resid=DD9BE5E7B16098D7%2114485