Updating SQL Server Analysis Services Via PowerShell
If you use the GUI install both the SQL Database Engine and SQL Analysis Services in the same server by clicking "Ok" and "Next" a lot, the default memory settings used by the DB Engine and SSAS allow for a risk of them starving each other and/or the operating system of memory. This can be especially risky if SQL runs as an account that can lock pages in memory; because it will.
I recently needed to deploy the SCOM MP's for SQL SSAS for the first time. They include monitors that validate the memory settings of SSAS to ensure that they don't conflict with the SQL Database Engine or the operating system. However they don't include recovery tasks. I'd prefer not to bother my DBA's with alerts that I can have SCOM auto-fix, so I set out to write Recovery Tasks for the SCOM Monitors. Scripting against the SQL Database Engine is extensively documented, but I had trouble finding example scripts for SSAS.
So here's a script that changes SSAS's Low Memory Limit and Total Memory Limit settings.
[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$myServer = New-Object Microsoft.AnalysisServices.Server
$myServer.connect("localhost")
$lml = $myServer.ServerProperties | WHERE {$_.Name -eq 'Memory\LowMemoryLimit'}
$tml = $myServer.ServerProperties | WHERE {$_.Name -eq 'Memory\TotalMemoryLimit'}
$lml.Value = 20
$tml.Value = 40
$myServer.Update()
Obviously I have no error handling here, and I hard-coded the connection string to "localhost" but I hope this serves as a basic example.
I tested the script on the following versions of SSAS.
- 10.50.4319.0 (2008 R2)
- 11.0.5058.0 (2012)
- 12.0.2381.0 (2014)
For additional reading:
https://msdn.microsoft.com/en-us/library/microsoft.analysisservices.server.aspx