Monday, July 2, 2012

SQL 2012 Upgrade Part 6: SSAS DB Processing Test


One thing we definitely had to be sure of when we upgraded to SQL Server 2012 was that all the Analysis Services databases could be processed successfully.  This process went pretty smoothly but not without a few hiccups. In the end, the issues we ran into were pretty easy to resolve.

SSAS Database Processing Script

Rather than process many dozens of Analysis Services databases manually one-by-one I came up with a PowerShell script to iterate through them all and process them one-by-one. The script I used is shown below. It's kind of ugly in this format but you should be able to cut-and-paste into your favorite PowerShell script editor and use it however you like.

You can pass in the Server name when you run the script from the PowerShell command line. If you don't specify the server name it will use whatever you set as the default when you copy it. The Database Name parameter works a little differently. It defaults to "All" which the script takes to mean that you want to process every database on the server. I do have the script set to ignore databases whose names start with "Tfs_". We use Microsoft's Team Foundation Server product and it doesn't like you to process the TFS analytic databases manually for some reason.

You may also want to change where the processing error files go. Changing the path in line 18 of the script will take care of that.

We have some databases that were developed some time ago and they don't always have proper keys in place; therefore, I had to create the script to allow for this. That is what the $errorConfig object is set up to do in lines 20-23 and used for in line 27.

Finally, here is the script (make sure there are no blank lines or anything at the top):
Param([string]$ServerName="MyTestSSASServer", [string]$DatabaseName="All")
## Add the AMO namespace
$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)

if ($server.name -eq $null) {
    Write-Output ("Server '{0}' not found" -f $ServerName)
    break
}

Clear-Host
foreach ($db in $server.Databases )
{
    if ((($DatabaseName -eq "All") -or ($db.Name -eq $DatabaseName)) -and !$db.Name.StartsWith("Tfs_"))
    {
        $errorLogFilename = "C:\ProcessLogs\" + $db.Name.Replace(" ", "_") + "_Process_Errors.log"
        $errorConfig = New-Object Microsoft.AnalysisServices.ErrorConfiguration($errorLogFilename)
        $errorConfig.KeyNotFound = "ReportAndContinue"
        $errorConfig.KeyErrorAction = "ConvertToUnknown"
        $errorConfig.KeyErrorLimit = -1
        Write-Output("{0}:Start Processing {1}, logging errors to {2} in SSAS Log directory" -f $(Get-Date -f "yyyyMMdd-HHmm"), $db.Name, $errorLogFilename)
        try
        {
            $db.Process("ProcessFull", $errorConfig)
        }
        catch
        {
            $error
        }
        Write-Output("{0}:Done Processing {1}" -f $(Get-Date -f "yyyyMMdd-HHmm"), $db.Name)
        Write-Output("")
        $errorConfig.Dispose()
    }
}

Processing Issues

Once I got the script put together I fired it off and, naturally, got blasted with errors. My first issue was not having the Oracle client installed. Since I cloned one of our query servers and not the processing server the Oracle client wasn't on the source server. (We keep both operations on separate servers.) It's hard to process cubes that query Oracle databases without the client so I installed it and kept moving.

None of the other problems, detailed below, were any big deal either.

Data Source Password Issues

Upgrading the SSAS instance to SQL Server 2012 appears to have cleared the passwords from an data sources that didn't use Windows Authentication. This affected both Oracle and SQL Server data sources. The only hard part about this was that I had to round up some passwords that I wasn't privy to as the login/users were set up before my day.

SQL Server Native Client Data Sources

As you probably know by now, the name of the SQL Server native client provider has changed. This makes all connection strings that use the old provider invalid. The upgrade process did not appear to fix the data sources that use the old provider. A little more research into the AMO object model turned up a way to modify the data source connection string programmatically so I threw together another script to fix all the data sources.

So, here's another PowerShell script for you:
Param([string]$Servername="WSXSQLSSAS01", [string]$DatabaseName="All")
## Add the AMO namespace
$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

[string]$ServerName = 'WSXSQLSSAS01'
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)

Clear-Host
foreach ($db in $server.Databases)
{
    if (($DatabaseName -eq "All") -or ($db.Name -eq $DatabaseName))
    {
        foreach ($ds in $db.DataSources)
        {
            if ($ds.ConnectionString.StartsWith("Provider=SQLNCLI10.1"))
            {
                $newConnectionString = $ds.ConnectionString.Replace("Provider=SQLNCLI10.1", "Provider=SQLNCLI11.1")
                Write-Output("{0} : Fixing {1} : {2}" -f $(Get-Date -f "yyyyMMdd-HHmm"), $db.Name, $ds.Name)
                Write-Output("                {0}" -f $newConnectionString)
                try
                {
                    $ds.ConnectionString = $newConnectionString
                    $ds.Update()
                }
                catch
                {
                    $error
                }
            }
        }
    }
}

SQL Server 2000 Data Sources

I posted about this issue once already so I won't go into it in a lot of detail here. Suffice it to say that the SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions. We had a couple AS databases that contained data sources to application databases (yes, bad form) that were still running on SQL Server 2000. All that was needed post-upgrade was to change the data source to the .NET Provider for SQL Server instead of the native client.

End of Line

That part of the story is over for me. Have you had an upgrade experience that didn't go so well that you would like to share? Let's hear about it in the comments.

No comments:

Post a Comment