Dynamics AX and SQL completely out of sync

(0) Share

Report

Posted on by Smartus

620

Hi everyone,

I have an AX 2009 environment which is completely out of sync with SQL.

The customer where the server is hosted, denied synchronization of AX for about 2 to 3 years.
They have a database trigger which stops AX from making changes to the database (I know it's not the way of working).

We have come to a situation where we are going to make everything back in sync, but it's not that easy.
They have made indexes on the database which they want in AX too, but there are many of them so a script would come in handy.

What I managed so far is, shortly noted, the following:

Run a SQL query in AX which is going to give me all tables in SQL with the indexes, fields and types.
I've seen in the resultset that some indexes have names like this: "_dta_index_DA_CUSTTABLE_75" which don't really has the naming convention of AX.
A script which makes an index by using treenode
A script which is going to loop over all tables in the AOT except from Views, Maps, Temp etc

What I wanted to do is a script which loops over the resultSet of my query, looks whether it exists in AX and if not, create the index.

The ideal situation is that I can synchronize afterwards without AX dropping indexes and recreating it all.
Because currently when I synchronize a table, which has indexes in SQL that the customer doesn't want to loose, AX drops everything and only creates the indexes that were in the AOT.

So if anyone has an idea how I should do this, I would be very greatfull.

Kind regards,
Vincent

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (2)

Acer 75 on at

Like (0)

Report

RE: Dynamics AX and SQL completely out of sync

Ohh and one last thing.

If you have problems with the synchronization of a lot of indexes.

Make sure you pop the SQL Server into MAXDOP = 0 mode prior to doing this. Don't do this while production is live.

Was this reply helpful? Yes No
Acer 75 on at

Like (0)

Report

RE: Dynamics AX and SQL completely out of sync

I know this is an old question but I have a follow-up for those of us who are using AX2009

As some here said AX “owns” the database and dba’s should not be adding indexes directly on the database.

AX will drop these next time it does a synchronization.

Don’t just add indexes based on the suggestions from the SQL Servers DMVs

Never add indexes where DataAreaID is NOT the first field unless you really understand what this causes.

(Statistics will be way better for this index compared to the rest and make the SQL Server use this instead of a better one. SQL Server is right 96% of the times – it’s pretty clever)

If you need indexes: Add them in the AOT. If you need them with included_columns. Add them in the AOT and recreate them on the SQL Server afterwards with the included columns.

The synchronization won’t see this and will leave the included columns on there.

Another thing I often hear is about performance problems in general:

If you suffer from this make sure – no one is accessing the database directly but AX

If they are – make damn sure they know what a query plan is and how its read and DON’T let them make “datawarehouse-like“ queries.. Queries running is AX should run in less than 1 second.

Also make sure you have fast storage and memory enough on the SQL Server to support your business queries. If your database is 2 Tb – make sure to have at least 200 Gb RAM or more. It’s cheaper to buy more RAM than to have users sit and do nothing.

A few other things you can do to improve performance.

Go here and download the first responder kit: www.brentozar.com/.../

Once that is done run:

sp_blitzFirst @SinceStartup = 1, @outputtype = 'top10'

This will give you the main reason for your system running slow

In case it’s CX_xxxxx or SOS_Scheduler_Yield run: sp_blitzCache @SortOrder = 'cpu'

In case it’s CX_xxxxx or PAGEIOLATCH: run: sp_blitzCache @SortOrder = 'reads'

In case it’s WRITELOG run: sp_blitzCache @SortOrder = 'writes'

This will give a list of queries to fix.

Want to find the queries the users are complaining the most about when “AX is slow”

Try sp_blitzCache @SortOrder = 'avg duration'

Just my 2 cents.

I still believe AX 2009 is the best ERP system out there.

AX 2012 became overly complicated in both business logic but also on the technical side. Having the code in the database is also pretty weird. Who came up with that? – 365 is waaay too expensive

Was this reply helpful? Yes No
rudra 6,534 on at

Like (0)

Report

RE: Dynamics AX and SQL completely out of sync

Hi Vincent,

Above query is to find the missing indexes in SQL database, accordingly we can create those missing indexes in AOT for better performance, this is the way we should investigate on index and fix it.

But i have no idea on how to find the missing indexes though AOT, i will investigate further and let u know if i found way.

Was this reply helpful? Yes No
Smartus 620 on at

Like (0)

Report

RE: Dynamics AX and SQL completely out of sync

Hi Rudra,

I am wondering, how can this query identify what index in SQL isn't available in the AOT?

It's not that SQL can look into the AOT and in the SQL dictionary are no indexes, or am I wrong?

I ran the query and it shows a few records with Table SQLDICTIONARY.

Regards,

Vincent

Was this reply helpful? Yes No
Suggested answer

rudra 6,534 on at

Like (0)

Report

RE: Dynamics AX and SQL completely out of sync

Hi Vincent,

You can find the missing indexes count by using the below query

SELECT str(user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ),18,2) AS [index_advantage] ,

