Attach and Detach Cubes Using AMO

goneDrinking

 

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

  1. I’m (finally!) moving house next week. So I won’t have any internet for a couple of weeks. I’ve been so busy with everything else I have not been able to write any posts that will publish during my time off. So output will be down for the next month or so.
  2. If anyone can point me in the direction of a site that helps with giving blog posts good titles let me know; I really do suck at coming up with decent blog post titles and need all the help I can get.

All that being said, let’s get on with today’s topic, using AMO to detach/attach cubes! Writing about using PowerShell to manage SSAS via AMO was the whole reason I created this blog before it expanded to sharing a whole lot more, so I always enjoy coming back to this subject as much as I enjoy writing the scripts.

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:

  • The attach asks for uname/pword because you need to run a remote invoke-command to get all the detach log files.
  • You need to know the drive letter the cubes are on: The only way I’ve found to get the drive letter via AMO is via the DbStorageLocation Property, but if you’re running this script then chances are there are no databases attached in order to get this property back. I’d love to fix it so that you do not need to know the drive.
  • The AttachCubes also presumes that all the cubes are on the same drive, and that you want to attach all of the databases that have a detach log file.
  • Split-path cmdlet is available in PowerShell 3 onwards.

[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

4 thoughts on “Attach and Detach Cubes Using AMO

  1. My idea for not having to supply the folder name: Get the server’s data directory via srv.ServerProperties["DataDir"].Value and loop through all the subdirectories ending with “.db”. Then parse out the database ID from the folder name. From there you either attach or detach as needed, depending on whether it exists in the server.Databases collection property. I have included code below, though I’m sure the formatting will be awful here.

    To detach:
    1. loop through the server data directory
    2. Parse the database ID from the folder name
    3. Find the database in the database collection and detach

    To attach:
    1. loop through the server data directory
    2. Parse the database ID from the folder name
    3. Look for the database in the database collection, and attach if not found

    static void DetachDatabases()
    {
    using (Server srv = new Microsoft.AnalysisServices.Server())
    {
    srv.Connect(“servername”);
    string dataDir = srv.ServerProperties["DataDir"].Value;
    foreach (string fullDirName in System.IO.Directory.GetDirectories(dataDir, “*.db”))
    {
    string simpleDirName = fullDirName.Split(new char[] { ‘\\’ }).Last();
    string dbID = simpleDirName.Substring(0, simpleDirName.IndexOf(‘.’));
    using (Database db = srv.Databases.Find(dbID))
    {
    if (db != null)
    {
    db.Detach();
    }
    }
    }
    }
    }

    static void AttachDatabases()
    {
    using (Server srv = new Microsoft.AnalysisServices.Server())
    {
    srv.Connect(“servername”);
    string dataDir = srv.ServerProperties["DataDir"].Value;
    foreach (string fullDirName in System.IO.Directory.GetDirectories(dataDir, “*.db”))
    {
    string simpleDirName = fullDirName.Split(new char[] { ‘\\’ }).Last();
    string dbID = simpleDirName.Substring(0, simpleDirName.IndexOf(‘.’));
    using (Database db = srv.Databases.Find(dbID))
    {
    if (db == null)
    {
    srv.Attach(fullDirName);
    }
    }
    }
    }
    }

  2. My idea for not having to supply the folder name: Get the server’s data directory via srv.ServerProperties["DataDir"].Value and loop through all the subdirectories ending with “.db”. Then parse out the database ID from the folder name. From there you either attach or detach as needed, depending on whether it exists in the server.Databases collection property. I have included code below, though I’m sure the formatting will be awful here.

    To detach:
    1. loop through the server data directory
    2. Parse the database ID from the folder name
    3. Find the database in the database collection and detach

    To attach:
    1. loop through the server data directory
    2. Parse the database ID from the folder name
    3. Look for the database in the database collection, and attach if not found

    static void DetachDatabases()
    {
    using (Server srv = new Microsoft.AnalysisServices.Server())
    {
    srv.Connect(“servername”);
    string dataDir = srv.ServerProperties["DataDir"].Value;
    foreach (string fullDirName in System.IO.Directory.GetDirectories(dataDir, “*.db”))
    {
    string simpleDirName = fullDirName.Split(new char[] { ‘\\’ }).Last();
    string dbID = simpleDirName.Substring(0, simpleDirName.IndexOf(‘.’));
    using (Database db = srv.Databases.Find(dbID))
    {
    if (db != null)
    {
    db.Detach();
    }
    }
    }
    }
    }

    static void AttachDatabases()
    {
    using (Server srv = new Microsoft.AnalysisServices.Server())
    {
    srv.Connect(“servername”);
    string dataDir = srv.ServerProperties["DataDir"].Value;
    foreach (string fullDirName in System.IO.Directory.GetDirectories(dataDir, “*.db”))
    {
    string simpleDirName = fullDirName.Split(new char[] { ‘\\’ }).Last();
    string dbID = simpleDirName.Substring(0, simpleDirName.IndexOf(‘.’));
    using (Database db = srv.Databases.Find(dbID))
    {
    if (db == null)
    {
    srv.Attach(fullDirName);
    }
    }
    }
    }
    }

  3. Hello! Sorry I’ve been late in replying.
    How on earth did I miss the server properties?! I also like the check that the database exists or not. Kudos, this is an excellent solution.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s