database Archives - DAXRunBase

Number sequence consumption monitoring

Once a company has been live for a while and they are doing Business As Usual, they often forget about maintaining one critical area. The system is using Number Sequences as identifiers, for which we have fixed, allocated range of values. Typical example is a general financial journal with a format like JNL18-###### with values between 000001 and 999999. Number Sequence consumption monitoring is essential!

If the sequence reaches the maximum value, you are no longer able to create new financial journals. Sometimes it is obvious when a number is about to run out, but in many cases AX has it hidden on a transactional level that is not necessarily visible for the users. When the sequence runs out, it can cause serious issues:

database locking
error messages
rolled back transactions.

Proactive monitoring is key to a healthy ERP system on many levels. Number sequence consumption monitoring is no exception. We are running a Transact-SQL script that keeps tracking of the number sequence utilization, and sends out an e-mail with entries reaching a set threshold. We are running the job based on a weekly schedule. We include any sequences that have used up at least 70% of their available range.

(more…)

By DAXRunBase|2022-03-29T18:35:13+02:00March 31st, 2020|Categories: AX 2012|Tags: AX 2012, database, Monitoring, Number sequence, SQL|0 Comments

Archiving SQL database backups using Azure blob storage

It is a good practice to keep multiple copies of our most precious data. By using on-premises SQL Server databases for AX 2012 or Dynamics 365 Finance and Operations, archiving SQL database backups to offsite-locations are a must. I have built automation for archiving SQL database backups using Azure Blob Storage.

Overview of the processes

Maintenance regime

Our maintenance regime looks like the following:

1x Weekly Full backup
6x Daily Differential backup
15 minute Transactional log backups

They are captured locally on the primary SQL instance, to keep the timestamps for last successful backups in our AlwaysOn cluster. Then we move the files to a shared network storage, which is visible to both High Availability sites, in case there is an outage and we need to a fail over and restore data.

In case of a natural disaster due to the close geographical proximity of the sites we needed an additional layer of safety.

Archiving offsite

Every night we are running a PowerShell script that uses the AzCopy utility. It is uploading our backup files on a Microsoft Azure cloud storage account.

You are paying for the network usage (IO) and the size occupied on the disks, so it is a good idea to have some sort of housekeeping. Our solution was to use an Azure RunBook to determine what to keep and what to delete. The current setup is to have 1 full backup file for a year available every quarter (4x 400 GB), and keep all full / differential / transactional files for the last month (4x 400 GB + 26x 10 GB).

This puts the total size occupied around 4 TB, and costs about 35 GBP a month using a cold storage. This price could go up if you also want to utilize a hot tier storage for the latest backup set. That is useful if you want to come back from the cloud on a short notice.

(more…)

By DAXRunBase|2020-03-23T13:39:50+01:00March 19th, 2020|Categories: AX 2012, Dynamics 365 for Finance and Operations, MSDyn365FO|Tags: #MSDyn365FO, archive, Azure, backup, Blob Storage, database, Dynamics 365, GitHub, PowerShell, SQL|0 Comments

Trace database cleanup in an efficient way

The Trace parser is an excellent tool for troubleshooting business functionality within Microsoft Dynamics AX 2012 and Dynamics 365 for Finance and Operations. Your Trace database can quickly grow large and it does affect the tools’ performance adversely.

Removing the old trace collections one-by-one is time consuming. You could utilize a stored procedure for cleaning up your AX trace database efficiently in SQL:

-- Replace AXTrace with your database name
USE [AXTrace]
GO
 
-- Truncate transaction log to reduce size
DBCC SHRINKFILE (N'AXTrace_log' , 0, TRUNCATEONLY)
GO
 
-- Iterate through the list of traces in the database
DECLARE cur CURSOR FOR 
       SELECT [TraceId] FROM [dbo].[Traces]
 
DECLARE @Id AS int
 
OPEN cur;
FETCH NEXT FROM cur into @Id;
 
-- Remove all traces in the database with the DeleteTrace stored procedure
WHILE (@@FETCH_STATUS = 0)
BEGIN
       EXEC [dbo].[DeleteTrace] @TraceId = @Id
       FETCH NEXT FROM cur into @Id;
END
 
CLOSE cur;
DEALLOCATE cur;
 
-- Truncate transaction log to reduce size
DBCC SHRINKFILE (N'AXTrace_log' , 0, TRUNCATEONLY)
GO

By DAXRunBase|2020-03-23T13:21:53+01:00March 31st, 2019|Categories: AX 2012, Dynamics 365 for Finance and Operations|Tags: AX 2012, cleanup, database, SQL, trace|0 Comments

AX database tuning and maintenance – How to keep it healthy

It is very crucial for your environment to audit, evaluate, then carry out an AX database tuning and maintenance task regularly in order to keep Microsoft Dynamics AX in a good shape.

There are many areas which are frequently being overlooked by partners and customers, for which Bertrand from the Microsoft PFE team has written an excellent summary that you should read here:

https://blogs.msdn.microsoft.com/axinthefield/top-10-issues-discovered-from-dynamics-ax-health-check/.

Many of these configurations are very important to keep your environment healthy from day 1. Often overlooked settings include for example the Index fill factor. It causes fragmentation as records are getting created and updated in your database, resulting in slower statement execution times and even timeouts, which is very bad and has been discussed in this post:

Improve AX performace by fixing bad Query plans

If you’ve got very large DBs like we have, you could be safe with setting 90% then doing an index rebuild to not bloat your DB size by too much. Other than that the MS recommendation is between 80-95%.

Having the correct SQL Trace Flags are also equally important, you could read about them again on the PFE blog. We are running 1117, 1118, 1224, 2371 and 4199. Also it is recommended to use the dataAreaIdLiteral AX setting if you have a multi-company environment, to avoid parameter sniffing.

(more…)

By DAXRunBase|2017-09-12T07:46:44+02:00May 11th, 2016|Categories: AX 2012|Tags: AX 2012, compression, database, maintenance, performance, SQL, TempDB, trace flags|1 Comment

Modified objects between two AX 2012 databases

In case if you were wondering what are the modified objects between two AX 2012 databases (renamed, or deleted entries based on OriginId), we could use the Linked Server feature of Microsoft SQL Server to find out.

Our scenario was an AX 2012 RTM Feature Pack installation that is being upgraded to R3 version, and we wanted to find out which objects have been renamed or removed (either DEL_ prefix, or complete deletion) between the two instances with my colleague, Peter Prokopecz.

The solution was to open SSMS and under Server Objects > Linked Servers add a connection for the other SQL instance, then we could use the queries below to find out which are the modified objects between two AX 2012 databases of any versions.

(more…)

By DAXRunBase|2016-03-14T11:00:11+01:00March 14th, 2016|Categories: AX 2012|Tags: AX 2012, compare, database, SQL|1 Comment