Getting SQL Services Information with Powershell

Hopefully you already know everything about your SQL estate, including what services are installed and what’s running them, either because it’s so small you just know, or, preferably, you have some kind of CMDB. But what if you don’t? Powershell comes to the rescue, of course.

This post details a script to capture basic information – service name, account, start mode and status – about the database engine, Agent and any SSRS/SSAS/SSRS installs. It requires remote WMI connectivity and, for SSIS, SMB access to enabled administrative shares on the drives where the service is installed.

(Note – if all you’re after is the Database Engine and/or SQL Agent info, and you’re running SQL 2008 R2 SP1 or higher, no need to carry on reading – just use the sys.dm_server_services DMV instead, it’s easy.)

To begin we need some kind of data source containing both the hostnames and instance names (MSSQLSERVER if default instance) of the servers in the estate – as we’ll be connecting to the hostname but making checks using the instance name. Either stored in a table;

$instancesquery = "SELECT Hostname, InstanceName
                    FROM dbo.Instances"

$instances = Invoke-Sqlcmd -ServerInstance SQL2017A -Database 'TestDB' -Query $instancesquery

or from another source, say, a csv file – in this case an example of a single instance, which I’ll use as an example source for the rest of the post;

$instances = Import-Csv -Path "C:\temp\servers.txt"

Then we can loop over the servers and use the Get-WmiObject Win32_Service cmdlet. For the database engine, Agent, SSRS and SSAS this is straightforward – we’re filtering on the service name looking for a reference to the instance, then disregarding any CEIP services and pulling out a few pieces of information. For example, a snippet of the script checking for a SSAS install;

$SSAS = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server Analysis Services ($($instance.InstanceName))" | Where-Object -Property DisplayName -NotLike "*CEIP*" | Select-Object DisplayName, StartName, State, StartMode
Write-Output $SSAS

SSIS is a bit trickier. You may have noticed that, unlike for the Database Engine, SSRS or SSAS, the instance name is not part of the service name;

So we can’t just filter on the service name to check which instance the service is installed as part of. Instead we need to find the MsDtsSrvr.ini.xml file associated with the service (by using the service PathName), open it up as XML (this is where the requirement for SMB and administrative share access arises) and look at the DtsServiceConfiguration.TopLevelFolders.Folder.ServerName element;

$ConfigXMLPath = ($SSIS.PathName.Substring(0,$SSIS.PathName.IndexOf('MsDtsSrvr.exe')) + 'MsDtsSrvr.ini.xml') -replace '"', ''

$ConfigXMLPath = "\\$($instance.Hostname)\" + $SSIS.PathName.Substring(1,1) + '$\' + $ConfigXMLPath.Substring(3)

[xml]$ConfigXML = Get-Content -Path $ConfigXMLPath

if($ConfigXML.DtsServiceConfiguration.TopLevelFolders.Folder.ServerName -like "*$($instance.InstanceName)*")
{
    Write-Output $SSIS
}
elseif(($ConfigXML.DtsServiceConfiguration.TopLevelFolders.Folder.ServerName -EQ ".") -and ($instance.InstanceName -eq 'MSSQLSERVER'))
{
    Write-Output $SSIS
}

An example output from the whole script, ran on a single server;

This shows I have the database engine and agent running from MSAs, and two extra services installed – SSAS running as a local service and SSIS running as an MSA, all set to auto-start and only SSIS not currently running.

The entire skeleton script is below (github link), using the CSV file example for retrieving host/instance names as a base and simply writing the output to console – it’s easily modifiable to change the source and/or save the output to a file or table.

<#       
                
$instancesquery = "SELECT Hostname, InstanceName       
                
                   FROM dbo.Instances"       
                
       
                
$instances = Invoke-Sqlcmd -ServerInstance SQL2017A -Database 'TestDB' -Query $instancesquery       
                
#>       
      
$instances = Import-Csv -Path "C:\temp\servers.txt"       
                          
