Thursday, 15 August 2013

PowerShell to Script SQL Analysis Service Database(XMLA Script) and ReApply on a different Server


Similar to my  post last month to script and reapply DB service database objects ( http://www.sqltechnet.com/2013/06/powershell-to-script-sql-database.html ), I was working on a project migrating Analysis services databases from SQL Server 2008 to SQL server 2012 and my client did not want to go the backup and restore route due to some changes to the base database schema structure. So we decided to script the AS database and re-apply it on the destination before we process the cubes. There was also a requirement to change the data sources after the source DB script was created as the server names has changed in the new SQL 2012 farm.
 
 I took the method mentioned in http://geekswithblogs.net/darrengosbell/archive/2010/02/22/ssas-automating-the-scripting-of-an-ssas-database.aspx as my base for scripting and created a wrapper to script the databases and change the data source as per the server name mentioned in the script as below. 

1. ScriptASDB.ps1

 
# Credits:- http://geekswithblogs.net/darrengosbell/archive/2010/02/22/ssas-automating-the-scripting-of-an-ssas-database.aspx
# Script to Script a Analysis Service Database
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 09/08/2013
# Script Help :-
#---------------
# Parameter 1 :- "-s" to specify the SQL Server Name
# Parameter 2 :- "-d" if only one database name to be scripted;Specify "databsename" if parameter 2 is -d
#     "-f" if a set of databases needs to be scripted;Specify "UNCFilepath of the file containing databse names" if parameter 2 is -f and "all" if parameter 2 is -f and you want to script all databses in the server
# Parameter 3:-  "-o" tp specify the UNC Folder path where output scripts nees to be stored
# Example1:- SQLASScript.ps1 -s TESTSERVER\INST1 -d TestDB -o d:\SQLAS
# Example2:- SQLASScript.ps1 -s TESTSERVER\INST1 -f C:\TestDBnames.txt" -o d:\SQLAS
# Example3:- SQLASScript.ps1 -s TESTSERVER\INST1 -f all -o d:\SQLAS

############################################
$DatasourceDBEngine = "SERVER1\DBINST"  #-Relational DataSource
$DatasourceASEngine = "SERVER1\ASINST"  #-OLAP DataSource
############################################

Clear-Host
$outputFolder = $null
$dateStamp = $null
$SSASServerName = $null
$SSASServer = @()
$db = @()
$dbs = @()
$xmlfile = $null
$xw = @()
$xml =@()
$ds = @()
$splitds = @()
$dblist = @()
$xmlmain = @()
$xmltopass = @()

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")|Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml")|Out-Null

############################################################## FUNCTION TO CHANGE DATASOURCE NAME ############################################################################
Function script:changedatasource([xml]$xmlobj)
{
 $xmlmain = $xmlobj
 foreach($ds in $xmlmain.Create.ObjectDefinition.Database.DataSources.DataSource)
 {
  if($ds.type -eq "RelationalDataSource")
  {
   $splitds = ($ds.ConnectionString).split(";")
   $splitds[1]="Data Source=$DatasourceDBEngine"
   $ds.ConnectionString = $splitds[0]+";"+$splitds[1]+";"+$splitds[2]+";"+$splitds[3]
  }elseif($ds.type -eq "OlapDataSource")
  {
   $splitds = ($ds.ConnectionString).split(";")
   $splitds[1]="Data Source=$DatasourceASEngine"
   $ds.ConnectionString = $splitds[0]+";"+$splitds[1]+";"+$splitds[2]+";"+$splitds[3]
  }else{Write-Host -ForegroundColor Red "Error:Unknown DataSource Type"}
  $xmlmain.Save($xmlfile)  
 }
}

