WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Friday, January 11, 2013

Setting up a SSAS Sync with Powershell

In the SSAS world, a method to separate processing and presentation is utilizing a two server setup.  The "processing" server is where all the heavy lifting is done and a secondary server is where the cubes are hit by end users and reports.  Given my newbie status in the BI space, I am guessing, but pretty sure that XMLA and SSIS tasks are the common methods for syncing a cube from one server to another.  A side benefit of the script I am about to share is its really easy to move a cube from one server to another for any reason, plug in the values and hit the button.

So, a picture is a worth a thousand words (this one probably doesn't qualify as I did it with Paint, but here is the general architecture.

Alright, first we will need a function to see if SSAS is actually running (with retry logic) - this is used throughout.


 function Verify-SSASConnection([Parameter(Mandatory=$True,Position=1)][Microsoft.AnalysisServices.Server]$SSASConnection,  
                 [Parameter(Mandatory=$True,Position=2)][string]$SSASName)  
 {  
   if($SSASConnection.Connected) {return;}  
     
   $SSASConnection  
     
   Write-Info "Attempting ReConnect for $SSASName"  
     
   [int]$i = 0;  
   while(!($SSASConnection.Connected))   
   {  
     if($i > $retryAttempts) {throw "Alert! $SSASName Unavailable, $cubeName Disconnected"}  
       
     try{  
       $SSASConnection.Connect($SSASName)  
       }  
     catch{  
       Write-Info "retry loop for $SSASName - $i" + $SSASConnection.Connected  
     }  
       
     if($SSASConnection.Connected) {return;}  
     $i++;  
     Start-Sleep -Seconds $retryDelay  
       
   } # looping retry      
   return;  
 }  

The steps below can be summarized pretty nicely into this:

  1. Detach Processing Cube and copy the entire cube folder to the Presentation Server (with a _new appended on the name) and then reattach Processing Cube
  2. Detach the Presentation Cube and rename the folder (with a _old)
  3. Swap the Processing Folder to previous Presentation Cube folder name.
  4. Bring the Presentation Cube back online
  5. Party

The script should be saved as a file and simply called via SQL Agent (Operating System Command in this case).  The parameters are the source (processing server), the presentation server, the cube and the mode it should be restored in.


 Param(  
   [Parameter(Mandatory=$True,Position=1)]  
   [alias("SRC")]  
   [string]$srcSSASName ,  
   [Parameter(Mandatory=$True,Position=2)]  
   [alias("DEST")]    
   [string]$destSSASName ,  
   [Parameter(Mandatory=$True,Position=3)]  
   [string]$CubeName ,  
   [Parameter(Mandatory=$True,Position=4)]  
   [ValidateSet("ReadOnly","ReadWrite")]  
   [string]$CubeModeParm    
 )  
 $ErrorActionPreference = "STOP"  
 [int]$retryDelay = 30  
 [int]$retryAttempts = 10  
   
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null  
   
 $source = New-Object Microsoft.AnalysisServices.Server  
 $destination = New-Object Microsoft.AnalysisServices.Server  
   
 if($cubeModeParm -eq "ReadOnly"){  
   $cubeMode = 1  
 } else {  
   $cubeMode = 0  
 }    
   
 function Write-Info([string]$msg)  
 {  
   $dt = (Get-Date)  
   if ($host.Name -eq "ConsoleHost"){Write-Host "$msg | $dt"} else {Write-Output "$msg | $dt"}  
 }  


Now grab the server names for the physical copy, the Regex below was not my first choice due to the fact I still don't get it completely, but this hombre advocates for it.


 $srcServerName = $srcSSASName -replace '^([\w\d]+).*$', '$1'  
 $destServerName = $destSSASName -replace '^([\w\d]+).*$', '$1'  

You will notice the Write-Info function use, its my hacky way of having the function decide how to output the myriad of debugging steps I have in this script.  It is nice when things aren't working to find the problem.  Now its time to connect and get the data directories for each instance.


 Write-Info "Starting process to move $cubeName from $srcServerName to $destServerName"  
   
   # Source Connect  
   try {  
      $source.Connect($srcSSASName)  
   }   
   catch {  
     throw "Source SSAS $srcSSASNAME Connect $Error "  
     Exit 1  
   }    
     
   # Destination Connect  
   try {  
      $destination.Connect($destSSASName)  
   }   
   catch {  
     throw "Destination SSAS $destSSASNAME Connect $Error "  
     Exit 1  
   }    
     
   #Fetch current ssas data directories and unc    
   try {  
     $srcDataDir = $source.ServerProperties.Item("DataDir").Value  
     $destDataDir = $destination.ServerProperties.Item("DataDir").Value  
     $uncSrcDataDir = "\\{0}\{1}\" -f $srcServerName, $srcDataDir.Replace(":","$")  
     $uncDestDataDir = "\\{0}\{1}\" -f $destServerName, $destDataDir.Replace(":","$")  
   
     If((test-path -Path $uncSrcDataDir) -eq $false) {throw "Source Data Directory Does Not Exist - $srcServerName , $srcDataDir"}  
     If((test-path -Path $uncDestDataDir) -eq $false) {throw "Destination Data Directory Does Not Exist - $uncdestDataDir"}   
   
   }  
   catch [system.exception] {  
     throw "Directory setup failed - $Error[0]"  
     Exit 1  
   }  


Ok, we have connected to the SSAS servers and got the data directories.  The next statement is a bit trickier as SSAS DBs have a ..db naming scheme, and we need to find the most recent revision to sync.


   $srcFolderName = (gci $uncSrcDataDir | ?{$_.Name -match "$cubeName\." -and $_.psIsContainer}| sort -property LastWriteTime -des | select-object -first 1).Name  
    
If the cube exists on the destination, this code gets those pieces of data set up. 
   

   $destFolderName = (gci $uncDestDataDir | ?{$_.Name -match "$cubeName\." -and $_.psIsContainer}| sort -property LastWriteTime -des | select-object -first 1).Name  
   IF($destFolderName -eq $null)  
   {  
     $destFolderName = $srcFolderName  
     Write-Info -msg "Destination does not exists for cube - using $srcFolderName"  
   }   
   $destLocalFolder = "{0}\{1}" -f $destDataDir,$destFolderName #path to cube to attach for ssas (not UNC)  
     
   #setup vars  
   $uncSrcDataDir = "{0}{1}" -f $uncSrcDataDir,$srcFolderName  
   $baseFolder = "{0}{1}" -f $uncDestDataDir,$destFolderName  
   $newDestFolder = ($uncDestDataDir + $destFolderName + "_new")  
   $oldDestFolder = ($uncDestDataDir + $destFolderName + "_old")  
       
   $srcFolderReAttach = ($baseFolder -replace '\\\\.+?\\(\w)\$','$1:')  
     
   if(test-path -Path $oldDestFolder) # Remove old folder if exists  
   {  
     Remove-Item -Path $oldDestFolder -Force -Recurse  
     Write-Info "Removing $oldDestFoler - still existed!"  
   }  
   Double check that the "new copy of the cube" isn't on the destination.  
   if(test-path -Path $newDestFolder)   
   {  
     $newestFileDate = (gci -Path $newDestFolder | Sort-Object LastWriteTime -Descending | Select-Object -First 1).LastWriteTime   
     $dateFilter = (Get-Date).AddSeconds(($retryAttempts * $retryDelay) * -1)  
       
     if($newestFileDate -ge $dateFilter) {  
     throw "Existence of $newDestFolder with recent file dates - a Sync is already running"  
     Exit 1;  
     }  
     # wait for one delay - then double check destination connection.  
     Start-Sleep -Seconds $retryDelay   
       
     Verify-SSASConnection ([ref]$destination) ($destSSASName)  
     Remove-Item -Path $newDestFolder -Force -Recurse  
     Write-Info "$newDestFolder Exists! Likely failure on prior run."  
   }  
   
    
Now separating the wheat from the chafe - detach the source cube so the files can be copied.


   try {  
     Write-Info "Starting Detach of $cubeName"   
     $source.Databases.GetByName($cubeName).Detach();  
   }    
   catch {  
     throw "Detach failed for $cubeName $Error[0]"  
     Exit 1  
   }  
Copy the folder (it will have lots of files depending on the cube), note the finally as if the copy fails the source cube will be brought back online.


   try {    
     Write-Info "Starting copy $newDestFolder "   
     Copy-Item -Path $uncSrcDataDir -Destination $newDestFolder -recurse -force  
   }    
   catch {  
     throw "Copy failed for $cubeName $Error[0] - Attempting reattach on Source"  
     Exit 1  
   }  
   finally  
   {  
     Write-Info "Attaching Source Cube $srcFolderName"  
       
     Verify-SSASConnection ([ref]$source) ($srcSSASName)  
     $source.Attach($srcDataDir + "\" + $srcFolderName)  
     $source.Disconnect();    
   }  
     
   Write-Info "Starting Destination Cube Detach Process $destSSASName and replace with $newDestFolder "  
     
   if(!(test-path -Path $newDestFolder)) {throw "New Folder Copy on $newDestFolder did not succeed - $error[0]"}    

  At this point, the copy of the updated cube has been completed and the source (processing cube) is back online for the next process command and we now detach the presentation cube.      
    

 try{  
     Verify-SSASConnection ([ref]$destination) ($destSSASName)  
       
     if($destination.Databases.Contains($cubeName))  
     {  
     $destination.Databases.GetByName($cubeName).Detach()  
       Write-Info "Detach of $destSSASName completed"  
     }   
   }    
   catch{  
     throw "Destination Detach Failed for $cubeName - $error[0]"  
   }    
     
   Write-Info "Attempting Folder Swap Main:$baseFolder Old:$oldDestFolder "  
    
Once the detach is complete, we now want to rename the current destination cube (append an _old to the name.  Upon failure, the presentation cube is brought back online.


   try{  
     if(test-path -Path $baseFolder)  
     {  
       Write-Info "Renaming Current Destination Folder $destFolderName"  
       Rename-Item -Path $baseFolder -NewName $oldDestFolder -Force   
     }  
       
   }   
   catch {  
     Write-Info "Failure of folder rename $destFolderName - attempting attach of old data"  
       
     Verify-SSASConnection ([ref]$destination) ($destSSASName)  
     $destination.Attach($destLocalFolder,$cubeMode) #1=readonly  
     $destination.Disconnect()     
       
     throw "Folder Swap Failed - $baseFolder. Attempting reAttach - $error[0]"  
   }  
     
   Write-Info "Renaming $newDestFolder to $destFolderName"  


The folder rename can be problematic so there is a rolling retry over this ( in testing, this has only failed once in a while, and usually one for one loop.  Note the finally again will bring the cube back online on failure or success.


   try{  
     [bool]$fileRenameComplete = $false  
     [int]$retries = 0  
     while($fileRenameComplete -eq $false)  
     {  
       try {  
         Rename-Item -Path $newDestFolder -NewName $destFolderName -Force  
         $fileRenameComplete = $true;  
       } catch {  
         Write-Info "Rename Failed for $newDestFolder"  
       }  
       $retries++;  
       IF($retries -ge $retryAttempts)   
       {  
         throw "Retries for $newDestFolder rename Failed!";  
       }     
       Start-Sleep -Milliseconds $retryDelay #very brief delay before retry  
     }  
   }   
   catch {  
     throw "Folder Replace with update failed $newDestFolder - $Error[0] "   
   }   
   finally{  
     Write-Info "Attaching Destination Cube $destLocalFolder"   
       
     Verify-SSASConnection ([ref]$destination) ($destSSASName)  
     $destination.Attach($destLocalFolder,$cubeMode) #1=readonly  
     $destination.Disconnect()     
   }  
   
   Write-Info "Cube attach completed - Starting $oldDestFolder removal "   

So, here is a Powershell version of a cube sync, the base set of code was borrowed/stolen from a co-workers implementation.  I was charged with adding the retry logic and fault tolerance.

Note: SQL Agent can be a bit painful when working with Powershell, that stuff is documented on the web.  


No comments:

Post a Comment