Dynamics AX

How to add new action under Context menu in Dynamics AX

2016-11-26T22:09:00.003-08:00

Each node in the AOT contains a set of available actions. You can access these actions from the
context menu, which you can open by right-clicking any node.
You can create custom actions for any element in the AOT by enlist a class as a new add-in by following:
1. Create a new menu item and give it a meaningful name, a label, and Help text.
2. Set the menu item’s Object Type property to Class.
3. Set the menu item’s Object property to the name of the class to be invoked by the add-in.
4. Drag the menu item to the SysContextMenu menu.
5. If you want the action available only for certain nodes, you need to modify the verifyItem
method on the SysContextMenu class.

How to Delete Company Transactions in AX 2012 including Trial balance?

2016-05-01T01:16:00.001-07:00

To delete Transactions data inside any company in ax, you can do by using the class SysDatabaseTransDelete under AOT.

But you may facing an issue since the Trial Balance will not be deleted so, to delete GL Trans by deleting the following tables:

GeneralJournalAccountEntry

GeneralJournalEntry

LedgerEntryJournal

LedgerEntry

Ledgerjournaltrans

Ledgerjournaltable

To remove the Trial Balance you have to follow the steps below:

1-) Modify 'handleTable()' method by adding 2 new cases

Case TableGroup::TransactionHeader:

Case TableGroup::TransactionLine:

void handleTable(SysDictTable sysDictTable)

{

TableGroup tableGroup;

if (tableSet.in(sysDictTable.id()))

return;

tableSet.add(sysDictTable.id());

if (sysDictTable && !sysDictTable.isTmp() && !sysDictTable.isMap())

{

tableGroup = sysDictTable.tableGroup();

// Handle company specific tables to be deleted

if (sysDictTable.dataPrCompany())

{

switch(tableGroup)

{

case TableGroup::Transaction:

case TableGroup::TransactionHeader:

case TableGroup::TransactionLine:

case TableGroup::WorksheetHeader:

case TableGroup::WorksheetLine:

this.handleTransTable(sysDictTable);

break;

default:

this.handleNonTransTable(sysDictTable);

break;

}

else

{

// Handle global tables to be deleted

switch(tableGroup)

{

case TableGroup::Transaction:

case TableGroup::TransactionHeader:

case TableGroup::TransactionLine:

case TableGroup::TransactionHeader :

case TableGroup::WorksheetHeader:

case TableGroup::WorksheetLine:

this.handleGlobalTransTable(sysDictTable);

break;

default:

break;

}

2) Add a new method to handle LEDGERJOURNALTABLE:

private void deleteLedgerJournalTables()
{

GeneralJournalEntry GJEntry;
GeneralJournalAccountEntry GJAEntry;
LedgerJournalTable ledgerjournalTable;
LedgerEntryJournal ledgerEntryJournal;

ttsBegin;
while select forupdate LedgerJournalTable
{
while select forUpdate ledgerEntryJournal
where ledgerEntryJournal.JournalNumber == ledgerjournalTable.JournalNum
//&& ledgerEntryJournal.dataAreaId == ledgerjournalTable.dataAreaId
{
while select forUpdate GJEntry
where GJEntry.LedgerEntryJournal == ledgerEntryJournal.RecId
{
delete_from GJAEntry where GJAEntry.GeneralJournalEntry == GJEntry.RecId;

GJEntry.delete();
}
ledgerEntryJournal.delete();
}
LedgerJournalTable.delete();
}
ttsCommit;
}

3) Modify the 'handleTransTable()' method to call the above method

void handleTransTable(SysDictTable sysDictTable)
{
switch(sysDictTable.id())
{
case tablenum(CustCollectionLetterLine):
case tablenum(InventDim):
case tablenum(DocuRef):
case tablenum(DirPartyRelationship) :

break;
case tablenum(LedgerJournalTable) :
this.deleteLedgerJournalTables();
break;

default:
this.deleteTable(sysDictTable);
break;
}
}

4) You may have to modify the 'deleteVoucher()' method in the 'LedgerJournalTrans' table to skip over releasing non-existing voucher numbers

public server void deleteVoucher(Voucher _voucher = this.Voucher)
{
LedgerJournalTable ledgerJournalTable = LedgerJournalTable::find(this.JournalNum);

if (! ledgerJournalTable.Posted && !this.Transferred)
{
if (_voucher && ! LedgerJournalTrans::existTransMinusThis(this.JournalNum, _voucher, this.RecId))
{
if (this.checkVoucherNotUsed(ledgerJournalTable, _voucher))
{
if (this.checkVoucherNotUsedDataSource(_voucher))
{
// replace the voucher number so it can be re-used
if (ledgerJournalTable.NumberSequenceTable) /* 28Nov12-Admin */
NumberSeq::releaseNumber(ledgerJournalTable.NumberSequenceTable, _voucher);

if (this.Voucher == _voucher)
{
// delete voucher template record if exists and the voucher on the line is not being changed
LedgerJournalTransVoucherTemplate::deleteForJournalOrVoucher(this.JournalNum, _voucher);
}
}
}
}
}
}

5) After running 'SysDatabaseTransDelete', rebuild balances for the financial dimension sets (General Ledger\Setup\Financial Dimensions\Financial dimension sets)

If you still have non-zero amounts in the Trial balance then you must manually remove the 'left-over' rows in the shared tables (results of your previous executions of the 'SysDatabaseTransDelete'). Identify these entries in the 'LedgerEntryJournal' table then use the following job to clear them:

