AX 2009, AX 2012, Development, Dynamics AX, Uncategorized

AX Database Maintenance

AX Databases perform better if regular Index Maintenance is done on SQL server. It is only suggested to run this during non-peak hours. A recurring job to be created to run index maintenance periodically.

I am herewith sharing statistics update strategies I read from a blog.

Statistics Update Strategies

SQL will keep statistics on indexes on how data is structured within each of those indexes in order to determine the best execution plan. These statistics look at the composition of data to determine such things as whether to seek (utilize the B-tree in the index) or scan (walk through the whole table) among many other execution options.

Within Dynamics AX, these statistics are kept up to date by the 2 options on the database

             auto_create_statistics – When column is specified in a query, statistics on that column are kept.

auto_update_statistics – When enough changes have been made to the column or index, SQL will update its statistics.

Best practice for the Dynamics AX database is to leave these ‘ON’, which is the default when installing Dynamics AX. However, you may have taken note of the text for auto_update_statistics which states “when enough changes have been made to the column”. What this means is that when a certain percentage of records have been changed, SQL will perform an update on the information that it has about the data. If a table contains a high number of rows, it can take a long time however for these statistics to be updated, which will then cause SQL to pick an execution plan that may not be the best option for the current data composition.

If you have large tables, it is recommended to run a nightly update only for those tables. We have found that transactional tables such as LEDGERTRANS and INVENTTRANS are prone to needing to be updated on a nightly basis. You have the option of turning auto_update_statistics off for those tables on a per-table basis. When doing an update statistics, it is recommended that you test how your data will be sampled. The more data that is sampled, the more accurate the statistics will be. For example, the following command will scan every record for the most accuracy

      UPDATE STATISTICS <table_name> WITH FULLSCAN

Depending on the size of the table, this can take multiple hours to complete, so you may choose to sample a certain percentage of records, at the expense of accuracy.

UPDATE STATISTICS <table_name> WITH SAMPLE 50 PERCENT

We can make no recommendations on what is the better option, each one should be tested for the time it takes, and the overall impact on execution plan performance.

SQL ships with a stored procedure called sp_updatestats. You can take advantage of this stored procedure which will automatically update statistics for those indexes and columns which have changed data.

If you are running SQL 2008 R2 SP1 or greater, you can enable this feature through Trace Flag 2371. The linked article has more explanation on this change.

Fragmentation

If you would like to know the fragmentation in indexes, run following query on the dynamics perf (if installed) database to find fragmentation details on the indexes.

 

SELECT DATABASE_NAME, 
       TABLE_NAME, 
       INDEX_NAME, 
       AVG_FRAGMENTATION_IN_PERCENT, 
       FRAGMENT_COUNT 
FROM   INDEX_STATS_CURR_VW 
ORDER  BY AVG_FRAGMENTATION_IN_PERCENT DESC

 

Alternatively,  you can run following query on the database to find the fragmentation on the indexes.

SELECT OBJECT_NAME(ps.OBJECT_ID)             AS TableName, 
       si.NAME, 
       ps.AVG_FRAGMENTATION_IN_PERCENT, 
       STATS_DATE(ss.OBJECT_ID, ss.STATS_ID) AS LastUpdatedStatistics 
FROM   sys.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(DB_NAME()), NULL, NULL, NULL, NULL) 
       ps 
       JOIN SYSINDEXES si 
         ON ps.OBJECT_ID = si.ID 
            AND ps.INDEX_ID = si.INDID 
       LEFT OUTER JOIN sys.STATS ss 
                    ON ss.OBJECT_ID = ps.OBJECT_ID 
                       AND ss.NAME = si.NAME 
WHERE  ps.AVG_FRAGMENTATION_IN_PERCENT > 10 
ORDER  BY ps.AVG_FRAGMENTATION_IN_PERCENT DESC 

I came across a very useful blog and a tool for Database maintenance. Please go through this blog for detailed helpful tool and maintenance. Thanks! to Ola Hallengren

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Download the SQL file by clicking the link below on Index Optimize, added here  as well for quick reference. Latest copy should be taken from the link above. Below link file extension is Key change that to SQL and use it. Please change the database name to your AX database name before run. And I would suggest to run this when you have proper downtime .

