Archive for the ‘Windows 2008 R2 MSDTC’ Category

Windows 2008R2 MSTDC Clustering how hard can it be. Linked SQL Servers

The DTC option is a simple feature just turn it on and it works eh yeh sometimes. but in advanced environments you need often some tweaks and how to’s

The following errors are common on misconfiguration on the SQL Cluster / MSDTC source

MSDTC encountered an error (HR=0x80000171)  Or if you are using linked SQL servers If the MSDTC is not working most of them solve the error with code, actually it is a misconfiguration of the MSDTC How to solve this error ?

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session.".

Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "SQL instance name". A nested transaction was required because the XACT_ABORT option was set to OFF.

Or error 7391

Well here is a little tip on how to solve this.

 

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session 

Typical MSDTC cluster resource , But in the resource kit there is a little tool that is named SUBINACL

SubInACL is a command-line tool that enables administrators to obtain security information about files, registry keys, and services, and transfer this information from user to user, from local or global group to group, and from domain to domain. For example, if a user has moved from one domain (DomainA) to another (DomainB), the administrator can replace DomainA\User with DomainB\User in the security information for the user’s files. This gives the user access to the same files from the new domain.
SubInACL enables administrators to do the following:

  • Display security information associated with files, registry keys, or services. This information includes owner, group, permission access control list (ACL), discretionary ACL (DACL), and system ACL (SACL).
  • Change the owner of an object.
  • Replace the security information for one identifier (account, group, well-known security identifier (SID)) with that of another identifier.
  • Migrate security information about objects. This is useful if you have reorganized a network’s domains and need to migrate the security information for files from one domain to another.

This update addresses the following issues:

  • Fixed bug where subinacl.exe failed to process command line arguments
  • Fixed bug where subinacl.exe failed to function correctly with cluster file shares

 

So now that we have the tool let us use this filling in the following command

subinacl /service msdtc /grand=”network services”=qsetil

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session 

Access denied ! , I am an Admin the CMD is a Admin why the syntax is correct .

Yes all above is ok except the MSDTC part it is a clusterd so you need to use the cluster part.

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session

Ok and now with the Clustered DTC Winking smile 

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session

Ok done now the error below is gone.

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session.".

Unable to start a nested transaction for OLE DB provider "SQLNCLI10" for linked server "SQL instance name". A nested transaction was required because the XACT_ABORT option was set to OFF.

Msg 7395, Level 16, State 2, Line 3

But what about the XACT_ABORT option was set to OFF and the error 7391 ?

Yes easy to solve turn it on.

  • Start the Distributed Transaction Coordinator (DTC or MSDTC) on all servers that are involved in the distributed transaction.
  • Issue this statement before you run your query:

    The XACT_ABORT option must be set to ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. This option is not required if the provider supports nested transactions.

  • Check whether any of the servers are on a cluster. The DTC on the cluster must have its own IP address. You must verify proper name resolution of the DTC service on each server. The IP address of the DTC must be defined in your name resolution system (such as WINS, DNS or LMHosts). Verify that each server can communicate with the other’s MSDTC by name, not just by IP address. Check in both directions. For example, check from server A to server B’s MSDTC, and then check from server B to server A’s MSDTC. You must resolve all name resolution problems on the network before you run your distributed query.

 

Below is a little example of how to test your transactions just change your servername [SQL instance name].

 

BEGIN TRAN

UPDATE [SQL instance name].test_DTC.dbo.Table1

SET Field1 = ‘MSDTC is a pain if it is not working but now it is working like a charm’

Where Field1 = ‘Second';

COMMIT TRAN

SET XACT_ABORT OFF

SET XACT_ABORT ON

Select * from [SQL instance name].test_DTC.dbo.Table1

 

OLE DB provider "SQLNCLI10" for linked server "SQL instance name" returned message "Cannot start more transactions on this session

Powershell Network Load Balancing

Teched 2010 Berlin #TEE10 #Teched

For Demo and learning Sites you need often thing that takes only a minute but when you play with it it takes more time than you want. So with this in my mind I created 3 powershell scripts that you can use for building a cluster , NLB Cluster and a MSTDC cluster resource.

This powershell script is fully unattended an yes it is not complete it can be improved.  But you can always adjust this. Similar scripts are there for creating Exchange NLB.

This script runs on node 1 and is creating the NLB on the two or more nodes. ( tested only on two nodes )

