Automating Major SQL Version Upgrade with DBATools

You can easily automate a major version upgrade, for example to SQL 2022, using Install-DbaInstance from the DBATools PowerShell module. As of this writing, there was not a documented example, so I played around with it and figured it out. 

If you use the configuration parameter option

$config = @{
    ACTION="Upgrade"
}

You will get this error: The setting 'FEATURES' is not allowed when the value of setting 'ACTION' is 'Upgrade'. The workaround is to use a configuration file to override the FEATURES option that the configuration parameter adds by default.  The configuration file must have a least these three options: ACTION, INSTANCENAME, and QUIET

The following example does a remote upgrade of a named instance to SQL 2022 

$options = '
[OPTIONS]
ACTION="Upgrade"
INSTANCENAME="instance_name"
QUIET="True"
'
Set-Content -Path 'c:\temp\config.ini' -Value $options
 
$paramsUpgrade = @{
    ComputerName      = 'computer_name'
    Version           = '2022'
    Path              = 'sql_install_path’
    UpdateSourcePath  = 'sql_cu_path’
    ConfigurationFile = 'c:\temp\config.ini'
    Restart           = $true
    Credential        = Get-Credential
    Confirm           = $false
}
Install-DbaInstance @paramsUpgrade

Finding Modules that Depend on Az.Accounts

I needed to use new functionality in the Az.Compute module so I updated it to the last version 

Update-Module Az.Compute

But then my script started failing with this error: "Method 'get_SerializationSettings' does not have an implementation." This article suggested to downgrade the version of the Az.Accounts module to 2.12.1. That got me thinking how many modules will be affected by the downgrade of Az.Accounts?

You can find the answer by finding the dependencies of the modules using Find-Module. For example for the Az.Compute the current version at the time of writing this post is 6.3.0 and it has a dependency on Az.Accounts 2.13.0

Find-Module -Name Az.Compute
 
Version Name       Repository Description                                                                           
------- ----       ---------- -----------                                                                           
6.3.0   Az.Compute PSGallery  Microsoft Azure PowerShell...
 
$r = Find-Module -Name Az.Compute
$r.Dependencies
 
Name           Value                                                                                                                                
----           -----                                                                                                                                
Name           Az.Accounts                                                                                                                          
MinimumVersion 2.13.0                                                                                                                               
CanonicalId    powershellget:Az.Accounts/2.13.0#https://www...  

To find all the modules that have a dependency on Az.Accounts 2.12.1 you can use the same cmdlet Find-Module to first get a list of all modules that start with Az, then for each module ($module) get all the versions, next for each module version ($module2), check each dependency ($dep) if it matches the name and version you are looking for (Az.Accounts and 2.12.1), then show the the respective info. The variable $found is used as short circuit flag to break the loop once a match is found 

$depName = 'Az.Accounts'
$depVersion = '2.12.1'
$modules = Find-Module -Name 'az.*' |
Where-Object {$_.Name -ne $refName} |
Sort-Object -Property Name
 
foreach ($module in $modules) {
    $modules2 = Find-Module -Name $module.Name -AllVersions
    $found = $false
    foreach ($module2 in $modules2){
        foreach ($dep in $module2.Dependencies){
            if ($dep.Name -eq $depName -and $dep.MinimumVersion -eq $depVersion){
                [pscustomobject]@{
                    ModuleName = $module2.Name
                    ModuleVersion = $module2.Version
                    DependencyName = $depName
                    DependencyVersion = $depVersion
                }
                $found = $true
                break
            }
            else {
                $found = $false
            }
        }
        if ($found) {
            break
        }
    }
}
 
