By Richie Lee

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_png

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"
}