Stacked instances of SQL 2017 Express, unable to complete a distributed txn

Question

Post reply

Stacked instances of SQL 2017 Express, unable to complete a distributed txn

Ken-204087

SSC Enthusiast

Points: 179

October 1, 2020 at 1:48 pm

#3792250

I have two instances of SQL 2017 Express installed on a given computer.

Computer name is WINDEV2004EVAL and the instances are named SQLExpress1 and SQLExpress2.

When I try to perform a distributed transaction between the two instances, I am encountering the following error after a three-minute stall

Msg 8501, Level 16, State 2, Line 6
MSDTC on server 'WINDEV2004EVAL\SQLExpress1' is unavailable

Looking at the Windows Application log, I see the following entries

Date and Time           Source              Description
9/29/2020 1:21:59 PM    MSSQL$SQLEXPRESS1   Resource Manager Creation Failed: 0x8004d102(XACT_E_DUPLICATE_GUID)
9/29/2020 1:20:29 PM    MSSQL$SQLEXPRESS1   Initializing Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [452dc8f8-8bbd-4da4-b3e2-e1e29d417a15] for server instance WINDEV2004EVAL\SQLEXPRESS1. This is an informational message only. No user action is required.
9/29/2020 1:20:29 PM    MSSQL$SQLEXPRESS1   Resource Manager Creation Failed: 0x8004d102(XACT_E_DUPLICATE_GUID)
9/29/2020 1:18:58 PM    MSSQL$SQLEXPRESS1   Initializing Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [452dc8f8-8bbd-4da4-b3e2-e1e29d417a15] for server instance WINDEV2004EVAL\SQLEXPRESS1. This is an informational message only. No user action is required.

The error seems to correspond to a clustered installation, which I am not using.

Below is a simplified version of what I'm trying to accomplish

On Instance#1, I setup a database, table and user

CREATE DATABASE [db1]
USE db1
CREATE TABLE [tbl1] (id int, val varchar(max))
CREATE LOGIN [loginJohn] with PASSWORD=N'secret567', DEFAULT_DATABASE=[db1], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE USER [userJohn] FOR LOGIN [loginJohn]
GRANT INSERT,SELECT,UPDATE,DELETE ON [tbl1] TO [userJohn]

On Instance#2, I setup a linked server

EXEC sp_addlinkedserver @server='WINDEV2004EVAL\SQLExpress1',@product='SQL Server';
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'WINDEV2004EVAL\SQLExpress1', @useself = 'false', @rmtuser = 'loginJohn', @rmtpassword = 'secret567';

On Instance#2, when I initiate a distributed transaction, it stalls for a few minutes before returning with an error

SET XACT_ABORT ON;
BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM [WINDEV2004EVAL\SQLExpress1].db1.dbo.tbl1
COMMIT

This issue only seems to occur with stacked instances of Express.

- I've tried the same setup with stacked instances of SQL 2017 Standard, and found no issues

- I've tried instances of SQL 2017 Express on separate computers, and also found no issues

Anyone know what the issue is? Is this just a limitation of Express?

Thank you,

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

DTC is supported with Express but from the error it looks like the service isn't running or you haven't configured DTC - or both. Ensure the service is running. For configurations, you configure it using run -> dcomcnfg or open Component Services. Since Microsoft keeps moving everything so there are dead links all over their articles and posts, you could refer to this BizTalk documentation on configuring it but check it soon before they move it:

Configure MSDTC on SQL Server and adapter client

Sue

ratbak Ten Centuries Points: 1358 More actions · Answer 2

Check the auto-close property on the relevant databases -- it defaults to on with SQL Server Express.

SELECT name,is_auto_close_on FROM sys.databases 
WHERE is_auto_close_on = 1 AND name = 'db1'

I don't know it for a fact, but I suspect that may cause issues in this scenario.

You may want to turn it off:

USE [master]
GO
ALTER DATABASE [db1] SET AUTO_CLOSE OFF WITH NO_WAIT
GO

https://www.mytechmantra.com/learnsqlserver/sql-server-best-practice-auto-close-database-option-should-remain-off/

Ken-204087 SSC Enthusiast Points: 179 More actions · Answer 3

Thanks for the input, I wasn't aware of the auto close property. But unfortunately, disabling had no effect with my issue.

Ken-204087 SSC Enthusiast Points: 179 More actions · Answer 4

Thanks @SueH, I do have MSDTC enabled and configured.

$regPath = 'HKLM:\SOFTWARE\Microsoft\MSDTC'
Set-ItemProperty -Path $regPath -Name 'AllowOnlySecureRpcCalls'          -Value 0
Set-ItemProperty -Path $regPath -Name 'FallbackToUnsecureRPCIfNecessary' -Value 0
Set-ItemProperty -Path $regPath -Name 'TurnOffRpcSecurity'               -Value 1
Set-ItemProperty -Path $regPath -Name 'SysprepInProgress'                -Value 0

$regPath = 'HKLM:\SOFTWARE\Microsoft\MSDTC\Security'
Set-ItemProperty -Path $regPath -Name 'DomainControllerState'            -Value 0
Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccess'                 -Value 1
Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccessAdmin'            -Value 0
Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccessClients'          -Value 1
Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccessInbound'          -Value 1
Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccessOutbound'         -Value 1
Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccessTip'              -Value 0
Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccessTransactions'     -Value 1
Set-ItemProperty -Path $regPath -Name 'XaTransactions'                   -Value 1

I do wonder why I'm getting the XACT_E_DUPLICATE_GUID error. Unfortunately there isn't much documentation on this topic.

Sue_H SSC Guru Points: 90860 More actions · Answer 5

I've gotten the error on configuration issues before but it doesn't mean that's the issue. And you are right - there is little information on it and on some of the DTC issues in general. The one other time I can think of where you could get the error would be is if something doesn't release it's DTC connection. You could try restarting DTC and see if that makes a difference on the error. It could also be related to RPC ports being blocked by the firewall since those are dynamic ports. You could try forcing those to be static ports and make sure the firewall is open for them. If you wanted to give that a shot, I followed this article before:

Configure Microsoft Distributed Transaction Coordinator (DTC) to work through a firewall

Sue

Ken-204087 SSC Enthusiast Points: 179 More actions · Answer 6

Hi @Sue_H

Thanks, I have tried disabling the firewall completely, even though I didn't think it was related since both SQL instances reside on the same physical server. It did not make a difference.

I've also tried restarting the MSDTC service numerous times.

I had used a configuration INI to install the two SQL Express instances on this server. And on a different server, I used the same configuration INI to install two SQL Standard instances. The server with the SQL Standard instances does not have any issue with distributed transactions between it's instances.

I'm leaning towards this being a bug with SQL Express (at least in this configuration).