SQL SERVER – Installation Fails With Error – A Constraint Violation Occurred

In this blog post we will discuss about Installation Fails With Error – A Constraint Violation Occurred.

The issue which we saw was that SQL Server cluster setup would create a network name resource in failover cluster manager and it would fail. Here is the message which we would see in the setup

The cluster resource ‘SQL Server (SQLSQL)’ could not be brought online due to an error bringing the dependency resource ‘SQL Network Name (SAPSQL)’ online. Refer to the Cluster Events in the Failover Cluster Manager for more information.
Click ‘Retry’ to retry the failed action, or click ‘Cancel’ to cancel this action and continue setup.

When we look at event log, we saw below message (event ID 1194)

Log Name: System
Source: Microsoft-Windows-FailoverClustering
Date: 20/06/2016 19:55:45
Event ID: 1194
Task Category: Network Name Resource
Level: Error
Keywords:
User: SYSTEM
Computer: NODENAME1.internal.sqlauthority.com
Description:
Cluster network name resource ‘SQL Network Name (SAPSQL)’ failed to create its associated computer object in domain ‘internal.sqlauthority.com’ during: Resource online.
The text for the associated error code is: A constraint violation occurred.
Please work with your domain administrator to ensure that:
– The cluster identity ‘WINCLUSTER$’ has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity ‘WINCLUSTER$’.
– The quota for computer objects has not been reached.
– If there is an existing computer object, verify the Cluster Identity ‘WINCLUSTER$’ has ‘Full Control’ permission to that computer object using the Active Directory Users and Computers tool.

Another client got “Access is denied” messages instead of “A constraint violation occurred” in above event ID. My clients have informed that they have logged in as domain admin so Access denied is impossible. Error from another client is below.

SQL SERVER - Installation Fails With Error - A Constraint Violation Occurred setup-err-01

I explained all of them that when network name is created in a cluster, it would contact active directory (AD) domain controller (DC) via Windows Cluster Network name computer account also called as CNO (Cluster Network Object). So, whatever error, we are seeing are possible because the domain admin account (windows logged in user account) is not used to create a computer object for SQL in AD.

To solve this problem, we logged into the domain controller machine and created the Computer Account: SAPSQL (called as VCO – Virtual Computer Object). Gave the cluster name WINCLUSTER$ full control on the computer name. If we carefully read error message, we have the solution already listed there. Then clicked on the retry option in the setup. The setup continued and completed successfully.

Solution/Workaround:

Here are the detailed steps (generally done on a domain controller by domain admin):

  1. Start > Run > dsa.msc. This will bring up the Active Directory Users and Computers UI.
  2. Under the View menu, choose Advanced Features.
  3. If the SQL Virtual Server name is already created, then search for it else go to the appropriate OU and create the new computer object [VCO] under it.
  4. Right click on the new object created and click Properties.
  5. On the Security tab, click Add. Click Object Types and make sure that Computers is selected, then click Ok.
  6. Type the name of the CNO and click Ok. Select the CNO and under Permissions click Allow for Full Control permissions.
  7. Disable the VCO by right clicking. (The account must be disabled so that when the Create Cluster wizard is run, it can confirm that the account it will use for the cluster is not currently in use by an existing computer or cluster in the domain.)

This is also known as pre-staging of the VCO.

 

 

Advertisements

How to Use the Cluster TMP file to Replace a Damaged Clusdb File in 2003 Cluster

If the Clusdb file is damaged you may be able to restore this file using a .tmp file in the cluster hive. The Clusdb file is the registry used for Microsoft Cluster Server and is reproduced on both nodes when you are using cluster server. Cluster Server creates a copy of the Clusdb file named Chkxxx.tmp located in the Mscs folder on the shared disk where the quorum information is.

How to Restore the Clusdb File From the Chkxxx.tmp File on Both Nodes