ModuleName           ModuleVersion DependencyName DependencyVersion
----------           ------------- -------------- -----------------
Az.Aks               5.3.2         Az.Accounts    2.12.1          
Az.ArcResourceBridge 0.1.0         Az.Accounts    2.12.1          
Az.Batch             3.4.0         Az.Accounts    2.12.1          
Az.Billing           2.0.1         Az.Accounts    2.12.1          
Az.CognitiveServices 1.13.1        Az.Accounts    2.12.1          
Az.Compute           5.7.0         Az.Accounts    2.12.1          
Az.ContainerRegistry 3.0.3         Az.Accounts    2.12.1          
Az.CosmosDB          1.10.0        Az.Accounts    2.12.1          
Az.CostManagement    0.3.1         Az.Accounts    2.12.1          
Az.DataProtection    1.2.0         Az.Accounts    2.12.1          
Az.EventGrid         1.6.0         Az.Accounts    2.12.1          
Az.EventHub          3.2.3         Az.Accounts    2.12.1          
Az.Kusto             2.2.0         Az.Accounts    2.12.1          
Az.Network           5.6.0         Az.Accounts    2.12.1          
Az.Reservations      0.12.0        Az.Accounts    2.12.1          
Az.Resources         6.6.0         Az.Accounts    2.12.1          
Az.Search            0.9.0         Az.Accounts    2.12.1          
Az.ServiceBus        2.2.1         Az.Accounts    2.12.1          
Az.Sql               4.5.0         Az.Accounts    2.12.1          
Az.SqlVirtualMachine 1.1.1         Az.Accounts    2.12.1          
Az.Storage           5.5.0         Az.Accounts    2.12.1          
Az.Websites          2.14.0        Az.Accounts    2.12.1          
Az.Workloads         0.1.0         Az.Accounts    2.12.1      

From the output you can see that in our example to use Az.Accounts 2.12.1 you require Az.Compute 5.6.0 instead of 6.3.0. Fortunately, Az.Compute 5.6.0 still had the new functionality I was looking. Thus, reinstalling the correct versions fixed the issue.

Uninstall-Module Az.Accounts -RequiredVersion 2.13.0
Install-Module Az.Accounts -RequiredVersion 2.12.1
Uninstall-Module Az.Compute -RequiredVersion 6.3.0
Install-Module Az.Compute -RequiredVersion 5.6.0

An Exception Occurred in SMO While Trying to Manage a Service

One way to automate SQL tasks is by using SMO, but sometimes when there are multiple SQL versions installed or uninstalled it may be corrupted and you may get "An exception occurred in SMO while trying to manage a service..." when using PowerShell or “Cannot connect to WMI provider. You do not have permission or the server in unreachable…” when using SQL Server Configuration Manager. In this post I will show you how to easily fix it. 

To use SMO with PowerShell, first you need to load the assembly

   

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement') | Out-Null

Next, you can create an instance of the .Net object, providing the name of the server, e.g. PABLITO

   

$s = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer PABLITO
$s

ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
Services           :
ClientProtocols    :
ServerInstances    :
ServerAliases      :
Urn                : ManagedComputer[@Name='PABLITO']
Name               : PABLITO
Properties         : {}
UserData           :
State              : Existing

Note that in the results above, services is blank, so when you reference it, you will get the error

   

$s.Services

The following exception occurred while trying to enumerate the collection:
"An exception occurred in SMO while trying to manage a service.".
At line:1 char:1
+ $s.Services
+ ~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], ExtendedTypeSystemException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator

The fix is to compile the sqlmgmproviderxpsp2up.mof of the highest SQL version installed in the machine. You can quickly search the path of the .mof, order by Creation Date, and displaying the Directory name

   

Get-ChildItem "c:\program files (x86)\Microsoft SQL Server\*\Shared\sqlmgmproviderxpsp2up.mof" |
Sort-Object CreationTime |
Select-Object Directory
 
Directory
---------
C:\program files (x86)\Microsoft SQL Server\90\Shared
C:\program files (x86)\Microsoft SQL Server\100\Shared
C:\program files (x86)\Microsoft SQL Server\110\Shared

You can see that this machine has three SQL versions installed SQL 2005, 2008, and 2012 (Yes I still support those old versions but not by choice) To get the highest version, you just need to add -Last 1 and assign the result to the variable $i to execute mofcomp

   

$i = Get-ChildItem "c:\program files (x86)\Microsoft SQL Server\*\Shared\sqlmgmproviderxpsp2up.mof" |
Sort-Object CreationTime |
Select-Object Directory -Last 1

mofcomp "$($i.Directory)\sqlmgmproviderxpsp2up.mof"

Microsoft (R) MOF Compiler Version 6.1.7600.16385
Copyright (c) Microsoft Corp. 1997-2006. All rights reserved.
Parsing MOF file: C:\program files (x86)\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof
MOF file has been successfully parsed
Storing data in the repository...
Done!

Now you can get the services with no errors

   

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SqlWmiManagement') | Out-Null
$s = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer PABLITO
$s.Services