IndexOptimize

Examples

A. Rebuild or reorganize all indexes with fragmentation on all user databases

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

B. Rebuild or reorganize all indexes with fragmentation and update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = ‘ALL’,
@OnlyModifiedStatistics = ‘Y’

C. Update statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = ‘ALL’

D. Update modified statistics on all user databases

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = ‘ALL’,
@OnlyModifiedStatistics = ‘Y’

E. Rebuild or reorganize all indexes with fragmentation on all user databases, performing sort operations in tempdb and using all available CPUs

EXECUTE dbo.IndexOptimize @Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = ‘Y’,
@MaxDOP = 0

F. Rebuild or reorganize all indexes with fragmentation on all user databases, using the option to maintain partitioned indexes on the partition level

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PartitionLevel = ‘Y’

G. Rebuild or reorganize all indexes with fragmentation on all user databases, with a time limit so that no commands are executed after 3600 seconds

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@TimeLimit = 3600

H. Rebuild or reorganize all indexes with fragmentation on the table Production.Product in the database AdventureWorks

EXECUTE dbo.IndexOptimize
@Databases = ‘AdventureWorks’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = ‘AdventureWorks.Production.Product’

I. Rebuild or reorganize all indexes with fragmentation except indexes on the table Production.Product in the database AdventureWorks

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = ‘ALL_INDEXES, -AdventureWorks.Production.Product’

J. Rebuild or reorganize all indexes with fragmentation on all user databases and log the results to a table

EXECUTE dbo.IndexOptimize
@Databases = ‘USER_DATABASES’,
@FragmentationLow = NULL,
@FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LogToTable = ‘Y’

Advertisements
Standard
AX 2012, Calling Webservice, Development, Rest APIs, Uncategorized

Calling Rest API from AX 2012

AX 2012 supports system.Net namespace interface. This interface provides simple programming interface for many of the protocols used on the networks today. Herewith i am sharing a simple example to call a rest API asynchronously.  it is very lightweight and has hardly an impact if you are calling asynchronously.

 

static void CallRestAPISample(Args _args)
{
 System.Net.WebHeaderCollection headers ;
 System.Net.WebClient webclient;
 System.Uri uri;
 
 // Create XML msg or string message as parameter to call the Rest API. 
 str msgstring = '<?xml version="1.0"?> ';
 
 msgstring += '<LogRequest>' ;
 
 msgstring += '<ApplicationName>AX Error Tracking</ApplicationName>';
 msgstring += ' <Date>06-22-2017 12:58:22 PM</Date>';
 msgstring += '<ThreadID>0</ThreadID>';
 msgstring += '<LogLevel> <INFO>false</INFO> <DEBUG>false</DEBUG> <ERROR>true</ERROR> <FATAL>false</FATAL> <WARN>false</WARN> </LogLevel>';
 msgstring += '<LogDetails> <ExceptionMessage>Test Exception</ExceptionMessage> <Message>This is test message from AX Logging Call.</Message> </LogDetails> ';
 msgstring += '<Metadata> <Data> <Name>ENV</Name> <Value>DEV</Value> </Data> <Data> <Name>Server</Name> <Value>AX AOS 1</Value> </Data> <Data> <Name>Source</Name> <Value>DAX</Value> </Data> </Metadata>';
 msgstring += '<Email> <From>FromAddr@Fromserver.com</From> <To>toaddrr@toserver.com</To> <Subject>AX Logging Test</Subject> <Message>This is test message</Message> <HasAttachments>false</HasAttachments> <IsBodyHtml>true</IsBodyHtml> <IncludeLogDetails>true</IncludeLogDetails> </Email>';
 msgstring += '</LogRequest>';
 
 
 info(strFmt("%1",time2str(timeNow(),1,1)));
 // initilizing host URI
 uri = new System.Uri("http://LogginServiceHost/api/Log/LogRequest");// FAKE SERVICE HOST ADDRESS
 // initializing webClient
 webclient = new System.Net.WebClient();
 headers = webclient.get_Headers();
 // Set header content type
 Headers.Add(System.Net.HttpRequestHeader::ContentType, "text/xml;charset=utf-8");
 // call API asynchronously
 webclient.UploadStringAsync(uri,msgstring);
 
 info(strFmt("%1",time2str(timeNow(),1,1)));

}

