Minor observation installing AX 2009

Some time back, I did a QA run on one installation suffering some performance issues. The consultant originally installing AX choosed to use the installer to create the database (shown in the picture below). Personally I prefer to create the database manually, both to get control over every aspect and it usually don't take more than 30 minutes to get everything in place manually. The solution at hand was running SQL Server 2008.

While looking into the database configuration (sp_helpdb), I noticed that the compatibility level was set to 90 which corresponds to SQL Server 2005. At this stage, I asked the consultant doing the installation why he had choosen to set the compatibility level to 90 and he then answered that he always used the AX installer to create the database.

Without testing this option when installing AX, it seems like Microsoft has provided a template database beeing attached to the specified SQL Server instance by the installer in the security context of the setup user. I guess the installer asks for some information like collation, file locations etc. but compatibility level keeps untouched.

Compatibility level is a database level configuration option and it is used by the database instance to decide which functionality the database instance should "activate" for each database. It's mostly used to provide backwards compatibility during migration of databases, but it could also impact the ability to utilize new features.

A good reference can be found at MSDN and compatibility level could possibly be a source for some time consuming investigations. It seems like compatibility level = 90 is OK even for an AX database running on SQL Server 2008, but I don't see any reason for not setting it to 100 to make sure all new features in SQL Server 2008 is enabled for the AX database.

Or always create the database manually using the system database model as the template (will in most situations match the version of SQL Server).