Setting Delete On Termination in Attached AWS Volumes

By default, any additional EBS volumes that you attach to an EC2 instance persist even after the instance terminates, unless Delete On Termination is set on each of the attached volumes. This AWS doc shows how to set it in the console and cli but not in PowerShell. 

However, the cli example uses "aws ec2 modify-instance-attribute" and you can find in the AWS PowerShell reference doc a cmdlet with similar name Edit-EC2InstanceAttribute. So here is how to set it with PowerShell.

First, you need to instantiate two classes InstanceBlockDeviceMappingSpecification and EbsInstanceBlockDeviceSpecification

$bdm = New-Object Amazon.EC2.Model.InstanceBlockDeviceMappingSpecification
$ebs = New-Object Amazon.EC2.Model.EbsInstanceBlockDeviceSpecification

Next assign values similar to the cli json format example

     

[
  {
    "DeviceName": "device_name",
    "Ebs": {
      "DeleteOnTermination": true
    }
  }
]

The equivalent in PowerShell is the following

     

$ebs.DeleteOnTermination = $true
$bdm.DeviceName = 'device_name'
$bdm.Ebs = $ebs

Finally, call the cmdlet

     

Edit-EC2InstanceAttribute -InstanceId 'id' -BlockDeviceMapping $bdm -Region 'region'

Search-AzGraph for All Subscriptions

A while ago I wrote about Search-AzGraph here. Since then, I have used it quite a bit and learned that depending on how you connect you may not query all subscriptions

Connecting Specifying a Subscription

To illustrate this behavior, let's say you have a service principal with access to 3 subscriptions, so to connect you would use the cmdlet Connect-AzAccount specifying the first subscription context

$paramAz = @{
    ServicePrincipal = $true
    TenantId         = 'tttttttt-tttt-tttt-tttt-tttttttttttt'
    Credential       = Get-Credential
    Subscription     = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
}
Connect-AzAccount @paramAz

Then you can query the resource count in all subscriptions with this Kusto query

$query = "
Resources
| summarize count() by subscriptionId
"

As expected, the cmdlet Search-AzGraph returned the resource count in all 3 subscriptions

Search-AzGraph -Query $query 

subscriptionId                       count_
--------------                       ------
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx    577
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz    111

However, if you change the context to the second subscription using Set-AzContext, you will see that now Search-AzGraph only shows the resource count for the second subscription

Set-AzContext -Subscription 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy'

Search-AzGraph -Query $query 

subscriptionId                       count_
--------------                       ------
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338

Moreover, if you change the context to the third subscription, you will see that Search-AzGraph shows the resource count for the second and third subscriptions

Set-AzContext -Subscription 'zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz'

Search-AzGraph -Query $query 

subscriptionId                       count_
--------------                       ------
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz    111

The reason is that Search-AzGraph uses the cumulative context when a subscription context is set in the connection, Get-Context shows the two subscriptions Search-AzGraph will use

(Get-AzContext).Account.ExtendedProperties.Subscriptions

yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy,
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz

A workaround is to pass to Search-AzGraph a list of all subscriptions using Get-AzSubscription

$subIds = (Get-AzSubscription).Id

Search-AzGraph -Query $query -Subscription $subIds

subscriptionId                       count_
--------------                       ------
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx    577
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz    111

Connecting Without Specifying a Subscription

In this scenario, you would use the cmdlet Connect-AzAccount without specifying a subscription

$paramAz = @{
    ServicePrincipal = $true
    TenantId         = 'tttttttt-tttt-tttt-tttt-tttttttttttt'
    Credential       = Get-Credential
}
Connect-AzAccount @paramAz

Next, you change the context to the second subscription using Set-AzContext, you will see that Search-AzGraph shows the resource count for all subscriptions, unlike our previous case

Set-AzContext -Subscription 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy'

Search-AzGraph -Query $query 

subscriptionId                       count_
--------------                       ------
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx    577
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz    111

Because a subscription context is not set in the connection, Get-Context will show all the subscriptions Search-AzGraph will use

(Get-AzContext).Account.ExtendedProperties.Subscriptions

xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy,
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz

Conclusion

If you specify a subscription context in the connection, Search-AzGraph will not look in all subscriptions when you change the context later. You can override this behavior by passing a subscription list. On the other hand, if you do not specify the subscription context in the connection, Search-AzGraph will look in all subscriptions regardless of changing the context.

