Working with an e-commerce setup that involves Microsoft Dynamics AX involves different set of challenges that with a stand-alone ecommerce website. The dependencies the AX architecture and “how AX works” are fundamental for a successful project.
BACKGROUND
The various versions of Sitecore Commerce use different components for communication with Microsoft Dynamics AX, also depending on if it for Dynamics 365 or “on-premise”. This list is compiled from on-premise projects.
Communication between Sitecore Commerce and Dynamics AX is handled by a Transaction Service (TS). The TS is a webservice/SOAP layer on top of Microsoft Dynamics business logic implemented in a bunch of DLL from Microsoft. It’s much like a POS but for the ecommerce site. The TS exposes all kind of features that you would expect from a POS like add-to-cart, create-account, create-order etc.
AX is job/batch-oriented system. It’s architected to be able to work with even bad, fall-outs or even no connection to the central Dynamics AX servers(HQ). The individual stores (physical) uses “channel databases” (MS SQL Database) with a specific schema. The ecommerce website uses an online channel database – multiple depending on setup. So, in a setup in AX with brick-and-mortar stores, there will be a lot of databases. AX uses lots of jobs to publish configurations and setup to the databases, and to pull back orders, update account information etc. In a lot of ways, it’s not a real-time system where you can expect data to “always be there”. For functions like, create-account, AX exposes a RTS, a Real-Time-Service. That’s is also another webservice. From a Sitecore Commerce perspective, it’s handled by the TS how it’s communicating with AX, if it’s SQL statements, RTS calls to central AX or something else.
HINT 1 - THE TRANSACTION SERVICE COMMUNICATES PRIMARILY WITH ONLINE CHANNEL DATABASES
It’s a general understanding that the TS, that uses the Microsoft Dynamics DLLs performs RTS calls to AX. Truths is that’s not. Most of the communication is pure SQL being executed from the MS DLLs and to the selected SQL Database (online channel database).
Only a few functions rely on RTS calls. Calls like, create-account, create-order, wish-list.
Why is this important to understand?
In a setup with multiple online channel databases, configuration and setup in the different channel databases is different. Basic data might not be the same. So, functions on the website may work fine on one channel database and not on the other.
AX setups can be extremely customized, and the Microsoft Dynamics DLLs are built from a set of expected things to be in place. That’s not always the case. Example could be that AX accounts automatically is distributed to all online channel databases. If that’s not the case in the setup, various functions will not work. E.g. for a logged in user add-to-cart will not work of the account doesn’t exist in the selected online channel database.
The biggest pain point in this, is that the Microsoft Dynamics DLLs are closed (except from two DLLs), meaning that identification of a given issue requires a bunch of tools and different skills. More on that later.
HINT 2 - PERFORMANCE ON THE WEBSITE DEPENDS ON THE AX SETUP
If your storefront/web-site is not architected with a base strategy to only use the TS/AX when really needed, the performance on the web site will be dependent the AX setup. This a where a typical clash between online/offline and 24×7 and brick-and-mortar understanding comes in to play. E.g. The function add-to-cart works purely on SQL between the TS/MS DLLs and the channel databases. If your website presents a basket on all pages on your site, you need to make sure that it’s handled to right way. Sitecore Commerce offers a way to cache the cart, avoiding call to the TS on every page load, but we have seen implementation where that wasn’t used.
So, be aware, and monitor transactions, performance and traffic from the TS to the channel databases.
HINT 3 - AX CONFIGURATION CHANGES ARE NOT EASY TO TRACK
A lot of configurations are naturally stored in the various online channel databases. Changes configured in AX and then gets distributed using a number jobs. It’s more common that standard that these online channel databases are customized through the various implementations – although the logic that works on these are from standard Microsoft DLL’s. This sometimes leads to “surprises” – surprises of the type “it worked 5 minutes ago and now it doesn’t, and we didn’t change any code.
Recommendation is to rely on extensive monitoring and detailed logging:
- Logging will help you identify the root causes
- Monitoring will alert you on changes on the AX side
Through our project we have gained so experience on what to look out for on the AX side. Based on these experiences we have built monitoring jobs that performs checks in the various online channel DBs for faster identification. There a lot of examples but name a few:
Check the RTS endpoints
- SQL TABLE ax.RETAILTRANSACTIONSERVICEPROFILE lists the endpoints, and
- DEFAULTTRANSACTIONSERVICEPROFILEVIEW shows the default profile.
Check that current selected Channel DB includes the corresponding OU (Organizational Unit)
- SELECT CHANNELID, OPERATINGUNITNUMBER, CONNECTIONSTRING, ISPUBLISHED, ISLOCAL, SERVER, [DATABASE] as ‘DATABASE’ FROM [crt].STORAGELOOKUPVIEW
Over time, on the specific projects, recommendation is to add monitoring jobs/tests based on the errors that occurs. So over time your monitoring should be able to catch any changes that makes on the online channel databases break – and over time eliminate that it happens at all.
TOOL: SQL Profiler
No surprise! AX is primarily build on MS SQL. Tables, Views, Stored Procedures, User Defined Functions – so a “classic” SQL Profiler Trace on very targeted actions expose what is really going on. And it often reveals what is wrong, missing or misconfigured in the online channel databases.
If you’re not strong on SQL Profiling, there’s a lot of good resources – to name a few:
TOOL: POSTMAN
Postman can be used for a lot of web API testing and development. It’s fast and easy, and since the architecture of Sitecore commerce powered by Dynamics AX includes a “transaction service” that basically Is a SOAP API on top of Microsoft Dynamics AX DLLs – we can use Postman to simulate the requests coming from connector/storefront running on our Sitecore site. This make is easy and fast to test features, and as a debugging or monitoring tool.
TOOL: TRANSACTION LOGGING
At Alpha Solutions we have built a small simple tool/component to log all traffic going in/out/through the transaction service. Using a database for the logging and a simple page for browser and filtering the log makes a big difference in identifying issues and get them resolved. We track incoming request as well as response, track execution time as well is an error was returned.
TOOL: DECOMPILE AND DEBUG
There’s no way around it! You simply need a tool to debug into the Microsoft Dynamics code. The largest part of the DLL’s is simply code from MS that you can’t modify in any way but decompiling and debugging into that code often gives you the needed information to crack a problem. There’s a lot of good tools for that, DotPeek or .NET Reflector. We can have the best success using .NET Reflector – money well spend!
Please remember that the SDK DLLs from Microsoft are open source, so including these in your project give you at least some better insights to what’s going on in the Dynamics AX logic. Also, customizations here will be needed if you client has a channel database setup that doesn’t follow Microsoft assumptions 100%. Say if you have a large amount of brick and mortar stores and a lot of accounts in AX, you might not want all your accounts to be replicated to all channel databases – for various reasons. This would be an example where you would need to do customizations for sure as the MS logic doesn’t take this into account.
TOOL: REMOTE DEBUGGING
In some cases, remote debugging is the only way to identify an issue fast. Especially on setups where UAT and test environments is not anywhere near the production setup and configuration. Read more about that here or here.
TOOL: NET TRACING
As always, a .NET tracing can bring a lot of valuable information on any traffic going in/out through a web application.
Detailed information in this blog. Or you can simply add this to the web.config of your transaction service:
WHY NOT FIDDLER?
Fiddler is obvious right? On a setup with Sitecore Commerce powered by Dynamics AX – on premise – it’s not that easy or obvious to use fiddler for a number of reasons:
- Most of the traffic and communication from the transactions service and to “AX” is handled through direct SQL statements from within the Microsoft Dynamics DLLs. So for that – a SQL Profiler/trace is way better.
- For the Real time service calls like CreateAccount it’s hard to setup up. First, the RTS Endpoint are configured in AX and obvious often configured to use HTTPS. So, this gives some challenges for development/debugging. Not impossible, but not just easy – and having all the other tools mentioned it hasn’t been worth it.
FINALIZING NOTES
I am 100% aware that this post is very specific and directed at specific use cases. Despite that fact, I hope that that the right person finds it, and find it very useful. All these tools, the overall understand on how Dynamics AX works have drastically speeded up our time to trace down issues and get them resolved.
ROLAND VILLEMOES
CTO
