MSDTC Operations

Microsoft Distributed Transaction Coordinator, abbreviated MSDTC or just DTC, is a somewhat aged but still very essential technology from Microsoft.

As a SQL Server DBA I have primarily met MSDTC under the Linked Server functionality. Here MSDTC serves the technical transactions between the SQL Server installation where the linked server is defined and the target of the linked server on another Windows Server. The MSDTC usage is implicit when using a linked server, primarily as the linked server is based on the OLEDB technology.

As a side note this is why you will often see OLEDB waits in SQL Server when working over a linked server.

Graphical User Interface

The primary GUI for MSDTC is the – also – elder Microsoft Management Console (MMC) component named "Component Services". This is a COM+ snap-in for MMC. If you are using a Danish Windows then the component is named "Komponenttjenester". It is not easy to find in-depth documentation on COM+ or MMC, and the voluminous (five-volume) "COM+ Developers´s Reference Library" is unfortunately no longer available in the book stores, that is either as new or used.

Usually you are working on the local MSDTC. You might find the path in Component Services long, but here it is:

1. Console Root
2. Component Services
3. Computers
4. My Computer
5. Distributed Transaction Coordinator
6. Local DTC

When you right-click Local DTC you have access to the properties of the Local DTC with three configuration areas in separate tabs. The first tab is on MSDTC tracing configuration.

The second tab is on MSDTC logging configuration.

The third and last tab is on MSDTC security configuration.

All the examples are with default values.

When expanding Local DTC you see two items:

• Transaction List
• Transaction Statistics

The first item Transaction List will give you a simple list of active transactions. But there is not much more you can see on each transaction or the transaction history.

The second item Transaction Statistics give you a rather simple graphical presentation of a few measures. There are no possibilities to drill-down or get other other details.

I think that the limited monitoring shows the age of the MSDTC component.

MSDTC Log Files

Is a standard Windows installation the MSDTC log files are in

C:\Windows\System32\MsDtc

The log files are in a proprietary binary Microsoft format, with no local tools to read the log files.

You can open the MSDTC log files with the command line tool tracefmt.exe. But you will have to download Windows SDK to get this tool. With Windows SDK you will get tracefmt.exe in both 32- and 64-bit editions in these (default) locations:

• C:\Program Files (x86)\Windows Kits\10\bin\10.0.22621.0\x64
• C:\Program Files (x86)\Windows Kits\10\bin\10.0.22621.0\x86

The paths are examples from the current Windows SDK version.

Using tracefmt.exe is not complicated but also not just point-and-click. Read the documentation and spend some time with the syntax to get a output you can use.

MSDTC Application Error

Some errors will surface in Windows Application Event Log like this example.

Log Name: Application
Source: Microsoft-Windows-MSDTC Client 2
Date: 28-11-2022 07:34:19
Event ID: 4879
Task Category: CM
Level: Warning
Keywords: Classic
User: N/A
Computer: SQLDB42.sqladmin.lan
Description:
MSDTC encountered an error (HR=0x80000171) while attempting to establish a secure connection with system SQLDB666.

MSDTC in AlwaysOn Availability Groups

As SQL Server AlwaysOn Availability Groups are based on Windows Failover Cluster, among others, you will have to go through some configuration details to get a robust transaction even during a failover. There are some basic documentation like Configure distributed transactions for an AlwaysOn availability group, but I really think you will have to do some more reading, build a sandbox and try several solutions thoroughly before going live.

This could be important if you are working wrestling with an application build with a high-level framework like .NET Entity Framework or Java Hibernate, as such frameworks tend to use client-side transactions and then rely on distributed transactions.

SqlClient 5.0

The .NET data provider SqlClient for SQL Server has been released in version 5.0 for general availability. This is a new major version so you are advised to check the changes, especially the breaking changes.

At first there are added support for TDS 8.0, but that will not affect most developers or their products. Anyway there is a breaking change with this on the Encrypt property. A quick solution in many cases is to add the keyword TrustServerCertificate with the value true to the connection string. This will look like this:
…Integrated Security=SSPI;TrustServerCertificate=true;…
Using TrustServerCertificate is also described in the Microsoft documentation „Connection String Syntax“ in the section „Using TrustServerCertificate“.

But the added support for SPN on server and failover will be interesting to many working on critical systems using SQL Server AlwaysOn Availability Groups and Kerberos authentication.

I think that the most significant change is the breaking change on added dependency on the Microsoft.SqlServer.Server package. You should really take a detailed look on this if you are working with SqlClient based systems. Even if you are not a developer or architect but "only" Database Administrator or DevOps Engineer. The migration from the namespace Microsoft.Data.SqlClient.Server in the .NET framework to the namespace Microsoft.SqlServer.Server in the SqlClient package will require some sort of refactoring code.

