How to change SQL Agent/Server start up account?

[scogeb]

Hi, I have an existing 2 node active-actvie SQL 2008 R2 Veritas 6.0 cluster.  When I installed the SQL instance on each node, I set a specific AD user for starting the SQL Agent and SQL Server service.  I set it the same on each node.  I now want to change that to a different user.  I know how to do that via the SQL Server Configuration Manager on each node, but is that the correct way to do it in a Veritas cluster?  Changing it there will restart the service and cause a failover I believe.  What is the correct way to do this? 

[mikebounds]

If you freeze the SQL service group before you run the SQL Server Configuration Manager then this will prevent a failover when the service is restarted.

Mike

[Wally_Heim · Accepted solution]

Hi scogeb,

This is going to cause problems.  With SQL on Windows we start the SQL services in the virtual server context.  Changing the startup account as you describe will cause the SQL services to be restarted outside of VCS and the SQL resources will go to an unknown state.

I have 2 approaches to change the SQL startup account for you. 

Approach #1:

1. Offline the SQL resources (not the entire service group) in VCS.

2. Change the startup account for SQL in SQL Studio or in SCM as needed.

3. Online the SQL resources as needed.

FYI - don't forget to change the startup account on the remaining nodes in the cluster.

Approach #2:

Or you could do this with SQL active on node 1

1. Node 2 (incative node) change the SQL startup account in SQL Studio or SCM

2. Fail over the SQL service group from node 1 to node 2.

3. Node 1 (now the incative node) change the SQL startup account in SQL Studio or SCM

Also keep in mind that the SQL2008 resource has a User, Domain and Password attributes that might need to be updated if you are also changing the account that DetailMonitoring should use.

Thank you,

Wally

[mikebounds]

Wally,

What would happen if you froze service group - I guess there are 2 scenarios:

1. VCS probes resource during restart and reports offline.  Then when it next probes it sees resource is online and hence reports online
    
2. VCS probes resource after service has restarted - are you saying in this instance, VCS knows what service account started the service and therefore expects to see service online with that service account or offline, but as it sees it online with a different account it reports unknown.

Mike

[scogeb]

Thanks for the posts guys, I appreciate the quick response!  Wally, you were right, it was going to cause problems.  Luckily this was just a testing environment. 

I tried to freeze the service group, as that was the only post I saw at the time.  It worked to a certain degree, but I ended up having to reboot both servers.  When I applied the change in SCM for the SQL service, it locked the server up.  After rebooting both servers and failing the service group between nodes a couple of times, all is good.

Wally, it would have been interesting to see if your solutions would've worked smoother, but I don't want to try to change it again, as I have it set correctly now.

Moral of the story, set the service startup account correctly in the beginning, LOL!

[Wally_Heim]

Hi scogeb,

Following Mike's suggestion should not have hung the server.  I'm not really sure why that happened.  It should have just put SQL into a state that VCS could not control.

Hi Mike,

Freezing the group is not really where the problem is at.  VCS when it starts the SQL services starts them in the virtual server context so that SQL thinks its running on a node that has the virtual server name.  When you change the SQL service account and restart SQL outside of VCS SQL starts up in the local node context.  After this happens the SQL resource in cluster will probe SQL and return an "Unknown" state.  If you check the SQL agent logs it will report that SQL is running outside of VCS control and is not running in the virtual server context.  VCS will not be able to start and stop SQL in this state. 

This is a Windows specfic item that is not seen with VCS on any other platform.

Thank you,

Wally

[scogeb]

Hi Wally, just a quick follow up. I have been playing around with this cluster a bit and have had to freeze the group for some other settings and everything worked good. Not really sure why the server froze up the first time.

[Wally_Heim]

Hi scogeb,

Good to hear everything is working fine now.  If you have any more questions please let us know.

Thank you,

Wally