Automate SSAS Syncing Part 4: Synchronise Databases Across Servers

Hello, and welcome to part 4 of the series. In this post, I’m going to do a straight up sync with none of the disk checks from yesterdays post. The full script at the end of the series will include the disk checks.

How to Sync Cubes

We store a template of the sync xmla file on the disk.  The template kinda looks like this, but because my blog is having such a hard time coping with the xml fomatting I have provided a screenshot of how it should look, so however the example is rendering in your browser is probably wrong, and you should update it to how the screenshot looks.


<Synchronize xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Source>
        <ConnectionString>Provider=MSOLAP.6;Data Source=@@Source@@;Integrated Security=SSPI</ConnectionString>
        
            @@DatabaseID@@
        
    </Source>
    <SynchronizeSecurity>IgnoreSecurity</SynchronizeSecurity>
    <ApplyCompression>true</ApplyCompression>
</Synchronize>

sync

Save this xmla as “Synchronize.xmla” at “C:\temp\”.

The @@databaseID@@ and @@source@@ values are updated using PowerShell and are saved to a new xmla file, so that the template can be reused. The new, saved sync xmla file is executed via the Invoke ASCmd PowerShell cmdlet.

Part of this script, the parts labelled “part 3a” and “part 5” are totally optional and can be deleted. In the xmla above there is the “SynchronizeSecurity” node which can be set to copy the source permissions over or not. Sadly there is not an option to ignore the data source. In my scenario I do not want to write over the data source, so part 3a copies the data source out of the target database, and then part 5 updates the data source of the synced database on the target server to what it was originally.

Including parts 3a and 5, the whole synchronise part is only 40 lines of code. This isn’t all that much, but highlights something significant about automating a process, and that is that automating a process requires much more effort than just the act of what it is you’re trying to automate. That is, there is little point in just writing 40 lines of code to automate syncing if the job fails all the time because of lack of disk space, or the service account not having sufficient permissions to run the sync.


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

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

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

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

foreach ($sourceDB in $sourcesvr.Databases)
{
$role = $null
$role = $sourceDB.Roles.FindByName($rolename)
if ($role -eq $null)
 {
 #Create Role
 $t = Get-Date -format "d MMM yy HH:mm:ss"
 "${t}: role $roletoCreate created on $sourcesvr $sourceDB"

 $roleToCreate = new-Object([Microsoft.AnalysisServices.Role])($rolename)
 $sourceDB.Roles.Add($roleToCreate)
 $roleToCreate.Update()

 #add the permission
 $t = Get-Date -format "d MMM yy HH:mm:ss"
 "${t}: admin permission added for $roletoCreate on $sourcesvr $sourceDB"

 $sourceDBperm = $sourceDB.DatabasePermissions.Add($roleToCreate.ID)
 $sourceDBperm.Administer = [Microsoft.AnalysisServices.ReadAccess]::Allowed
 $sourceDBperm.Update()
 $role = $sourceDB.Roles.FindByName($rolename)
 }

 #check to see if role that exists has administer permission
 #if not then administer is added

 $sourceDBperm = $sourceDB.DatabasePermissions.FindByRole($newroles)
 if ($sourceDBperm.Administer -eq $false)
 {
 $t = Get-Date -format "d MMM yy HH:mm:ss"
 "${t}: admin permission added for $roletoCreate on $sourcesvr $sourceDB"
 $sourceDBperm.Administer = [Microsoft.AnalysisServices.ReadAccess]::Allowed
 $sourceDBperm.Update()
 }

#part 2

$foundMember = $null
foreach ($member in $role.Members)
{
 if ($member.Name -eq $syncAccount)
 {
 $foundMember = $member
 }
}

If ($foundMember -eq $null)
{
 $t = Get-Date -format "d MMM yy HH:mm:ss"
 "${t}: Adding access to " + $syncAccount

 $newMem = New-Object Microsoft.AnalysisServices.RoleMember($syncAccount)
 $role.Members.Add($newMem)
 $role.Update()
}
else
{
 $t = Get-Date -format "d MMM yy HH:mm:ss"
 "${t}: Usergroup " + $syncAccount + " already added"
}

#part 3a
#we want the data source to be the same as what is was before sync
#get connection string before update
$targetdb = $targetsvr.Databases.FindByName($sourceDB.name)
$targetds = $targetdb.DataSources.FindByName('DW2012')
$targetconnectionString = $targetds.ConnectionString

#part 4; the sync

$syncTemplate = "C:\temp\Synchronize.xmla"
$syncexec = "C:\temp\Synchronize$sourceDB.xmla"

$syncxmla = Get-Content $syncTemplate
$syncxmla = $syncxmla.Replace("@@DatabaseID@@", $sourceDB)
$syncxmla = $syncxmla.Replace("@@Source@@", $sourcesvr)
$syncxmla | Out-File $syncexec

$t = Get-Date -format "d MMM yy HH:mm:ss"
"${t}: Synchronising $sourceDB from $sourcesvr to $targetsvr"
[xml]$resultXml = Invoke-ASCmd -InputFile $syncexec -Server $targetsvr -TraceFile $tf -TraceLevel Low
Write-Output $resultXml.InnerText

#part 5
#update the data source
#use the connection string we got at the beginning of the process
$resetds = $targetdb.DataSources.FindByName('DW2012')
$resetds.connectionstring = $targetconnectionString
$resetds.Update()
$t = Get-Date -format "d MMM yy HH:mm:ss"
"${t}:datasource updated for $targetdb to $targetconnectionString"
}

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

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