Calculating Network Latency Between Always On Availability Group Replicas

We had a business requirement to add a cloud-based asynchronous replica to an on-premises SQL Server Always On Availability Group. After adding a high-volume database to the Availability Group, we noticed that the log send queue started growing rapidly.

Our initial suspicion was that network latency between the on-premises primary replica and the cloud replica was contributing to the problem. However, before requesting a network upgrade, we needed a way to prove that network performance was actually affecting data movement.

One useful approach is to use the Always On data movement Extended Events trace described in Microsoft's article:

Troubleshooting data movement latency between synchronous-commit AlwaysOn Availability Groups

Although Microsoft's example focuses on a synchronous-commit replica, the same data movement events can be used to investigate an asynchronous replica, with some important differences.

The First Symptom: A Growing Send Queue

The first indication was that the log send queue was significantly larger than the redo queue.

For example:

SELECT
    ar.replica_server_name AS ReplicaName,
    DB_NAME(drs.database_id) AS DatabaseName,
    drs.log_send_queue_size AS LogSendQueueKB,
    drs.redo_queue_size AS RedoQueueKB
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar
    ON drs.replica_id = ar.replica_id
WHERE DB_NAME(drs.database_id) = 'DB1';

The result looked like this:

ReplicaName	DatabaseName	LogSendQueueKB	RedoQueueKB
SQL1	DB1	79,604,352	88

The important observation is the difference between the two queues. The send queue was approximately 76 GB, while the redo queue was only 88 KB.

This suggested that the secondary was not primarily struggling to redo the log it had already received. Instead, a significant amount of log was accumulating before it could be delivered to the secondary.

That made network throughput or latency a strong candidate for further investigation.

Capturing the Data Movement Trace

The next step was to capture an Always On data movement trace on both the primary and secondary replicas.

It is important to run the trace for approximately the same period on both servers so that the events can be correlated.

In our case, we captured the trace for 2 minutes and 30 seconds.

IF EXISTS
(
    SELECT *
    FROM sys.server_event_sessions
    WHERE name = 'AlwaysOn_Data_Movement_Tracing'
)
BEGIN
    DROP EVENT SESSION [AlwaysOn_Data_Movement_Tracing]
    ON SERVER;
END
GO

CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER
ADD EVENT sqlserver.hadr_apply_log_block,
ADD EVENT sqlserver.hadr_capture_filestream_wait,
ADD EVENT sqlserver.hadr_capture_log_block,
ADD EVENT sqlserver.hadr_capture_vlfheader,
ADD EVENT sqlserver.hadr_db_commit_mgr_harden,
ADD EVENT sqlserver.hadr_log_block_compression,
ADD EVENT sqlserver.hadr_log_block_decompression,
ADD EVENT sqlserver.hadr_log_block_group_commit,
ADD EVENT sqlserver.hadr_log_block_send_complete,
ADD EVENT sqlserver.hadr_lsn_send_complete,
ADD EVENT sqlserver.hadr_receive_harden_lsn_message,
ADD EVENT sqlserver.hadr_send_harden_lsn_message,
ADD EVENT sqlserver.hadr_database_flow_control_action,
ADD EVENT sqlserver.hadr_transport_flow_control_action,
ADD EVENT ucs.ucs_connection_flow_control,
ADD EVENT sqlserver.hadr_transport_receive_log_block_message,
ADD EVENT sqlserver.log_block_pushed_to_logpool,
ADD EVENT sqlserver.log_flush_complete,
ADD EVENT sqlserver.recovery_unit_harden_log_timestamps
ADD TARGET package0.event_file
(
    SET filename = N'E:\AlwaysOn_Data_Movement_Tracing.xel',
        max_file_size = (500),
        max_rollover_files = (4)
)
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO

ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing]
ON SERVER STATE = START;

WAITFOR DELAY '00:02:30';

ALTER EVENT SESSION [AlwaysOn_Data_Movement_Tracing]
ON SERVER STATE = STOP;
GO

Important: Extended Events can generate a significant amount of data on a busy SQL Server. Keep the capture window as short as practical and monitor the size of the .xel files.

Finding a Log Block to Correlate

Once the trace has been collected, we need to identify the database in the trace.

On the secondary replica, run:

SELECT group_database_id
FROM sys.availability_databases_cluster
WHERE database_name = 'DB1';

In our example, the result was:

group_database_id
------------------------------------
3F5E2823-002E-46FD-A971-869ED3892B27

We can use this value to locate events associated with DB1 in the Extended Events output.

For example, the secondary trace contained events like these:

name	timestamp	database_replica_id	mode	log_block_id
hadr_transport_receive_log_block_message	2026-08-04 09:20:17.2059851	3F5E2823-002E-46FD-A971-869ED3892B27	2	28346698302255272
hadr_apply_log_block	2026-08-04 09:20:17.2060235	3F5E2823-002E-46FD-A971-869ED3892B27	2	28346698302255032
hadr_transport_receive_log_block_message	2026-08-04 09:20:17.2060465	3F5E2823-002E-46FD-A971-869ED3892B27	1	28346698302255392
hadr_apply_log_block	2026-08-04 09:20:17.2060554	3F5E2823-002E-46FD-A971-869ED3892B27	2	28346698302255152
hadr_transport_receive_log_block_message	2026-08-04 09:20:17.2060730	3F5E2823-002E-46FD-A971-869ED3892B27	1	28346698302255512
hadr_transport_receive_log_block_message	2026-08-04 09:20:17.2060836	3F5E2823-002E-46FD-A971-869ED3892B27	2	28346698302255392
hadr_apply_log_block	2026-08-04 09:20:17.2060849	3F5E2823-002E-46FD-A971-869ED3892B27	2	28346698302255272