static void tg_deleteTables(Args _args)
{
GeneralJournalEntry GJEntry;
GeneralJournalAccountEntry GJAEntry;
LedgerJournalTable ledgerjournalTable;
LedgerEntryJournal ledgerEntryJournal;

ttsBegin;
while select forUpdate ledgerEntryJournal
where ledgerEntryJournal.JournalNumber like 'clau*'
{
while select forUpdate GJEntry
where GJEntry.LedgerEntryJournal == ledgerEntryJournal.RecId
{
delete_from GJAEntry where GJAEntry.GeneralJournalEntry == GJEntry.RecId;

GJEntry.delete();
}
ledgerEntryJournal.delete();
}

ttsCommit;
info('completed');
}

How to post PO by Code in Dynamics AX

2015-12-15T00:16:00.001-08:00

I have task to integrate Dynamics AX with another application by posting PO from out side Dynamics AX, so I used AIF and build new web service to be used from out of AX and call the method below to post PO (packing slip, or invoice) .
public str CreatePostProductReceipt(PurchId _PurchId, Num _PackingSlip, ItemId Itemid, Qty qty,
InventSiteId InventSiteId ='', InventLocationId InventLocationId= '' , inventBatchid batchid = '', InventSerialId serialId = '', inventsizeId inventsizeId ='', InventColorId InventColorId ='')
{
PurchFormLetter             purchFormLetter;
PurchParmUpdate             purchParmUpdate;
PurchParmTable              purchParmTable;
PurchParmLine               purchParmLine;
PurchTable                  purchTable;
PurchLine                   purchLine;
PurchId                     purchId;
Num                         packingSlipId;
InventDim                   inventDim;
str                 ret='';
System.Exception    err;
;
packingSlipId   = _PackingSlip;
purchTable      = PurchTable::find(_PurchId);
ttsBegin;
try
{
// Create PurchParamUpdate table
purchFormletter = PurchFormLetter::construct(DocumentStatus::PackingSlip); // to post invoice change to DocumentStatus::invoice
purchFormLetter.createParmUpdate(true);
purchParmUpdate = PurchFormLetter.purchParmUpdate();
// Set PurchParmTable table
purchParmTable.clear();
purchParmTable.TransDate                = SystemDateGet();
purchParmTable.Ordering                 = DocumentStatus::PackingSlip;
purchParmTable.ParmJobStatus            = ParmJobStatus::Waiting;
purchParmTable.Num                      = packingSlipId;
purchParmTable.PurchId                  = purchTable.PurchId;
purchParmTable.PurchName                = purchTable.PurchName;
purchParmTable.DeliveryName             = purchTable.DeliveryName;
purchParmTable.OrderAccount             = purchTable.OrderAccount;
purchParmTable.CurrencyCode             = purchTable.CurrencyCode;
purchParmTable.InvoiceAccount           = purchTable.InvoiceAccount;
purchParmTable.ParmId                   = purchParmUpdate.ParmId;
purchParmTable.insert();
// Set PurchParmLine table
while select purchLine
where purchLine.PurchId == purchTable.purchId && purchline.ItemId == Itemid
{
purchParmLine.InitFromPurchLine(purchLine);
inventDim = purchline.inventDim(true);
// Set batch and serial number
if(InventSiteId != '')
inventDim.InventSiteId = InventSiteId;
if(InventLocationId != '')
inventDim.InventLocationId = InventLocationId;
if(batchid != '')
inventDim.inventBatchId = batchId;
if(serialid != '')
inventDim.inventSerialId = serialID;
if(inventsizeId != '')
inventDim.inventsizeId = inventsizeId;
if(InventColorId != '')
inventDim.InventColorId = InventColorId;
purchParmLine.InventDimId = inventDim::findOrCreate(inventdim).inventDimId;
purchParmLine.ReceiveNow    = 1 ; //PurchLine.PurchQty;
purchParmLine.setInventReceiveNow();
purchParmLine.ParmId        = purchParmTable.ParmId;
purchParmLine.TableRefId    = purchParmTable.TableRefId;
purchParmLine.setQty(DocumentStatus::PackingSlip, false, true);
purchParmLine.setLineAmount();
purchParmLine.insert();
}
ttsCommit;
purchFormLetter = PurchFormLetter::construct(DocumentStatus::PackingSlip);
purchFormLetter.transDate(systemDateGet());
purchFormLetter.proforma(false);
purchFormLetter.specQty(PurchUpdate::PackingSlip);
purchFormLetter.purchTable(purchTable);

// This is the ID we hard code as the product receipt ID, if we do the posting via UI
// user would have the option to manually enter this value
purchFormLetter.parmParmTableNum(purchParmTable.ParmId);
purchFormLetter.parmId(purchParmTable.ParmId);
purchFormLetter.purchParmUpdate(purchparmupdate);
purchFormLetter.run();
return "OK";
}
catch (Exception::CLRError)
{
err = CLRInterop::getLastException();
ret = err.ToString();
return ret;
}
Return "Error";
}

How to Call Report from Dynamics AX 2009

2015-06-30T04:49:00.001-07:00

After you created your report in AX you can call report from AX form by follow the below steps:

1- Create menu item with type output by drag and drop report to MenuItems nodes under AOT.

2- Open the form that you need to call report from.

Note: the form should be have dataSource with the table in report

3- Drag and Drop menu Item created in step 1 in the form under any button group.

4- Add the following method to Report

void initFromCaller(Args _args)
{
str QrderId; // field used as Rang and used to filter data
QueryBuildDataSource qbds; // should be represent report datasource and selected record in the form

;

    if (! _args ||
        ! _args.caller() ||
          _args.dataset() != tablenum(TableName) )
        return;

QrderId = _args.record().(fieldnum(TableName,FieldName));

qbds = element.query().dataSourceTable(tablenum(TableName));

    if(!qbds.findRange(fieldnum(TableName,FieldName)))
    {
        qbds.addRange(fieldnum(TableName,FieldName)) ;
    }
    qbds.findRange(fieldnum(TableName,FieldName)).value(queryvalue(QrderId));

}