Standard
AX 2009, AX 2012, Development, dialog, Dynamics AX, User parameters, without dialogable class, without runbasebatch

Quick Dialog to take user entered parameters without dilaogable class creation

Following job uses dialog class to take parameters from user and run the job

static void UserParamSample(Args _args)
{

wmslocationID     wmslocationID;

str                           palletstoRAF;

dialog          dlg = new dialog();

 dialogField     dlgPalletIDs,dlgwmslocation;

 dlgPalletIDs = dlg.addField(typeid(ItemFreeTxt),”Container number”,”Enter pallet Ids seperated by commas”);
dlgwmslocation = dlg.addField(typeid(wmslocationid),”Location”);

if(!dlg.run())

return;

else

{

palletstoRAF = dlgPalletIDs.value();

wmslocationid = dlgwmslocation.value();

//Run the Job

  }

}

Standard
AX 2012, Development

Dynamics AX 2012 connect to Visual studio online , Team Foundation Server

With Visual Studio Online, Now you can have your project data on cloud and accessible anywhere. if you are a MSDN subcriber then you can start on VS online today by clicking here. Else you start a trial here .

How to connect to visual Studio online :

With a simplistic scenario when no Branching is setup below are the steps to follow.

1. Create a Team project in your VS online

At the home page of your VS online create new team project by choosing a suitable process template.

Create a Team Project

Create a Team Project

2. Add members to the project using manage members

Add members

3.  Go to AX client and configure TFS as version control for AX

Enable Version control in AX.

Enable TFS in AX.

Enable TFS in AX.

Setup VS online site , make sure that you follow the same pattern with port number etc. else it doesn’t get connected.

Setup TFS online

Setup TFS online

Standard
AX 2009, AX 2012, Development, Dynamics AX, Uncategorized

How to send a file to the printer in dynamics AX

How to send a file to the printer in dynamics AX

Following Job demonstrates on how to send a file from the file system to the locally connected printer( printer physically can be network or local but it should be added to the client and its name should be visible in the control panel–> devices and printers)

static void sendToPrinter(Args _args)
{
PrintJobSettings printJobSettings = new PrintJobSettings();
Dialog dialog = new Dialog();
DialogField dialogFileName;
str CoreLabelExe;
str CoreLabelParm;
;
// initiallize printer with the printer name. This initiallization automatically
// gets dll file details and port details.
printJobSettings = new PrintJobSettings();
printJobSettings.SetTarget(PrintMedium::Printer);
printJobSettings.deviceName(‘Microsoft XPS Document Writer’);
dialogFilename = dialog.addField(typeid(FilenameOpen));
if (dialog.run())
{
// finding executable DLL
CoreLabelExe = WinAPI::findExecutable(dialogFileName.value());
// Create Shell command parameter
CoreLabelParm = strFmt(‘ /PT “%1” “%2” “%3” “%4″‘,
dialogFileName.value(),
printJobSettings.printerPrinterName(),
printJobSettings.printerDriverName(),
printJobSettings.printerPortName());
// run dll on the shel with parameter
winAPI::shellExecute(CoreLabelExe, CoreLabelParm);
}
}

Standard
AX 2012, Development, Dynamics AX

Creating File Using Text I/O class

you can use Text I/O class to create a flat file in file system. TextIO supports Unicode characters.

code Class_LXKFormattedTextFile (1).

Download above class xpo and then change the file extension from “.KEY” to “.XPO”

Class Declaration

Class Declaration

Init File

Init File

Run

Run

init Device

init Device

Format Text

Format Text

Create Data

Create Data

Can create file

