Archive for the ‘Windows 2008 SQL 2008 Cluster’ 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:

    SET XACT_ABORT ON

    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 Create Windows 2008 R2 Cluster

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.So If you want to create a Cluster you can do this in the GUI or with a Powershell Script.

So make sure the disk are formatted and that Your network is ok. and if you running this script in a environment with a Windows 2003 DC with DNS. You may have to set extra security to get things working.

You can find the Scripts Here : Powershell Scripts

Powershell Create Cluster Teched 2010 Berlin #TEE10 #Teched

This Cluster creation script creates a two node cluster with a witness disk at your choice. First we do the import of the modules and add the feature for failover clusters this section you have to do this on every node. You can do this remote if you want. Or run the script on the other node and abort it on the input section.

 

#Importing Microsoft`s PowerShell-modules
Write-Host "Install System Modules" -ForegroundColor yellow
ImportSystemModules
#Add-WindowsFeature
Write-Host "Add Failover Clustering" -ForegroundColor yellow
Add-WindowsFeature Failover-Clustering
#Importing Microsoft`s PowerShell-module for administering NLB Clusters
Write-Host "Install ServerManager Modules" -ForegroundColor yellow
Import-Module ServerManager

Here we put the cluster names , netbios, clustername, IP etc.

#Variables for creating the new cluster
Write-Host "Cluster Properties Hostname / Node Names" -ForegroundColor yellow
$Clusternode1 = Read-Host "Enter ClusterNode 1 for the new cluster"
$Clusternode2 = Read-Host "Enter ClusterNode 2 for the new cluster"
$ClusterFqdn = Read-Host "Enter Cluster for the new cluster"
$ClusterIP = Read-Host "Enter cluster primary IP"
#Clear old Cluster info
Write-Host "Old Cluster Configuration Will Be ereased just to make sure" -ForegroundColor yellow
Clear-ClusterNode $Clusternode1 -force
Clear-ClusterNode $Clusternode2 -force

#Validating new cluster
Write-Host "Validate Windows 2008 R2 Cluster…" -ForegroundColor yellow
Test-Cluster $Clusternode1 -ignore network,inventory,storage

#Creating new cluster
Write-Host "Create Windows 2008 R2 Cluster…" -ForegroundColor yellow
New-Cluster -Node "$Clusternode1" -Name $ClusterFqdn -NoStorage -StaticAddress "$ClusterIP"

If you have multiple disks and you don’t know what is cluster disk 4 or 6 or what disk should be used for the Quorum disk ? Well I did make a selection that shows only the smallest disk that are available

#Show Cluster Disk Available
Write-Host "Show Cluster Disk Available…" -ForegroundColor yellow
Get-ClusterAvailableDisk
#Show Wittness Disk Capable
Write-Host "Show Wittness disk for Cluster Available…" -ForegroundColor yellow
Get-ClusterAvailableDisk | ?{ $_.Size -lt 1573741824}

Sure You can change this for not to put in “cluster disk 1” but use only the disk number.

# Place the Wittness disk
Write-Host "Choose a Wittness disk for the Windows 2008 R2 Cluster… Must be "Cluster Disk # "" -ForegroundColor yellow
$ClusterQuorumdisk = Read-Host "Enter Wittness disk Cluster Disk number"

# Add disks to the Cluster
Write-Host "Add disks to Windows 2008 R2 Cluster…" -ForegroundColor yellow
Get-ClusterAvailableDisk | Add-ClusterDisk

Here Is the mounting off the cluster disk and I put a sleep in it this way I make sure the disk is online

#Show Wittness Disk Capable
Write-Host "Show Wittness disk for Cluster Available…" -ForegroundColor yellow
Get-ClusterAvailableDisk | ?{ $_.Size -lt 1573741824}
Start-Sleep -Seconds 5
# Change the quorum model
Write-Host "Change the Quorum model Windows 2008 R2 Cluster…" -ForegroundColor yellow
Set-ClusterQuorum -NodeAndDiskMajority $ClusterQuorumdisk

The Second node is added If you have a four node cluster just add them here

Add-Clusternode $Clusternode2 ,$Clusternode3 , $Clusternode4

But you must also use the input section above

$Clusternode3 = Read-Host "Enter ClusterNode 3 for the new cluster"

$Clusternode4 = Read-Host "Enter ClusterNode 4 for the new cluster"

 

 

# Add Cluster node
Write-Host "Add Cluster node to Windows 2008 R2 Cluster…" -ForegroundColor yellow
Add-Clusternode $Clusternode2

You can find the Scripts Here : Powershell Scripts

Powershell Create Cluster 

Teched 2010 Berlin #TEE10 #Teched

MSDTC Clustering Logfile Bug

For some enhanced settings I want to change the log file settings in the MSDTC settings.

It should been between 1 MB and 512 MB so I set my logfile setting to 512 Mb.

Invalid log file ? What ??  Why is 70 MB between 1 and 512 Mb  ?

clip_image001

So it turns out that 64 MB is the max logfile size for the MSDTC log.

Is it a bug ? Yes and No The error message is not clear for this You can’t set this in the GUI you can only set this in the Registery.

clip_image002 clip_image003

There is more diskspace so this can’t be the issue.

Set the LOG size above 64 MB

By default, the Maximum log file size is 64 MB. For general scenarios, one should not need more than that. But if you have a reason to increase the log file size past 64 MB, you can push this up to 512 MB provided you have a certain registry entry – MaxLogSize under MSDTC registry key.

For standalone servers, this is under HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSDTC .

For Clustered DTC, it is at HKEY_LOCAL_MACHINEClusterResources<ResID>MSDTCPRIVATEMSDTC

Create a DWORD entry MaxLogSize and set it to 512 (decimal)

But What about the security settings ? IF I look I the services than I see a domain account running this services.

clip_image001[4]

But On the DCOMCNFG.exe I see no service account but when I change something here the service account is reset to the Networkservice.

clip_image002[4]

This is also a GUI bug If you look in the register you can see the service account is there and in the services it is also there.

So be carefull If you change something in the cluster MSDTC gui your services account will go to the default settings !

the NETWORKService

WordPress Tags: MSDTC,Logfile,want,file,Invalid,Registery,issue,Maximum,MaxLogSize,HKEY_LOCAL_MACHINE,SOFTWARE,Microsoft,Cluster,Resources,ResID,MSDTCPRIVATE,Create,DWORD,DCOMCNFG,service,Networkservice,settings,change,should,size,default,registry,entry,under,account,also

  • Tag