Inventory Closing slow

2015-06-04T03:01:00.001-07:00

May The below link lead you to solve Dynamics ax closing slowness issue by creating new indexes in the main tables used in the recalculation and closing process in Dynamics ax.

Inventory Closing slow:

JOB OPPORTUNITY

2015-04-15T06:40:00.001-07:00

An Egyptian Group is hiring:

1- AX 2012 R3 Functional Specialist (specially Logistics, Banking, and GL)

2- AX 2012 R3 Junior Developer (X++, MS Visual Studio 2010 or newer, SSRS, MorphX)

Requirements:

+1 years experience in AX 2012 Microsoft certified is a plus

** If interested, please send your CV to: m.anwar@raneen.com.eg

Your email subject should include the Job you are seeking.

(Vacancies)contracts and permanent jobs

2015-04-13T04:20:00.001-07:00

Good morning,
The market for Microsoft Dynamics is very busy at the moment and we have a number of contracts and permanent roles that might be of interest to you, please take a moment to have a look.
TPC 008
Dynamics NAV Developer – based in southern Denmark, solid NAV development skills essential 5000DKK per day
TPC 015
SSRS AND SSIS BI Developer in an AX environment – UK South Coast, must have proven commercial SSRS and SSIS experience, perm role up to £50k
TPC 016
Dynamics CRM PreSales Consultant – London – working with a successful tier 1 international Microsoft Partner, £100k package available for the right person
TPC 017
Dynamics CRM development consultant – Dynamics CRM development within the hedge fund industry, based in St James, London, immediate start £400 per day
TPC 018
Dynamics AX Consultant – Paris based AX Financial Consultant, within the retail / fashion industry, this is an upgrade for a platform with over 4000 users, must have experience of larger implementations. €750 per day
TPC 019
Dynamics AX Developer – Munich – For an aviation client must be able to work in Munich due to the nature of the project there is NO work offsite option €600 per day
TPC20
Dynamics AX PM – Manchester based Dynamics AX PM role within the insurance industry, working for an end user, but will also have oversight of the Microsoft partner staff, this is an AX2015 upgrade from an AX2009 install. £700 per day
If you are interested in any of these roles or are becoming available, please get in touch on CMerchant@templeton-recruitment.com
Have a great week

Object has not been initialized Erro in Ax 2012

2015-03-22T06:20:00.001-07:00

When user open journal lines from GL ---> General journal ---> Line

got below error

Solution:

1- Delete *.auc files from user Machine

2- release Usage data under Tools –> option –> Usage Data ---> reset button

Running AX report through batch job in AX2009 (batch printing)

2015-02-21T07:21:00.001-08:00

Running batch report in AX2009 (batch printing)

Since the change of AX2009 batch framework, printing of report through batch can be done through two methods:
- Printing from server (to printer or file)
- Printing from client (the legacy batch processing)
Prerequisites: For both of the method, there're some mandatory setup:

Enable any of the AOS that you want to make it as batch AOS
(Administration -> Setup -> Server configuration)
Create batch group for printing.
Eg. One for client printing and one for server printing.
(Administration -> Setup -> Batch groups)
Add the batch group created in #2 into the AOS enabled for batch processing (in #1)

complete article available here

Dynamics AX Workflow types - AX 2012

2015-01-24T22:13:00.001-08:00

To create a workflow, you must first select the type of workflow that you want to create. This topic lists the types of workflows that you can create in each module. The topic also describes what each type of workflow is used for, and whether the workflows of each type are associated with a specific company in the organization or with the whole organization.

https://technet.microsoft.com/en-us/library/dd362043.aspx

Data Import/Export Framework error

2015-01-21T23:55:00.001-08:00

Troubleshoot issue with a Data Import/Export Framework installation from cumulative update 7 for Microsoft Dynamics AX 2012 R2

After you install cumulative update 7 for Microsoft Dynamics AX 2012 R2, the Data Import/Export Framework appears to be installed, but you receive error messages when many forms are opened like the following:

You receive an “Assembly containing type Microsoft.Dynamics.AX.DMF.ServiceProxy.DmfEntityProxy is not referenced.” error

Reason: When you install cumulative update 7 for Microsoft Dynamics AX 2012 R2, the Data Import/Export Framework appears to be installed for members of the System Administrators role. However, the binary components of the framework are not present.
Solution:

please click here to get solution

Regular maintenance activities in Dynamics AX that affect performance

2015-01-20T22:34:00.001-08:00

The following list describes some of the maintenance activities that we recommend that you perform regularly in your production environment:

Defragment indexes – You can defragment indexes from either SQL Server Management Studio or the Intelligent Data Management Framework (IDMF).
Update SQL Server statistics from SQL Server Management Studio – We recommend that you run both manual and automatic updates of statistics. Manual updates may become more important as the size of your database increases, because automatic updates are less likely to be completed on large data sets.
Reduce the size of the database – You can use IDMF to keep the size of the production database small. A small database makes database operations more efficient. For example, you can delete or archive data that is not required in your production system.

Troubleshoot an installation of the Data Import/Export Framework from InformationSource

2015-01-20T02:57:00.001-08:00

This post describes how to troubleshoot issues with a Data Import/Export Framework installation from InformationSource.

The Data Import/Export Framework does not compile

After you install the Data Import/Export Framework, if you cannot compile, validate that the Data Import/Export Framework was installed correctly.

Verify that the Microsoft Dynamics AX Data Import/Export Framework service is running.
Verify that the Data Import/Export Framework DLLs are present in C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin folder:
- Microsoft.Dynamics.AX.DMF.Mapper.dll
- Microsoft.Dynamics.AX.DMF.PreviewGrid.
- Microsoft.Dynamics.AX.DMF.ServiceProxy.dll
- DMFConfig.xml
- Microsoft.Dynamics.AX.DMF.DriverHelper.dll

