2012 AlwaysOn AG could not failover automatically when service runs as Built-in account

Question

2012 AlwaysOn AG could not failover automatically when service runs as Built-in account

cherish.j.wang

Right there with Babe

Points: 758
More actions
January 3, 2014 at 4:24 pm

#287782

confusion
I am confused recently weeks for SQL Server 2012 Alwayson AG HA could not failover automatically when SQL Service is running as built-in account.
according
From Microsoft SQL Server 2012 online library (link & description words are listed below), we can actually do that from the description, right?
Link:
----------description (you can search built-in:ctrl + F )
However, if the SQL Server service is running as a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication, and the wizard will be unable to create a database mirroring endpoint on the server instance. In this case, we recommend that you create the database mirroring endpoints manually before you launch the wizard.
----------
Environment
DC: Windows 2008 R2 ENT + File Share Quorum
AlwaysOnNode1: Windows 2008 R2 ENT + SQL 2012 Developer
AlwaysOnNode2: Windows 2008 R2 ENT + SQL 2012 Developer
Demo Testing
But according to my testing, SQL Server 2012 AG could not failover automatically.
Step1. WSFS configuration (done)
Step2. SQL Server 2012 install (done/ this time I used domain user,not the local system built-in account)
Step3. Enable AG
Step4. AlwaysOn AG Creation and configuration, you can pick the script enclosed up and check.
Step5. before the last step in the script, AlwaysOn AG works fine (both STOP Primary Node SQL Service and Shutdown OS, Alwayson AG works fine and could failover automatically). But after my change the RUn as user account to Local System, Alwayson AG could not do that failover.
Questions
1. is there any misunderstanding Microsoft online library description?
2. is there any issue for my script for my purpose?
3. if my understood is right, how to do that to hit the goal?
Any suggestions would be appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

cherish.j.wang Right there with Babe Points: 758 More actions · Answer 1

There are so rarely topics, articles or blog to discuss the tech details. I just find only one Chinese blog. But when go though and do it similarly, I am failed failover when change the run as user to Local System. is there anybody can help me?

references blog:

http://www.cnblogs.com/TeyGao/p/3380059.html

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

cherish.j.wang (1/3/2014)
confusion
I am confused recently weeks for SQL Server 2012 Alwayson AG HA could not failover automatically when SQL Service is running as built-in account.
according
From Microsoft SQL Server 2012 online library (link & description words are listed below), we can actually do that from the description, right?
L
----------description (you can search built-in:ctrl + F )
However, if the SQL Server service is running as a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication, and the wizard will be unable to create a database mirroring endpoint on the server instance. In this case, we recommend that you create the database mirroring endpoints manually before you launch the wizard.
----------
Environment
DC: Windows 2008 R2 ENT + File Share Quorum
AlwaysOnNode1: Windows 2008 R2 ENT + SQL 2012 Developer
AlwaysOnNode2: Windows 2008 R2 ENT + SQL 2012 Developer
Demo Testing
But according to my testing, SQL Server 2012 AG could not failover automatically.
Step1. WSFS configuration (done)
Step2. SQL Server 2012 install (done/ this time I used domain user,not the local system built-in account)
Step3. Enable AG
Step4. AlwaysOn AG Creation and configuration, you can pick the script enclosed up and check.
Step5. before the last step in the script, AlwaysOn AG works fine (both STOP Primary Node SQL Service and Shutdown OS, Alwayson AG works fine and could failover automatically). But after my change the RUn as user account to Local System, Alwayson AG could not do that failover.
Questions
1. is there any misunderstanding Microsoft online library description?
2. is there any issue for my script for my purpose?
3. if my understood is right, how to do that to hit the goal?
Any suggestions would be appreciated.

You will need to use certificate based authentication for your endpoints, this link[/b] details how to create these.

This link details transport security for database mirroring and AlwaysOn.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

cherish.j.wang Right there with Babe Points: 758 More actions · Answer 3

Perry Whittle (1/4/2014)
cherish.j.wang (1/3/2014)
confusion
I am confused recently weeks for SQL Server 2012 Alwayson AG HA could not failover automatically when SQL Service is running as built-in account.
according
From Microsoft SQL Server 2012 online library (link & description words are listed below), we can actually do that from the description, right?
L
----------description (you can search built-in:ctrl + F )
However, if the SQL Server service is running as a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication, and the wizard will be unable to create a database mirroring endpoint on the server instance. In this case, we recommend that you create the database mirroring endpoints manually before you launch the wizard.
----------
Environment
DC: Windows 2008 R2 ENT + File Share Quorum
AlwaysOnNode1: Windows 2008 R2 ENT + SQL 2012 Developer
AlwaysOnNode2: Windows 2008 R2 ENT + SQL 2012 Developer
Demo Testing
But according to my testing, SQL Server 2012 AG could not failover automatically.
Step1. WSFS configuration (done)
Step2. SQL Server 2012 install (done/ this time I used domain user,not the local system built-in account)
Step3. Enable AG
Step4. AlwaysOn AG Creation and configuration, you can pick the script enclosed up and check.
Step5. before the last step in the script, AlwaysOn AG works fine (both STOP Primary Node SQL Service and Shutdown OS, Alwayson AG works fine and could failover automatically). But after my change the RUn as user account to Local System, Alwayson AG could not do that failover.
Questions
1. is there any misunderstanding Microsoft online library description?
2. is there any issue for my script for my purpose?
3. if my understood is right, how to do that to hit the goal?
Any suggestions would be appreciated.
You will need to use certificate based authentication for your endpoints, this link[/b] details how to create these.
This link details transport security for database mirroring and AlwaysOn.

as you said, "the link" is mine & "The link" is Microsoft references.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

Have you read the articles I supplied??

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

cherish.j.wang Right there with Babe Points: 758 More actions · Answer 5

Perry Whittle (1/8/2014)
Have you read the articles I supplied??

Hi Perry,

Thanks for your reply. But I really created database mirroring endpoint use certification for outbound and inbound. you can see the attachment enclosed named AG_Configure_BuildInUsers.sql.

but, when I changed the run as Local System to start SQL Service, we can't fail over AG. that's my question. Do you have any ideas?

thanks

cherish.j.wang Right there with Babe Points: 758 More actions · Answer 6

I found the where the issue is. There is a maximum failures failover limitation on WSFC resources. we can just increase the Maximum failures count to 10 or more for testing purpose.

cause

After some research, we traced it to the ‘Maximum failures in the specified period’ setting at the cluster group (service application) level.

There is a limitation of maximum failures failover during 6 hours (by default the value is only 1). It turns out that the testing activity involved some failures already due to which the above limit was already reached. So we can’t failover twice automatically during less than 6 hours.

solution

Start > Administrative Tools > Server Manager > Features > Failover Cluster Manager > Services and Applications > properties > Failover > Maximum failures in the specified period is 1.

Increase the maximum failures count to 10 or larger value.

result

after that we can failover successfully.

Thanks all.