SSISDB Best Practices

SSISDB Best Practices

22, May Klaas

In this article I talk about how I transformed a blog post into a PowerShell script. More particular Koen Verbeeck's article on SSISDB Best Practices.
It checks some settings on a SSISDB catalog and SSIS server and reports what complies and what not.

Reading the blog post

SSIS catalog Best practices on MSSQLTips.

Koen Verbeeck explains some guidelines concerning the configuration of the SSIS catalog and SSISDB. The catalog is available starting from SQL Server 2012.
Koen ends with the advice:

Take a look at the SSIS catalogs in your environments and double check if they are configured correctly.

That's exactly what I want to do now. But, of course, not by opening SSMS.

Extract the Commandments

While you should read the entire article to get the reasoning behind those rules, for my purpose I reduce the content to the actual rules.
We find some should be set on the SSISDB database, and others on the configuration of the catalog.

Thou shalt:

  • CLR needs to be enabled to create the catalog
  • The Server-wide Default Logging Level should be 'Performance'
  • The Retention period should be around 30
  • Clean Logs Periodically should be 'True'
  • SSISDB Recovery Model should be 'Simple'
  • Maximum Number of Versions per Project should be '3'

Where are those things?

  • IsSqlClrEnabled is a SQL Instance configuration property
  • RecoveryModel is a Database property

We shall get both through a SMO connection to a SQL Server. 

# Connect to the Sql Instance:
$sqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlinstance
  • MaxProjectVersions
  • OperationLogRetentionTime
  • OperationCleanupEnabled
  • ServerLoggingLevel
    are all properties of the Catalog named 'SSISDB', which we find with the "Microsoft.SqlServer.Management.IntegrationServices" namespace. 
    # Connect to the SSIS:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;
$sqlConnectionString = "Data Source=$sqlinstance;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
# Create the Integration Services object
$SSIS = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection

Check for a Catalog

We know that there's only one catalog, named 'SSISDB' starting with SQL Server 2012. Maybe this can change in the future. For now, we can skip all checks on catalog names and numbers. We just quit if the catalog count is 0. 

# If there is no catalog, it all ends here:
if ( $SSIS.Catalogs.Count -eq 0 ) {
Write-Host "There is no SSIS catalog on $sqlinstance." -ForegroundColor Red 
return
}

Do I pass?

This is the part where we get to know whether we comply to Koen's rules or not.
We simply collect the properties we need and show them in red or green. You can guess what that means, no?

I'm using Write-Host here, and I'm not ashamed about that! You'll find lots of advice saying "Never use Write-Host!!", and I agree when talking about functions and modules, and any case where you want your script to be versatile and you want to share with others. Also in situations where you want to automate the collection in a Scheduled Task, for example, you should not use Write-Host.
But here, I intend to use this simple script Ad Hoc, and want to see immediately what's right and what's wrong. 

$cat = $ssis.catalogs['SSISDB']
if ( $sqlserver.Configuration.IsSqlClrEnabled -eq 1 )
{ Write-Host "CLR is enabled on $sqlinstance." -ForegroundColor Green }
 else { Write-Host "CLR is not enabled on $sqlinstance." -ForegroundColor Red }

if ( $sqlserver.Databases['SSISDB'].RecoveryModel -ne 'Simple' ) { $color = 'Red' } else { $color = 'Green' }
 Write-Host "The SSISDB database on $sqlinstance recovery model is $($sqlserver.Databases['SSISDB'].RecoveryModel)." -ForegroundColor $color

if ( $cat.MaxProjectVersions -ge $maxversions) { $color = 'Red' } else { $color = 'Green' }
 Write-Host "The SSIS catalog on $sqlinstance keeps $($cat.MaxProjectVersions) versions of each project." -ForegroundColor $color

if ( $cat.OperationLogRetentionTime -ge $retentiondays) { $color = 'Red' } else { $color = 'Green' }
 Write-Host "The SSIS catalog on $sqlinstance keeps the logs for $($cat.OperationLogRetentionTime) days." -ForegroundColor $color

if ( $cat.OperationCleanupEnabled -ne $true) { Write-Host "The SSIS catalog logs are not purged automatically." -ForegroundColor Red }
 else { Write-Host "The SSIS catalog on $sqlinstance logs are purged automatically." -ForegroundColor  Green }

if ( $cat.ServerLoggingLevel -eq 'Basic') { $color = 'Red' } else { $color = 'Green' }
 Write-Host "The SSIS catalog on $sqlinstance logging level is $($cat.ServerLoggingLevel) ." -ForegroundColor $color

