Some time back we talked about AX Data Refresh with Powershell. Today I wanted to talk about some AX Refresh changes and two new scripts AX Monitor and AX Report.
Let's go over some of the details.
- Works for AX 2009 and 2012.
- Works with multiple AX instances on the same box.
- It does not require AOS information on every run.
- Compares Registry information to validate database settings. It removes services pointing to different databases.
- Allows Help Desk to start an environment refresh even if they don't have permissions on the database.
- Basic data scrub.
- Runs as Task Scheduler and sends an email when it is done.
- With a backup folder. It selects the newest backup to restore.
- With no backup folder. It only restores previous configuration (it assumes the DBA team had restored the database).
The next two scripts I have been using on customers for some time. In particular, one of my biggest and longest DSE customer.
- The beginning, the script was sending alerts if the number of SQL blocks had exceeded the threshold. Especially after the R3 upgrade, we noticed situations where SQL had 100% CPU due to some parameter sniffing. The script monitors the database for CPU, blocking and wait time. If CPU gets above the threshold, it collects all statements running, filter by tables (pre-defined filter) and runs update statistics to generate a new sample. That approach reduced production issues due to CPU by 99% (like D365 watchdog).
- It also collects troubleshooting data; SQL Statements and Execution Plans on every run, and Statistics changes every 30 min.
- The alert email helps to mitigate the situation before it becomes a system outage. The email subject changes depending on the script action during high CPU.
- In a growing AX environment, sometimes the number of servers to manage and manage can consume a great deal of time. The script manages:
- Perfmon Files - creates a file a day, archives files from servers to a different location, process blg files and shows values outside the best practices.
- Collect data from all servers (event logs, failed reports, SQL logs) and AX data (batch jobs with error, jobs running long, MRP runtime, AOS restarts, and crashes).
- It provides an environment 'picture' daily, making it easier to find and solve problems.
More info can be found at DynamicsAxTools. Check the Wiki page.
To make it more intuitive and easy to use. There is a WPF form to help with environment settings and running Powershell as a job.
- Create Database
- Create Email credential
- Create Email Server settings
- Create an Environment
- Add Servers and Deploy perfmon templates
- Check your Environment and Start/Stop services
- Schedule Scripts as jobs
I still have a couple more scripts to share; multi-thread silent installation and POS terminal data collector. Once I remove some possible PII data they might have I will post it here.
DISCLAIMER Microsoft Corporation. All rights reserved. MIT License Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY (INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF BUSINESS PROFITS, BUSINESS INTERRUPTION, LOSS OF BUSINESS INFORMATION, OR OTHER PECUNIARY LOSS), WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. THE SOFTWARE IS NOT SUPPORTED UNDER ANY MICROSOFT STANDARD SUPPORT PROGRAMS OR SERVICES. THE OPINIONS AND VIEWS EXPRESSED ARE THOSE OF THE AUTHOR AND DO NOT NECESSARILY STATE OR REFLECT THOSE OF MICROSOFT. Microsoft is a registered trademark or trademarks of Microsoft Corporation in the United States and/or other countries.