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!

  • 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 Nov 18 '16 at 15:47
  • Sorry about that when I meant failover I was referring to the AAG – user2923332 Nov 18 '16 at 18:34
  • And what error did you get with the proxies? (and what method are you using to take the backups?) – Nic Nov 18 '16 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 – user2923332 Nov 18 '16 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 Nov 18 '16 at 20:59
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.

  • 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. – user2923332 Nov 18 '16 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 Nov 19 '16 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 Nov 20 '16 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 Nov 21 '16 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 Nov 23 '16 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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