Nesting Invoke-SqlCmd in the Pipeline

If you need to collect data from an list of SQL Servers stored in a table, you would write a PowerShell script that uses Invoke-SqlCmd to query the list from the table and then loop through each instance to execute another query. However, PowerShell 5 does not support nesting Invoke-SqlCmd in the pipeline. The workaround is to use an intermediate variable or use PowerShell 7. 

Scenario 

For example you have a table with the SQL instance names stored in SQL1

create table instances(name varchar(255))
insert instances values ('SQL1')
insert instances values ('SQL2')
insert instances values ('SQL3')

To query the list, you use Invoke-SqlCmd in PowerShell 5

$query = "select name from instances"
Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query

To loop through each instance, you pipe the results and use Invoke-SqlCmd to execute another query to let's say get the version

Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query |
ForEach-Object {
    $query2 = "select instance = serverproperty('InstanceName'), version = serverproperty('ProductVersion')"
    Invoke-Sqlcmd -ServerInstance $_.name -Query $query2
}

The script will fail with this error

# Invoke-Sqlcmd : The WriteObject and WriteError methods cannot be 
# called from outside the overrides of the BeginProcessing, ProcessRecord,     
# and EndProcessing methods, and they can only be called from within the same thread.
# Validate that the cmdlet makes these calls correctly, or contact Microsoft Customer 
# Support Services.

Workaround 

Use a variable $instances to hold the results of the first Invoke-SqlCmd and then pipe it

$instances = Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query 
$instances |
ForEach-Object {
    $query2 = "select instance = serverproperty('InstanceName'), version = serverproperty('ProductVersion')"
    Invoke-Sqlcmd -ServerInstance $_.name -Query $query2
}

or use PowerShell 7

Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query |
ForEach-Object {
    $query2 = "select instance = serverproperty('InstanceName'), version = serverproperty('ProductVersion')"
    Invoke-Sqlcmd -ServerInstance $_.name -Query $query2
}

# instance     version
# --------     -------
# SQL1         13.0.5865.1
# SQL2         13.0.5865.1
# SQL3         13.0.5492.2

Not Defined as Subscriber Error

In SQL 2019, if you create a transnational push subscription to a database that participates in an Availability Group, using the subscriber name format <AGListener>, <port number> then it will fail with the error:

Msg 20032, Level 16, State 1, Procedure distribution.dbo.sp_MSadd_subscription, Line 175 [Batch Start Line 0]
'AGLISTENER,54321' is not defined as a Subscriber for 'SERVER1\INSTANCE1'.
Msg 14070, Level 16, State 1, Procedure sys.sp_MSrepl_changesubstatus, Line 1249 [Batch Start Line 0]
Could not update the distribution database subscription table. The subscription status could not be changed.
Msg 14057, Level 16, State 1, Procedure sys.sp_MSrepl_addsubscription_article, Line 384 [Batch Start Line 0]
The subscription could not be created.
Msg 20021, Level 16, State 1, Procedure sys.sp_MSrepl_addpushsubscription_agent, Line 258 [Batch Start Line 0]
The subscription could not be found.

Similar error will happen if you upgrade the remote distributor to SQL 2019. It is a bug fixed in CU5 released last week.

Assuming you have a replication environment like this

• Publisher: SERVER1/INSTANCE1
• Distributor: SERVER2/INSTANCE2
• Subscriber: AGLISTENER,54321

The script to reproduce the issue is:

-- Publisher
create database pub_test
go

use pub_test
create table test(a int primary key, b varchar(255))
go
exec sp_replicationdboption @dbname = N'pub_test', @optname = N'publish', @value = N'true'
exec sp_addpublication @publication = N'test', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true'
exec sp_addpublication_snapshot @publication = N'test'
exec sp_addarticle @publication = N'test', @article = N'test', @source_owner = N'dbo', @source_object = N'test'
go

-- Susbcriber
create database sub_test
go

-- Publisher

exec sp_addsubscription @publication = N'test', @subscriber = N'AGLISTENER,54321', @destination_db = N'sub_test', @subscription_type = N'Push'
exec sp_addpushsubscription_agent @publication = N'test', @subscriber = N'AGLISTENER,54321', @subscriber_db = N'sub_test'