Best practices: Autogrowth of the database, what not to forget

Some system administrators are happy already when Dynamics Ax is fully installed and up-and-running.  But there is more.  Ask yourself: Is everything set at its best?  Did you do everything to ensure stability in the future as well?
An often overlooked setting here is the growth of your Ax SQL database, a forgotten setup which may come back to haunt you later as a performance issue.

When creating a database in MS SQL server, you receive the default database settings.  For autogrowth settings, these include an autogrowth setting of 1 Mb.

There are 2 major reasons on why you want to review this setting:

• Letting the database grow with small portions gives an almost constant extra overhead for your transactions, slowing you down.
• Physical fragmentation can have a severe effect on the overall performance of your database.

So what should you do then?  What's the best practice here?

Don't rely on the autogrowth parameters.  Size your database, based on the expectations.  Like expected transactions for sales, ledger accounts, production etc.  That may mean to set an initial size big enough to start working and an evaluation after 1 or 3 months.  To be repeated each half year for instance.  So the set file size would cover the requirements, without the need to grow automatically.

You may leave the autogrow on, no problem.  But it should be considered merely part of a contingency plan for unexpected growth.  Set an autogrowth rate of for example 200 or 500 Mb.
Be sure to check on your database size from time to time.  Or even better, setup some proper monitoring right from the start.  Being proactive is key here.  It's always better to do your SQL maintenance (=resizing of the database) in non-peak hours.

For those not knowing where to look for or how change these settings:

From the Microsoft SQL Server Management Studio, select the database in question.
Right click the database and choose Properties. Under Files, you find the database file information, including the Autogrowth settings.

Note: You might wanna check the Auto Shrink feature as well, make sure it's switched off.

Different ways of getting the current date: Which one should you pick?

There are a number of ways in Dynamics Ax to get the current date.
Maybe you've tried some of them, and most likely they all returned the same result.  Albeit that's actually depending on your installation and setup.

What's the difference?  Which method should you use when writing code to get the current date?
A short overview:

today

The today function uses the date of the machine.
This method is deprecated, for the simple reason that there's no support for different time zones.


systemDateGet

This function returns the session date.  If there is no specific session date set, it will return the system date.
Remember what the session date is?
It's a feature which allows you to change the date used within your specific Ax session, fe to back-date transactions.  Changing the posting date (all modules), changing the invoice date (and subsequent due date).  Note that for system fields of a table like CreatedDateTime and ModifiedDateTime, the computer date is used and not the Ax system date.

You can set the session date under Tools - Session:

DateTimeUtil::getSystemDateTime

This method gets you the session date as well, if not specified the system date.
The method returns a utcdatetime value.  You can use DateTimeUtil::Date to extract the date part.
Only DateTimeUtil::getSystemDateTime compensates for the time zone of the user.
Remember there can be a difference in date and time between server and client.
From application point of view, when establishing business logic, this method is the natural choice.

This last method is considered best practice in Ax.

In code, the different options:

static void WhatsTheDate(Args _args)
{  ;
   info(date2str(today(),123,2,2,2,2,4));
   info(date2str(systemdateget(),123,2,2,2,2,4));
   info(date2str(DateTimeUtil::date(DateTimeUtil::getSystemDateTime()),123,2,2,2,2,4));
   info(date2str(DateTimeUtil::date(DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::getSystemDateTime(), DateTimeUtil::getUserPreferredTimeZone())),123,2,2,2,2,4));
}

Conclusion: Use DateTimeUtil::getSystemDateTime, unless you have very good reasons not to.

In a following blog post, I'll dive deeper into the functions available with DateTimeUtil, with different timezone support and user-related timezone settings.

DateSeparator: What's missing in the documentation of date2Str

The function date2Str provides a good way to convert a date to a string in Dynamics Ax.
The string output can be controlled, you can specify the notation with different parameters.
It goes something like this:

   str date2Str(
    date date,
    int sequence,
    int day,
    int separator1,
    int month,
    int separator2,
    int year
    [, int flags = DateFlags::None])

In an example:

static void Example_date2str(Args _args)
{   ;
    info(date2str(systemdateget(),123,2,1,2,1,4));
}

But the documentation lacks a bit of details around the different separators to use.

The different options are all listed here, but the actual values to use in the date2Str function are omitted.  The function requires an integer (or enum) as parameter for the date separator.
And not a char or string, like "/" or "-".
You can use the enum DateSeparator for this.  This enum has following enumerations:

Name	Value	Description
Auto	99	Auto
None	0	None
Space	1
Dot	2	.
Hyphen	3	-
Slash	4	/

This part of the documentation got updated with the newly released Ax 2012 version.  So the missing info only applies to versions 3.0, 4.0 and 2009.  (Like you can find both in Ax help system - see screendump above- or online at MSDN over here.)
Same example as above, but less cryptic:

static void Example_date2str(Args _args)
{   ;
    info(date2str(systemdateget(),
        123,
        dateDay::Digits2,
        DateSeparator::Space,
        DateMonth::Digits2,
        DateSeparator::Space,
        DateYear::Digits4));
}

Good to note here as well is that there is another way of setting the notation: Use the regional settings of the user.
For that, just set all the formatting parameters to -1.

Like this:

static void Example_date2str(Args _args)
{   ;
    info(date2str(systemdateget(),-1,-1,-1,-1,-1,-1));
}

Dynamics Ax actually has a predefined function for this in the Global class, being date2StrUsr.

static void Example_date2str(Args _args)
{   ;
    info(date2strUsr(systemdateget()));
}

This function depends on date2Str, just like in the example above.  From the Ax source code:

static TempStr date2StrUsr(date transDate,int flags = DateFlags::None)
{
    return date2str(transDate,-1,-1,-1,-1,-1,-1,flags);
}

Happy New Year!

Thanx for reading my blog, now and in the future.  Wishing you all the best for 2012.

Best wishes for 2012

A big thank you for the nice comments as well!

Dynamics Ax and the cloud - Part 2

In the last blog post we talked about Dynamics Ax and the cloud.  And the fact that Microsoft's latest release of Dynamics Ax, Ax 2012, is not the cloud version (some hoped for).  Is it a sign?  Should we be unhappy about that?

Microsoft already has some cloud solutions up and running, focussed on business users.  With Office 365, CRM 2011 for example.   With  varying degrees of success, on all levels.
Take availibility for example: Customers of Microsoft’s Business Productivity Online Services (BPOS), with hosted Exchange and Sharepoint solutions, had to work without mail for a few days after a MS upgrade mid 2011.  In August 2011, it was MS CRM 2011 and Office 365 which suffered from outages. And again.
Every IT department has to deal with downtime one time or another.  But when an IT department serves thousands of customers with an online solution, it's high profile, big news.  And Microsoft can hopefully learn from these experiences and improve them, for an Ax cloud release later.

Another level where Microsoft has not reached the desired success (yet) is the propagation of their cloud solution platform.
Microsoft Azure platform has been around for some time now.  And strangely enough, the critics are more or less aligned on this one: A good solid offering, but MS did a bad job on marketing it.  And that's strange for a company like MS, as that has always been one of their key strengths in the past.

But it should be clear that even with recent takeovers from competitors SAP and Oracle this does not mean that Microsoft is running behind, on the contrary.  Microsoft has put its pawns on the chessboard and is (almost) ready to play.

And what about any other players than the big three? 

Google, the household name when it comes to the cloud, isn't free from glitches either.  It would also be interesting to see if they can increase the acceptance of their business solutions with business users as well.  Google depends heavily on their advertising model.  For that, they go through everything you do on the internet: all your searches, all your mail, ...
How is Google going to convince companies that their business critical data is stored safe with them?

Maybe the real threat comes from another familiar name, albeit maybe a bit unexpected here: Amazon.
Amazon, still a relatively young company, made it from an online book store to a software company.
While Amazon did not escape the run of outages in 2011, at least they communicated clearly about them.  Always.
They seem to understand the need of information of the users in such cases, in order to uphold confidence of the community in their solution.

When Denmark based ProISV, a software developer focussed on ISV's (Independent Software Vendors), released their Ax Cloud solution, they made the link with Amazon.  (Betting on two horses actually, as they also work with MS Azure.)

Maybe IBM can become an important player here.  They have the servers and infrastructure experience, they have made the transition to a services company as well.

Conclusion
The software business is on the move, to the cloud.  We gonna see lots of announcements and news stories focussing on ERP in the cloud in 2012, without doubt. 
As details of the next major release of Dynamics Ax (version 7.0, post Ax 2012) will gradually become available in 2012, it will also be exciting times for Ax customers.

Did Dynamics Ax already make it to the cloud?

SAP is the latest to jump on the cloud bandwagon with their aquisition of SuccessFactors, an established player in cloud offerings.  First weekend of December 2011 it was announced that the deal was worth $3.4 billion, not a minor expense.  Last October we saw this other big ERP player Oracle buy RightNow for $ 1.5 billion.  Earlier in October, Oracle unveiled Oracle Public Cloud. That's when they publicly entered the cloud arena, with an 'Enterprise cloud for your business' as Oracle states it.
You can argue whether they are all just hosting solutions or real cloud solutions.  But it's clear these contenders are more then willing to link their corporate names to the cloud.  Cloud isn't about consumers anymore, it's also about enterprises.

And what about Microsoft?  And Dynamics?  Where are they?

Well with Dynamics CRM 2011 already has a cloud offering for one member of the Dynamics family of products. When it was released January 2011 the online features were a key selling point.
Dynamics NAV 7 has been postponed once or twice.  Currently slated for Fall 2012, it expects to be  run on the Azure platform.  And the client to be a webbrowser, whether NAV is running on premise or in the cloud.

At Convergence 2011 in Atlanta, Steve Ballmer already announced all members of the Dynamics family are or will be engineered to "deliver the full benefits of the cloud".  And that's with focus on Azure, Microsoft's cloud development platform.

And the recently released Dynamics Ax 2012, is that a cloud version?  The answer is 'no'. 
During the virtual launch event in August 2011 Hal Howard, corporate VP of ERP at Microsoft, already stated that Ax 7, not AX 6 will be the first real Ax cloud offering.  He repeated that statement in his keynote at the Dynamics Ax 2011 Technical Conference in Nice. 
So Ax is not the first, not even the second to make it to the cloud.

Should Ax customers and MS partners be unhappy about that?  More about that in the next blog post.

Error: Cannot create another system semaphore

When trying to start the Dynamics Ax AOS service, the attempt fails.
You may receive following error message:

Error: Cannot create another system semaphore

When you check the Windows event log, you may come across following error:

Fatal SQL condition during login.

Probable causes:

1. The SQL Server service is down, verify this first.
2. The account that has been setup to start the AOS service, does not have the proper security rights for the Ax SQL database.

Regarding number 2:
If you copy your database from one environment to another (from Production to Test for example), make sure you have the right security setup in place.  Does the startup account used for the AOS service have access to the SQL database?

Once the account has access, make sure it has proper access.
If you don't want to give it the db_owner role, make sure at least following roles are assigned to the service account

• db_ddladmin
• db_datareader
• db_datawriter

Also make sure permission to execute both stored procedures is in place.



After you verified the above, you should be able to start the AOS service.