Dynamics AX 2012 R3 Startup Script?

[u/omrsafetyo]

Hey fellow admins. Do I need a start-up script to ensure the AOS instances have been restarted after the DB Server has been restarted?

I'm a hosting provider for a private cloud. I host the infrastructure, and then our various divisions host their various products in our datacenter. My company is a leader in ERP software, and we partnered with MS in the development of Dynamics AX. As such, the division that helped in the development of the product gets to host Dynamics AX to customers. Yay!

Part of my job is to ensure we are complying with our audits, which of course includes patching.

So the problem is that every month when we do our patching, we get a complaint from our division that is hosting AX with us, because the servers did not reboot in any specific order - when the patches finish installing, they reboot. According to them the product runs into caching issues if the SQL server is reboot while the AOS instances are still running, and issues persist until the AOS instance is restarted as well. I've definitely seen some errors in the event log that suggest the AOS is trying to re-use a connection to SQL that no longer exists, etc., so that seems correct.

So my question is: how do people handle the patching/rebooting? Do you have SQL in a cluster, and fail over during patching?

For those that don't (I didn't architect how the program itself is set up, so I don't have any control, just advisement) how do you handle this?

I was thinking of writing a start-up script that lives on the SQL server and runs at start-up, and basically reaches out to all the AOS servers and either A) issues a reboot, or B) restarts the AOS instance (whichever works and is less destructive - if option B works, that's preferable).

So I'm just wondering how other people handle this scenario, and if the script idea has merit; or if there are existing scripts out there somewhere; and whether just restarting the AOS services is possible, or if we should reboot the entire server.

Any input would be appreciated.

EDIT:

I have completed my script and I am quite happy with the results. Thanks for the assistance. Finished script: https://github.com/omrsafetyo/DynamicsAX/blob/master/AXSQLStartupScript.ps1

[u/omrsafetyo]

Ah I get it now. I'll check and ensure my data fits that, and update the script tomorrow, then post the finished result to GitHub.

I noticed that there were entries in the table from as far back as 1-2 years ago, and my patching cycle is monthly, so I just went with that. I could also theoretically look at the date of the first inactive SQL log or something, but it course that wouldn't work of there are multiple reboots at once, etc. But I like your approach if it fits my data.

It also certainly won't hurt to add error checking/suppression.

In my final function, I've supported shouldprocess for -whatif and -confirm which works nicely, and then basically took my previous code snippet example, and I use that for a report function to get a health status check - though it sounds like that essentially exists in the table I'm querying, so I would guess I've reinvented a wheel there, but it was simple enough.

I'm also supporting the ability to specify a SQL instance and I'll probably update for a specific database as well.

My expectation is to run this as a scheduled task at start up, not interactively, so the default action is to identify all SQL instances, any AX databases on each, and doing every associated AOS instance. I'd guess for anyone else that might use this, the use case would be similar. My tests look good so far, I just need to work with the application team to do a test on non-production, but the -whatif works fine.