Resolution

Copy the DLLs from the installation location (C:\Program Files\Microsoft Dynamics AX 2012 Data Import Export Framework Client Component) to the C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin folder.

Exception message while you use Data Import/Export Framework

While you use the Data Import/Export Framework, you might receive the following error message:

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException

Verify that the following files are present in the C:\Program Files (x86)\Microsoft Dynamics AX\60\Server\Bin folder on the server that is running the AOS instance:

DMFConfig.xml
DMFClientConfig.xml
Microsoft.Dynamics.AX.DMF.ServiceProxy.dll.config

Resolution

Copy the .xml and the config files from the installation location (C:\Program Files\Microsoft Dynamics AX 2012 Data Import Export Framework Server Component) to the C:\Program Files (x86)\Microsoft Dynamics AX\60\Server\Bin folder on the server that is running the AOS instance.

Changes to the location of the Data Import/Export Framework service

If you have to update the location where you run Integration Services and the Data Import/Export Framework service, you can update the endpoint address in the Microsoft.Dynamics.AX.DMF.ServiceProxy.dll.config file to use the new server name.

<endpoint address="http://<<NEW MACHINE NAME>>:7000/DMFService/DMFServiceHelper.svc"

Note

The Microsoft.Dynamics.AX.DMF.ServiceProxy.dll.config file is located in the C:\Program Files (x86)\Microsoft Dynamics AX\60\Server\Bin folder on the server that is running the AOS instance.

12 Ways to Improve Your Productivity at Work

2014-11-17T14:57:00.001-08:00

Between constant meetings, phone calls and emails, staying productive at work can be a challenge. However, the odds of staying productive can be greatly improved by taking some simple steps in order to stay efficient.

Here are 12 tips from the Bayt.com team to help you to make the most of your time at work:

1. Don’t let emails take over your day

We are all so accustomed to email that as soon as we see a new email in our inbox we instinctively click it on, focus on the content of the email, and respond. These seemingly tiny activities during the day can quickly add up to large amounts of time that not only cause your day to slip away, but also distract you from completing your current task.

If replying to or disposing of an e-mail takes less than two minutes, do it right away. Send less to receive less: Keep your e-mails short, and write fewer of them. Here are some tips from the career experts at Bayt.com to help you control your work emails.

2. Eliminate unnecessary meetings

Face-to-face communication is essential (email is fraught with misinterpretation), but be ruthless about protecting your time. Avoid every meeting that isn’t truly necessary.

3. Learn how to say ‘no’

While everyone wants to be a good team player at work, saying ‘no’ is sometimes the right thing to do. If you are not the best person for a task, or if you have other pending deadlines, it is OK to turn down a work request. read more

The administration of number sequences – AX 2012

2014-11-12T02:24:00.001-08:00

The administration of number sequences is performed by using actions provided in the Administration group on the action pane on the Number sequences list page.

Organization Administration –> Common –> Number Sequences ---> Number Sequences

Status list

Provides a list of numbers that have been generated for continuous number sequences, but which have not been committed to the database. The numbers are either currently being used in a user session, are reserved for future use in a user session, or are free for use if a new client user session requests a new number for a particular number sequence in the list. If a new number does not exist for a specific continuous number sequence, it is generated by the sequence number framework from the next value for that number sequence in the Number sequence table (NumberSequenceTable).

Manual cleanup

Allows the administrator to manually clean up numbers in the status list. Use of this option is only recommended after an unexpected system failure; in such rare circumstances, numbers might not be automatically cleaned up.

History

Provides the history of changes to the number sequences themselves.

A number of administrator tasks can be performed from the Details page. An administrator can, for example, schedule an automated periodic cleanup for every number sequence by entering intervals on the Automatic cleanup FastTab as displayed in image below

An administrator can also assign number sequences by using a page in the parameters forms in individual application modules. For example, you can view or assign the number sequences to specific references in the General ledger module. You can navigate to the form by using the path General ledger > Setup > Parameters.

How to reset TTSBegin/TTSCommit in AX

2014-11-11T04:38:00.001-08:00

We are using TTSBegin and TTSCommit when update and create a record in AX, but some times an error is occurred and through an exception.

If we did not abort TTS by using TTSAbort statement between try and catch keyword as below

try

{

ttsBegin;

// your update code

ttsCommit;

}

catch

{

ttsAbort;

}

so we may facing this error below,

To fix this error please run the following job

static void ResetTTS(Args _args)
{
    while (appl.ttsLevel() > 0)
    {
        info(strfmt("Level %1 aborted",appl.ttsLevel()));
        ttsAbort;
    }
}

Make sure that the X++ code has been compiled to Microsoft .NET Framework CIL - AX Batch Error

2014-11-10T04:43:00.001-08:00

I developed custom class which extend RunBaseBatch system class to run in server, After created my class and run the class though batch job, there is an error is occurred and got the following error in log.

"Unable to construct an object from the class Sample Class in the batch framework. Make sure that the X++ code has been compiled to Microsoft .NET Framework CIL, and that the constructor does not require any parameters"

After search through internet I got solution by run “Generate incremental CIL” as displayed below

Hint: May it takes time depend on server configuration.

Performance considerations for number sequences in Dynamics AX

2014-11-09T00:57:00.001-08:00

Consider the following information about how the configuration of number sequences can affect system performance before you set up number sequences.

Continuous and non-continuous number sequences

Number sequences can be continuous or non-continuous. A continuous number sequence does not skip any numbers, but numbers may not be used sequentially. Numbers from a non-continuous number sequence are used sequentially, but the number sequence may skip numbers. For example, if a user cancels a transaction, a number is generated, but not used. In a continuous number sequence, that number is recycled later. In a non-continuous number sequence, the number is not used.

