By Richie Lee

Hello, and welcome to part 3 of the series on syncing databases across servers. The first two parts focused on ensuring that the role exists on the source server with the appropriate permissions to sync. We then check and add the service account of the target instance to ensure that the sync can occur.

To be honest, if your SSAS databases are very small, or you know you have plenty of space on your disks, then these checks are unnecessary. But many of the SSAS databases I sync are hundreds of gigabytes, and synchronisation can take a long time, so it always makes sense to verify that enough disk space exists in the first place rather than it rolling back because of lack of disk space.

There are several checks that you can make on the disk sizes. It really depends if your servers are alike. For instance, we have one very large instance that we use to consolidate multiple, smaller instances, so disk checks are more complicated in this scenario. To give you an idea of what we could check for, I have 4 checks below:

Here are the links for the other parts of the series Automate SSAS Syncing Part 1: Create SSAS Database Role Automate SSAS Syncing Part 2: Create SSAS Role And Add Member Automate SSAS Syncing Part 3: Using PowerShell to Check for Disk Space on SSAS Instances


[CmdletBinding()]
param(
[Parameter(Position=0,mandatory=$true)]
[string] $sourceInstance,
[Parameter(Position=1,mandatory=$true)]
[string] $targetInstance,
[Parameter(Position=2,mandatory=$true)]
[string] $syncAccount)

[void][System.reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$rolename = "Sync00"
$syncAccount = "PROD\svc_PRE1SQLAnalysis"

$sourceInstance = "pre1sql02"
$targetInstance = "pre1sql04"

$targetsvr = new-Object Microsoft.AnalysisServices.Server
$targetsvr.Connect($targetInstance)

$sourcesvr = new-Object Microsoft.AnalysisServices.Server
$sourcesvr.Connect($sourceInstance)

foreach ($db in $sourcesvr.Databases)
{
$targetDisk = $targetsvr.ServerProperties

$targetDisk = $targetDisk | Select-Object name, value | Where-Object {$_.Name -eq 'DataDir'} | Select-Object -Expand value 
$TargetDisk.ToString() | Out-Null
$TargetDriveLetter = $targetDisk.Substring(0,2)

$targetdisksize = Get-WmiObject Win32_LogicalDisk -ComputerName $targetsvr -Filter "DeviceID='$TargetDriveLetter'" | Select-Object -Expand Size
$targetfreespace = Get-WmiObject Win32_LogicalDisk -ComputerName $targetsvr -Filter "DeviceID='$TargetDriveLetter'" | Select-Object -Expand FreeSpace
$targetdisksize = $targetdisksize / 1gb
$targetfreespace = $targetfreespace / 1gb

# get disk details for source server

$sourceDisk = $sourcesvr.ServerProperties

$sourceDisk = $sourceDisk | Select-Object name, value | Where-Object {$_.Name -eq 'DataDir'} | Select-Object -Expand value 
$sourceDisk.ToString() | Out-Null
$SourceDriveLetter = $SourceDisk.Substring(0,2)

$sourcedisksize = Get-WmiObject Win32_LogicalDisk -ComputerName $sourcesvr -Filter "DeviceID='$sourceDriveLetter'" | Select-Object -Expand Size
$Sourcefreespace = Get-WmiObject Win32_LogicalDisk -ComputerName $Sourcesvr -Filter "DeviceID='$SourceDriveLetter'" | Select-Object -Expand FreeSpace
$sourcedisksize = $sourcedisksize / 1gb
$Sourcefreespace = $Sourcetfreespace / 1gb

$sourceDBfolder = $sourceDB.name

$sourceDBSize = 
 invoke-command -computername $sourcesvr -scriptblock{param ($sourceDBfolder, $SourceDisk) $path = Get-ChildItem $SourceDisk | Where-Object {$_.PSIsContainer -eq $true -and $_.Name -match $sourceDBfolder}
 $path = $SourceDisk+"\"+$path
 $fso = New-Object -comobject Scripting.FileSystemObject
 $folder = $fso.GetFolder($path)
 $size = $folder.size
 $size = $size / 1gb
 $size
 } -Args $sourceDBfolder, $sourceDisk

 # first check; are the target and source disks the same size?

 if ($targetdisksize -ge $sourcedisksize)
{
  $t = Get-Date -format "d MMM yy HH:mm:ss"
  "${t}: both target and source disk sizes are the same or target is larger"
}

# if disks are not the same size, then how much smaller is the target? 
# we stil might be able to sync with the smaller disk

else 
{
$total = $sourcedisksize - $targetdisksize 
$total = $total /1gb
  $t = Get-Date -format "d MMM yy HH:mm:ss"
  "${t}: target disk is smaller than the source disk by $total gb "
}

# figure out what the difference is between the sizes of the databases on the source and target servers
# if the expected growth (the difference between how much data is on the source server minus how much data is on target server)
# is greater than the free space on the target server then chances are the sync will fail
# remember that the target database will also drop data that does not exist in source server, so this is worst case scenario
# we will break out of script if there is not enough space
# remember this check does not take into account that there will be 2 copies of the database at then end of the sync, this is handled in the next check

   $expectedGrowth = ($sourcedisksize - $sourcefreespace) - ($targetdisksize - $targetfreespace)
   "source disk size "+ $sourcedisksize
   "source free space "+ $sourcefreespace
   "target disk size "+ $targetdisksize
   "target free space "+ $targetfreespace

  $expectedGrowth = [Math]::Round($expectedGrowth, 4)
  if ($expectedGrowth -ge $targetfreespace)
  {
    $expectedGrowth=$expectedGrowth/1gb
    $targetfreespace=$targetfreespace/1gb
    $t = Get-Date -format "d MMM yy HH:mm:ss"
    write-error "${t}: there is not enough free space on the target server for the anticipated growth. Expected growth is $expectedGrowth gb, whilst free space is $targetfreespace gb"
    break
  }


# when a sync runs, it first copies all the data that exists in the source database that does not exist in the target database 
# over the network from the source database into a temporary folder
# the sync job then copies out the remaining data form the target database into the temporary folder
# if any data exists in the target server that does not exist in the sdource server, this is deleted/not copied over
# once the copy is complete, the temp folder is renamed to the database name and the number on the folder name is incremented by 1
# the original folder is then deleted and replaced by the new folder
# this means at any one time when syncing there will be 2 copies of the databases on disk
# this is not a problem if the databases are small. however it is a very big problem if the databases are very big.


if ($targetfreespace -le $sourceDBSize)
{
$t = Get-Date -format "d MMM yy HH:mm:ss"
Write-Error "${t}: There may not be enough free space to synchronize the database $sourceDB. Consider dropping the target database $sourceDB on $targetsvr."
break


}

}