Pick a log_block_id, preferably one toward the latter part of the trace. This increases the chance that the same log block was captured in both the primary and secondary traces.

For this example, we selected:

28346698302255392

Searching for the Log Block on the Secondary

Searching for that log block ID on the secondary produced the following results:

name	timestamp	database_replica_id	mode	log_block_id
hadr_transport_receive_log_block_message	2026-08-04 09:20:17.2060465	3F5E2823-002E-46FD-A971-869ED3892B27	1	28346698302255392
hadr_transport_receive_log_block_message	2026-08-04 09:20:17.2060836	3F5E2823-002E-46FD-A971-869ED3892B27	2	28346698302255392
hadr_log_block_decompression	2026-08-04 09:20:17.2061251	NULL	NULL	28346698302255392
hadr_log_block_decompression	2026-08-04 09:20:17.2061274	NULL	NULL	28346698302255392
hadr_apply_log_block	2026-08-04 09:20:17.2061456	3F5E2823-002E-46FD-A971-869ED3892B27	2	28346698302255392
log_block_pushed_to_logpool	2026-08-04 09:20:17.2063843	NULL	NULL	28346698302255392
log_flush_complete	2026-08-04 09:20:17.2068296	NULL	NULL	28346698302255392

Searching for the Same Log Block on the Primary

Next, search the primary trace for the same log_block_id:

28346698302255392

The relevant events were:

name	timestamp	database_replica_id	availability_replica_id	log_block_id
hadr_capture_log_block	2026-08-04 09:19:07.1730670	3F5E2823-002E-46FD-A971-869ED3892B27	D91DF70A-8226-4022-93F6-12F4D5B77699	28346698302255392
hadr_capture_filestream_wait	2026-08-04 09:19:07.1730674	3F5E2823-002E-46FD-A971-869ED3892B27	D91DF70A-8226-4022-93F6-12F4D5B77699	28346698302255392
hadr_capture_log_block	2026-08-04 09:19:07.1730690	3F5E2823-002E-46FD-A971-869ED3892B27	D91DF70A-8226-4022-93F6-12F4D5B77699	28346698302255392
hadr_capture_log_block	2026-08-04 09:20:16.6305401	3F5E2823-002E-46FD-A971-869ED3892B27	D91DF70A-8226-4022-93F6-12F4D5B77699	28346698302255392
hadr_capture_log_block	2026-08-04 09:20:16.6305465	3F5E2823-002E-46FD-A971-869ED3892B27	D91DF70A-8226-4022-93F6-12F4D5B77699	28346698302255392
hadr_log_block_compression	2026-08-04 09:20:16.6307262	NULL	D91DF70A-8226-4022-93F6-12F4D5B77699	28346698302255392
hadr_log_block_send_complete	2026-08-04 09:20:16.9981083	NULL	NULL	28346698302255392

The darker gray row is the key event we need from the primary replica: hadr_log_block_send_complete.

Asynchronous Replicas Have Some Important Differences

One important detail is that the events captured for an asynchronous replica differ slightly from those shown in Microsoft's example for a synchronous replica.

With asynchronous replication, the primary compresses the log blocks before sending them, and the secondary decompresses them after receiving them.

As a result, we can see:

• hadr_log_block_compression on the primary
• hadr_log_block_decompression on the secondary

There is also an important difference in how the primary waits for the secondary.

With an asynchronous replica, the primary does not wait for the secondary to harden the log block before continuing. Therefore, we should not expect to see the same hadr_receive_harden_lsn_message round-trip used in the synchronous-commit example.

This makes the primary-to-secondary portion of the data movement particularly useful when investigating network latency for an asynchronous replica.

The Events We Need to Calculate Network Latency

The two events we are interested in are:

Primary Replica

hadr_log_block_send_complete

2026-08-04 09:20:16.9981083

Secondary Replica

hadr_transport_receive_log_block_message

2026-08-04 09:20:17.2060465

Both events correspond to the same log block:

28346698302255392

Calculating the Network Latency

We can calculate the difference between these timestamps using DATEDIFF:

SELECT DATEDIFF(
    millisecond,
    '2026-08-04 09:20:16.9981083',
    '2026-08-04 09:20:17.2060465'
);

The result is approximately: 208

In other words, the elapsed time between the primary reporting the log block as sent and the secondary reporting that it received the log block was approximately 208 milliseconds.

Conclusion

This gave us a much stronger data point than simply saying, "the network seems slow."

We were able to correlate the same log_block_id across the primary and secondary replicas and measure the elapsed time between the hadr_log_block_send_complete event on the primary and the hadr_transport_receive_log_block_message event on the secondary.

In this example, that measurement was approximately 208 milliseconds.

Combined with the rapidly growing send queue and relatively small redo queue, the trace provided evidence that network performance was a significant factor in the asynchronous replica's ability to keep up with the primary.

