In this blog post I will provide some details on how the AOS server manages it SQL connections and some tips on troubleshooting blocked connections in the database. All of my description is based on the SQL Server backend but similar techniques are applicable for Oracle also, only the DB tools are different. Let’s first start with quick primer on SQL connection management inside Ax.
When connecting to SQL Server, Dynamics uses ODBC APIs to connect to the database. There is a cost in establishing the connection and logging in the user , hence the AOS uses a connection caching mechanism that allows recycling of connections when not in use. Internally, the AOS keeps track of 3 types of connections which are:
· Regular application connection – All application code use this connection.
· RECID connection – There is a dedicated connection to the SystemSequences table. This is used by the RECID allocator inside the AOS.
· Read Only/SysLastValue connection – This is a shared connection that is used for SysLastValue and read only queries.
Note, that the AOS internally distinguishes between these connection types but externally when looking in the database or in the application it is not possible to differentiate between them easily. In fact if a particular connection is cached and then reused again it might end up being reused as a different type of connection than its original type although there are some restrictions on the types when reusing the connections
The connection cache is implemented as a FIFO queue where the connection is stored while it is not being used. By not being in use I mean that there is no active SQL statement from the AOS to the database backend using that connection. The AOS also keeps track of the state of the connections before putting them in the cache for reuse. If the connection is bad due to connectivity errors to the database or any other errors that would cause errors in the future, then that connection will not be reused and it will deleted which will close the connection to the database.
Now let’s see how to look at some of this information from outside the AOS. There are a variety of ways to look at the active connections in a database server. For our discussion we will consider two alternatives, one using the tools provided by SQL Server and the other from within Ax itself. Let’s first look at the option or using SQL Server. My favorite tool for a quick overview of connections is the “Activity Monitor” tool that is available in the SQL Server Management Studio. This tool can be found under the Management folder in the object explorer. There are lots of columns for each active connection but the Process ID (SPID) is the one of interest to us. When you first start an AOS server you will see 2 SPIDS which have “Microsoft Dynamics Ax” under the Application column. These are the connections which are currently opened by the AOS and they could be either active or an inactive connection in the cache.
SQL Server 2005
SQL Server 2008 R2
From inside Ax, you can see some of the above information using the “Online Users Form”. The Client Sessions tab has a column called SPIDs that shows the connections that are currently active on a particular session. I should make you aware that you don’t get to see the whole picture when using this form. To start with you only see the active connections that are assigned to a session. This explains why sometimes you will not see any value in the SPIDs columns for some of the users. Basically it means that the user does not have any active connection to the database at that instant of time. Another limitation is that the SPID will be populated only if the client where you are viewing the form is connected to the same AOS as the other session. So if you have multiple AOS’ in a cluster you will not see all the active SPIDs in the system by opening a single instance of the online users form. You will have to open the form in separate clients each connected to a different AOS.
Now let’s look at how we can use some of the above information to troubleshoot scenarios where sessions are blocked in the database and how to get rectify the situation. You can again start with the Activity Monitor to look lookup the SPID of the blocked session. The activity monitor also gives you the SPID that it blocking any other SPID and also the DB resource that is causing the contention. You can then use the online users form to determine the owner of the session in AX for the blocked and blocking SPIDs. The form provides an ability to terminate an existing session in Ax. But you have to be careful in terminating the correct session. If you try to terminate the blocked session you will see that the online users form changes the status to “Ending – Blocked”. This state indicates that the AOS tried to terminate the session but it was not successful since this session has some open resources and it cannot be safely terminated. One option to solve this situation is to terminate the session that is blocking the other session(s). If the termination is successful, the database connection is closed and this will free up the DB resources for the blocked session(s).
In the online users form when you try to terminate a session sometimes you might see the status change to “Ending – Waiting for AOS”. This happens when you terminate a session that is not in the same AOS as the client that sent the terminate request. In this case the request is placed to the other AOS and it monitors for terminated session in the background and will it terminate it eventually when it processes the request.
In addition to the above techniques there are alternate techniques to troubleshoot blocking in the SQL Server database. You can query the database provided Dynamics Management Views (DMVs) to get more details on the resources that are being consumed or blocked.
I hope you found the above information useful and interesting. Let us know if you would like more information in related areas on how the AOS works and options to manage it. We would also like to hear any suggestions for improvements in any of the areas covered in this topic.
No comments:
Post a Comment