############################################################## FUNCTION TO SCRIPT THE AS DATABASE ############################################################################
Function script:writexmlfn($SSASSource,$ASDbname)
{
 $SSASServerName = $SSASSource
 $SSASServer = New-Object Microsoft.AnalysisServices.Server
 # Connect to the SSAS server
 Try{$SSASServer.Connect($SSASServerName)}
 Catch{Write-Host -ForegroundColor Red "Error:Executing $vwfile"
    Write-Host -ForegroundColor Red $_.Exception.Message
    EXIT;
   }
 if($ASDbname -eq "-all")
 {
  $dbs = $SSASServer.Databases
 }
 else
 {
  $dbs =  $SSASServer.Databases |where {$_.name -in $ASDbname}
 }
 foreach($db in $dbs)
 {
 write-Host "Scripting: " $db.Name
 $script:xmlfile = "$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla"
 $xw = new-object System.Xml.XmlTextWriter("$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla", [System.Text.Encoding]::UTF8) 
    $xw.Formatting = [System.Xml.Formatting]::Indented 
    [Microsoft.AnalysisServices.Scripter]::WriteCreate($xw,$SSASServer,$db,$true,$true) 
    $xw.Close()
 $xml = New-Object xml
 $xml.Load($xmlfile)
 changedatasource $xml
 }
 $SSASServer.Disconnect()
}

##############################################################FUNCTION MAIN()############################################################################
if($args.Length -ne 6)
{
Write-Host "Incorrect Input Paramenter" -ForegroundColor Red
$uParameterHelp = "
Help:-
******

# Script Help :-
#---------------
# Parameter 1 : -s to specify the SQL Server Name
# Parameter 2 : -d if only one database name to be scripted;Specify "databsename" if parameter 2 is -d
#    -f if a set of databases needs to be scripted;Specify "UNCFilepath of the file containing databse names" if parameter 2 is -f and all if parameter 2 is -f and you want to script all databses in the server
# Parameter 3:  -o tp specify the UNC Folder path where output scripts nees to be stored
# Example1: SQLASScript.ps1 -s TESTSERVER\INST1 -d TestDB -o d:\SQLAS
# Example2: SQLASScript.ps1 -s TESTSERVER\INST1 -f C:\TestDBnames.txt -o d:\SQLAS
# Example3: SQLASScript.ps1 -s TESTSERVER\INST1 -f all -o d:\SQLAS"
Write-Host $uParameterHelp -ForegroundColor Blue
}
<#START:Install MAIN Program#>
elseif((($args[0] -eq "-s") -or ($args[0] -eq "-S")) -and (($args[2] -eq "-d") -or ($args[2] -eq "-D") -or  ($args[2] -eq "-f") -or ($args[2] -eq "-F"))-and (($args[4] -eq "-o") -or ($args[4] -eq "-O")))
{
 $outputFolder = $args[5]+"\"
 $dateStamp = (get-Date).ToString("yyyyMMdd") 

 # Variable for your SSAS server name
 $SSASServerName = $args[1]
 
 if((($args[2] -eq "-d") -or ($args[2] -eq "-D")) -and ($args[3] -ne $null))
 {
  $dbname = $args[3]
  writexmlfn $SSASServerName $dbname
 }
 elseif((($args[2] -eq "-f") -or ($args[2] -eq "-F")) -and (($args[3] -eq "all") -or ($args[3] -eq "ALL")))
 {
  $dbname = "-all"
  writexmlfn $SSASServerName $dbname 
  
 <#$xml.Create.ObjectDefinition.Database.DataSources.DataSource.ConnectionString
 $xml.Create.ObjectDefinition.Database.DataSources.DataSource.ConnectionString = "Provider=SQLNCLI10.1;Data Source=EREIDWSDV01\DWDEV;Integrated Security=SSPI;Initial Catalog=ERSBI_Warehouse"
 $xml.Create.ObjectDefinition.Database.DataSources.DataSource.ConnectionString
 $xml.Save($xmlfile)#>
 }elseif((($args[2] -eq "-f") -or ($args[2] -eq "-F")) -and (($args[3] -ne "all") -or ($args[3] -ne "ALL")))
 {
  if(Test-Path $args[3])
  {
   $dblist = Get-Content $args[3]
   writexmlfn $SSASServerName $dblist
  }
 }
}
else
{
Write-Host "Incorrect Input Paramenter" -ForegroundColor Red
$uParameterHelp = "
Help:-
******

# Script Help :-
#---------------
# Parameter 1 : -s to specify the SQL Server Name
# Parameter 2 : -d if only one database name to be scripted;Specify "databsename" if parameter 2 is -d
#    -f if a set of databases needs to be scripted;Specify "UNCFilepath of the file containing databse names" if parameter 2 is -f and all if parameter 2 is -f and you want to script all databses in the server
# Parameter 3:  -o tp specify the UNC Folder path where output scripts nees to be stored
# Example1: SQLASScript.ps1 -s TESTSERVER\INST1 -d TestDB -o d:\SQLAS
# Example2: SQLASScript.ps1 -s TESTSERVER\INST1 -f C:\TestDBnames.txt -o d:\SQLAS
# Example3: SQLASScript.ps1 -s TESTSERVER\INST1 -f all -o d:\SQLAS"
Write-Host $uParameterHelp -ForegroundColor Blue
}

