PowerShell - Report Server Integration - Part 2

The first part in establishing Reporting Services is to configure SS2008.  Between cmdlets and WMI objects, this script became a success.  (Catch up on your SS2008 PowerShell reading here).

You can read all about how to manually use the management tools to do this at http://msdn.microsoft.com/en-us/library/bb283151.aspx.

In order to repeat this process through PowerShell, my script does the following steps:
1)  Generate and execute a new database SQL script.
2)  Generate and execute a permissions SQL script.
3)  Connect SS2008 Reporting Services to the new Report Server database.
4)  Stop, then Start the SS2008 Reporting Services service.

Note:  The "Get-WmiObject" cmdlet seemed to give me some fits when it was all lower cased (instead of the camel case at the beginning of this note).

# #######################################################
# ##### Configure SQL Server Report Server - PowerShell Script #####
# #######################################################

try
{
    Write-Log $logFile "Being configuration of SQL Server 2008 Reporting Services" $lvlInfo

    # Capture the wmi namespace property into a local variable
    $wmiNameSpace = "root\Microsoft\SqlServer\ReportServer"

    # Retrieve the ReportServer namespace definition object
    $nsDef = Get-WmiObject -ComputerName $serverName -class "__namespace" -namespace $wmiNameSpace -ErrorAction SilentlyContinue
   
    # Check for existence of namespace definition object
    if ($nsDef -eq $null)
    {
        throw "Failed to find the local Report Server."
    }
   
    # Expand the wmi namespace property to include the specific Report Server instance
    $wmiNameSpace = "$($wmiNameSpace)\$($nsDef.name)\v10\Admin"
   
    # Retrieve the MSReportServer_ConfigurationSetting object
    $rsConfig = Get-WmiObject -ComputerName $serverName -class "MSReportServer_ConfigurationSetting" -namespace $wmiNameSpace -ErrorAction SilentlyContinue
   
    # Check that the object was successfully retrieved
    if ($rsConfig -eq $null)
    {
        throw "Failed to obtain Report Server configuration object."
    }

    # Create the SQL script that will generate a new ReportServer database
    $methResult = $rsConfig.GenerateDatabaseCreationScript($reportServer, $lcid, $true)

    # Check the success of creating the SQL script
    if ($methresult["HRESULT"] -ne 0)
    {
        throw "Failed to create the SQL Script for generating a new report server database."
    }

    # Set the local SQL Server as the base location for executing SQL scripts
    Set-Location SQLSERVER:\SQL\$serverName\$instName

    # Execute the SQL Script for creating a new Report Server
    invoke-sqlcmd -Query $methResult["Script"] -Username $sqlUser -Password $sqlPwd -OutputSqlErrors $true | Out-Null
   
    # Set the base location back to the original command prompt for executing scripts
    Set-Location $currentLocation

    # Create a SQL Script that provides the service account access to the newly created database

    $methResult = $rsconfig.GenerateDatabaseRightsScript($sqlServiceAcct, $reportServer, $false, $true)

    # Check the success of creating the SQL script
    if ($methresult["HRESULT"] -ne 0)
    {
        throw "Failed to create the SQL Script for providing access for the service account to the new report server database"
    }

    # Set the local SQL Server as the base location for executing SQL scripts
    Set-Location SQLSERVER:\SQL\$serverName\$instName

    # Execute the SQL Script for providing the service account access to the new Report Server
    invoke-sqlcmd -Query $methResult["Script"] -Username $sqlUser -Password $sqlPwd -OutputSqlErrors $true | Out-Null

    # Set the base location back to the original command prompt for executing scripts
    Set-Location $currentLocation

    # Set the SQL Server Reporting Services to use the newly created Report Server
    $methResult = $rsConfig.SetDatabaseConnection($dbServer, $reportServer, $credType, $sqlUser, $sqlPwd)

    # Check the success of setting the database connection
    if ($methresult["HRESULT"] -ne 0)
    {
        throw "Failed to change SQL Server's Reporting Services to use the newly created Report Server"
    }

    # Stop and Start SQL Server's Reporting Services to ensure changes take affect
    $rsconfig.SetServiceState($false, $false, $false)
    $rsconfig.SetServiceState($true, $true, $true)

    # With the restarting of the service, recapture the WMI object and confirm changes
    $rsConfig = Get-WmiObject -ComputerName $serverName -class "MSReportServer_ConfigurationSetting" -namespace $wmiNameSpace -ErrorAction SilentlyContinue

    # Check that the SQL Server Reporting Services is ready for SharePoint integration.
    if ($rsConfig.IsSharePointIntegrated -eq $false)
    {
        throw "The SQL Server Reporting Services Report Server database is not configured for SharePoint integration."
    }

    Write-Log $logFile "Successfully Configured SQL Server with a new Report Server and is ready for SharePoint integration." $lvlInfo
}
catch
{
    Write-Log $logFile $Error[0] $lvlError
}

# Ensure the return of the current location back to local system.
Set-Location $currentLocation

********************
PowerShell - Report Server Integration - Part 1
PowerShell - Report Server Integration - Part 3
PowerShell - Report Server Integration - Part 4
********************