Issue
Today I'm going to share my experience which I've faced last week while installing SQL Server 2012 on a Windows Server 2008 R2 Failover Cluster. All SQL Server component got installed but it shows failed in the last. On further investigation, that happened due to CNO permission Issue. SQL Server cluster name was not created within AD, and Windows failover cluster name doesn’t possess the required permissions to create the object.
Here is the error:
Now here is the actual blog starts,
How do I manually add the SQL Server Agent to the cluster resource group?
You will also not be able to see the SQL Server Agent on the Other Resources section of the SQL Server cluster resource group means that it has not been created successfully. You can verify this by trying to add a new resource in the clustered resource group, that will not be listed in it.
Resolution:
Manually add the SQL Server Agent resource type to the SQL Server cluster resource group
Step 1 : Create the SQL Server Agent resource type
Using cmd prompt execute below command:
Step 2 : Add the SQL Server Agent resource to the SQL Server Cluster Resource Group.
Using the Failover Cluster Manager, right-click on the SQL Server cluster resource group
select Add a resource -> More resources ... -> A - Add SQL Server Agent
Step 3 : Set the private properties of the SQL Server Agent resource.
We need to assign the VirtualServerName and InstanceName properties of the SQL Server Agent resource to match those of the SQL Server resource.
Using the Failover Cluster Manager, double-click the SQL Server Agent resource to open up the Properties window. Click on the Properties tab to display the VirtualServerName and InstanceName properties. Enter the appropriate values for the properties and click OK.
Step 4 : Add the SQL Server resource as a dependency for the SQL Server Agent resource you just created. Then add the SQL Server service as a dependency to the SQL Server Agent service as in a stand-alone instance.
Using the Failover Cluster Manager, click on the Dependencies tab of the SQL Server Agent Properties dialog box and select SQL Server under the Resource drop-down list. Click OK.
Step 5 : Modifying SQL Server registry keys
Having an incomplete or corrupted SQL Server installation also means that there are registry keys that have not been properly written or updated. It is important to backup your registry prior to performing these tasks.
5.1. Open the Registry Editor and navigate to the following registry hives.
For default instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ConfigurationState
For a named Instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft Microsoft SQL Server\MSSQL10_50.INSTANCENAME\ConfigurationState
5.2. Check the values of all the registry keys. If the value is greater than 1, it means that there was a failure either during the installation or configuration phase while running the setup process. In my environment, all of the registry keys have a value of 2.
5.3. Change all of the registry key values to 1.
Step 6 : Run a Repair of the SQL Server 2008 R2 installation
After all of the ground work has been done, you can now perform a repair of the SQL Server instance. To do this, run the setup.exe from the SQL Server 2012 installation media and click the Maintenance link on the left-hand side. You can then click the Repair link to run the repair process.
Restart SQL Server Services and Thats it!!
Today I'm going to share my experience which I've faced last week while installing SQL Server 2012 on a Windows Server 2008 R2 Failover Cluster. All SQL Server component got installed but it shows failed in the last. On further investigation, that happened due to CNO permission Issue. SQL Server cluster name was not created within AD, and Windows failover cluster name doesn’t possess the required permissions to create the object.
Here is the error:
The cluster resource ‘SQL Server (ClusterName)’ could not be brought onlineOnce granted the proper permission then SQL Server cluster resource group was successfully brought online. Then noticed that the SQL Server Agent was not listed as a resource type under the Other Resources section of the cluster resource group.
due to an error bringing the dependency resource ‘SQL Network Name(ClusterName)’ online.
Refer to the Cluster Events in the Failover Cluster Manager for more information.
Now here is the actual blog starts,
How do I manually add the SQL Server Agent to the cluster resource group?
You will also not be able to see the SQL Server Agent on the Other Resources section of the SQL Server cluster resource group means that it has not been created successfully. You can verify this by trying to add a new resource in the clustered resource group, that will not be listed in it.
Resolution:
Manually add the SQL Server Agent resource type to the SQL Server cluster resource group
Step 1 : Create the SQL Server Agent resource type
Using cmd prompt execute below command:
cluster.exe restype "SQL Server Agent" /create /DLL:SQAGTRES.DLL
Step 2 : Add the SQL Server Agent resource to the SQL Server Cluster Resource Group.
Using the Failover Cluster Manager, right-click on the SQL Server cluster resource group
select Add a resource -> More resources ... -> A - Add SQL Server Agent
Step 3 : Set the private properties of the SQL Server Agent resource.
We need to assign the VirtualServerName and InstanceName properties of the SQL Server Agent resource to match those of the SQL Server resource.
Using the Failover Cluster Manager, double-click the SQL Server Agent resource to open up the Properties window. Click on the Properties tab to display the VirtualServerName and InstanceName properties. Enter the appropriate values for the properties and click OK.
Step 4 : Add the SQL Server resource as a dependency for the SQL Server Agent resource you just created. Then add the SQL Server service as a dependency to the SQL Server Agent service as in a stand-alone instance.
Using the Failover Cluster Manager, click on the Dependencies tab of the SQL Server Agent Properties dialog box and select SQL Server under the Resource drop-down list. Click OK.
Step 5 : Modifying SQL Server registry keys
Having an incomplete or corrupted SQL Server installation also means that there are registry keys that have not been properly written or updated. It is important to backup your registry prior to performing these tasks.
5.1. Open the Registry Editor and navigate to the following registry hives.
For default instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ConfigurationState
For a named Instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft Microsoft SQL Server\MSSQL10_50.INSTANCENAME\ConfigurationState
5.2. Check the values of all the registry keys. If the value is greater than 1, it means that there was a failure either during the installation or configuration phase while running the setup process. In my environment, all of the registry keys have a value of 2.
5.3. Change all of the registry key values to 1.
After all of the ground work has been done, you can now perform a repair of the SQL Server instance. To do this, run the setup.exe from the SQL Server 2012 installation media and click the Maintenance link on the left-hand side. You can then click the Repair link to run the repair process.
Restart SQL Server Services and Thats it!!