foreach ($instance in $instances)                     
{       
    Write-Output "Connecting to $($instance.Hostname)\$($instance.InstanceName).."       
                         
    try       
    {               
        $test = Get-WmiObject Win32_Service -ComputerName $instance.Hostname -ErrorAction Stop                 
    }       
    catch       
    {       
        Write-Output "Error connecting remotely to WMI"       
                
        continue       
    }       
                  
    # Database Engine                
    $DatabaseEngine = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server ($($instance.InstanceName))" | Select-Object DisplayName, StartName, State, Startmode       

    Write-Output $DatabaseEngine       
                

    # SQL Agent               
    $SQLAgent = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server Agent ($($instance.InstanceName))" | Select-Object DisplayName, StartName, State, Startmode       

    Write-Output $SQLAgent       
                  
    # SSAS       
    $SSAS = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server Analysis Services ($($instance.InstanceName))" | Where-Object -Property DisplayName -NotLike "*CEIP*" | Select-Object DisplayName, StartName, State, StartMode       

    Write-Output $SSAS       

    # SSRS       
    $SSRS = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server Reporting Services ($($instance.InstanceName))" | Where-Object -Property DisplayName -NotLike "*CEIP*" | Select-Object DisplayName, StartName, State, StartMode       
                
    Write-Output $SSRS       

    #SSIS       
    $SSISinstances = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -Like "SQL Server Integration Services*" | Where-Object -Property DisplayName -NotLike "*CEIP*" | Select-Object DisplayName, PathName, StartName, State, StartMode       
                

    try       
    {       
        foreach($SSIS in $SSISinstances)       
        {          
            $ConfigXMLPath = ($SSIS.PathName.Substring(0,$SSIS.PathName.IndexOf('MsDtsSrvr.exe')) + 'MsDtsSrvr.ini.xml') -replace '"', ''       

            $ConfigXMLPath = "\\$($instance.Hostname)\" + $SSIS.PathName.Substring(1,1) + '$\' + $ConfigXMLPath.Substring(3)       

            [xml]$ConfigXML = Get-Content -Path $ConfigXMLPath              

            if ($ConfigXML.DtsServiceConfiguration.TopLevelFolders.Folder.ServerName -like "*$($instance.InstanceName)*")       
            {       
                Write-Output ($SSIS | Select-Object DisplayName, StartName, State, StartMode)       
            }       
            elseif (($ConfigXML.DtsServiceConfiguration.TopLevelFolders.Folder.ServerName -EQ ".") -and ($instance.InstanceName -eq 'MSSQLSERVER'))       
            {       
                Write-Output ($SSIS | Select-Object DisplayName, StartName, State, StartMode)       
            }       
        }       
    }       
    catch       
    {       
        Write-Output "SSIS Error: " + $error[0]       
    }       
}