See more in the blog post "Released: General Availability of Microsoft.Data.SqlClient 5.0" and the Release Notes on GitHub.

History

2022-08-10 : Post created.
2024-01-20 : Update with TrustServerCertificate.

Execute on a subset of databases

Sometimes it is necessary to execute a command or a statement on a subset of the databases in a instance. A quick solution is to generate the commands in a SELECT on sys.database and then execute the output.

But when this has been done a few times for a recurring task the solution becomes boring and tiresome. And then a more automated solution is required.

Automating the entire statement build and execxution over a cursor and using sp_executesql looks like a nice T-SQL solution.

DECLARE @DatabaseID AS INT;
DECLARE @DatabaseName AS NVARCHAR(50);
 
DECLARE @DatabaseCursor AS CURSOR;
SET @DatabaseCursor = CURSOR LOCAL FAST_FORWARD FOR
  SELECT name, database_id
  FROM sys.databases
  WHERE databases.name LIKE 'wso2am!_%' ESCAPE ('!');
OPEN @DatabaseCursor;

FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE @BackupSql NVARCHAR(4000) = N'BACKUP DATABASE [' + @DatabaseName + N'] TO DISK =''R:\BACKUP\' + @DatabaseName + '.bak'' WITH COPY_ONLY;';
  DECLARE @Msg NVARCHAR(2047) = CONVERT(nchar(27), SYSUTCDATETIME()) + N'Z  Backup [' + @DatabaseName + N']...'
  RAISERROR (@Msg, 0,0) WITH NOWAIT;
  DECLARE @BeginTime DATETIME2 = SYSUTCDATETIME();
  EXECUTE master.dbo.sp_executesql @stmt = @BackupSql;
  DECLARE @EndTime DATETIME2 = SYSUTCDATETIME();
  SET @Msg = N'  Duration = ' + CAST(DATEDIFF(s , @BeginTime, @EndTime) AS NVARCHAR(42)) + N's';
  RAISERROR (@Msg ,0,0) WITH NOWAIT;

  FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
END
 
CLOSE @DatabaseCursor;
DEALLOCATE @DatabaseCursor;

The example above is part of cloning a subset of databases. I hope that you can use this in other situations.

RAISERROR with UNIQUEIDENTIFIER

The T-SQL type Uniqueidentifier is not a formatting possibility for at RAISERROR message. But converting the value to a string is a quick fix. This example is based on the Uniqueidentifier output from the procedure sp_add_jobschedule.

DECLARE @_schedule_uid UNIQUEIDENTIFIER = N'7f6a5df0-a68f-4b07-992f-b7595958061f';
DECLARE @_schedule_code NVARCHAR(50) = CONVERT(NVARCHAR(50), @_schedule_uid);
RAISERROR ( N'Schedule UID = %s', 0,0, @_schedule_code ) WITH NOWAIT;

The output is then

Schedule UID = 7F6A5DF0-A68F-4B07-992F-B7595958061F

A small detail is that with the conversion the value is also converted to upper-case.

Descriptions

Databases and objects in databases on SQL Server can be described with the Extended Property MS_Description. This description can be read by and used with Visual Studio, which gives a handy access to detailed documentation.

Unfortunately does instances and server-level objects like logins not have Extended Properties. So these elements can not have a description direct attached. There are some alternatives you can consider for these elements such like custom registry keys, file in root directory. But please don't create a custom table in the master database!

Description Lifecycle

A description can be created with the stored procedure sp_addextendedproperty. The procedure has eight parameters to control the name, value and level in the object hierarchy of the extended property. The value of the description has the type sql_variant, and the size can not be more than 7,500 bytes.

Later the description can be changed with the stored procedure sp_updateextendedprocedure.

Sometimes a tool or a clone can require the description to be removed. This can be done with the stored procedure sp_dropextendedproperty.

Extended Properties can be viewed through the catalog view sys.extended_properties or the function sys.fn_listextendedproperty.

Example on table description

EXECUTE sys.sp_updateextendedproperty
  @name=N'MS_Description', @value=N'List of SQL Server collations',
  @level0type=N'SCHEMA',@level0name=N'sqladmin',
  @level1type=N'TABLE',@level1name=N'collation';

SQL Server Management Studio

