Compare Modelstores using tableDiff Utility

Did you know SQL Server 2005 was shipped with a tool that makes it possible to analyze differences between two tales? This tool is called tablediff.

The reason I mention this is because I had to quickly check two Modelstores where there were some differences I didn't expect to find. My Modelstore-import failed and the reason seemed to be some conflicting elements.

I was thinking about just writing a SQL query to look it up. It would have involved setting up a Linked Server since these Modelstores where on two different SQL Server instances. While I was considering my options, and searching the web for possible options, I came across this old tool. I remember seeing it before, but never had the use of it. It seem to fit the bill perfectly, as all I was interested in was comparing the table ModelElement for conflicting elements.

Basically, it is a command line utility that ships with SQL Server, and you can run it with parameters for source, destination and some additional options. For details visit: http://technet.microsoft.com/en-us/library/ms162843.aspx. One of the benefits is its performance, and the ModelElement can contain a million rows or more. In my example, the comparison took less than 30 seconds.

The command I used was this:

tablediff.exe -sourceserver sql2012\test -sourcedatabase ax2012_test_model -sourceschema dbo -sourcetable modelelement -destinationserver sql2012 -destinationdatabase ax2012_prod_model -destinationschema dbo -destinationtable modelelement -et diff1 -f c:\compare.sql -c

The output file contained SQL for updating the destination, but I was only interested in the Query itself. Investigating the output file, searching for the string "update " (trailing space after the command), I found the dozen elements that was causing the conflict. I could then further list the ElementHandles in a new SQL where I would list them out to see what elements they were. Example:

SELECT ET.ELEMENTTYPENAME, ME.* FROM MODELELEMENT ME
JOIN ELEMENTTYPES ET ON ME.ELEMENTTYPE = ET.ELEMENTTYPE
WHERE ME.ELEMENTHANDLE IN (860734,860739,860746)

The utility is installed if you opt for installing the Replication Features, and will be installed normally under C:\Program Files\Microsoft SQL Server\100\COM\

I might wrap a PowerShell script around it later, but not today.

Remember to upgrade the modelstore schema

I was preparing for an RTM to R2 upgrade the other day and while I was just making sure the RTM was prepared and ready for being upgraded I noticed an error I haven't seen before. As soon as I opened the About-dialog, the SQL Server would throw an error.
Here is an extract of the error:

"FASTFIRSTROW" is not a recognized table 
hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE 
function, ensure that your database compatibility mode is set to 90.

A quick search on the net and I found out others had seen this error too. Since I was doing this upgrade on a fresh installed SQL Server 2012 I was now bit by a deprecated keyword "FASTFIRSTROW". This hint is now replaced by FAST n (TechNet).

Easy fix was simply just to update the modelstore schema. Apparently, whoever upgraded this RTM didn't read the CU3 instructions properly. One of the necessary steps in this upgrade was to reinitialize the schema. You can basically reinitialize the schema whenever and as often as you like. You will not lose your modelstore data - it is just a schema update. Not every CU contains schema changes, so read the upgrade instruction on partnersource when doing an upgrade.

My preferred way of updating the schema is this simple PowerShell command:

Initialize-AXModelStore -Server MySQLServerNameAndInstanceName -Database MyModelStoreDatabaseName

When I think about it, I would like the upgrade software do this initialization as part of the upgrade. From the top of my head I can't see any reason why not.

Setting up scheduled processing of cubes in AX2012

Here is a quick guide on how you can easily and quickly setup scheduled processing of the cubes for Microsoft Dynamics AX 2012. The same approach works for other Analysis Databases, but sometimes it helps to put things in context in order to make it easier for you AX geeks out there.

I'm assuming you have setup and configured the standard Dynamics AX Analysis Database. Preferably also run a manual processing and analysed any errors or warnings. My example is from AX 2012 R2 on SQL Server 2012, and I just want to have it process without any error logging.

Start off with logging on to the Analysis Instance using SQL Server Management Studio. Find the "Dynamics AX initial" OLAP Database. If you have created multiple partitions, they will have their separate OLAP Database.

Right click and choose Process to open the processing dialog:

In the process dialog click the "Change Settings..." button:

A new windows will pop up. Head over to the tab "Dimension key errors" and click "Use custom error configuration". Choose to "Ignore error count" and define error conditions to "Ignore error". Here you can opt in for "Report and continue" and also have any issues be reported to a Log file, but do a test and pay attention to the Log filesize. It may grow rapidly, and even become several GB. 

Press OK, but don't start the processing. Now with the current setup, choose to Script the Action to a New Query Window:

The Action will be scripted as a XML structure. We will now use this command in a SQL Server Agent Job.

Head over to SQL Server Agent and create a new Job:

On the section for Steps, add a new Step:


The new Step needs to be of type SQL Server Analysis Service Command. It will be run under the SQL Server Agent Service Account, so make sure this service account has Read access to the Dynamics AX Transaction Database. Paste in the XML command from previous steps and save the step by pressing OK:


Now you can chose the Schedule section and create a new schedule for this job. You may chose to have it process on a daily basis, or multiple times per day. Choose whatever is suitable for your needs.

Finally, do a test of the Job and make sure it runs through. If it doesn't, use Job History to find the cause of any errors. Feel free to comment or ask, if you get stuck.

Thanks for reading!

Create a new Baseline Database for AX2012

The Baseline database in AX2012/AX2012R2 is optional when installing the database(s). During installation of the databases, this is the only point it gets installed. So what if you need to setup a Baseline database after having installed AX2012? If you have a Baseline database laying around, you could restore it - and hope the schema is valid for the version of AX you are working on.

There is another easy method. :-)

Start by creating a new database, an empty database. I'm giving it around 2GB of space in my example.

Set the recovery mode to Simple. You don't need point-in-time recovery for this database.

Then run the PowerShell command Initialize-AXModelstore on the database.

Observe the database now has the elements required for a modelstore.

Make sure you add the necessary users with proper access, so they can use the new Baseline database.

Finally, head over to the AOS Server Configuration and choose the Baseline database.

That is it. :-)