Unable to stop SQL Server Service if SQLServer2008 agent moniters the service

Problem

We can't manually stop SQL Server Service which is monitored by SQLServer2008 agent even though the Service Group (which has SQLServer resource) is frozen.
We can also see the same behavior if we enter the Maintenance Mode in ApplicationHA.

Error Message

C:\>net stop MSSQLSERVER
The following services are dependent on the SQL Server (MSSQLSERVER) service.
Stopping the SQL Server (MSSQLSERVER) service will also stop these services.

SQL Server Agent (MSSQLSERVER)

Do you want to continue this operation? (Y/N) [N]: y
The SQL Server Agent (MSSQLSERVER) service is stopping..
The SQL Server Agent (MSSQLSERVER) service was stopped successfully.

The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service could not be stopped.

Cause

SQL Server products have a feature to restart SQL server (or SQL server agent) in case it stops unexpectedly.
We also have made IMF compatible with this feature.

Therefore if SQL Agent IMF detects that SQL server has gone down it will read the setting in SQL server side and restart service in case Auto restart is set to true.

Due to this, we see that the "net stop MSSQLSERVER" command is unable to succeed. As VCS has restarted the service in background.

Solution

  Steps to disable SQL Server Service auto restart:

1.        Ensure SQL server and SQL Server Agent services are running

2.        Launch SQL server management studio and connect to localhost

3.        Expand localhost node and right click on “SQL Server Agent” node and select “Properties”

4.        Uncheck the check boxes –

a.        Auto restart SQL server if it stops unexpectedly

b.       Auto restart SQL server Agent if it stops unexpectedly

5.        Click OK

SQL agent caches this setting – hence subsequent “net stop” command will still keep failing if agent cache is not refreshed.

Agent cache can be refreshed by re-enabling IMF or restarting VCS.

Steps to re-enable IMF:

1.        Open configuration

haconf -makerw

2.        Disable IMF

hatype -modify SQLServer2008 IMF -update Mode 0

3.        Enable IMF

hatype -modify SQLServer2008 IMF -update Mode 3

OR

Steps to restart VCS:

1.        Stop VCS

hastop –local -force

2.        Start VCS 

hastart

# If the customer uses ApplicaitonHA, enter the Maintenance Mode and exit the mode for refreshing agent cache.

Applies To

 This problem will occur if  IMF mode is 3 (Default Value).