Write-Host in red and green

Some extras

While examining the Catalog properties we can see there are OperationLogNumberOfRecords and OperationLogSize properties. And although not asked for, I thought it would be nice to know what's already in the SSIS Catalog Operations Log. 

Write-Host "There are $($cat.OperationLogNumberOfRecords) log records in the SSIS catalog operations log on $sqlinstance.`n`
that's $($cat.OperationLogSize / 1024) MB." -ForegroundColor Yellow

Operationlogsize

It's also obvious what Folders, Projects and Packages stands for, so I just added those using nested loops.

 $cat.Folders | foreach {
    $_.projects | foreach {
        $_.packages |
            select @{l='FolderId';e={$_.parent.parent.FolderId}}, @{l='Folder';e={$_.parent.parent.Name}}, @{l='ProjectId';e={$_.parent.ProjectId}}, @{l='Project';e={$_.parent.Name}}, @{l='Package';e={$_.Name}} | Format-Table -AutoSize
        }
    }

A complete script

# supply the necessary variables:
$sqlinstance = 'YourServerName'
$maxversions = 3
$retentiondays = 31

# Connect to the Sql Instance:
Import-Module sqlserver
$sqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlinstance

# Connect to the SSIS:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;
$sqlConnectionString = "Data Source=$sqlinstance;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
# Create the Integration Services object
$SSIS = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection

# If there is no catalog, it all ends here:
if ( $SSIS.Catalogs.Count -eq 0 ) {
Write-Host "There is no SSIS catalog on $sqlinstance." -ForegroundColor Red 
return
}

# examine all the rules:
$cat = $ssis.catalogs['SSISDB']
if ( $sqlserver.Configuration.IsSqlClrEnabled -eq 1 )
{ Write-Host "CLR is enabled on $sqlinstance." -ForegroundColor Green }
 else { Write-Host "CLR is not enabled on $sqlinstance." -ForegroundColor Red }

if ( $sqlserver.Databases['SSISDB'].RecoveryModel -ne 'Simple' ) { $color = 'Red' } else { $color = 'Green' }
 Write-Host "The SSISDB database on $sqlinstance recovery model is $($sqlserver.Databases['SSISDB'].RecoveryModel)." -ForegroundColor $color

if ( $cat.MaxProjectVersions -ge $maxversions) { $color = 'Red' } else { $color = 'Green' }
 Write-Host "The SSIS catalog on $sqlinstance keeps $($cat.MaxProjectVersions) versions of each project." -ForegroundColor $color

if ( $cat.OperationLogRetentionTime -ge $retentiondays) { $color = 'Red' } else { $color = 'Green' }
 Write-Host "The SSIS catalog on $sqlinstance keeps the logs for $($cat.OperationLogRetentionTime) days." -ForegroundColor $color

if ( $cat.OperationCleanupEnabled -ne $true) { Write-Host "The SSIS catalog logs are not purged automatically." -ForegroundColor Red }
 else { Write-Host "The SSIS catalog on $sqlinstance logs are purged automatically." -ForegroundColor  Green }

if ( $cat.ServerLoggingLevel -eq 'Basic') { $color = 'Red' } else { $color = 'Green' }
 Write-Host "The SSIS catalog on $sqlinstance logging level is $($cat.ServerLoggingLevel) ." -ForegroundColor $color

# some extras:
Write-Host "There are $($cat.OperationLogNumberOfRecords) log records in the SSIS catalog operations log on $sqlinstance.`n`
that's $($cat.OperationLogSize / 1024) MB." -ForegroundColor Yellow

 $cat.Folders | foreach {
    $_.projects | foreach {
        $_.packages |
            select @{l='FolderId';e={$_.parent.parent.FolderId}}, @{l='Folder';e={$_.parent.parent.Name}}, @{l='ProjectId';e={$_.parent.ProjectId}}, @{l='Project';e={$_.parent.Name}}, @{l='Package';e={$_.Name}} | Format-Table -AutoSize
        }
    }

To be continued

This is by no means a finished script. It's not flexible, portable or versatile, but it works.
I just wanted to show a way to use PowerShell to automate checks found in a blog post.
Maybe one day this can be turned into a proper function. I need to write comment based help, add error handling and proper parameters, make it accept multiple Sql instances and piped in parameters, supply alternative credentials, and return proper objects instead of text.

Previous Post Next Post

[email protected]