That data gave us the evidence we needed to justify a network upgrade rather than treating the problem as a general SQL Server performance issue.

References

• Microsoft SQL Server: Troubleshooting data movement latency between synchronous-commit AlwaysOn Availability Groups

Anatomy of a SQL Server CDC Cleanup Job (and How to Keep It Healthy)

Change Data Capture (CDC) is great—until it isn’t. Under heavy change volume, the CDC cleanup job may fall behind. When that happens, Change Tables (CTs) grow rapidly, and the cleanup job can block the capture job, which can in turn delay or stall downstream consumers. 

This post explains: how to quickly estimate CT growth, how the cleanup job actually deletes rows, and the three main tuning knobs to keep CDC stable. 

Quick Health Check: Approximate CT Row Counts 

To get a fast row-count estimate of each CDC Change Table:

SELECT
c.object_id,
t.name,
p.rows
FROM cdc.change_tables c
JOIN sys.tables t
ON c.object_id = t.object_id
JOIN sys.partitions p
ON t.object_id = p.object_id
WHERE p.index_id IN (0,1)
ORDER BY c.object_id;

Example output:

object_id     name                   rows
-----------   ---------------------  --------
82099333      dbo_customer_CT         167702
98099390      dbo_district_CT         270010
114099447     dbo_item_CT             0
370100359     dbo_new_order_CT        134634
386100416     dbo_warehouse_CT        135522
562101043     dbo_order_line_CT       2018297
578101100     dbo_stock_CT            1343322
1973582069    dbo_orders_CT           191732
--------------------------------------------
Total                                 4,261,219

CDC Cleanup Defaults (Retention + Threshold) 

CDC cleanup behavior is driven mainly by: 

• Retention (how long CT rows are kept) 
• Threshold (rows deleted per batch) 

Check current settings:

EXEC sys.sp_cdc_help_jobs;

Example output:

job_type  job_name           retention  threshold
--------  -----------------  ---------  ---------
capture   cdc.tpcc_capture          0          0
cleanup   cdc.tpcc_cleanup       4320       5000

Defaults: Retention = 4320 minutes (72 hours / 3 days) 

Threshold = 5000 rows per delete batch 

What the Cleanup Job Actually Does 

Extended Events typically reveals that the cleanup job processes CTs sequentially via a cursor:

DECLARE #hchange_table CURSOR LOCAL FAST_FORWARD
FOR
SELECT capture_instance, start_lsn
FROM [cdc].[change_tables]
WHERE (@capture_instance IS NULL)
OR (capture_instance = @capture_instance);

Because cdc.change_tables is clustered by object_id, it tends to delete CTs in object_id order. With @p1 = 5000 (threshold), you’ll often see patterns like:

DELETE TOP (@p1) FROM [cdc].[dbo_customer_CT]    WHERE __$start_lsn @p2 (35 times)
DELETE TOP (@p1) FROM [cdc].[dbo_district_CT]    WHERE __$start_lsn @p2 (56 times)
...
DELETE TOP (@p1) FROM [cdc].[dbo_stock_CT]       WHERE __$start_lsn @p2 (323 times)

Large tables can dominate runtime and prevent cleanup from ever catching up. 

The Three Tuning Knobs

1) Adjust the Threshold 

Higher threshold = deletes more per batch (often more efficient), but can increase contention. 

Lower threshold = smaller deletes, but more loops and potentially longer runtime. 

Example:

EXEC sys.sp_cdc_change_job
@job_type  = N'cleanup',
@threshold = 2000;

2) Reduce Retention 

Reducing retention means less data to keep, making cleanup easier. But consumers must be able to ingest changes within the retention window. Example (2160 minutes = 36 hours):

EXEC sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention  = 2160;

3) Run Cleanup More Frequently 

First, find the schedule ID:

USE msdb;
GO
SELECT j.name AS job_name,
s.schedule_id,
s.name AS schedule_name
FROM dbo.sysjobs j
JOIN dbo.sysjobschedules js
ON j.job_id = js.job_id
JOIN dbo.sysschedules s
ON js.schedule_id = s.schedule_id
WHERE j.name = N'cdc.tpcc_cleanup';

Then update it (e.g., every 15 minutes):

USE msdb;
GO
EXEC dbo.sp_update_schedule
@schedule_id = 171,      -- from query above
@enabled = 1,
@freq_type = 4,          -- daily
@freq_interval = 1,
@freq_subday_type = 4,   -- minutes
@freq_subday_interval = 15,
@active_start_time = 000000; -- midnight

Last Resort: Truncating CT Tables (High Risk) 

If cleanup still can’t keep up, truncating CT tables may be the fastest recovery path—but it irreversibly deletes change history.

Safe sequence:

1. Stop the capture job
2. Ensure consumers ingest remaining changes
3. Truncate CT tables
4. Restart capture job 

Warnings:

• You may create data gaps for downstream consumers.
• Consumers may need a full reload/re-baseline after truncation.
• Do this only with stakeholder approval and a clear recovery plan.

Not Able to Add Article or Subscription After Upgrading

In SQL 2022, there is change in the linked server format used by replication for the listener subscriber with non default port, as shown below, the old format name is LISTENER,54321 and the new format is LISTENER and the port is in the provider string select name, provider_string from sys.servers