Continuous number sequences are typically required for external documents, such as purchase orders, sales orders, and invoices. However, continuous number sequences can adversely affect system response times because the system must request a number from the database every time that a new document or record is created.

If you use a non-continuous number sequence, you can enable Preallocation on the Performance FastTab of the Number sequences form. When you specify a quantity of numbers to preallocate, the system selects those numbers and stores them in memory. New numbers are requested from the database only after the preallocated quantity has been used.

Unless there is a regulatory requirement that you use continuous number sequences, we recommend that you use non-continuous number sequences for better performance.

Automatic cleanup of number sequences

In case of a power failure, an application error, or other unexpected failure, the system cannot recycle numbers automatically for continuous number sequences. You can run the cleanup process manually or automatically to recover the lost numbers.

Carefully consider server usage when you plan the cleanup process. We recommend that you perform the cleanup as a batch job during non-peak hours.

How to pass parameter to SSRS report from Dynamics AX

2014-11-05T01:02:00.001-08:00

The below code for Passing parameters to SSRS using X++ code.

MenuFunction SSRS_MyReport;

Args Args;

str parmId ="";

;

// create reference to menu item “OverTime” which is in AOT
SSRS_MyReport = new MenuFunction(menuItemOutputStr(OverTime),MenuItemType::Output);

Args = new Args();

// Set parameters and parameter value
// I have 3 Parameters Nationality, EmplGroup, PeriodID

parmId = "Nationality=KSA&EmplGroup=HERD&PeriodID=2014_05";

// Assign parameters to report
Args.parm(parmId);

// Run the report
SSRS_MyReport.run(Args);

Notes:

1- Parameters name is case sensitive, so it should set name as predefined in report.

2- You can pass more than one parameters by add “&” between each parameter.

3- Not add space between parameters, it cause an error

Recalculation Error Dynamics AX 2009

2014-11-04T01:04:00.001-08:00

while running inventory recalculation process, I got this error

“Cannot edit a record in stock transactions (InventTrans) An update
conflict occurred due to another user process deleting the record or changing
one or more fields in the record"

solution to avoid this error, go to AOT –> DataDictionary –> Tables ----> InventTrans

right click on table to display properties window then change OccEnabled property to No.

then resume recalculation again, it will work successfully.

Configuring the instance of SQL Server for Dynamics AX

2014-11-01T22:45:00.001-07:00

Improve SQL performance lead us to optimize AX performance, this post is one of posts to achieve that

1- Configuring max degree of parallelism

The max degree of parallelism option is a setting that affects the entire instance of SQL Server. Microsoft Dynamics AX workloads generally perform better when intra-query parallelism is disabled. However, the upgrade process benefits from parallelism, as do activities that are used exclusively for batch jobs or maintenance. Use the following settings when the system performs maintenance activities or an upgrade:

· Before an upgrade to a new release of Microsoft Dynamics AX, or before a large number of maintenance or batch activities, set max degree of parallelism to the smallest of the following values:

· 8

· The number of physical processor cores

· The number of physical processor cores per non-uniform memory access (NUMA) node

· When the Microsoft Dynamics AX database is used in a production environment, set max degree of parallelism to 1.

Use the following statements to set the value of max degree of parallelism.

Examine the output from the second sp_configure 'max degree of parallelism' statement, and confirm that the value has been changed. In the following query, the first sp_configure 'max degree of parallelism' statement sets the value of max degree of parallelism to 1. The second sp_configure 'max degree of parallelism' statement returns a value of 1.

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'max degree of parallelism', 1;

RECONFIGURE;

EXEC sp_configure;

For more information, see max degree of parallelism Option. For general guidelines, see Knowledge base article 329204, General guidelines to use to configure the MAXDOP option. For tips from the SQL Server team, visit the SQL Server Relational Engine team's blog, SQL Server Engine Tips.

2- Configuring max server memory

SQL Server dynamically acquires and frees memory as required. Typically, an administrator does not have to specify how much memory is allocated to SQL Server. However, the max server memory option can be useful in some environments. Make sure that sufficient memory is available for the operation of Windows Server. For more information, see Configure SQL Server and storage settings, later in this topic.

If you find that the dynamic allocation of memory adversely affects the operation of Windows Server, adjust the value of max server memory based on the available random access memory (RAM). For more information, see Effects of min and max server memory.

3- Monitoring available memory

Make sure that sufficient memory is available for the operation of Windows Server. For example, make sure that you run a dedicated instance of SQL Server on a server that has at least 4 gigabytes (GB) of memory. If the available memory for the server drops below 500 megabytes (MB) for extended periods, the performance of the server may degrade.

Use the Memory: Available Mbytes performance counter for the Windows Server operating system to determine whether the available memory drops below 500 MB for extended periods. If the available memory drops below 500 MB frequently or for extended periods, we recommend that you reduce the max server memory setting for SQL Server or increase the physical memory of the server.

Detailed guidance about memory management is beyond the scope of this topic. For more information about how to monitor memory and troubleshoot performance issues, see the Windows Server and SQL Server documentation.

4- Allocating storage for tempdb

We recommend that you determine the total size of the data files and transaction log files that are required for the tempdb database, and that you set a specific value. Do not use automatic growth, or autogrow, setting for space management. Instead, use autogrow as a safety mechanism, so that tempdb can grow if tempdb files use the space that was originally allocated to them. Follow this process to determine the number and placement of data files.

· Determine the number of processors that are available to SQL Server. Unless you are using an affinity mask, this number is same as the total number of processors that you see on the Performance tab of Windows Task Manager. When hyperthreading is not enabled, each processor corresponds to a processor core. Affinity masks and processor cores are beyond the scope of this topic. For more information, see the Windows Server and SQL Server documentation.