2. REAPPLYAS.ps1

I have used Invoke-ASCMD mehod to reapply the xmla scripts, invoke-ascmd method can be found in sqlps module of powershell and unfortunately this model comes only with SQL Server2012 client, so you need a SQL Server 2012 client installed to run this script but the script will execute perfectly for SQL 2008 AS, we only need the SQL 2012 client only for the invoke the command.

# Script to Reapply scripted Analysis Service database
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 12/06/2013
# Script Help :-
#---------------
# Parameter 1 :- "-s" to specify the SQL Server Name
# Parameter 2 :- "-p" to specify folder path of the script files
# Example1:- ScriptDB.ps1 -s "SQL Server Name" -p "Folder Path"
# Example2:- ScriptDB.ps1 -s MyTestServer -P U:\SQLServer1


Clear-Host
Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
import-module "sqlps" –disablenamechecking -ErrorAction SilentlyContinue
Import-module "sqlascmdlets" -ErrorAction SilentlyContinue


<#*************************************************START:Main Program***************************************************************#>
<#Command Line Argument Verification#>
if($args.Length -ne 4)
{
Write-Host "Incorrect Paramenter, use -s to specify the SQL Server Name and -p to specify folder path of the script files" -ForegroundColor Red
$uParameterHelp = "
Help:-
******
# Parameter 1 :- -s to specify the SQL Server Name
# Parameter  :- -p to specify folder path of the script files
# Example1:- ScriptDB.ps1 -s "SQL Server Name" -p "Folder Path"
# Example2:- ScriptDB.ps1 -s MyTestServer -P U:\SQLServer1"
Write-Host $uParameterHelp -ForegroundColor Blue
}
<#START:Install MAIN Program#>
elseif((($args[0] -eq "-s") -or ($args[0] -eq "-S")) -and (($args[2] -eq "-p") -or ($args[2] -eq "-P")))
{
$SQLInstance = $args[1]
$basepath = $args[3]
 if ((Test-Path $basepath) -eq $false)
 {
   Write-host -ForegroundColor Red "Script folder specified in the Argument cannot be found"
   EXIT;
 }
 Write-Host "Creating Databases....."
 Write-Host ""
   foreach($asdbfile in Get-ChildItem $basepath)
   {
    $asdbfullpath = $basepath+"\"+$asdbfile
    Write-Host ""
    Write-Host "Executing XMLAScript " $asdbfile
    
    Try{Invoke-ASCmd -InputFile $asdbfullpath -Server $SQLInstance -ErrorAction Continue}
    catch{ 
            Write-Host -ForegroundColor Red "Error:Executing $asdbfile"
         Write-Host -ForegroundColor Red $_.Exception.Message
       }
   }
}
else
{
Write-Host "Incorrect Paramenter, use -s to specify the SQL Server Name and -p to specify folder path of the script files" -ForegroundColor Red
$uParameterHelp = "
Help:-
******
# Parameter 1 :- -s to specify the SQL Server Name
# Parameter  :- -p to specify folder path of the script files
# Example1:- ScriptDB.ps1 -s "SQL Server Name" -p "Folder Path"
# Example2:- ScriptDB.ps1 -s MyTestServer -P U:\SQLServer1"
Write-Host $uParameterHelp -ForegroundColor Blue
}
Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

No comments:

Post a Comment