You can find the Scripts Here : Powershell Scripts

Powershell Create NLB

The first thing you might want to change is the network adapter name I used adapter 3 and renamed it to NLB later in the NLB name is used as parameter,

#Set IP for NLB Write-Host "Set NLB IP and change Network adapter" -ForegroundColor yellow Netsh interface ip set address name="Local Area Connection 3" static 1.1.1.1 255.255.255.0 Netsh interface set interface name="local area connection 3" newname="NLB"

The Ip adress is needed to set the NLB don’t worry the IP will be removed when the NLB is complete.

The other part is the powershell script is connecting thrue powershell to the other node.

First give the netbios name from the second node ( make sure your DNS is OK )

#Adding additional cluster nodes based on user input
Write-Host "Give Second NLB host" -ForegroundColor yellow
$Node2Fqdn = Read-Host "Enter 2e NLB node"

Here with the “function EndPSS { get-pssession | remove-pssession } “
and “endpss “ I connect and disconnect to the other node. the problem here was. in the basic security you can only use 5 remote connections. I can adjust this or make a workaround so the script works in the default security template.

Now I connect from node 1 to node 2 and placed a IP and renamed the NIC

function EndPSS { get-pssession | remove-pssession }
#Set Network Adapter
#Enter-PSSession -ComputerName $Node2Fqdn
invoke-command -computername $Node2Fqdn -scriptblock { Netsh interface ip set address name="local area connection 3" static 1.1.1.2 255.255.255.0}
invoke-command -computername $Node2Fqdn -scriptblock { Netsh interface set interface name="local area connection 3" newname="NLB"}
Write-Host "Placed NLB IP and changed NIC to NLB" -ForegroundColor yellow
exit-PSSession
endpss

#Import-Module NetworkLoadBalancingClusters
Write-Host "Import-Module NetworkLoadBalancingClusters On Remote Node" -ForegroundColor yellow
Enter-PSSession -ComputerName $Node2Fqdn
invoke-command -computername $Node2Fqdn { Import-Module NetworkLoadBalancingClusters}
exit-pssession
endpss

#Add Remote Node To NLB
Write-Host "Adding cluster node $Node2Fqdn" -ForegroundColor yellow
Get-NlbCluster | Add-NlbClusterNode -NewNodeName $Node2Fqdn -NewNodeInterface NLB

#Remove Old IP
Remove-NlbClusterNodeDip 1.1.1.2 -Hostname $Node2Fqdn -force
Write-Host " Remove old IP node " -ForegroundColor yellow

Here I removed the IP that was needed to create the NLB.

You can find the Scripts Here : Powershell Scripts

Powershell Create NLB

Powershell MSDTC cluster add in

Teched 2010 Berlin #TEE10 #Teched

For Demo and learning Sites you need often thing that takes only a minute but when you play with it it takes more time than you want.

So with this in my mind I created 3 powershell scripts that you can use for builing a cluster , NLB Cluster and a MSTDC cluster resource.

So If you want to create a MSDTC resource in your Cluster you can do this in the GUI or with a Powershell Script.

You can find the Scripts Here : Powershell Scripts

Powershell MSDTC cluster add in

First We load the powershell modules If it is a cluster you should do this on all the Cluster Nodes

Servermanagercmd -install Application-Server
Servermanagercmd -install AS-Dist-Transaction AS-Ent-Services –allSubFeatures

I Comment every line for what it does.

# Create a new HA Server Role – Distributed Transaction Coordinator
Add-ClusterServerRole -Name ServerDTC -Storage "Cluster Disk 6" -StaticAddress 192.168.1.1

Here we create the resource and chuck it to a disk and IP

# Add the MSDTC Service to the new Server Role
Get-ClusterGroup ServerDTC | Add-ClusterResource -Name MSDTC-ServerDTC -ResourceType "Distributed Transaction Coordinator"

Put in the MSDTC services.

# Create Dependencies for the DTC group
Add-ClusterResourceDependency MSDTC-ServerDTC ServerDTC
Add-ClusterResourceDependency MSDTC-ServerDTC "Cluster Disk 6"
# Start DTC group
Start-ClusterGroup ServerDTC

 

So Now that this is ready you can configure the MSDTC but remember the items that I mentioned in my previous blog.

http://robertsmit.wordpress.com/2010/10/20/msdtc-clustering-logfile-bug/

  • Tag