I was trying to install SQL Server 2008 R2 on a
Windows Server 2008 R2 Failover Cluster, but the setup did not complete
successfully. Upon further investigation, the issue seems to be caused by a DNS
entry that was then fixed by the systems administrator. While the SQL Server
cluster resource group was successfully brought online after the DNS issue was
fixed, I noticed that the SQL Server Agent was not listed as a resource type
under the Other Resources section of the cluster
resource group. How do I manually add the SQL Server Agent to the cluster
resource group?
Solution
Not seeing the SQL Server Agent on the Other
Resources section of the SQL Server cluster resource group
means that it has not been created successfully. This is usually caused by an
incomplete or corrupt installation when you perform a single-node cluster
installation of SQL Server. In this particular case, the single-node cluster
installation failed because the virtual server name could not be registered to
the DNS. You can verify this by trying to add a new resource in the clustered
resource group. In the screenshot below, you do not see the SQL Server Agent in
the list of available resources - only the SQL Server resource is available.
While searching for a Microsoft KB article that would
provide a resolution for this issue, I saw a problem similar to mine that was
posted on the MSDN Forums.
We can manually add the SQL Server Agent as a clustered resource type.
But before we do this, we need to make sure that the SQAGTRES.DLL file
has already been copied to the C:\WINDOWS\SYSTEM32 folder. This DLL file gets
copied as part of the failover cluster installation together with the main DLL
used by the SQL Server database engine - SQSRVRES.DLL.
While the functions of these two Resource DLLs are beyond the scope of this
tip, you can learn more from this SQL-Server-Performance.comarticle.
To fix the SQL Server Agent resource on the clustered
resource group, you need to perform the following steps below:
NOTE: These steps can be performed either
via the Failover Cluster Manager, cluster.exe or Windows PowerShell. Since we
all have different preferences, you decide which method to use. I am including
all three options where applicable. When choosing to use Windows PowerShell,
make sure you import the Windows PowerShell modules, in particular, the
FailoverClusters module using the Import-Module cmdlet as defined here.
Manually add the SQL Server Agent
resource type to the SQL Server cluster resource groupStep 1
Create the SQL Server Agent resource type
Using cluster.exe,
cluster.exe
restype "SQL Server Agent" /create /DLL:SQAGTRES.DLL
Using Windows PowerShell,
Add-ClusterResourceType
"SQL Server Agent" C:\Windows\system32\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
Using cluster.exe,
cluster
resource "SQL Server Agent" /create /group:"SQL Server
(MSSQLSERVER)" /type:"SQL Server Agent"
Using Windows PowerShell,
Add-ClusterResource
-Name "SQL Server Agent" -ResourceType "SQL Server Agent"
-Group "SQL Server (MSSQLSERVER)"
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. In
my environment, the VirtualServerName property is SQLCLUS and
the InstanceName is MSSQLSERVERsince I am using a default instance.
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.
Using cluster.exe,
cluster
resource "SQL Server Agent" /priv VirtualServerName=SQLCLUS
cluster resource "SQL Server Agent" /priv InstanceName=MSSQLSERVER
Using Windows PowerShell,
Get-ClusterResource
"SQL Server Agent" | Set-ClusterParameter VirtualServerName SQLCLUS
Get-ClusterResource "SQL Server Agent" | Set-ClusterParameter
InstanceName MSSQLSERVER
Step 4
Add the SQL Server resource as a dependency for the SQL Server Agent resource
you just created.
This is the same as adding the SQL Server service as a dependency to the
SQL Server Agent service 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.
Using cluster.exe,
cluster
resource "SQL Server Agent" /adddep:"SQL Server"
Using Windows PowerShell,
Add-ClusterResourceDependency
"SQL Server Agent" "SQL Server"
You can also verify if the SQL Server Agent resource
has all of the nodes in the cluster listed as possible resource owner. Usually,
if this is done correctly at the cluster resource group level, all of the
resource types inherit the settings.
Using the Failover Cluster Manager, click on the Advanced
Policies tab of the SQL Server Agent Properties dialog box
to see the list of Possible Owners.
Using cluster.exe,
cluster
resource "SQL Server Agent" /listowners"
Using Windows PowerShell,
Get-ClusterResource
"SQL Server Agent" | Get-ClusterOwnerNode
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.
- 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
- 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.
Change all of the registry key values
to 1.
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
2008 R2 installation media and click the Maintenance link
on the left-hand side. You can then click the Repairlink
to run the repair process. A more detailed procedure is defined here.
NOTE: Make sure that you run the repair process
on the node that does not own the SQL Server cluster resource group, or in
other words, the passive node. If you are, manually fail over the SQL Server
cluster resource group to the other node before proceeding with the repair.
Bring the SQL Server Agent Resource Online
Once the repair process completes successfully, you can now bring the
SQL Server Agent resource online.
Using the Failover Cluster Manager, right-click the SQL Server Agent
resource and select Bring this resource online option.
Using cluster.exe,
cluster
resource "SQL Server Agent" /online
Using Windows PowerShell,
Start-ClusterResource
"SQL Server Agent"
That's it Now the SQL Agent is Online