· Based on performance testing of the OLTP workload for Microsoft Dynamics AX, we recommend that you maintain one tempdb data file per processor. For more information, see the performance benchmark reports on PartnerSource or CustomerSource.

· Isolate tempdb on dedicated storage, if you can. We recommend that you move the primary data file and log file for tempdb to high-speed storage, if high-speed storage is available. The Microsoft Dynamics AX database runs in read committed snapshot isolation (RCSI) mode. In RCSI mode, row versions are stored in tempdb. By creating multiple files for tempdb data, even if these files reside on the same storage device, you can improve the performance of tempdb operations.

· Determine the size of the tempdb data files and log files. You must create one primary data file and one log file. Determine how many additional, secondary data files you require for the tempdb data. For best results, create data files of equal size. The total number of data files must equal the total number of processor cores. The aggregate size of the primary data file and all other data files must equal the total data size that you determined for the tempdb database.

For more information, see Optimizing tempdb performance.

· Resize the primary data file and log file for tempdb. Move the primary data file and log file to dedicated storage, if dedicated storage is available. The primary tempdb data file cannot be moved while the instance of SQL Server is running. To complete the move, you must use an ALTER DATABASE statement and restart the instance of SQL Server. For more information, see ALTER DATABASE.

Note: The data files and transaction log files for tempdb can reside on the same storage device.

· If space is available on the drive where tempdb files are allocated, do not configure the autogrow property for data files and log files as a percentage. Instead, configure the autogrow property as a specific number of megabytes. If you can, configure the data files and log files to grow by 100 to 500 MB, depending on the available space. Monitor the data files, and when they grow, adjust the original allocation to prevent automatic growth later. If the autogrow property is configured in megabytes instead of as a percentage, the allocation of space is more predictable, and the chance of extremely small or large growth increments is reduced.

· Monitor the tempdb data files and log files to make sure that they are all sized correctly, and that all data files are of equal size. Use SQL Server Management Studio or a transact-SQL query to view the database properties. Verify that all the data files are of equal size, and that they have the same size as the value that you originally provided. If one or more files have grown, adjust the initial size of all files.

Configuring physical storage for SQL server of Dynamics AX

2014-10-27T21:57:00.001-07:00

This post provides general recommendations for physical storage. Determine the applicability of these recommendations to your environment. Some storage area network (SAN) vendors may have alternative recommendations that take precedence. Recommendations are listed in order of priority.

Many factors contribute to optimal I/O performance for a disk. By default, Windows Server 2008 aligns partitions. When you upgrade to Windows Server 2008, preexisting partitions are not automatically aligned and must be manually rebuilt to guarantee optimal performance. Therefore, until you rebuild the migrated partitions, alignment of disk partitions remains a relevant technology.

Check existing disks on the server, and be aware of the differences in the analysis of basic partitions and dynamic volumes. Rebuild the partitions, if you can, and appropriate and create all new partitions based on guidance from the SAN vendor. If the vendor does not provide recommendations, follow the best practices for SQL Server. See Disk Partition Alignment Best Practices for SQL Server.

The partition offset value must be a multiple of the stripe size. In other words, the expression, partition offset / stripe size, must resolve to an integer value.

Create the tempdb database files, data files for the Microsoft Dynamics AX database, and Microsoft Dynamics AX log files on disk arrays of type RAID 1, RAID 0 + 1, or RAID 10. We recommend RAID 10 for these files. Do not use RAID 5.
Store the data files for the Microsoft Dynamics AX database on separate physical stores from the transaction log files.
Store the tempdb data files on a separate physical store from the data files and log files for the Microsoft Dynamics AX database.
Store other database files on separate physical stores from the data files and log files for tempdb and the Microsoft Dynamics AX database.

Tune data access settings [AX 2012]

2014-10-26T02:52:00.001-07:00

You might want to tune the database settings for Microsoft Dynamics AX to improve performance. Settings that you can tune include connections, query settings for the use of literals, string functions, or hints, concurrency mode used for database changes, and the table and index options, such as table and index data compression, and index fill factor and sort in tempdb settings.

Before changing settings, you should trace the usage of your Microsoft Dynamics AX database to ensure that you have clear understanding of performance under the current settings.

Test all tuning changes before implementing them in a production environment. In a test or development environment, make a single change and then test your system's performance before making another change.

Tune connections

The following table lists common connection issues, and also some adjustments to try in the Server Configuration Utility.

Symptom

Adjustments to try

Queries that return a large number of rows execute slowly.

Increase the Maximum buffer size value in small increments.

If this adjustment has worked, the number of round trips to the database, as measured in Performance Monitor by a decrease in the value of SQL Server statistics: batchrequestsPerSecond value. Stop increasing the value when the rate of improvement diminishes.

You may also want to change the Maximum buffer size value if you receive an error similar to the following:

The total, internal size of the records in your joined Select statement is 29374 bytes, but Microsoft Dynamics is by default performance-tuned not to exceed 27646 bytes. It is strongly recommended that you split your tables(s) into smaller units.

Maximum buffer size refers to the size of buffer the kernel allocates for holding input/output data to and from SQL Server. The buffer can be used to hold more than one row of the entire result set for output binding, and it should be allocated large enough to hold at least one row. The buffer size needed for one row depends on the number of tables joined (exist join excluded) and the size of the aggregated table columns. When large numbers of joins are used, or wide tables, a customer may encounter the issue that the maximum buffer size is less than the space needed to hold one row of the result set.

Due to the way the kernel handles data binding, the aggregated table column size is always the width of the entire table. Therefore, attempting to resolve the issue by limiting the field projection list does not help. You can fix the issue by rewriting the join, or by increasing the Maximum buffer size value.