To restore the Clusdb file from the Chkxxx.tmp file on both nodes in the cluster, use the following steps:

  • On both nodes, change the Startup value to Manual for the following items: Clusdisk (in the Devices tool in Control Panel)
    Cluster Service (in the Services tool in Control Panel)
  • Shut down node 2.
  • Restart node 1.
  • Copy the Chkxxx.tmp file with the most recent time and date stamp from the \Mscs folder on the shared quorum drive to a disk and to the local %SystemRoot%\Cluster folder.
  • Rename the Clusdb file in the %SystemRoot%\Cluster folder to Clusdb.old.
  • Rename the Chkxxx.tmp file in the %SystemRoot%\Cluster folder to Clusdb.
  • Change the Startup value back to the following settings: System for Clusdisk (in the Devices tool in Control Panel)
    Automatic for the Cluster service (in the Services tool in Control Panel)
  • Restart node 1.
  • Verify that the Cluster service starts correctly after you restart the node.
  • Start node 2.
  • On node 2, copy the Chkxxx.tmp file from the disk to the %SystemRoot%\Cluster folder.
  • Rename the Clusdb file in the %SystemRoot%\Cluster folder to Clusdb.old.
  • Rename the Chkxxx.tmp file in the %SystemRoot%\Cluster folder to Clusdb.
  • Change the Startup value back to the following settings: System for Clusdisk (in the Devices tool in Control Panel)
    Automatic for the Cluster service (in the Services tool in Control Panel)
  • Restart node 2.
  • Verify that the Cluster service starts correctly after you restart the node and that you can failover resources.

 

How to Restore the Clusdb File From the Chkxxx.tmp File on Only One Node

To restore the Clusdb file from the Chkxxx.tmp file on only one node, use the following steps:

  • Using the Services tool in Control Panel, set the Cluster service to Manual on the node where the service does not start.
  • Restart the node.
  • From the correctly functioning node, copy the Chkxxx.tmp file with the most recent time and date stamp from the Mscs folder on the shared quorum drive to a disk.
  • On the non-functioning node, rename the Clusdb file in the %SystemRoot%\Cluster folder.
  • Copy the Chkxxx.tmp file from the disk to the %SystemRoot%\Cluster folder.
  • Rename the Chkxxx.tmp file in the %SystemRoot%\Cluster folder to Clusdb.
  • Using the Services tool in Control Panel, start the Cluster service.
  • Verify that the service starts correctly and that you can failover resources.
  • Set the Cluster service back to Automatic using the Services tool in Control Panel.

 

How to change the MSDTC logs path in cluster from One disk to another disk in windows 2008

Recently I was working on a box which had MSDTC issue and I was unable bring up the MSDTC resource as there was some changes done. MSDTC drive was deleted and MSDTC is unable to recognize the MSDTC log file path.

MSDTC is an acronym for Microsoft Distributed Transaction Coordinator. As the name says, MSDTC is a Windows service providing transaction infrastructure for distributed systems. In this case, a transaction means a general way of structuring the interactions between autonomous agents in a distributed system. Each transaction is a state transformation with four key properties – the ACID properties: Atomic (all or nothing), Consistent (legal), Isolated (independent of concurrent transactions) and Durable (once it happens, it cannot be abrogated). There are different techniques that implement the ACID properties but the most known one is two-phase commit.

In my scenario MSDTC is configured in Windows Server Failover Cluster and I need to change the path of the MSDTC Log file.

Open Component Services from Administrative Tools

How to change the MSDTC Logs in Windows Server 2008_00

After that Component Services will open

How to change the MSDTC Logs in Windows Server 2008_01Explorer Component Services

How to change the MSDTC Logs in Windows Server 2008_02You need to go to the Distributed Transaction and then open the Clustered DTC and select the Object under that

How to change the MSDTC Logs in Windows Server 2008_03Right click on that Object and click Properties

How to change the MSDTC Logs in Windows Server 2008_04

 

Now you need to select the Logging Tab

How to change the MSDTC Logs in Windows Server 2008_05Now click Browse and point to a new path and click apply and Ok

 

You are done 🙂

Cluster node /forcecleanup & Error -2147352567

How to do complete cleanup of the failed cluster service from Windows 2003 Cluster:-

This is a not so common issue I can across this week . The background is as follows.

  • You decided to evict a node from your cluster.
  • There is a communication failure between nodes and a warning appears that the cluster was unable to remove clustering components from the evicted node.
  • You log onto the node in question and from a command prompt run “cluster node /forcecleanup”

The command responds with the output show in the screen shot below

 

1

 

Your server is now in limbo. You cannot un-install the cluster service and you cannot re-join the cluster, the cluster network driver and cluster disk driver are still online. Most people would re-install the server from scratch at this point. There is however a workaround that will save you time.

1.    Open up regedit on the system in question & Navigate to HKEY Local Machine\Software\Microsoft\Windows NT\Currentversion\Cluster Server

 

2

2.   Right click ClusterInstallationState and choose Modify

3.   Change the value to 3

3

4.   Click OK

5.   Exit Regedit and reboot your server.

6.   Log back into windows and go to a command prompt.

7.   Run “Cluster node /forcecleanup” you should see the following output

5

That’s it !! No rebuild required. You can now operate the node as a stand alone box or join another cluster.