Can create file

Creating File Using Text I/O class

Aside
AX 2012, Uncategorized, What is new

Dynamics AX 2012 R2 has got an excellent feature, ‘Data Partitions”. It enables Business data isolation but sharing the same business application.

Benefits

  • If an organization runs multiple business entities that shares the same business application, can Share IT infrastructure across independent businesses.
  • While sharing the IT infra, Organization achieves strict data isolation across businesses.
  • This reduces Total cost of ownership (TCO) for managing IT infrastructure.

Data partition basics

Below are some basic features of data partitions. The implementor should understand these before designing/preparing for data partitions.

Understanding Partion and Legal entitiy

Understanding Partition and Legal entity

  • Metadata (Business application)  is common across all partitions.
  • Enabling/disabling configuration key affects all partitions.
  • Named user licenses are calculated per principal, i.e. AD user, regardless of the number of partitions they have access to.
  • A default partition named Initial is always created for each AX instance.
  • DAT company exists in every partition. It is automatically created when a new partition is created.
  • A company with same name can exist in multiple partitions. Intercompany doesn’t work across partitions
  • AX users are defined per partition.
    • With in a partition a principal can be associated with one AX user.
    • Each user has a “default” partition associated with them, however a user can be authorized to access data in more than one partition.
    • AX user IDs other than admin and Guest are unique across partitions.
    • The principal that creates a new partition automatically becomes the admin user in the partition
    • Each partition contains at least one company or legal entity. A legal entity occurs in only one partition. When you create a legal entity, the system assigns it to the current partition. The legal entity can never be moved to another partition. However, its data can be exported from the partition and then imported to another company in another partition.
    Data Partition Structure

    Data Partition Structure

    • User to role assignment is per partition.
    • Organizations (e.g. departments, cost centers, etc.)  and hierarchies are defined within a partition.

    Data Abstraction Levels

    • User-Role-Organization assignment is per-partition.
    • Intended partition is specified at session creation time via client configuration.
    • If no partition is specified then the user’s default partition is chosen
  •  In a partitioned system, Services and Application Integration Framework (AIF) is a shared subsystem. To guarantee that incoming requests are correctly isolated, you can restrict an inbound integration port to a particular partition. Additionally, you can specify a target partition for an incoming request by including the partition key in an XML element in the header of the document. Similarly, outbound responses indicate the source partition for the response data by including the partition key in the header.   Because AIF uses a single gateway queue, a system administrator can view all documents in the queue, AIF history, or exceptions list in any partition. The forms that display these lists now have a field that shows the partition key for each document. for more info click
  • Like AIF, the batch processing framework is a shared subsystem. One batch server is shared across partitions. However, each batch job is associated with a specific partition. The batch server executes batch jobs in the context of the correct partition. To view batch jobs or their history, you must log on to the partition that the batch jobs are associated with.
  • Separate organizational hierarchies – Each partition contains its own organizational hierarchy, which includes one or more legal entities. Like a new deployment of Microsoft Dynamics AX, each partition that is created contains the DAT company as a default legal entity. System administrators can add legal entities to each partition. Legal entities are never shared between partitions, even if the legal entities have the same name.

Data partitioning architecture

Datapartition architecture

Recommendation

Implementation choice must be made carefully as the companies between two partitions cannot be merged and Intercompany features cannot be used. The only option is to use Data migration tool kit for data export import between partitions and AIF for inter company operations.

How it is implemented Technically?

  1. A partition Table is introduced. that keeps the list of partitions in the system.
  2. The partition key  is applied in all contexts as data area id used to apply, like all queries, forms etc.
  3. Every table in the system now has one more field named partition, that is nothing but the record id of the record created in the step one.
  4. Partition table implementation
  5. Cross company query doesn’t work across paritions
  6. Global function getcurrentpartitionrecid() introduced.

References

  1. What’s New: Data partitioning [AX 2012]
  2. Partitions, Companies, and Data Isolation in Microsoft Dynamics AX [AX 2012]

Dynamics AX 2012 R2 Data Partitioning

Aside