1

I have a 2 node Always On Availability Group cluster where we are planning to take backups to a file share which will then be moved to Azure cloud. We have a service account from the Windows team for the backup job, but the startup account of the database engine and the SQL Server Agent are local accounts. Using proxies did not help me with the situation so I am now thinking of providing the service account credentials in the SQL Server Configuration Manager.

What I want to know is how do I proceed?

This is what I have planned. Please let me know if I have missed something in the flow

  1. Change service account on the Node2 (secondary) - both SQL Server (InstanceName) (database engine) and SQL Server Agent (InstanceName) (job engine)
  2. Pause synchronization
  3. Restart the SQL Server (InstanceName) service and the SQL Server Agent (InstanceName)
  4. Failover the AOAG to the secondary, then change the account details of the primary (now the seconday)
  5. repeat step 2. and 3.
  6. Fail back to the primary again

Please let me know if I am missing any step here

Thanks!

Improve this question
5
  • Do you have a failover cluster (FCI) or are you using Availability Groups (AG)? Or both? Your question seems to use the concept of the two interchangeably.
    – Nic
    Commented Nov 18, 2016 at 15:47
  • Sorry about that when I meant failover I was referring to the AAG
    – buddinggeek
    Commented Nov 18, 2016 at 18:34
  • And what error did you get with the proxies? (and what method are you using to take the backups?)
    – Nic
    Commented Nov 18, 2016 at 19:15
  • right now, I am using the native maintenance plans for backups. But as part of standardization I plan to move everything to olla. I actually got no error, I was not able to principals to the proxy account
    – buddinggeek
    Commented Nov 18, 2016 at 19:24
  • You need to add credentials first, then add proxies that reference those credentials. MSDN has a good write up about setting up agent proxies (and credentials). msdn.microsoft.com/en-us/library/ms175834.aspx Might be a good way to negate service account changes.
    – Nic
    Commented Nov 18, 2016 at 20:59

2 Answers 2

Reset to default
0

If you have local accounts and an AG then you must be using mirroring certificates - is this correct? If so then your switchover plan is likely going to be fine because nothing is going to change from the AG's point of view.

I have done something similar just recently to replace local accounts and expiring AG mirroring certificates with service accounts and using that Windows authentication. After the local accounts were in place I ran Alter Endpoint Hadr_Endpoint For Database_Mirroring (Authentication = Negotiate) on both sides, followed by Alter Endpoint Hadr_Endpoint State = Stopped on each, and then started them again. I monitored the endpoint status from their DMVs to make sure they started and checking the ERRORLOG for issues, and confirmed that the AG dashboard was healthy. The final test to confirm it is a failover.

When you say using agent proxies didn't help though - are you sure you used one properly? What happened? I really would have thought it would work.

Improve this answer
5
  • I was given a file share to which backups were to be taken from olla hallengren's scripts. Was getting an error saying "directory not found" . Someone suggested proxies which I tried, and I was not able to add principals to the proxy account details.
    – buddinggeek
    Commented Nov 18, 2016 at 18:33
  • Mirroring endpoints don't care about certificate expiration as they run on top of service broker - which also isn't affected by certificate expiration (neither does TDE for the record, either).
    – Sean Gallardy
    Commented Nov 19, 2016 at 14:41
  • I don't think mirroring uses service broker - they have two distinct endpoints. Also the mirroring endpoint at least in 2012 will fail to start when using expired certificates. BOL documents that you need to set the expiry date carefully, I've witnessed it fail to start on expired certificates, and Remus Rusanu has written the same.
    – Cody Konior
    Commented Nov 20, 2016 at 11:43
  • I stand corrected, the expiration is checked (I swear I remember using expired certs). For the SB use part, look at some error messages with ags you'll see " ... service broker endpoint...".
    – Sean Gallardy
    Commented Nov 21, 2016 at 2:48
  • Hi Sean I've seen that error but it's not caused by AGs using SB. I asked the MS SQL Tiger Team on Twitter today and they said AG doesn't use SB though there are design elements that are similar.
    – Cody Konior
    Commented Nov 23, 2016 at 16:18
0

You also need to grat connect on hadr endpoint on both nodes

use [master] GO GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain1\Service_Start_User] GO

Sorry for formatting. Writing from mobile.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.