name              provider_string
LISTENER,54321    NULL
LISTENER          addr=tcp:LISTENER,54321

After the upgrade to SQL 2022 a couple of behaviors where observed: 

The first one is adding an article to a publication of a subscriber using the old format won’t generate a snapshot: A snapshot was not generated because no subscription needed initialization 

The second one is creating a new publication for an existing subscriber in the old format will get this error:

'SQL1' is not defined as a Subscriber for 'SQL2'. Could not update the distribution database subscription table. The subscription status could not be changed. The subscription could not be created. The subscription could not be found. Changed database context to 'dummy'. (Microsoft SQL Server, Error: 20032) 

Or 

Cannot insert the value NULL into column ‘freq_subday_interval’, table ‘distribution.dbo.MSrepl_agent_jobs’; column doen not allow nulls. UPDATE fails. Could not update the distribution database subscription table. The subscription status could not be changed. The subscription could not be created. The subscription could not be found. Changed database context to 'dummy'. (Microsoft SQL Server, Error: 20032) 

The workaround is to enable TF 15005 in both the publisher and distributor as well as creating aliases for the listener subscribers with non default port numbers, example LISTENER3 

If publisher and distributor are AG then aliases for the listener publisher and listener distributor are also needed, LISTENER1 and LISTENER2, respectively, in both 32 and 64-bit SQL Native Client, as shown below, in a PowerShell script

$aliases = @(
   @{name = 'LISTENER1'; value = 'DBMSSOCN,LISTENER1,54321'}
   @{name = 'LISTENER2'; value = 'DBMSSOCN,LISTENER2,54321'}
   @{name = 'LISTENER3'; value = 'DBMSSOCN,LISTENER3,54321'}
)

