Before I start this post, I want to mention a few things:

We recently upgraded to SQL Server 2014 and have applied both CU1 and CU2 to all of our servers. With both CU1 and CU2 we experienced an issue when applying the updates to boxes running SSAS only. It occurred once with CU1 and once with CU2, on separate boxes, out of well over 20 boxes being patched. I think the Windows Server editions were 2012 R2, and the SQL Server editions are enterprise licensed. Anyway the problem was that after patching we were unable to start SSAS at all. It just completely killed the instances. We were unable to discover what the problem was; there was no logging or any info in event viewer. So we had to un-install and reinstall SSAS. After this we were able to apply the patch successfully. This means we lost our databases. The fortunate thing is we always test our patching in dev, then test, then UAT before deploying to prod, then lastly hot-fix environment, and our issue happened in UAT both times, so it wasn’t prod that was affected!

The upshot of this is we are now detaching our databases before applying Cumulative Updates in case we have the same issue again; then we only have to un-install/install and can attach the cubes to the new instance. But we have many instances running many more ssas db’s, so detaching/attaching can be laborious through the UI. So I wrote 2 PowerShell scripts that use AMO to detach all databases and attach all databases.

Here is the detach. it’s very straight-forward.

[CmdletBinding()]

  param(

    [Parameter(Position=0,mandatory=$true)]

    [string] $ssasInstance)

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$svr=New-Object Microsoft.AnalysisServices.Server

$svr.connect($ssasInstance)

foreach ($db in $svr.Databases)

{

Write-Host "Detaching " $db -foregroundcolor darkgreen -backgroundcolor white

$db.detach()

Write-Host "Detached " $db -foregroundcolor magenta -backgroundcolor white

}

The attach is a little more complex:

[CmdletBinding()]

param(

[Parameter(Position=0,mandatory=$true)]

[string] $ssasInstance,

[Parameter(Position=1,mandatory=$true)]

[string] $driveLetter)

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$svr=New-Object Microsoft.AnalysisServices.Server

$svr.connect($ssasInstance)

$cred = Get-Credential

$session = New-PSSession -credential $cred -ComputerName $ssasInstance

$cubeDirPath = $driveLetter + ":\"

$Result = Invoke-Command -ScriptBlock {gci -Path $args[0] -rec -filter *.detach_log | Select-Object FullName | Where-Object {!($_.psiscontainer)} | foreach {$_.FullName}} -Args $cubeDirPath -session $session

foreach ($detachLog in $Result)

{

$cubePath = split-path $detachLog

$svr.attach($cubePath,"ReadWrite")

Write-Host "Attached " $detachLog -foregroundcolor magenta -backgroundcolor white

}

Save each of these to a separate .ps1 file you open up a PowerShell console and drag and drop the file there (or copy as path, whichever way you like), or open using PowerShell ISE, and press enter. It’ll ask for ssasInstance, and in the case of the attach, the drive letter. Enter only the drive letter (not the path, or “:") and the script will do the rest.

If you like this then I’ve got a few other Powershell scripts for cube status querying/updating.

Find size of dimensions

Turn On Lazy Aggregations

Finding Unprocessed Objects

Process SSAS Databases Using PowerShell

Setting Aggregation Designs on SSAS Partitions