Dynamics AX Infrastructure and Server

Synchronization enhancements in Dynamics Ax 2009

This post covers the Synchronization functionality in Dynamics Ax 2009 and some of the enhancements that were made from the previous release.

Check Synchronize feature in Dynamics AX 2009

You may have noticed the changes when Synchronizing during Upgrade or maybe you have seen a Synchronize database form when making a change to an AOT table that would permanently lose data (making the size of a field smaller for example). The fact is that the Synchronize feature has received an interesting face lift for Ax 2009.

If you are familiar with Ax Synchronization you know that under the covers it is just looking for differences between the AOT and the Database and then updating the Database to make it look like the AOT. First it will look for tables that need to be dropped from the Database, then tables that need to be created, altered or modified and finally synchronize will fix the views.

There are two important features that we wanted to add to the Check\Synchronize feature in the Ax 2009 release, we wanted to make sure that you where notified when a change in the AOT resulted in irretrievable database changes and we wanted to give you enough information about the changes that you could bypass the synchronization engine in Dynamics Ax 2009 completely. Fortunately, we were able to satisfy both requirements with a very simple design decision.

In Dynamics Ax 2009 when you run Check Synchronize we are actually doing two full Synchronization passes. During the first pass we do not make any changes to the database, but we do generate a complete record of the Sql statements that we would have used if it had been the final pass. After the first full pass we have added a Synchronize Database form that gives you a complete report of the changes. Once you select Continue on the Synchronize database form we will do a second full pass and this time we will update the database. Note that we do not use the information from the first pass in the second, they are independent.

Synchronize Database form:

Between the two full passes we are now showing a Synchronize database form with all of the information on the changes that may impact the database.

Administrator->Periodic->SQL administration->Table actions->Check/Synchronize form

There is another version of this form that pops up when you are making a change to the AOT that results in permanent data loss in the database. For example if you modify the size of a Table String field from 10 characters to 2 you will get the following form pop up when you save.

As you  can see this is really the same form but with the Export DDL  button removed. In both cases looking at the Warnings you will see a message indicating what the Synchronization problem will be if you continue, in this case it reads: “The 'Field1' field string length decreased from 10 to 2 which could result in data truncation.”

Export DDL.

The export DDL button allows you to save to a file the complete set of SQL statements that Synchronize would have used to update the database to make it look like the AOT. As you can imagine this is a great help for debugging problems but it also allows you to bypass the Dynamics AX synchronization engine. Please note the warning that you get when clicking this button:

 The file that is generated uses comment tags to separate the different steps, this makes it a little more readable. In this particular example the interesting part of the file looks like this:

/* table drops */

/* table create/alter/modify */

/* block */

CREATE TABLE "DBO".X6188X  (FIELD1 NVARCHAR(2) NOT NULL DEFAULT '' ,DATAAREAID NVARCHAR(4) NOT NULL DEFAULT 'dat' ,RECVERSION INT NOT NULL DEFAULT 1 ,RECID BIGINT NOT NULL CHECK (RECID <> 0) )

INSERT INTO X6188X SELECT {fn SUBSTRING(FIELD1,1,2)},DATAAREAID,RECVERSION,RECID FROM TABLE1

DROP TABLE TABLE1

EXECUTE SP_RENAME X6188X, TABLE1

We have changed the size of TABLE1 Field1 from 10 to 2 in the AOT, the synchronization engine is going to update the Database in the following manner:

·         create a dummy table DBO.X6188X that has the same schema as TABLE1 _except_ with a field size of NVARCHAR(2) instead of NVARCHAR(10)

·         Copy all of the data from TABLE1 to X6188X, this will use the Database truncation algorithm to move all of the data from TABLE1 Field1 size 10 to X6188X Field1 size 2.

·         Delete Table1

·         Rename X6188X to Table1

·         (not shown) update the SQLDICTIONARY table by dropping all references to TABLE1 and recreating all data on the new TABLE1

Bypassing the AOS Synchronization engine

The above scheme allows partly bypassing some of the synchronization steps and this could be a very useful scenario during Upgrade where we use Check Synchronize and the Synchronization time is critical.

There is nothing stopping you from executing the statements in the exported file directly (maybe using multiple client machines in parallel where possible?) or to modify the synchronization steps to improve performance (move the inserts to the SQLDICTIONARY table to BCP for example). As long as all of the statements generated are correctly executed you will have synchronized the database without relying on the AOT.  Because the second full pass is independent of the first pass, when you click Continue the Synchronization engine will realize it no longer has to do any database updates and complete very fast.

Turning off the Synchronize Database form

It is possible that the Synchronize Database form is now showing up unexpectedly during some of your scripts, if so you can programmatically set it on or off using the SysSqlSync ShowSysSqlSync global cache setting, here is a job that will turn off the form and automatically synchronize:

static void ShowSysSqlSync(Args _args)

{

    SysGlobalCache gc;

    str owner = 'SysSqlSync';

    str key   = 'ShowSysSqlSync';

    boolean showEnabled = false; //set to true to enable.

    ;

    gc = appl.globalCache();

    gc.set(owner, key, showEnabled);

    info(strfmt('Show SqlSync: %1', gc.get(owner, key))); 

}

Hope this was helpful,  and let us know if you would like us to cover more in this feature area.

Angel Saenz-Badillos

Troubleshooting orphaned sessions and SPIDS in Dynamics Ax

After my last post was published I got some questions on how orphaned SQL processes (SPIDs) are cleaned up in Ax when an AOS or client crashes. A simple answer to that would be that the behavior is different depending on what crashed and what that process was doing at that instant of time.

Let’s take about the client side first. If a client crashes or is killed due to unresponsiveness the server will terminate the session of the client on the server after a certain interval when it does get a ping from the client.  In Ax 4.0 and in Dynamics Ax 2009 each client sends a ping request to the server periodically indicating that it is alive.  The server terminates client sessions that do not send the ping request within a predetermined time interval of 90 seconds.  If an AOS kills a client session you will see an error similar to the following message in the AOS event viewer.

Object Server 02: No ping from 3. Terminating the session.

Sometimes you may see the following warning in the event viewer following the above error:

Object Server 01: RPC error: Client provided an invalid session ID 3

The second event happens when a client connects to a server that was previously terminated by the server. In this case the server has removed this session from its memory but later on the client sent an RPC request to the server but the server will flag this as invalid session.

Now coming back to the database SPID, if this client was connected to the database the connection will be closed but if this client’s SQL connection is blocked behind some other connection then this SQL connection will not be closed as the AOS cannot close the connection in the database. 

For the server depending on the type of the crash the outcome is something similar. If a session on the server is terminated due to some error on the server, the server will close any open SQL connections unless those are blocked in the database. If there is an access violation leading to a crash in the server which causes the process to terminate then all the SQL connections from that process will close.

So in most cases you will not have orphaned processes (SPIDS) in the database. The SPIDS might exist only because they are blocked behind some other SPID and the AOS could not clean them up successfully in the database. These SPIDS will be cleaned up when the blocking SPID in the database finishes its current actions. Because of the connection pooling that is implemented inside the AOS server we do not recommend terminating SPIDS manually in the database while the AOSs' are still running.

I hope you found this information useful. For the past couple of posts I have concentrated mainly on session management and troubleshooting session in the AOS. My upcoming posts are going to be slightly different as I am going to focus on some X++ developer oriented topics. I will start with some posts on the Query framework in Ax and we will also have posts by some other folks working in the Ax platform team.

Please let us know if you would like us to cover a specific topic.

Thank You,

Amar Nalla

Troubleshooting blocked SPIDS in AOS

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.

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.

 Thank you,

Amar Nalla 

What’s new in Set based operations in Dynamics Ax 2009

In this blog post we will provide some details on some of the enhancements that were added to the various set based operations in Dynamics Ax 2009.

X++ language contains three operators for set based data manipulation:

1. INSERT_RECORDSET

2. UPDATE_RECORDSET

3. DELETE_FROM

These operators have been significantly enhanced in Dynamics Ax 2009. This blog entry will focus on the new features in these operators.

What’s new in Ax 2009

UPDATE_RECORDSET supports inner/outer join

In previous version of Dynamics Ax, UPDATE_RECORDSET supported single table set based operations as well as exists and not exists join.

In Ax 2009, UPDATE_RECORDSET has been enhanced to support inner and outer joins. The following is an example illustrating the enhancement.

static void UpdateTotals(Args _args)

{

    NewTaxes taxTable;

    Orders ordersTable;

    ;

    ttsbegin;

    UPDATE_RECORDSET ordersTable 

    SETTING Total = ordersTable.Total + ( ordersTable.Total * ( taxTable.TaxPercent / 100 ) )

    JOIN taxTable

    WHERE ordersTable.CountryId == taxTable.CountryId;

    ttscommit;

}

The above example updates the grand total in the orders table with grand total plus tax amount by joining to the tax table on the country code.

INSERT_RECORDSET supports literals

Literal support for INSERT_RECORDSET is one of the most requested features from our customers. This feature comes in handy especially in the case of upgrade. The following is an example illustrating the enhancements.

static void UpgradeCustomersAndVendors(Args _args)