Because large joins and wide rows may lead to performance issues, we use this limit to catch performance issues and ask customers to rethink their joins.

Results for ad hoc queries are returned slowly

Verify that the appropriate indexes are in place.

Tune queries

If queries in the system are running slowly, you may want to change settings for literals, string functions, or hints.

Adjust the use of hints

In Microsoft Dynamics AX, you can allow developers to override the index selected by the query optimizer. In most situations, allowing the query optimizer to select an index for a query results in improved performance.

Changes in the use of hints

The following changes to hints have been made:

· The OPTION (FIRSTFAST) hint is applied by default for form data sources. It can be suppressed, or set to a specific value. Use of this option appends an OPTION (FAST) to the SQL Server query.

· OPTION( FAST) is now set to what we expect the number of rows to be returned to the database in a single roundtrip. This is based on maximum buffer size.

· FASTFORWARD cursors are used for all user queries unless the query is a full text search.

Change table and index options

Table and index options that can be changed from within Microsoft Dynamics AX include table compression settings, and index compression, fill factor, and sort in tempdb settings.

Data compression options

If you have appropriate hardware, we recommend that you set all tables and indexes to use page-level compression. Compression saves disk space and memory consumption, but increases CPU consumption.

When page-level compression has been set, expect between 10-15 percent additional CPU consumption.

If you are concerned about CPU consumption, we recommend that you start by setting the ten largest tables in your system to use table compression.

Use the guidance in the following resources to help you determine how or whether to compress your tables and indexes:

· SQL Server Books online: Creating Compressed Tables and Indexes

· SQLCat article: Data Compression: Strategy, Capacity Planning and Best Practices

· Database Engine team blog post: Compression Strategies.

Fill factor options

We recommend that you not set the fill factor options (fill factor and pad index) for all indexes. Only set fill factor values on indexes that show rapid fragmentation, where fragmentation has a performance penalty. Adjusting fill factor can be important for tables that you want to retrieve sequential rows from, for example, sales line.

We recommend that you work with your database administrators to identify the tables to set this value for.

Sort in tempdb

We recommend that you not set the sort in tembdp options for all indexes. Only enable sort in tempdb for indexes that show rapid fragmentation, where fragmentation has a performance penalty.

We recommend that you work with your database administrators to identify the tables to set this value for.

To change table and index options

1. Click System administration > Periodic > Database > SQL administration. Select all tables, all indexes, or a specific table or index, and then click Table and index options.

2. Select the options to set, and then click Save.

The SQL statement that will be executed is displayed at the bottom of the form.

Important

The changes that you have made will not be applied unless you specify that they should be applied by using the SQL administration form.

To turn off data compression, click Enable compression, click None for the type of compression, and then close the Select table and index options form, and then click Apply compression in the SQL administration form.

3. To apply changes in the SQL administration form:

o For tables, click Table actions, and then click Apply compression.

o For indexes, click Index actions, and then click Reindex.

Change the concurrency mode

Concurrency mode settings enable you to reduce locking conflicts in your system. Set concurrency mode settings only at the table or statement level, not throughout your Microsoft Dynamics AX program.

Optimistic concurrency

An optimistic concurrency strategy does not lock data when the data is retrieved from the database for future modification. Therefore, no locks are held while filters and other business logic are being applied. Data is locked only when an update is performed. If any data has been changed by another transaction between the time of the retrieval and the time of the update, the change is detected and an Infolog exception is displayed.

Pessimistic concurrency

A pessimistic concurrency strategy uses an update lock to lock data when the data is retrieved from the database for future modification. Locks are held while filters and other business logic are being applied, in addition to being held during an update. Data cannot be changed by other transactions.

Acquiring an update lock for a large volume of rows increases the lock escalation from row level to table level in SQL Server. This can block other users and reduce transaction throughput.

Table-level concurrency settings

If the global concurrency mode is set to Optimistic concurrency mode enabled per table, table-level control of concurrency settings is available using the OccEnabled property.

Runtime update options

If you are encountering many update errors, you may want to use the runtime update options to help troubleshoot. These options are intended for temporary use only, as they might significantly slow performance.

Use Writes all UPDATE conflict exceptions to the log to write all update conflict exceptions to the log.

Use Update record version automatically to have Microsoft Dynamics AX search in memory for the recordID every time that that a record is updated, and then change the update values in all instances of the record.

If you have a table set to optimistic concurrency, and you are experiencing optimistic concurrency violations that affect the performance of the transactions that use the table, then first set the Writes all UPDATE conflict exceptions to the log option. If, from your analysis, it appears that the use of optimistic concurrency is causing a problem, then set the table to pessimistic concurrency.

To change concurrency settings

1. Click System administration > Setup > Database > Select concurrency mode.

2. Select the appropriate concurrency mode and runtime options for your environment.

3. Click Close.

Statement-level concurrency settings

You can use the optimisticlock or pessimisticlock keywords in a SELECT statement to override the global or table concurrency mode settings.

For more information, see the following topics:

· Best Practice Performance Optimizations: Database Design and Operations

· Transaction Integrity

· Exception Handling with try and catch Keywords

· Select Statement Syntax

· Table Properties

Tune data access settings [AX 2012]

2014-10-23T07:38:00.001-07:00

Before changing settings, you should trace the usage of your Microsoft Dynamics AX database to ensure that you have clear understanding of performance under the current settings.

Tune connections

The following table lists common connection issues, and also some adjustments to try in the Server Configuration Utility.

Symptom

Adjustments to try

Queries that return a large number of rows execute slowly.

Increase the Maximum buffer size value in small increments.

You may also want to change the Maximum buffer size value if you receive an error similar to the following:

Because large joins and wide rows may lead to performance issues, we use this limit to catch performance issues and ask customers to rethink their joins.