$registryPaths = @(
   'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo'
   'HKLM:\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo'
)
foreach ($alias in $aliases) {
   foreach ($registryPath in $registryPaths) {
      if (-not (Test-Path $registryPath)) {
          New-Item -Path $registryPath -Force
      }
      New-ItemProperty -Path $registryPath -Name $alias.name `
      -Value $alias.value -PropertyType String -Force
   }
}

Then you should be able to add the article or to create the subscription without specifying the port number

Multi-Subnet Log Reader Agent

To connect the replication log reader agent to a multi-subnet Always On publisher you must add the parameter -MultiSubnetFailover 1 to the job step as documented here. Example: 

-Publisher [SQLPUB1] -PublisherDB [dummy] -Distributor [SQLDIST] -DistributorSecurityMode 1 -Continuous -MultiSubnetFailover 1 

However the log reader fails with error below when the publisher SQLPUB1 and distributor SQLDIST are in different subnets

TCP Provider: The wait operation timed out. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037) Get help: http://help/MSSQL_REPL22037 

A trace shows that the log reader agent executes the following on the publisher:

declare @p3 nvarchar(128)
set @p3=NULL
declare @p4 nvarchar(128)
set @p4=NULL
exec "master"."sys"."sp_executesql";1 N'exec master.sys.sp_helpdistributor
@distributor = @p1 output, @distribdb = @p2 output',N'@p1 sysname output, 
@p2 sysname output',@p3 output,@p4 output
select @p3, @p4

and it will fail with a similar error

OLE DB provider "MSOLEDBSQL" for linked server "repl_distributor" returned message "Login timeout expired". OLE DB provider "MSOLEDBSQL" for linked server "repl_distributor" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". Msg 258, Level 16, State 1, Line 0 TCP Provider: The wait operation timed out. 

As you can see, the log reader is accessing the distributor from the publisher using the link server

The workaround is to add the MultiSubnetFailover = ‘Yes’ to link server repl_distributor using sp_serveroption which as of this writing is not documented

USE master;
GO
EXEC sp_serveroption
    @server = N'repl_distributor',
    @optname = N'provider string',
    @optvalue = N'MultiSubnetFailover=Yes';
GO
 

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'

Listing Azure Databases

If you are a data professional and need to list all the databases in your organization, across all subscriptions and resource providers, then you can easily accomplish this task by using Azure Resource Graph. We will start with writing the Kusto query in the Azure portal and later we will use PowerShell to execute the query programatically.

Azure Resource Graph Explorer

The easiest way to write a Kusto query is using the explorer. In the Azure portal search box, type Resource Graph Explorer and click on it. On the left pane search for sql, and you will see all the resource types that contain sql in the name. Click on resources, then click on the resource type microsoft.sql/servers/databases, both will show in the query editor in the right like this:

resources
 | where type == "microsoft.sql/servers/databases"

Next click on Run query and that's it, you wrote your first Kusto query!

In this example you will notice that master shows as a third database, you can filter it out by adding another where operator:

 | where name != "master"

It is a good practice to only show the columns needed (less data travel over the wire), you can select the columns with the project operator:

 | project type, name, kind, subscriptionId

Since the query will search in all subscriptions, you can create another query to find the subscriptions in your organizations using resourcecontainers of type microsoft.resources/subscriptions, and you will use the project operator to select what is needed such as subscriptionId and susbcriptionName:

 resourcecontainers
 | where type == "microsoft.resources/subscriptions"
 | project subscriptionId, subscriptionName = name

Now you can join both queries using the join operator with the column subscriptionId, and selecting again only the columns needed with the project operator:

resources
 | where type == "microsoft.sql/servers/databases"
 | where name != "master"
 | project type, name, kind, subscriptionId
 | join kind = inner (
   resourcecontainers
   | where type == "microsoft.resources/subscriptions"
   | project subscriptionId, subscriptionName = name
   ) on subscriptionId
 | project type, name, kind, subscriptionName

Azure PowerShell

Now that you have the Kusto query under your belt, you can call it from a PowerShell script using the cmdlet Search-AzGraph. But first you will need the Az modules, then connect to Azure:

Connect-AzAccount

Next you can run the same query from above. In addition, you can add other database resource types (MySQL and PostgreSQL) using the in operator

$query = '
resources
 | where type in (
    "microsoft.sql/servers/databases"
    ,"microsoft.sqlvirtualmachine/sqlvirtualmachines"
    ,"microsoft.sql/managedinstances"
    ,"microsoft.dbforpostgresql/servers"
    ,"microsoft.dbformysql/servers"
   )
 | where name != "master"
 | project type, name, kind, subscriptionId
 | join kind = inner (
   resourcecontainers
   | where type == "microsoft.resources/subscriptions"
   | project subscriptionId, subscriptionName = name
   ) on subscriptionId
 | project type, name, kind, subscriptionName
'
Search-AzGraph -Query $query

This was just a peek of the capabilities that Azure Resource Graph provides and hopefully it sparked your interest to learn more about it.

Using the .Net Oracle Data Access Client

There is a cmdlet for SQL Server databases called Invoke-Sqlcmd that will allow you to run TSQL commands but there is not a cmdlet for Oracle. This is where the .Net framework comes to the rescue.

Microsoft no longer provides the Oracle library. So we need to download it from the Oracle website link below

https://www.oracle.com/database/technologies/dotnet-odacmsi-vs2017-downloads.html

Then, we can use Add-Type to add the .Net class to a PowerShell session, and use a basic .Net framework syntax to execute database commands, as shown below.

Add-Type -Path 'C:\Program Files (x86)\Oracle Developer Tools for VS2017\odp.net\managed\common\Oracle.ManagedDataAccess.dll'

$conString = "User Id=myuser;Password=mypassword;Data Source=mydatabase"
$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection
$con.ConnectionString = $conString
$con.Open()

$cmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand
$cmd.Connection = $con
$cmd.CommandText = 'select banner from v$version'
$rs = $cmd.ExecuteReader()

while ($rs.Read()) 
{
    Write-Host "$($rs.GetValue(0))"
}

$con.Close()
$con.Dispose()

Azure DevOps Build Queue Wait Time

Azure DevOps is used to automate CI/CD pipelines. It uses an agent to execute one job at a time. If we have more jobs to execute than agents then some jobs have to wait in the queue. This post is about how to gather metrics about the wait time to decide when to add more agents.

Agents are organized into pools and the data needed is available via REST API.  The URI to list the pools is

https://dev.azure.com/{your organization}/_apis/distributedtask/pools

and the URI to list the jobs running on each pool is

https://dev.azure.com/{your organization}/_apis/distributedtask/pools/{pool id}/jobrequest

To authenticate to Azure DevOps, you will need your access token and replace it in the PowerShell script below

$token = "{your access token}"
$bytes = [System.Text.Encoding]::UTF8.GetBytes(":$($token)")
$base64bytes = [System.Convert]::ToBase64String($bytes)
$headers = @{ "Authorization" = "Basic $base64bytes"}

$uri = 'https://dev.azure.com/{your organization}/_apis/distributedtask/pools/{pool id}/jobrequests'

$r = Invoke-RestMethod -Uri $uri -Headers $headers -Method Get -ContentType "application/json"

The result is a PSCustomObject with other nested PSCustomObject as Get-Member shows, so  expressions are needed to query the nested objects for example reservedAgent. The time fields such as queueTime are strings, so expressions are also required to convert to universal time

$r.value | gm

   TypeName: System.Management.Automation.PSCustomObject

Name                   MemberType   Definition
----                   ----------   ----------
Equals                 Method       bool Equals(System.Object obj)                     
GetHashCode            Method       int GetHashCode()                                  
GetType                Method       type GetType()                                     
ToString               Method       string ToString()                                  
agentDelays            NoteProperty Object[] agentDelays=System.Object[]               
assignTime             NoteProperty string assignTime=2019-07-19T21:40:26.8666667Z     
data                   NoteProperty System.Management.Automation.PSCustomObject
definition             NoteProperty System.Management.Automation.PSCustomObject
demands                NoteProperty Object[] demands=System.Object[]                   
hostId                 NoteProperty string hostId=22399273-0444-4322-aea9-2e628bac6c60 
jobId                  NoteProperty string jobId=12f1170f-54f2-53f3-20dd-22fc7dff55f9  
lockedUntil            NoteProperty string lockedUntil=2019-07-19T22:05:33.4833333Z    
matchesAllAgentsInPool NoteProperty bool matchesAllAgentsInPool=True                   
orchestrationId        NoteProperty string orchestrationId=d476f3d0-c2b4-41d5-9ebb
owner                  NoteProperty System.Management.Automation.PSCustomObject
planId                 NoteProperty string planId=d476f3d0-c2b4-41d5-9ebb-789eb9704ca7 
planType               NoteProperty string planType=Build                              
poolId                 NoteProperty int poolId=9                                       
queueTime              NoteProperty string queueTime=2019-07-19T21:40:26.4633333Z      
receiveTime            NoteProperty string receiveTime=2019-07-19T21:40:29.379468Z     
requestId              NoteProperty int requestId=1024                                 
reservedAgent          NoteProperty System.Management.Automation.PSCustomObject 
scopeId                NoteProperty string scopeId=0020fc51-3569-4cdc-8c2b-2affa5401996
serviceOwner           NoteProperty string serviceOwner=00025394-6065-48ca-87d

The final script parses the required info and filters out Pool Maintenance jobs and data older than 3 hours using Where-Object.

$r.value `
| Select-Object requestId, poolId, result,`
  @{Label=”name”; Expression={$_.definition.name}},`
  @{Label=”agent”; Expression={$_.reservedAgent.name}},`
  @{Label=”queued”; Expression={(get-date $_.queueTime).ToUniversalTime()}},` 
  @{Label=”assigned”; Expression={(get-date $_.assignTime).ToUniversalTime()}},` 
  @{Label=”received”; Expression={(get-date $_.receiveTime).ToUniversalTime()}},` 
  @{Label=”finished”; Expression={(get-date $_.finishTime).ToUniversalTime()}}`
| Where-Object {$_.name -ne 'PoolMaintenance' `
  -and $_.queued -gt (Get-Date).AddHours(-3).ToUniversalTime()}`
| Select-Object requestId, poolId, result, name, agent, queued,`
  @{Label=”wait”; Expression={$_.assigned-$_.queued}},`
  @{Label=”duration”; Expression={$_.finished-$_.received}}`
| Format-Table

requestId poolId result    name              agent   queued                wait     duration
--------- ------ ------    ----              -----   ------                ----     --------
     1023      9 succeeded project-for-demo2 AGENT01 7/19/2019 8:00:31 PM  00:33:40 00:00:25
     1022      9 succeeded project-for-demo1 AGENT01 7/19/2019 7:55:04 PM  00:00:00 00:38:03

From the results above, request id 1023 was waiting 33 minutes for request id 1022 to complete. If a 33 minute wait is not acceptable then a second agent can be added to process the job. Also this data can be collected and stored for trend analysis.

SqlServerDsc Account

The PowerShell SqlServerDsc module uses the account [NT AUTHORITY\SYSTEM] to login to SQL Server by default. However, I was puzzled by why it can execute admin commands since it was not in the sysadmin role. It turns out that [NT AUTHORITY\SYSTEM] is like a member of the group [NT SERVICE\Winmgmt] and it will use the group permissions.

For example, to enable CLR on the named instance SERVER01\SQL01, we can use this script

[DSCLocalConfigurationManager()]
Configuration LcmPush 
{    
    Node SERVER01
    {
        Settings 
        {
            AllowModuleOverwrite = $True
            ConfigurationMode = 'ApplyOnly'
            RefreshMode = 'Push'
        }
    }
}

Configuration SqlServerConfig
{
    Import-DscResource -ModuleName PSDesiredStateConfiguration
    Import-DscResource -ModuleName SqlServerDsc

    node SERVER01
    {
        SqlServerConfiguration clr
        {
            ServerName     = 'SERVER01'
            InstanceName   = 'SQL01'
            OptionName     = 'clr enabled'
            OptionValue    = 1
        }
    }
}

$MofPath = 'C:\DSC\LCM'
LcmPush -OutputPath $MofPath
Set-DscLocalConfigurationManager -Path $MofPath -Force 

SqlServerConfig -OutputPath $MofPath
Start-DscConfiguration -ComputerName 'SERVER01' -Path $MofPath -Force -Wait -Verbose 

A SQL trace will show that the account used is [NT AUTHORITY\SYSTEM]

If we remove [NT AUTHORITY\SYSTEM] from the sysadmin server role to and disable CLR

ALTER SERVER ROLE [sysadmin] DROP MEMBER [NT AUTHORITY\SYSTEM]
GO
sp_configure 'clr enabled', 0
reconfigure
GO

And execute the script again, it will still succeed

VERBOSE: [SERVER01]: LCM:  [ Start  Set      ]  [[SqlServerConfiguration]clr]
VERBOSE: [SERVER01]:                            [[SqlServerConfiguration]clr] Found PowerShell module SqlServer already imported in the session.
VERBOSE: [SERVER01]:                            [[SqlServerConfiguration]clr] Connected to SQL instance 'SERVER01\SQL01'.
VERBOSE: [SERVER01]:                            [[SqlServerConfiguration]clr] Configuration option 'clr enabled' has been updated to value '1'.
VERBOSE: [SERVER01]:                            [[SqlServerConfiguration]clr] The option was changed without the need to restart the SQL Server instance.
VERBOSE: [SERVER01]: LCM:  [ End    Set      ]  [[SqlServerConfiguration]clr]  in 0.3290 seconds.

If we remove [NT SERVICE\Winmgmt] from the sysadmin server role to and disable CLR

ALTER SERVER ROLE [sysadmin] DROP MEMBER [NT SERVICE\Winmgmt]
GO
sp_configure 'clr enabled', 0
reconfigure
GO

And execute the script again, it will error out

Exception calling "Alter" with "0" argument(s): "Alter failed. "
    + CategoryInfo          : NotSpecified: (:) [], CimException
    + FullyQualifiedErrorId : FailedOperationException
    + PSComputerName        : SERVER01
 
The PowerShell DSC resource '[SqlServerConfiguration]clr' with SourceInfo '::22::9::SqlServerConfiguration' threw one or more non-terminating errors while running the Set-TargetResource 
functionality. These errors are logged to the ETW channel called Microsoft-Windows-DSC/Operational. Refer to this channel for more details.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : NonTerminatingErrorFromProvider
    + PSComputerName        : SERVER01
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1

There is very little documentation of what [NT SERVICE\Winmgmt] is for, but the experiment above shows that [NT AUTHORITY\SYSTEM] behaves like a member of the group [NT SERVICE\Winmgmt] even though is not actually a group

Copy Data with PowerShell Benchmark

To copy a large amount of rows from one SQL Server (server1) to another (server2), I compared the .Net data provider I used back with PS 2.0, with the new sqlserver module -OutputAs DataTable. The results are shown below.

Using .Net, we need to write more code but it is faster. Using -OutputAs DataTable, we can write much less code but it takes longer since it need to load the table in memory. Note -OutputAs DataRows is the default which is even more slower since it will copy one row at a time

Repro

On server1, create a view to easily generate 6.4 million rows

create view my_spt_values
as
select c1.* from spt_values c1 cross join spt_values c2

On server2, create a stage table

select * into test_stage from spt_values where 1=2

Create the scripts below to copy from server1 to server2

Sqlclient.ps1

$conTargetString = "Data Source=server2;Initial Catalog=mydb;Integrated Security=True"

$conTarget = New-Object System.Data.SQLClient.SQLConnection($conTargetString)
$conTarget.Open()

$conBulk = New-Object System.Data.SQLClient.SQLBulkCopy($conTargetString, [System.Data.SQLClient.SqlBulkCopyOptions]::TableLock)
$conBulk.DestinationTableName = 'test_stage'
$conBulk.BatchSize = 10000000

$cmdTarget = New-Object System.Data.SQLClient.SQLCommand
$cmdTarget.Connection = $conTarget

$cmdTarget.CommandText = "truncate table test_stage"
$cmdTarget.ExecuteNonQuery()

$conSourceString = "Data Source=server1;Initial Catalog=mydb;Integrated Security=True"
$conSource = New-Object System.Data.SQLClient.SQLConnection($conSourceString)
$conSource.Open()

$cmdSource = New-Object System.Data.SQLClient.SQLCommand
$cmdSource.CommandText = "select * from my_spt_values"
$cmdSource.Connection = $conSource

$tabSource = $cmdSource.ExecuteReader()
$conBulk.WriteToServer($tabSource)

$conSource.Close()
$conSource.Dispose()

$conTarget.Close()
$conTarget.Dispose() 

Readcmd.ps1

Invoke-Sqlcmd -ServerInstance server2 -Database mydb -Query "truncate table test_stage"

Read-SqlViewData -ServerInstance server1 -Database master -SchemaName dbo -ViewName my_spt_values -OutputAs DataTable |
Write-SqlTableData -ServerInstance server2 -Database myfb -SchemaName dbo -TableName test_stage -Passthru 

Sqlcmd.ps1

Invoke-Sqlcmd -ServerInstance server2 -Database mydb -Query "truncate table test_stage"

Invoke-Sqlcmd -ServerInstance server1 -Database master -Query "select * from my_spt_values" -OutputAs DataTables |
Write-SqlTableData -ServerInstance server2 -Database mydb -SchemaName dbo -TableName test_stage -Passthru 

Measure

Measure-Command -Expression {.\sqlclient.ps1} | select seconds
Measure-Command -Expression {.\readcmd.ps1} | select seconds
Measure-Command -Expression {.\sqlcmd.ps1} | select seconds


Seconds
-------
     12
     36
     45

Reading the Data

While measuring, open task manager to see the memory consumption of each of the scrips, we will see that the first method did not load the entire result set to memory while the latter 2 methods did

On server1, a SQL trace will show the duration to select the same amount rows. The first method is the fasted with a 12 seconds. The last 2 methods take longer (18 and 24 seconds respectively) since they have to wait until the rows are loaded into memory, so they will hold a share lock on the table longer too, which may cause blocking in the source if other processes are writing to it

Also sp_WhoIsActive will show the wait type of ASYNC_NETWORK_IO which means the it is waiting for the client (PowerShell) to process the rows

Writing the Data

On server2, a SQL trace, will show the other benefit of using the .Net bulkcopy: the lock table hint, so the logical reads are much more less. In addition, bulkcopy allows to control the batch size to not fill up the transaction log if there is lots of concurrent transactions

We can see that to copy a large amount of rows, the .Net data provider is much faster because it does not load the entire result set into memory, and allows more control with hints such as table lock that saves IO

Analyzing SQL Error Logs with Kusto

We can leverage Azure Kusto to analyze SQL error logs. It is easy to learn if you know SQL querying, in fact here is a link on how SQL translates to Kusto. My favorite two operators to reduce large amount of errors to a small number of patterns are: autocluster and reduce

Autocluster

It will find a common pattern on the given columns. In the example below, 60% of the column values in host, source_name, and message, are from replication errors on a particular host

Reduce

It will find common patterns in the message and replace them with "*" to easily aggregate them. In the example below we can see the frequency of the most common errors.

Data Points Outside Time Range

I was using the cool Microsoft Azure plugin for Grafana to easily develop a dashboard for SQL error logs in Azure Kusto, and noticed the message "Data Points Outside Time Range" even though my data points were on range. After trial and error, I found out that if I add "asc" to the "order by" the message will disappear

Example of the original Kusto query where the message "Data Points Outside Time Range" appears

By adding "asc" to the "order by" the message will disappear

The reason is quite silly. I was learning Kusto from a TSQL background. In Kusto the "order by" defaults to descending order while in TSQL it defaults to ascending order.

Monitoring Identity Column Values

Identity columns are often used for generating key values. Once create it, we often forget about it. However, once the max value allowed by the data type is reached, no more rows can be inserted.

To avoid unexpected outage, we can use sys.identity_columns to monitor if the values are approaching the max limit.  The 6 data types supported are: tinyint, smallint, int, bigint, numeric, and decimal.

For example, we can create 3 tables with identity columns, assign a seed value, and insert some data

create table paul_test1 (a tinyint identity, b char(1))
create table paul_test2 (a int identity, b char(1))
create table paul_test3 (a numeric(6,0) identity, b char(1))

dbcc checkident('paul_test1', reseed, 200)
dbcc checkident('paul_test2', reseed, 2000000000)
dbcc checkident('paul_test3', reseed, 999999)

insert paul_test1 (b) values ('x')
insert paul_test2 (b) values ('x')
insert paul_test3 (b) values ('x')

If we insert one more row on the third table

insert paul_test3 (b) values ('x')

We will get this error:

Msg 8115, Level 16, State 1, Line 62 Arithmetic overflow error converting IDENTITY to data type numeric. Arithmetic overflow occurred.

We can query sys.identity_columns to find the identity column last value of a table and calculate the percent used of the positive values. Then we can run the query on each database using sp_MSforeachdb, as shown on the script below (e.g. C:\TEMP\identity_values.sql)

create table #monitor_identity(
instance_name varchar(255)
,database_name varchar(255)
,table_name varchar(255) 
,schema_name varchar(255)
,column_name varchar(255) 
,current_value numeric(38)
,max_value numeric(38)
,pct_used numeric(38,2)
)
insert #monitor_identity
exec sp_MSforeachdb '
select 
@@servername
,''?''
,object_schema_name(object_id, db_id(''?''))
,object_name(object_id, db_id(''?''))
, name
, cast(last_value as numeric(38))
, max_value = case TYPE_NAME(system_type_id)
when ''tinyint'' then 255
when ''smallint'' then 32767
when ''int'' then 2147483647
when ''bigint'' then 9223372036854775807
when ''numeric'' then cast(replicate(''9'',precision) as numeric(38))
when ''decimal'' then cast(replicate(''9'',precision) as numeric(38))
end
, pct_used = cast(last_value as numeric(38)) / case TYPE_NAME(system_type_id)
when ''tinyint'' then 255
when ''smallint'' then 32767
when ''int'' then 2147483647
when ''bigint'' then 9223372036854775807
when ''numeric'' then cast(replicate(''9'',precision) as numeric(38))
when ''decimal'' then cast(replicate(''9'',precision) as numeric(38))
end * 100
from ?.sys.identity_columns with (nolock)
where last_value is not null
'
select
instance_name  
,database_name 
,schema_name 
,table_name 
,column_name 
,current_value 
,max_value 
,pct_used 
from #monitor_identity
where pct_used > 70
order by 
database_name 
,schema_name 
,table_name 
drop table #monitor_identity