My Favourite Data Type (T-SQL Tuesday #136)

My favourite data type is date.

Introduced in SQL Server 2008, the date datatype brought storage of dates in the YYYY-MM-DD format – 0001-01-01 to 9999-12-31, 3 bytes.
This was an addition to the existing types of datetime – 2021-03-09 12:20:16.021, 8 bytes – and smalldatetime – 2021-03-09 12:20, 4 bytes.

It solved a range problem (datetimes can’t store years prior to 1753, smalldatetimes 1900) that previously required hacking around, but so did the new datetime2 type, so that’s not why I’m nominating it my favourite data type for T-SQL Tuesday.

The reason is simply what it is – a date without a time component.

Often we just want a date and don’t care about the time at all, nevermind the time to millisecond accuracy. Think transaction dates, staff joining dates, DOBs, etc. But the existing types foisted a time upon the column along with extra bytes of storage, and I couldn’t begin to count the times I’ve had to type this pattern;

WHERE DateTimeColumn BETWEEN '2020-03-09 00:00:00.000' AND '2020-03-09 23:59:59.999'

-- or if performance is not an issue

WHERE CONVERT(DATE, DateTimeColumn) = '2020-03-09'

With date it’s simple;

WHERE DateColumn = '2020-03-09'

Three cheers for this three part, three byte hero.

Now, I was still at college when date was born and years away from touching SQL. One might think that 13 years later the code I’ve been working with recently as a dev/DBA would have plentiful usage of date when appropriate…

Unfortunately not. This would have been very different if date had been in SQL 2005 or 2000, but it wasn’t – so the result is oodles of datetimes left lying around because the schemas had already been designed.
Then they hang around not only because existing code is extended over the years, but because people doing entirely new deployments copy conventions from existing codebases.

It’s a great example of a ghost of a missing feature still haunting the present. I wonder how many GB/TB (/PB??) on the planet is wasted by datetimes with 00:00:00.000 time components right now.

This month’s T-SQL Tuesday is hosted by Brent Ozar

Changing SQL Server Login Auditing setting with T-SQL/Powershell

The login auditing setting on a SQL Server instance controls the auditing of logins – either all, successful only, failed only or none. It’s set via SSMS on the Server properties dialog;

and is responsible for these types of message in the log;

You might want to change this for several reasons – perhaps you have strict auditing requirements so want everything logged, or perhaps you only care about failures – especially if you have a noisy application that floods the log with successful logins.

It’s straightforward enough to change in SSMS – though note that a service restart is required for changes to take effect – but what if you want to change this setting on a bunch of servers? Fortunately, it can be programmatically read/changed using the (undocumented) xp_instance_regread and xp_instance_regwrite extended stored procedures, reading from/writing to the Windows registry value AuditLevel (specifically, the value in the correct key for the instance the proc is executed from) – that the setting in SSMS actually represents;

Illustration purposes only – we don’t need to go into the Registry manually for this.

This key maps to the following values;

  • 0 – None
  • 1 – Successful logins only
  • 2 – Failed logins only
  • 3 – All logins only

To retrieve the value, we can use xp_instance_regread ;

DECLARE @result INT
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @result OUTPUT
SELECT @result AS 'AuditLevel'

To set it, we use xp_instance_regwrite. An example of setting it to 2 (Failed logins only);

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 2

So by calling this procedure we can script out a read/change to the setting to multiple instances at once using Powershell. (Yep, we could directly modify the registry with Powershell and skip the call, but doing it this way benefits from the wrapping of the registry modification in the SQL extended proc, and thus the correct key for the instance being automatically used thanks to the ‘instance’ versions of the reg read/write procs vs the standard xp_regread/regwrite)

Retrieving the key is a straightforward iteration over instances. In this example I’ve populated an $instances variable with an array of instance names, but this can be substituted with reading from a text file or database;

$regread = "DECLARE @result INT
            EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @result OUTPUT
            SELECT @result AS 'AuditLevel'"

$instances = @('SQL1', 'SQL2', 'SQL3')

foreach($instance in $instances)
{
    try
    {          
        $flag = 99
        $flag = (Invoke-Sqlcmd -ServerInstance $instance -Query $regread -ConnectionTimeout 3 -QueryTimeout 3 -ErrorAction Stop).AuditLevel
       
        Write-Host "$($instance): $($flag)"
     }
     catch
     {
         Write-Host "$($instance): Error"
         Write-Host $error[0]
     }
}

Setting the key is also a straightforward iteration. In this example I’m also setting the value to ‘2’ (Failed logins only);

$regwrite = "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
                N'Software\Microsoft\MSSQLServer\MSSQLServer', 
                N'AuditLevel', REG_DWORD, 2"

$instances = @('SQL1', 'SQL2', 'SQL3')

foreach($instance in $instances)
{
    try
    {          
        Invoke-Sqlcmd -ServerInstance $instance -Query $regwrite -ConnectionTimeout 3 -QueryTimeout 3 -ErrorAction Stop
        Write-Host "$($instance.ConnectionString): Success"
    }
    catch
    {
        Write-Host "$($instance): Error"
        Write-Host $error[0]
    }
}

After modification, the first script can be re-ran to confirm the modifications – and don’t forget a service restart is required to kick the setting into action.

Disclaimer – the registry-editing procedures used here are undocumented, and editing the registry is generally best avoided – any such code should be tested thoroughly before use in production.

Automating SSISDB Migration 4/4 – Copying Object Parameter Changes with Powershell

Last in this four-part series on automating a non-wizard SSIS migration (Part one, two and three).

To recap, previous scripts covered exporting both MSDB and SSISDB projects and then – after manual upgrade/deployment- copying environments and their references. The final piece of the puzzle is to copy object parameter values that have been manually overriden – where a value is shown in bold in SSMS after clicking Configure on a project/package and the ‘Edit Value’ has some manually-set value;

I wrote a function Copy-SSISObjectParameters to do this. It runs at Project level, so takes a Project name along with source/target servers and Folder name as parameters. As in the last post’s script for copying environments, I’m using the inbuilt SSISDB procedure [catalog].[set_object_parameter_value] to wrap the process.

Because of the way Powershell handles passing boolean values, I had to do some plumbing involving creating and passing a BIT variable in the SQL batch (the proc will accept a numeric 1 or 0 but will essentially corrupt the parameter making it uneditable in the GUI), otherwise it’s quite straightforward, and works for both Project and Package level ‘overrides’. The -WhatIf switch outputs the SQL statements it would normally run without the switch.

Example – Copy-SSISObjectParameters -SourceServer SQLSSIS2014 -TargetServer SQLSSIS2017 -FolderName DailyETL -ProjectName LoadDailySales

Unfortunately the Code Syntax Block plugin isn’t displaying the full code nicely below and it would be an awful lot of manual editing to fix, especially inappropriate for a blog post series on automation, but it’s working as normal on github – Copy-SSISObjectParameters

And that’s probably it for my Powershell-SSIS repo for now, pending further migration work that might need something adding, and pending the fate of SSIS in the face of ADF, for that matter!

function Copy-SSISObjectParameters
 {
 param
 (
     [Parameter(position=0, mandatory=$true)][string]$SourceServer,
     [Parameter(position=1, mandatory=$true)][string]$TargetServer,
     [Parameter(position=2, mandatory=$true)][string]$FolderName,
     [Parameter(position=3, mandatory=$true)][string]$ProjectName,
     [Parameter(position=4, mandatory=$false)][switch]$WhatIf
 )
 <#
 .SYNOPSIS
 Copies object parameter overrides from a SSIS project on a source instance to a target instance.
 .DESCRIPTION
 Copies object parameter overrides - that is, 'Edited' values on Configuration dialog - from a SSIS project 
 on a source instance to a target instance. This includes both project and package-level overrides,
 but does not include environment references, which are copied as part of the Copy-SSISEnvironments function.
 .PARAMETER SourceServer
 Specifies the source SQL instance name
 .PARAMETER TargetServer
 Specifies the target SQL instance name
 .PARAMETER FolderName
 Specifies the SSISDB folder the project is in. Case sensitive.
 .PARAMETER ProjectName
 Specifies the SSIS project (including packages) to copy the object parameter overrides from and to. Case sensitive.
 .PARAMETER WhatIf
 Outputs generated SQL statements only - does not execute them or make any other changes.
 .OUTPUTS
 No direct outputs from function - updates target server SSISDB object parameters
 .EXAMPLE
 PS> Copy-SSISObjectParameters -SourceServer SQLSSIS2014 -TargetServer SQLSSIS2017 -FolderName DailyETL -ProjectName LoadDailySales
 Sets project/package parameter overrides in SSIS project DailyETL/LoadDailySales on server SQLSSIS2017 to the same as the project on SQLSSIS2014
 >
 Import-Module SqlServer
 Check that folders and projects exist on both servers
 $checkexistsquery = "SELECT 1 FROM [catalog].[folders] f
                             INNER JOIN [catalog].[projects] p
                                 ON f.folder_id = p.folder_id
                             WHERE f.[name] = '$($FolderName)'
                                 AND p.[name] = '$($ProjectName)'"
 try
 {
     $check = Invoke-Sqlcmd -ServerInstance $SourceServer -Database SSISDB -Query $checkexistsquery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop
 }
 catch
 {
     Write-Output "Error connecting to source server."
     Write-Output $error[0]
     return
 }
 if(!$check)
 {
     Write-Output "Error: Folder or project doesn't exist on source server."
     return
 }
 try
 {
     $check = Invoke-Sqlcmd -ServerInstance $TargetServer -Database SSISDB -Query $checkexistsquery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop
 }
 catch
 {
     Write-Output "Error connecting to target server."
     Write-Output $error[0]
     return
 }
 if(!$check)
 {
     Write-Output "Error: Folder or project doesn't exist on target server."
     return
 }
 Get manually overridden parameter values (value_type V)
 $getoverridevaluesquery = "SELECT op.object_type, op.object_name, op.parameter_name, op.data_type, op.default_value
                             FROM [catalog].[object_parameters] op
                             INNER JOIN [catalog].[projects] p
                                 ON op.project_id = p.project_id
                             INNER JOIN [catalog].[folders] f
                                 ON p.folder_id = f.folder_id
                             WHERE f.[name] = '$($FolderName)'
                             AND p.[name] = '$($ProjectName)'
                             AND op.value_type = 'V'
                             AND op.value_set = 1"
 $overridevalues = Invoke-Sqlcmd -ServerInstance $SourceServer -Database SSISDB -Query $getoverridevaluesquery -ConnectionTimeout 3 -QueryTimeout 5 -ErrorAction Stop
 foreach($overridevalue in $overridevalues)
 {
     if($overridevalue.data_type -eq 'String')
     {
         if($overridevalue.object_type -eq 20) # Project parameter
         {
             $updateparameterquery = "EXEC [catalog].[set_object_parameter_value]
                                                                         @object_type = $($overridevalue.object_type),
                                                                         @folder_name = '$($FolderName)',
                                                                         @project_name = '$($ProjectName)',
                                                                         @parameter_name = '$($overridevalue.parameter_name)',
                                                                         @parameter_value = N'$($overridevalue.default_value)',
                                                                         @value_type = 'V'"
         }
         elseif($overridevalue.object_type -eq 30) # Package parameter
         {
             $updateparameterquery = "EXEC [catalog].[set_object_parameter_value]
                                                                         @object_type = $($overridevalue.object_type),
                                                                         @folder_name = '$($FolderName)',
                                                                         @project_name = '$($ProjectName)',
                                                                         @parameter_name = '$($overridevalue.parameter_name)',
                                                                         @parameter_value = N'$($overridevalue.default_value)',
                                                                         @object_name = '$($overridevalue.object_name)',
                                                                         @value_type = 'V'"
         }
     }
     elseif($overridevalue.data_type -eq 'Boolean')
     {
         if($overridevalue.default_value -eq 'True')
         {
             $bool_value = 1
         }
         else
         {
             $bool_value = 0
         }
     if($overridevalue.object_type -eq 20) # Project parameter     {         $updateparameterquery = "DECLARE @var BIT;                                  SET @var = $($bool_value);                                  EXEC [catalog].[set_object_parameter_value]                                                                     @object_type = $($overridevalue.object_type),                                                                     @folder_name = '$($FolderName)',                                                                     @project_name = '$($ProjectName)',                                                                     @parameter_name = '$($overridevalue.parameter_name)',                                                                     @parameter_value = @var,                                                                     @value_type = 'V'"     }     elseif($overridevalue.object_type -eq 30) # Package parameter     {         $updateparameterquery = "DECLARE @var BIT;                                  SET @var = $($bool_value);                                  EXEC [catalog].[set_object_parameter_value]                                                                     @object_type = $($overridevalue.object_type),                                                                     @folder_name = '$($FolderName)',                                                                     @project_name = '$($ProjectName)',                                                                     @parameter_name = '$($overridevalue.parameter_name)',                                                                     @parameter_value = @var,                                                                     @object_name = '$($overridevalue.object_name)',                                                                     @value_type = 'V'"     } } else # Integer or other number {     if($overridevalue.object_type -eq 20) # Project parameter     {         $updateparameterquery = "EXEC [catalog].[set_object_parameter_value]                                                                     @object_type = $($overridevalue.object_type),                                                                     @folder_name = '$($FolderName)',                                                                     @project_name = '$($ProjectName)',                                                                     @parameter_name = '$($overridevalue.parameter_name)',                                                                     @parameter_value = $($overridevalue.default_value),                                                                     @value_type = 'V'"     }     elseif($overridevalue.object_type -eq 30) # Package parameter     {         $updateparameterquery = "EXEC [catalog].[set_object_parameter_value]                                                                     @object_type = $($overridevalue.object_type),                                                                     @folder_name = '$($FolderName)',                                                                     @project_name = '$($ProjectName)',                                                                     @parameter_name = '$($overridevalue.parameter_name)',                                                                     @parameter_value = $($overridevalue.default_value),                                                                     @object_name = '$($overridevalue.object_name)',                                                                     @value_type = 'V'"      } } if($WhatIf) {     $updateparameterquery } else {     try     {         Invoke-Sqlcmd -ServerInstance $TargetServer -Database SSISDB -Query $updateparameterquery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop         Write-Output "Updated parameter $($overridevalue.parameter_name)"     }     catch     {         Write-Output "Error updating parameter $($overridevalue.parameter_name)"         Write-Output $updateparameterquery         Write-Output $error[0]     } }
 }
 }

Automating SSISDB Migration 3/4 – Copying SSIS Environments with Powershell

Part 3 of a 4-part series on automating an SSISDB migration without using the SSISDB Upgrade Wizard. Parts one and two covered exporting MSDB and SSISDB projects, after which upgrading then deploying from Visual Studio is neccessary. This post assumes this is done and the next task is to copy the environments over, as they exist outside of SSIS projects and are thus not carried with them.

To automate this we have to do a few different things;

  • Copy the environments
  • Copy environment variables
  • Copy environment references (between environment to project)
  • Copy environment-object parameter links

I created the Copy-SSISEnvironments function shown below to perform these steps. It works at folder level, taking a source/target server and foldername. Though it’s possible to directly update the internal schema tables with the required values, I’m using the built-in catalog stored procedures which wrap and sanity check the required code for safety, namely;

  • [catalog].[create_environment]
  • [catalog].[create_environment_variable]
  • [catalog].[create_environment_reference]
  • [catalog].[set_object_parameter_value]

The function supports ‘relative’ references (that is projects referencing an Environment in the same Folder), string, boolean and numeric datatypes, de and re-encrypting encrypted strings, and both project and package-level object references. It doesn not support copying ‘absolute’ references (projects referencing an Environment in a different folder) or decrypting non-string datatypes as I had no need for that in our environment. The -WhatIf switch will simply output the SQL statements it would otherwise execute, and is recommended to run before use to check the output.

Example – Copy-SSISEnvironments -SourceServer SQLSSIS2014 -TargetServer SQLSSIS2017 -FolderName MySSISFolder

Github link to the Powershell-SSIS repo I’ve added this to.

function Copy-SSISEnvironments
{
param
(
    [Parameter(position=0, mandatory=$true)][string]$SourceServer,
    [Parameter(position=1, mandatory=$true)][string]$TargetServer,
    [Parameter(position=2, mandatory=$true)][string]$FolderName,
    [Parameter(position=3, mandatory=$false)][switch]$WhatIf
)

<#
.SYNOPSIS
Copies environments and references from one SSISDB folder to the same folder on another server

.DESCRIPTION
Copies environments - including variables, project references and object-variable links - from a source SSISDB folder to the same folder on a target server.
Works with string, numeric, boolean and encrypted string variables and relative project references.
Does not copy absolute references.

.PARAMETER SourceServer
Specifies the source SQL instance name

.PARAMETER TargetServer
Specifies the target SQL instance name

.PARAMETER FolderName
Specifies the SSISDB folder to copy the environment from and to. Case sensitive.

.PARAMETER WhatIf
Outputs generated SQL statements only - does not execute them or make any other changes.

.OUTPUTS
No direct outputs from function - updates target server SSISDB folder

.EXAMPLE
PS> Copy-SSISEnvironments -SourceServer SQLSSIS2014 -TargetServer SQLSSIS2017 -FolderName DailyETL

Copies environment data from SSISDB folder 'DailyETL' on server SQLSSIS2014 to server SQLSSIS2017
#>

Import-Module SqlServer

### Check that folder exists on both servers
$checkexistsquery = "SELECT 1 FROM [catalog].[folders] f
                     WHERE f.[name] = '$($FolderName)'"

try
{
    $check = Invoke-Sqlcmd -ServerInstance $SourceServer -Database SSISDB -Query $checkexistsquery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop
}
catch
{
    Write-Output "Error connecting to source server."
    Write-Output $error[0]
    return
}


if(!$check)
{
    Write-Output "Error: Folder $($FolderName) doesn't exist on source server."
    return
}

try
{
    $check = Invoke-Sqlcmd -ServerInstance $TargetServer -Database SSISDB -Query $checkexistsquery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop
}
catch
{
    Write-Output "Error connecting to target server."
    Write-Output $error[0]
    return
}

if(!$check)
{
    Write-Output "Error: Folder $($FolderName) doesn't exist on target server."
    return
}



$source_folder_id = 0
$target_folder_id = 0

$GetFolderIDQuery = "SELECT folder_id
                    FROM [catalog].folders
                    WHERE [name] = '$($FolderName)'"

# Get folder_id of source and target folders
$source_folder_id = (Invoke-Sqlcmd -ServerInstance $SourceServer -Database SSISDB -Query $GetFolderIDQuery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop).folder_id
$target_folder_id = (Invoke-Sqlcmd -ServerInstance $TargetServer -Database SSISDB -Query $GetFolderIDQuery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop).folder_id

$GetEnvironmentQuery = "SELECT environment_id, [name], [description]
                            FROM [catalog].environments
                            WHERE folder_id = '$($source_folder_id)'"

# Get environments from the source folder
$envs = (Invoke-Sqlcmd -ServerInstance $SourceServer -Database SSISDB -Query $GetEnvironmentQuery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop)


# Get environment references 
$GetEnvironmentRefsQuery = "SELECT p.project_id, p.[name] AS 'ProjectName', er.reference_type, er.environment_name
                            FROM [catalog].projects p
                            INNER JOIN [catalog].environment_references er
	                            ON p.project_id = er.project_id
                            WHERE p.folder_id = $($source_folder_id)"

try
{
    $env_refs = Invoke-Sqlcmd -ServerInstance $SourceServer -Database SSISDB -Query $GetEnvironmentRefsQuery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop
}
catch
{
    Write-Output "Error retrieving environment references"
    $GetEnvironmentRefsQuery
    Write-Output $error[0]
}

 
foreach($env in $envs)
{
    # Create environment on target server
    $CreateNewEnvironmentQuery = "EXEC [SSISDB].[catalog].[create_environment] @folder_name = '$($FolderName)', @environment_name = '$($env.name)', @environment_description = '$($env.description)'"

    if($WhatIf)
    {
        Write-Output $CreateNewEnvironmentQuery
    }
    else
    {
        try
        {
            Invoke-Sqlcmd -ServerInstance $TargetServer -Database SSISDB -Query $CreateNewEnvironmentQuery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop
            Write-Output "Created environment $($env.name)"
        }
        catch
        {
            Write-Output "Error creating environment"
            Write-Output $CreateNewEnvironmentQuery
            Write-Output $error[0]

            return
        }
    }

    # Get environment variables
    $GetEnvironmentVarsQuery = "SELECT variable_id, [name], sensitive, [type], [description], [value]
                                FROM [catalog].environment_variables
                                WHERE environment_id = $($env.environment_id)"

    $env_vars = (Invoke-Sqlcmd -ServerInstance $SourceServer -Database SSISDB -Query $GetEnvironmentVarsQuery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop)

    # Loop over environment variables
    foreach($env_var in $env_vars)
    {
        if($env_var.type -eq 'String')
        {
            # If variable is sensitive string, pull the encrypted value from [internal] table and decrypt it
            if($env_var.sensitive -eq 1)
            {
                $getencryptedvaluequery = "SELECT [internal].[get_value_by_data_type](DECRYPTBYKEYAUTOCERT(CERT_ID(N'MS_Cert_Env_' + CONVERT(NVARCHAR(20), [environment_id])), NULL, [sensitive_value]), [type]) AS DecryptedValue
                                            FROM [SSISDB].[internal].[environment_variables]
                                            WHERE variable_id = $($env_var.variable_id)"

                try
                {
                    $decryptedvalue = (Invoke-Sqlcmd -ServerInstance $SourceServer -Database SSISDB -Query $getencryptedvaluequery -ConnectionTimeout 3 -QueryTimeout 5 -ErrorAction Stop).DecryptedValue

                    $CreateEnvironmentVarQuery = "EXEC [SSISDB].[catalog].[create_environment_variable] @environment_name = '$($env.name)',
	                                                                                                    @folder_name = '$($FolderName)',
	                                                                                                    @variable_name = '$($env_var.name)',
	                                                                                                    @Sensitive = 1,
	                                                                                                    @data_type = '$($env_var.type)',
	                                                                                                    @description = '$($env_var.description)',
	                                                                                                    @value = N'$($decryptedvalue)'"
                }
                catch
                {
                    Write-Output "Failed to retrieve encrypted variable value"
                    $getencryptedvaluequery
                    Write-Output $error[0]
                }

            } 
            else
            {
                $CreateEnvironmentVarQuery = "EXEC [SSISDB].[catalog].[create_environment_variable] @environment_name = '$($env.name)',
	                                                                                                @folder_name = '$($FolderName)',
	                                                                                                @variable_name = '$($env_var.name)',
	                                                                                                @Sensitive = 0,
	                                                                                                @data_type = '$($env_var.type)',
	                                                                                                @description = '$($env_var.description)',
	                                                                                                @value = N'$($env_var.value)'"
            }
        }
        elseif($env_var.type -eq 'Boolean')
        {
            if($env_var.value -eq 'True')
            {
                $bool_value = 1
            }
            else
            {
                $bool_value = 0
            }

            $CreateEnvironmentVarQuery = "EXEC SSISDB.[catalog].[create_environment_variable] @environment_name = '$($env.name)',
	                                                                                            @folder_name = '$($FolderName)',
	                                                                                            @variable_name = '$($env_var.name)',
	                                                                                            @Sensitive = $([int]$env_var.sensitive),
	                                                                                            @data_type = '$($env_var.type)',
	                                                                                            @description = '$($env_var.description)',
	                                                                                            @value = $($bool_value)"

        }
        else
        {
            $CreateEnvironmentVarQuery = "EXEC SSISDB.[catalog].[create_environment_variable] @environment_name = '$($env.name)',
	                                                                                            @folder_name = '$($FolderName)',
	                                                                                            @variable_name = '$($env_var.name)',
	                                                                                            @Sensitive = $([int]$env_var.sensitive),
	                                                                                            @data_type = '$($env_var.type)',
	                                                                                            @description = '$($env_var.description)',
	                                                                                            @value = $($env_var.value)"
        }
        

        if($WhatIf)
        {
            Write-Output $CreateEnvironmentVarQuery
        }
        else
        {
            try
            {
                Invoke-Sqlcmd -ServerInstance $TargetServer -Database SSISDB -Query $CreateEnvironmentVarQuery -QueryTimeout 5 -ConnectionTimeout 5 -ErrorAction Stop
                Write-Output "Created variable $($env_var.name)"
            }
            catch
            {
                Write-Output "Error creating environment variable."
                Write-Output $CreateEnvironmentVarQuery
                Write-Output $error[0]
            }
        }
    }

    # Check if a reference exists for this environment, then for projects in target folder
    foreach($env_ref in $env_refs)
    {
        if($env_ref.reference_type -eq 'A')
        {
            Write-Warning "Warning - Absolute reference detected. Add manually. Project name $($env_ref.ProjectName)"
        }
        elseif($env_ref.environment_name -eq $env.name)
        {
            $CheckProjectExistsQuery = "SELECT 1 AS 'Result' WHERE EXISTS (SELECT [name]
                                                            FROM [catalog].projects
                                                            WHERE folder_id = $($target_folder_id)
                                                            AND name = '$($env_ref.ProjectName)')"

            #### Add reference for $env_ref.ProjectName

            try
            {
                $exists = (Invoke-SqlCmd -ServerInstance $TargetServer -Database SSISDB -Query $CheckProjectExistsQuery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop).Result

                if($exists -eq 1)
                {
                    # Add environment reference for this project
                    $CreateEnvironmentRefQuery = "EXEC [SSISDB].[catalog].[create_environment_reference] @environment_name = '$($env.name)',
														    @project_name = '$($env_ref.ProjectName)',
														    @folder_name = '$($FolderName)',
														    @reference_type = R,
                                                            @reference_id = NULL"
                    
                    if($WhatIf)
                    {
                        Write-Output $CreateEnvironmentRefQuery
                    }
                    else
                    {
                        try
                        {
                            Invoke-Sqlcmd -ServerInstance $TargetServer -Database SSISDB -Query $CreateEnvironmentRefQuery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop
                            Write-Output "Created reference between $($env_ref.ProjectName) and $($env.name)"


                            
                            #### Set object parameters to use environment variables where linked

                            $getobjectparamsquery = "SELECT object_type, [object_name], parameter_name, referenced_variable_name
                                        FROM catalog.object_parameters
                                        WHERE project_id = $($env_ref.project_id)
                                        AND value_type = 'R'"

                            $object_params = Invoke-Sqlcmd -ServerInstance $SourceServer -Database SSISDB -Query $getobjectparamsquery -ConnectionTimeout 3 -QueryTimeout 5 -ErrorAction Stop

                            foreach($object_param in $object_params)
                            {
                                if($object_param.object_type -eq 20) # Project parameter
                                {
                                    $setobjectparamquery = "EXEC [catalog].[set_object_parameter_value]
                                                                    @object_type = $($object_param.object_type),
                                                                    @folder_name = '$($FolderName)',
                                                                    @project_name = '$($env_ref.ProjectName)',
                                                                    @parameter_name = '$($object_param.parameter_name)',
                                                                    @parameter_value = '$($object_param.referenced_variable_name)',
                                                                    @value_type = 'R'"    
                                }
                                elseif($object_param.object_type -eq 30) # Package parameter
                                {
                                    $setobjectparamquery = "EXEC [catalog].[set_object_parameter_value]
                                            @object_type = $($object_param.object_type),
                                            @folder_name = '$($FolderName)',
                                            @project_name = '$($env_ref.ProjectName)',
                                            @parameter_name = '$($object_param.parameter_name)',
                                            @parameter_value = '$($object_param.referenced_variable_name)',
                                            @object_name = '$($object_param.object_name)',
                                            @value_type = 'R'"  
                                }

                                try
                                {
                                    Invoke-Sqlcmd -ServerInstance $TargetServer -Database SSISDB -Query $setobjectparamquery -ConnectionTimeout 3 -QueryTimeout 5 -ErrorAction Stop
                                    Write-Output "Linked parameter $($object_param.parameter_name) to env var $($object_param.referenced_variable_name)"
                                }
                                catch
                                {
                                    Write-Output "Error linking parameter."
                                    $setobjectparamquery
                                    Write-Output $error[0]
                                }

                            }

                        }
                        catch
                        {
                            Write-Output "Error creating environment reference."
                            $CreateEnvironmentRefQuery
                            Write-Output $error[0]
                        }
                    }
                }
            }
            catch
            {
                Write-Output $CheckProjectExistsQuery
                Write-Output $error[0]
            }


            if($WhatIf)
            {
                #### Set object parameters to use environment variables where linked

                $getobjectparamsquery = "SELECT object_type, [object_name], parameter_name, referenced_variable_name
                                        FROM catalog.object_parameters
                                        WHERE project_id = $($env_ref.project_id)
                                        AND value_type = 'R'"

                $object_params = Invoke-Sqlcmd -ServerInstance $SourceServer -Database SSISDB -Query $getobjectparamsquery -ConnectionTimeout 3 -QueryTimeout 5 -ErrorAction Stop

                foreach($object_param in $object_params)
                {
                    if($object_param.object_type -eq 20)
                    {
                        $setobjectparamquery = "EXEC [catalog].[set_object_parameter_value]
                                                        @object_type = $($object_param.object_type),
                                                        @folder_name = '$($FolderName)',
                                                        @project_name = '$($env_ref.ProjectName)',
                                                        @parameter_name = '$($object_param.parameter_name)',
                                                        @parameter_value = '$($object_param.referenced_variable_name)',
                                                        @value_type = 'R'"    
                    }
                    elseif($object_param.object_type -eq 30)
                    {
                        $setobjectparamquery = "EXEC [catalog].[set_object_parameter_value]
                                @object_type = $($object_param.object_type),
                                @folder_name = '$($FolderName)',
                                @project_name = '$($env_ref.ProjectName)',
                                @parameter_name = '$($object_param.parameter_name)',
                                @parameter_value = '$($object_param.referenced_variable_name)',
                                @object_name = '$($object_param.object_name)',
                                @value_type = 'R'"  
                    }

                    $setobjectparamquery
                }
            }

        }

    }

}

}