dbmigs.last_user_seek ,

dbmid.[statement] AS [Database.Schema.Table] ,

dbmid.equality_columns ,

dbmid.inequality_columns ,

dbmid.included_columns ,

dbmigs.unique_compiles ,

dbmigs.user_seeks ,

dbmigs.avg_total_user_cost ,

dbmigs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )

INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK )

ON dbmigs.group_handle = dbmig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK )

ON dbmig.index_handle = dbmid.index_handle

Inner join sys.sysdatabases as sysdb with ( NOLOCK )

ON sysdb.dbid= dbmid.database_id

where sysdb.name = 'Your Database Name'

ORDER BY index_advantage desc

Was this reply helpful? Yes No
Smartus 620 on at

Like (0)

Report

RE: Dynamics AX and SQL completely out of sync

Hi Tommy,

I know, normally AX is in charge of what happens with the database, which is also what we said ...

So with the Red-Gate tool I would compare the out-of-sync database with a database that is sync'ed by AX. And it will then say: this index exists in the out-of-sync database but not in the sync'ed one? Kind of a text comparison tool but then for database schemas?

Thanks for this tool, very usefull stuff.

Regards,

Vincent

Was this reply helpful? Yes No
Smartus 620 on at

Like (0)

Report

RE: Dynamics AX and SQL completely out of sync

Hi Denis and Rudra,

Thanks for your answers.

I didn't know about the DTA indexes that they were created by the Database Engine Tuning Adviser, so thanks for that.

I already tried to create such a report which indicates whether I am missing indexes (or fields in the index) or not. But I didn't managed to come up with a full functional report generation of this.

These are currently the cases that I know when AX creates an index (or fields in the index):

- First the normal index creation through the AOT

- The CreateRecIDIndex property

- If no index or property was set, AX automatically creates a recid index

- If the index is enable or not

- If the index' configuration key is enabled

- If you don't have an index that is unique, it takes the smallest index and adds the recid into the fields of the index (only in SQL)

I think these are all the available options.

So I need to check my resultset with indexes and its fields against these conditions to see if they need to exist or not.

So I am going to continue with the generation of a report which indicates all missing indexes.

I will get back if I have some sort of solution.

Thanks again,

Vincent

Was this reply helpful? Yes No
Verified answer

Tommy Skaue Moderator on at

Like (1)

Report

RE: Dynamics AX and SQL completely out of sync

Sounds more like the DBA is out of sync with how AX works. ;-)

In Dynamics AX, the ERP solution *owns* the schema, not the DBA.

Perhaps the customer (or DBA) is willing to save some time by purchasing this tool from Red-Gate:

www.red-gate.com/.../sql-compare

There is a 14 day trial and one of the features is "Create reports in HTML or Excel detailing database differences".

That way you can compare a backup of invalid schema with a synced and valid schema and throw a developer on the task to create missing indexes. Unless you manage to automate that part and create a x++ script that creates the indexes for you.

Was this reply helpful? Yes No
Suggested answer

rudra 6,534 on at

Like (0)

Report

RE: Dynamics AX and SQL completely out of sync

Even i faced this kind of scenario once but atlast i just manually created all the missing indexes in AX Tables through AOT by comparing the SQL table indexes.

As Denis you can find and delete all the unused indexes and also investigate for missing indexes as well.

If indexes were created in SQL tables then when we sync the database from AX then it will drop all the indexes created in SQL tables, its an usual behaviour AX.

For Index sync please the blog we discussed earlier, Re-index from AX or SQL

Was this reply helpful? Yes No
Verified answer

Denis Macchinetti 16,444 on at

Like (0)

Report

RE: Dynamics AX and SQL completely out of sync

Hi Vincent

"_dta_index" are indexes created with the "Database Engine Tuning Adviser" Sql tool.

www.sqlservercentral.com/.../dta-and-hypothetical-indexes

www.mssqltips.com/.../sql-server-database-engine-tuning-advisor-for-performance-tuning

I'm agree with you about the script.

However, I suggest you to create firstly a report with the SQL Index not present in AX and understand if really are needed.

Also, review the Indexes and check if there are unused Indexes using a Sql script, like www.mssqltips.com/.../querying-sql-server-index-statistics

Was this reply helpful? Yes No