In SQL Server Management Studio (SSMS) the description on a object can be set as described above. But there are two more ways to add a description to a object. They are short described below, but the order they are mentioned in does not imply any ranking of the possibilities.
The first way to add a description is in the SSMS table design tool. Right-click on a object in the Object Explorer and click Design. Show the properties of the object with another right-click or press Alt+Enter. Then you can edit the Description property.

The second way to add a description is in the Database Diagrams of a given database. In here you can access the properties of a objects and edit the Description property.

Visual Studio

In SQL Server Management Studio (SSMS) extended properties are in the object properties. Here each extended property like MS_Description can be created, viewed, changed and deleted.

A Visual Studio data project can be created on a existing database or with a new database. Open the design of a table and the description in MS_Description is in the properties window, usually down to the right in Visual Studio.

In the example above the table "collation" has the description "SQL Server collations", that is placed in the extended property MS_Description of the table.

Actually the value of the description can also be handled (create/change) in a Visual Studio data project.

Data Classifications

When Microsoft introduced data classification with SQL Server 2012 this information was stored in extended properties. But with SQL Server 2016 this information is in (hidden) system tables.

Discussion

I can only recommend that you put the extended property MS_Description on each database and every object in the databases. At first it wil be a disturbance and somewhat annoying. But when you databases get in production, have lived for a few years and new developers og DBAs are to keep the database alive, then you (hopefully) will experience the benefits. That be in SSMS, Visual Studio or a 3rd part tool.

As a start you could add MS_Description to your static code test. At least on database, data objects and functional object. Later you can extend the test to cover more technical parts of the database like filegroups or users.

History

2023-01-01 : SSMS section added.
2022-05-31 : Entry created.

Restore AdventureWorks

There are several SQL Server sample databases from Microsoft, and one of the easiest to start and still somewhat up-to-date is AdventureWorks. There are various versions and editions of the database, and they are described in the Microsoft document "AdventureWorks sample databases".

This is an example on how to restore AdventuresWorks on a SQL Server Database Engine instance. The scripts and technical details are in the Github repository SQLAdmin (AdventureWorks).

Before you start restoring you have to make the backup file available to the instance. This can be done by copying the file to the default backup location of the Database Engine instance.

Then you can start the restore and some minor configurations to make the database available as below.

The configurations are not necessary to make the database available to you, but they are nice to make it general available and functional on SQL Server. Configuring Query Store on the database is optional before SQL Server 2022, but could be handy on a non-production instance.

History

2024-10-28 : Post made general and with scripts in Github.
2022-05-05 : Post created on SQL Server 2019

Check for pending restart

 Windows pending restart is unfortunately not a singular registration, but is in three differen places in the Windows Registry:

• HKLM: SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired
• HKLM: System\CurrentControlSet\Control\Session Manager, PendingFileRenameOperations
• HKLM: SOFTWARE\CapaSystems\RestartStatus, Reboot

The last one is specific for the Capa (CapaSystems.dk) systems, that I have met in a IT infrastructure.

xp_dirtree (T-SQL)

It is possible to get the content of a directory from T-SQL with the Extended Stored Procedure (XP) master.dbo.xp_dirtree. This only requires the SQL Server service account to have access to the directory and have browse rights.

Syntax:

EXECUTE master.dbo.xp_dirtree <Directory>, <Depth>, <IsFile>

• <Directory> nvarchar(???): Full path (name) of directory. Can be UNC path. The value can end with '\' or not.
• <Depth> int: Depth of list in directory.
• <IsFile> int: Show if row is on a file. If the value in non-zero then the result will show if a row is in a file.

The result is a table with the three columns subdirectory, depth and file where

• subdirectory is the name of the subdirectory or file in the given directory.
• depth is the depth of the directory/file in the given directory. One ('1') is the root of the current directory.
• file indicates if the directory is a file ('1') or not ('0'). If not a file then it is a directory.

For some reason the value for <Directory> can't be from SERVERPROPERTY like
EXECUTE master.dbo.xp_dirtree SERVERPROPERTY('InstanceDefaultBackupPath'), 1,1;.
The error is
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'InstanceDefaultBackupPath'.
Not even with CONVERT like
EXECUTE master.dbo.xp_dirtree CONVERT(nvarchar(512), SERVERPROPERTY('InstanceDefaultBackupPath')), 1,1;
where the error is
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.

The system function SERVERPROPERTY can be used through a temporary variable like this
DECLARE @_backuppath nvarchar(512) = CONVERT(nvarchar(512), SERVERPROPERTY('InstanceDefaultBackupPath'));
EXECUTE master.dbo.xp_dirtree @_backuppath, 1,1;

Example:

EXECUTE master.dbo.xp_dirtree N'D:\MSSQL\Backup', 1, 1;
List (backup) files in folder.

Unfortunately this Extended Stored Procedure is not documentd by Microsoft. Like many other XP's...

History

2024-06-25: Examples on SERVERPROPERTY added.
2022-03-25: Post published.

Using sp_executesql with PowerShell

 The system extended stored procedure sp_executesql is recommended in general to execute dynamic SQL to protect from SQL injection attack. Using sp_execute in PowerShell is like using any other stored procedure in PowerShell. But I prefer to do it in ADO.NET instead of using dynamic CmdLets like Invoke-SqlCmd. The reason is that I have a rather tight control with the connectivity and can make the call really fast.

The example below is a test on if a database exists with the given name. I have chosen to implement the test with a COUNT() function call so that the result always is the same type (integer) no matter if the database exists or not. Also COUNT() is usually very effective in the statement execution.

First I give the database name to look for:

$DbName = 'SSISDB'

Then I prepare the call and set up the connection to the SQL Server installation:

$SqlInstanceName = '(local)\MSSQL2019'
$CnnStr = "Server=$SqlInstanceName;Integrated Security=SSPI"
$SqlStatement = 'SELECT COUNT(*) AS [db_count] FROM [master].[sys].[databases] WHERE [name]=@dbname;'
$SqlParameters = '@dbname sysname'
$CnnSql = New-Object System.Data.SqlClient.SqlConnection
$CnnSql.ConnectionString = $CnnStr
$CnnSql.Open()

Then I call sp_executesql with the query and get the result. I close the connection as soon as possible to release the session resources:

$CmdSql = New-Object System.Data.SqlClient.SqlCommand
$CmdSql.Connection = $CnnSql
$CmdSql.CommandText = '[master].[sys].[sp_executesql]'
$CmdSql.CommandType = [System.Data.CommandType]::StoredProcedure
$CmdSql.Parameters.Add('@stmt', [System.Data.SqlDbType]::NVarChar, -1).Value = $SqlStatement
$CmdSql.Parameters.Add('@params', [System.Data.SqlDbType]::NVarChar, 500).Value = $SqlParameters
$CmdSql.Parameters.Add('@dbname', [System.Data.SqlDbType]::NVarChar, 128).Value = $DbName
$Rdr = $CmdSql.ExecuteReader()
while ($Rdr.Read()) { $DbCount = $Rdr['db_count'] }
$Rdr.Close()
$CnnSql.Close()

Afterwards I can work with the result from the query:

"Db Count: $DbCount"

This example can be used for calling any stored procedure.

Windows 11 on VMware Workstation

 With VMware Workstation version 16.2 it is prepared for Windows 11. There are more details in the blog post "Workstation 16.2 Now Available". But with the increased requirements for Windows 11 there are some configuration you have to do before installation.

1. Set firmware type to UEFI and enable secure boot. The is required to enable TPM. The setting is in Options > Advanced: Firmware type.
2. Set encryption on the virtual machine. This is required to enable TPM. The setting is in Options: Access Control. You have to generate a password for the VM, but that is quite straight forward.
3. Add Trusted Platform Module (TPM). This is required by Windows 11.

With these configurations in place the Windows 11 installation is quite similar to the Windows 10 installation.

If you plan to use the Windows 11 installation for usage without internet access, you should complete the installation with a local offline account. This is done during the final parts of the installation where you are expected to log on.

Top-ten posts 2021

Looking back at 2021 not only as another year with the Covid-19 pandemic. For various reasons it has only been a year with fewer posts than planned. 

The top-ten from 2021 is very much like the top-ten list from 2020 (link).

1. SqlBulkCopy with PowerShell (2017-07-30)
2. ISO 8601 date formatting using PowerShell (2008-08-14)
3. DBCC CHECKDB with PowerShell (2014-10-09)
4. xp_instance_regwrite syntax (2013-09-10)
5. T-SQL formatted duration (2017-04-29)
6. Audit Log for Analysis Services (2014-01-08)
7. Start shared HTA with PowerShell (2012-09-13)
8. SQL Server Agent schedule owner (2010-02-08)
9. PowerShell MessageBox (2014-03-15)
10. Windows Storage Spaces (2019-03-27)

The date on each post is the creation date. Some posts I have updated since the creation, and then the history is described in the post itself. The oldest post is more than ten years old, and that I personally find quite satisfying.

Still I do not have many readers, That is fine with me as the purpose of this blog is more a personal collection of refined notes. Looking at the number of views on each post there is a rather big difference. The #1 post has about 20 times more views than post #10.