Output

To run the script in multiple instances, we can have a list of instances in a text file (e.g. C:\TEMP\instance_list.txt)

instance1
instance2

Then we can easily use a PowerShell script to pipe the instance list content to Invoke-Sqlcmd, and pipe to Out-GridView

Get-Content C:\TEMP\instance_list.txt | Invoke-Sqlcmd -InputFile C:\TEMP\identity_values.sql | Out-GridView

Unable to Query the Redirected Server for Original Publisher

sp_redirect_publisher uses the account credential used when the Publisher was added to the Remote distributor and stored in msdb.dbo.MSdistpublishers. So if a SQL authentication account was used such as 'sa' or 'distributor_admin,' replication may fail when the password expired or the account doesn't exist.

For example, if we execute the following, the login column should be blank

select * from msdb.dbo.MSdistpublishers

To fix it run the following

exec [sys].[sp_changedistpublisher] 'host\instance', 'security_mode', '1'
exec [sys].[sp_changedistpublisher] 'host\instance', 'security_mode', '1'

Now the login column will be blank

Otherwise we will get these errors when the password expired

Unable to query the redirected server ',' for original publisher '\' and publisher database '' to determine the name of the remote server; Error 18456, Error message 'Error 18456, Level 14, State 1, Message: Login failed for user 'sa'.'.'. 

Unable to query the redirected server ',' for original publisher '\' and publisher database '' to determine the name of the remote server; Error 18456, Error message 'Error 18456, Level 14, State 1, Message: Login failed for user 'distributor_admin'.'.'.