Results for ad hoc queries are returned slowly

Verify that the appropriate indexes are in place.

Tune queries

If queries in the system are running slowly, you may want to change settings for literals, string functions, or hints.

Adjust the use of hints

Changes in the use of hints

The following changes to hints have been made:

· The OPTION (FIRSTFAST) hint is applied by default for form data sources. It can be suppressed, or set to a specific value. Use of this option appends an OPTION (FAST) to the SQL Server query.

· OPTION( FAST) is now set to what we expect the number of rows to be returned to the database in a single roundtrip. This is based on maximum buffer size.

· FASTFORWARD cursors are used for all user queries unless the query is a full text search.

Change table and index options

Table and index options that can be changed from within Microsoft Dynamics AX include table compression settings, and index compression, fill factor, and sort in tempdb settings.

Data compression options

If you have appropriate hardware, we recommend that you set all tables and indexes to use page-level compression. Compression saves disk space and memory consumption, but increases CPU consumption.

When page-level compression has been set, expect between 10-15 percent additional CPU consumption.

If you are concerned about CPU consumption, we recommend that you start by setting the ten largest tables in your system to use table compression.

Use the guidance in the following resources to help you determine how or whether to compress your tables and indexes:

· SQL Server Books online: Creating Compressed Tables and Indexes
· SQLCat article: Data Compression: Strategy, Capacity Planning and Best Practices
· Database Engine team blog post: Compression Strategies.

Fill factor options

We recommend that you work with your database administrators to identify the tables to set this value for.

Sort in tempdb

We recommend that you not set the sort in tembdp options for all indexes. Only enable sort in tempdb for indexes that show rapid fragmentation, where fragmentation has a performance penalty.

We recommend that you work with your database administrators to identify the tables to set this value for.

To change table and index options

1. Click System administration > Periodic > Database > SQL administration. Select all tables, all indexes, or a specific table or index, and then click Table and index options.

2. Select the options to set, and then click Save.

The SQL statement that will be executed is displayed at the bottom of the form.

Important

The changes that you have made will not be applied unless you specify that they should be applied by using the SQL administration form.

3. To apply changes in the SQL administration form:

o For tables, click Table actions, and then click Apply compression.
o For indexes, click Index actions, and then click Reindex.

Change the concurrency mode

Concurrency mode settings enable you to reduce locking conflicts in your system. Set concurrency mode settings only at the table or statement level, not throughout your Microsoft Dynamics AX program.

Optimistic concurrency

Pessimistic concurrency

Acquiring an update lock for a large volume of rows increases the lock escalation from row level to table level in SQL Server. This can block other users and reduce transaction throughput.

Table-level concurrency settings

If the global concurrency mode is set to Optimistic concurrency mode enabled per table, table-level control of concurrency settings is available using the OccEnabled property.

Runtime update options

Use Writes all UPDATE conflict exceptions to the log to write all update conflict exceptions to the log.

To change concurrency settings

1. Click System administration > Setup > Database > Select concurrency mode.
2. Select the appropriate concurrency mode and runtime options for your environment.
3. Click Close.

Statement-level concurrency settings

You can use the optimisticlock or pessimisticlock keywords in a SELECT statement to override the global or table concurrency mode settings.

For more information, see the following topics:

· Best Practice Performance Optimizations: Database Design and Operations

· Transaction Integrity

· Exception Handling with try and catch Keywords

· Select Statement Syntax

· Table Properties

Uninstall Microsoft Dynamics AX

2014-09-30T05:35:00.001-07:00

This topic explains how to uninstall Microsoft Dynamics AX by using Add or Remove Programs on the Control Panel.

Databases, log files, and application files must be removed manually. Information about how to manually remove components is also included in this topic.

Uninstall components by using Add or Remove Programs

Use this procedure to remove Microsoft Dynamics AX components.

Open Add or Remove Programs. (Start > All Programs > Control Panel > Add or Remove Programs).
Select the component that you want to remove, and then click Remove. The components that are listed in the following table can be removed.

Option	Removes
Microsoft Dynamics AX Components	Selecting this option removes the following components: o Client o Role Centers and the Enterprise Portal framework o Workflow o Reporting extensions o Debugger o Enterprise Portal developer tools o Reporting tools o .NET Business Connector o AIF Web services o BizTalk adapter o Synchronization proxy for Microsoft Office Project o Synchronization service for Microsoft Office Project This option removes all components that are installed on the local computer. You cannot select to remove individual components.
Microsoft Dynamics AX Client Help Files	Select this option to remove all Help files in all installed languages. You should not remove Help files unless the client is also being removed.
Microsoft Dynamics AX Object Server (instance name)	Select this option to remove a specific Application Object Server (AOS) instance. Before you remove an AOS instance, use the Microsoft Dynamics AX Configuration utility to point all clients to a valid AOS instance, or update the shared configuration file. When you remove an AOS instance, it is not automatically removed from the list of batch and load balancing servers. After you uninstall an AOS instance, you must manually delete it by using the Server configuration form or the Cluster configuration form.

A message box asks you to confirm that you want to uninstall. To proceed, click Yes.

Remove remaining components manually

If you choose to remove an entire installation, some components remain after Add or Remove Programs is finished. The following table provides more information about removing components manually.

To remove this	Do this
Application files	Delete the application file directory from the location that you installed it to.
Database and log files	Use database server administration tools to delete the database and log files.
Role Centers and the Enterprise Portal framework	Delete Web sites by using SharePoint Central Administration.
Reporting extensions	· Delete SQL Server Reporting Services objects, such as data sources, reports, and report models by using Reporting Services. · Delete the contents of the Microsoft Dynamics AX report folder.
Analysis extensions	Delete SQL Server Analysis Services objects, such as databases, cubes, and models by using Analysis Services.