{

    Cust customers;

    Vend vendors;

    Person personTable;

    TypeOfPerson literal;

    ;

    literal = TypeOfPerson::Customer;

    ttsbegin;

    INSERT_RECORDSET personTable ( Name, Type )

    SELECT Name, literal FROM customers ;

    ttscommit;

    literal = TypeOfPerson::Vendor;

    ttsbegin;

    INSERT_RECORDSET personTable ( Name, Type )

    SELECT Name, literal FROM Vendors ;

    ttscommit;

}

The above example inserts data into the person table from customer table and vendor table and stamps each record with a literal value of person type.

RowCount

The application can now get the row count of all the set based operations. The following is an extension of the above UPDATE_RECORDSET example.

static void UpdateTotals(Args _args)

{

    NewTaxes taxTable;

    Orders ordersTable;

    ;

    ttsbegin;

    UPDATE_RECORDSET ordersTable

    SETTING Total = ordersTable.Total + ( ordersTable.Total * ( taxTable.TaxPercent / 100 ) )

    JOIN taxTable

    WHERE ordersTable.CountryId == taxTable.CountryId;

    if ( ordersTable.RowCount() > 0 )

    {

        info ( strfmt (' Number of rows updated %1', ordersTable.RowCount() ) );

    }

    else

    {

        info ( 'Tax rates not found. Please populated valid tax rates');

    }

    ttscommit;

}

When does set based operation turn into row by row operation

In Ax 4, the following will cause the set based operation to downgrade to row by row operation:

1. Table is entire-table cached.

2. Delete/Update/Insert methods, aosValidate* methods are overridden on the target table.

3. Alerts have been setup to be triggered.

4. Database log has been setup.

This list has grown in Ax 2009. The list now includes:

1. Record Level security being enabled. Details are presented below in a separate section.

2. Any of the tables participating in the joins or target or source table is temporary/configuration key being disabled.

3. When assignment operator for INSERT_RECORDSET or UPDATE_RECORDSET contains date time manipulation operations such as adding or subtracting from date time column.

4. AosValidate* methods are overridden on the joined tables. AosValidation has been extended to include joined tables since UPDATE_RECORDSET now supports complex joins.

Set based operations and Record Level Security

Record Level Security (RLS) is appended as a where predicate to all the SELECT statements if RLS is enabled for a given user group, company and the table.

If RLS is not enabled through code, RLS is not applied.

<table>. recordLevelSecurity(<true/false>)

For DELETE_FROM, RLS is always applied as a where predicate. So, enabling RLS does not downgraded DELETE_FROM operation.

For UPDATE_RECORDSET, if RLS is applied on source table or join tables, the operation is not downgraded to row by row operation. But if RLS is enabled on target table, then the set based operation is downgraded to row by row operation so that security can be validated for each row being inserted or updated.

Thanks,

Dynamics Ax 4.0 Session Types

In this blog post I will provide details on the management of the various user sessions inside the Dynamics Ax server (AOS). I will use the Online users form as a reference to provide context to the material provided below. I hope to provide answers to some of your questions regarding various session types with my post.

Background and Basics

In versions prior to 4.0, Dynamics Ax used to persist the session information in a file which had a “.udb “extension. This file was stored along with the other application files and it had data about the current servers and rich clients (Ax32.exe) currently running in a deployment. Due to scalability and stability concerns we changed the architecture significantly in 4.0 and moved this information to the backend database.

In the 4.0 release the following database components are responsible for persisting and managing the session information in the database

·         SysServerSessions table – This table is used to manage the AOS instances. Each running AOS in an installation has a row in this table with the STATUS column equal to 1.

·         SysClientSessions table – This table is used to manage the various client sessions connected to different AOS servers. Each client session has a row in this table with the STATUS column equal to 1.

·         CREATESERVERSESSIONS stored procedure – This stored procedure is used to populate the SysServerSessions table. This stored procedure is invoked during the startup of an AOS server.

·         CREATEUSERSESSIONS stored procedure – This stored procedure is used to populate the SysClientSessions table. This stored procedure is invoked each time a client is launched.

The stored procedures main purpose is to provide a unique ID which is used as an identifier for each session in the AOS. They are also used to enforce the license checks which limit the number of concurrent active sessions based on the numbers specified in the license file.

You don’t need to access the database to get a glimpse of the session data as most of this information is exposed via the Online users form that is available to administrators. You can also use the xSession API to programmatically access the session related information.

Online Users Form – Server Instances

You can use this tab in the form to get information on both the current AOS instances and client sessions. Let’s first take a quick look at the Server Instances tab.

Welcome Post

Welcome to the Dynamics AX Infrastructure and Server team blog.  We will be discussing the infrastructure and development focused topics in this blog by the team including our development, test, and program management team.  We welcome your feedback to make this a productive site!

Best regards,

-Guru

Raghavendran Gururajan

Senior PM, Dynamics AX Server Platform