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

Monday, February 11, 2013

Moving SSRS 2012 with Powershell and Bourbon

Recently, talk of splitting a fairly large SSRS instance off on to its own box came up.  Given the BI shop at my employer is aggressive and gets things done often, there are lots of reports and they are used by people daily to make decisions.  The one issue as I see it with being aggressive and not having heavy handed code reviews or dictatorial team leads is things don't get done the same way each time.  So, in this case, what you have is some reports pointing to localhost, some point to the server name and others to the server alias.  In our case, we want to get everything pointing to the alias to make it easier when we move.

Naturally, Powershell came up as the magical elixir to this predicament, even though there may be other viable ways.  My thoughts were I would be able to grab someone else's script easily enough and just modify it for our environment, but that didn't work out well.  It appears few people are using SQL Server 2012 and SSRS in native mode (or at least they choose to blog about it, ask question on it in forums, etc)  For one reason or another, Sharepoint seems to be more prominent, and that is where we are going as well, piece by piece.

This script follows the easy road of getting collections into variables and then looping through them, there are much more condensed ways to write this script.  I doubt this will work with any other of the Reporting Services versions.

So here is the script - be darn careful since I haven't kicked the tires on this completely yet.  I have commented out the main damaging part toward the bottom, as of my tests, this report will manage both shared and embedded data sources.  I have commented this pretty hard, and as the careful eye of @Equerystrian pointed out, I even had lies in my comments.  Which explains why commenting code is bad :)

Note: I made a few edits after not liking how it handled the shared data sources, the process now collects them for each report and then updates a unique list at the end, a bit cleaner.  

 [Boolean]$DoDamage = $false     #If you wish to update the DataSources  
 $SSRS2012Server = "MySSRSServer"   #SSRS Server  
 $OldSQLServer = "MyOldDataSource"    #Old DataSource Server Name you want to replace  
 $NewSQLServer = "MyNewDataSource"     #New DataSource Server Name   
 $ReportPathMatch = "/Development*" # To specify certain reports, * for all  
 #URL to the web service  
 $SSRSurl = "http://{0}/ReportServer/ReportService2010.asmx" -f $SSRS2012Server  
 # Get a handle on the service  
 $SSRS = New-WebServiceProxy -uri $SSRSurl -UseDefaultCredential;   
 # Get a collection of the reports on the server  
 $itemlist = $SSRS.ListChildren("/", $true) | Where-Object {$_.TypeName -eq "Report" -and $_.Path -like $ReportPathMatch} ;  
 #Creating a report tracker  
 $ReportChanges =@()  
 $SharedDataSources = @(); #Track Shared Data Sets for Updating after checking all reports  
 #Creates a report tracking object  
 function Add-ReportChanges  
   param([string]$reportPath, [string]$connString, [string]$dataSourceType , [string]$newConnString)  
   $report = New-Object System.Object  
   $report | Add-Member -type NoteProperty -name "Report" -value $reportPath  
   $report | Add-Member -type NoteProperty -name "Connection" -value $connString  
   $report | Add-Member -type NoteProperty -name "DataSourceType" -value $dataSourceType  
   $report | Add-Member -type NoteProperty -name "NewConnection" -value $newConnString  
   return $report    
 #Loop Through Each Report  
 Foreach($item in $itemlist)  
   Write-Host "Checking " $item.Path  
     $dsList = $null #clearing it out explicitly  
     #Any given report may have multiple data sources, grab the collection  
     $dsList = $ssrs.GetItemDataSources($item.Path)  
     #Loop through each DS in the report  
     for($i=0;$i -le $dsList.Length-1;$i++)  
       $ds = $dsList[$i];  
       $newItem = $null  
       $dsType = $null    
       $dataSource = $null  
       $oldConnString = $null  
       #If the DS has a reference it would appear to be a shared datasource  
       if($ds.Item.Reference -ne $null)   
         $dataSource = $ssrs.GetDataSourceContents($ds.Item.Reference);  
         $dsType = "Shared"  
       else #embedded datasource  
         $dataSource = $ds.Item  
         $dsType = "Embedded"  
       $oldConnString = $dataSource.ConnectString  
        # Old Name Exist in Connection  
       if($oldConnString -ilike "*Data Source=$OldSQLServer*")  
         # If a Shared Datasource add to list for updating later, if embedded, update it.    
         If($dsType -eq "Shared")   
           $SharedDataSources += $ds.Item.Reference   
           $ReportChanges += Add-ReportChanges -reportPath $item.Path -connString $oldConnString -dataSourceType $dsType -newConnString "Shared"        
           # Make a Copy of the datasource  
           $newitem = $dataSource  
           # Simple replace of the server name  
           $newItem.ConnectString = $oldConnString -iReplace $OldSQLServer, $NewSQLServer  
           $dsList[$i] = $newItem  
           $DataSourceChanged = $true # Flip this bit so we know to save this DS collection back to SSRS   
           $ReportChanges += Add-ReportChanges -reportPath $item.Path -connString $oldConnString -dataSourceType $dsType -newConnString $newItem.ConnectString  
         $ReportChanges += Add-ReportChanges -reportPath $item.Path -connString $oldConnString -dataSourceType $dsType -newConnString ""        
     } #for each datasource    
     # Push the datasource back on the report  
       Write-Host "Updating Datasources for " $Item.Path  
       IF($DoDamage) {$SSRS.SetItemDataSources($Item.Path, $dsList)}  
     Write-Host "Error on " $item.Path $Error[0]  
 #Now get a unique list of the shared data sources and update them  
 Foreach($sh in $SharedDataSources | Sort-Object | Get-Unique)  
   $ref = $ssrs.GetDataSourceContents($sh);  
   $oldConnString = $ref.ConnectString  
   $ref.ConnectString = $oldConnString -iReplace $OldSQLServer, $NewSQLServer  
     Write-Host "Updating $oldConnString for $sh"  
     Write-Host "Would have updated $sh with " $ref.ConnectString  
 $ReportChanges | out-gridview  

To close out this post, near my home is a distillery that buys the bulk of their grains from local farmers.  If you are into Vodka, Bourbon or Rye, give http://www.45thparallelspirits.com/ a try.  Here is a picture of my favorite spirit they produce.

No comments:

Post a Comment