Totzkeeeeee's Blog

Microsoft's Navision Attain ERP system is not the best in the world but for the SMB crowd it's a pretty solid offering with a lot of functionality.  It's very easy to customize (and we've taken full advantage of that but that's a tale for another time) and quite user-friendly.  The user interface is highly responsive and easily navigable with the keyboard.  Anyone doing significant data-enty work knows the importance of that.

Where Navision starts to fall down though is on the back end.  The C/SIDE database has some interesting features but it fails miserably when it comes to scalability and performance.  C/SIDE only supports locking at the table level.  I'll repeat that.  Table level locking is as much granularity as you get.  At most we have 70 or so concurrent users and during the busy hours of the day we have people getting blocked.  Only about 15 or 20 of these users are actually doing data entry for new orders.  A well designed Microsoft Access application can handle that for crying out loud!  We don't have a really huge database either.  It's currently sitting at sixty gigabytes with about forty gigabytes of data in it.

This brings me to the next issue.  Navision supports SQL Server 2000 as a back end datastore.  Fabulous.  It's has to be at least marginally better.  The migration path couldn't be simpler.  Backup using the C/SIDE front end and then do a restore into a SQL Server 2000 database using the SQL front end client.  Simplicity itself.

Or so one is led to believe.

The first challenge is that C/SIDE doesn't seem to care what date you enter so long as the format is correct.  The date 04/09/0002 is perfectly acceptable.  Nevermind that the Gregorian calendar didn't start until 1753.  SQL, on the other hand, has a problem with this data due to the fact that time doesn't exist for it prior to 1753.  This becomes painfully apparent during the restore to SQL Server.

This is obviously a known issue and we are most definitely not the first to attempt this migration.  There is a code unit within Navision that will search all date fields in all tables looking for dates that would be considered invalid on SQL Server.  The authors of this code unit even had the foresight to loop through all fields in all tables and build a list of fields to test so as to trap customizations.  Pretty slick.  It takes nine hours to run the date checker.  Not the fastest process but not so long as to cause anybody any real grief.  Fix the dates in the resulting list and then feed it into a dataport and it uses the information to update the invalid records.  Simplicity itself.  Ahh, the sweet bliss of ignorance...

The restore was running smoothly and was approximately sixty percent complete when suddenly the client program became unresponsive and the hard drives started thrashing like harvest time on the prairies.  The optimist in me thought that it might have stopped to build indexes or something before continuing; the cynic knew better.  The restore was obviously rolling back the transaction log and about an hour later my suspicions were confirmed.

Once the thrashing subsided and the smoke cleared I was treated to a lovely little dialog with the following information:

---------------------------
Microsoft Navision Attain
---------------------------
The Purch. Rcpt. Header table contains a field with a date value that cannot be used with SQL Server:

Record: No.='PR12345'
Field: Pmt. Discount Date='04/09/0002'
---------------------------
OK  
---------------------------

Well, actually no, it is not “OK“ but what choice does one have?  It seems that a single record slipped past the update routine that was supposed to fix them.  Turns out that the date was bad in the spreadsheet used for the dataport to fix them.  Somebody is getting a beating, but they'll survive. 

Here's what really bugs me about this:

1. Why not tell me the error first, and then roll it back?  Why make me wait so long to find out something has gone wrong? 
2. This is a FULL RESTORE.  Why in the hell is it rolling back a transaction?!? 

A database restore is an all-or-nothing proposition.  If it fails for any reason, just truncate the damn tables or, for that matter, drop the whole freaking database and get on with it!  Remember, this is Navision code that is running here not SQL Server.  They could have implemented this anyway they wanted. 

Ok, back to the beginning.  Did I mention that doing a backup using the Navision client application takes about THREE or FOUR hours?  So, having fixed the bad date we kick off another backup of the database to prepare for the next test restore into SQL Server.  BTW, this is running on some pretty decent hardware.  Dual Xeons with Gigs of RAM and a fibre-channel attached EMC SAN with lots of lovely 15K RPM SCSI discs in a stripe set.  Zooooom.

The backup finishes and I kick off the next test restore with renewed optimism.  “That bad date was just a little anomaly. ”  I tell myself.  Yes, it was; and it's got friends.  At the exact same point in the restore (about 2 or three hours or so into it) I get the same hour or so of thrashing followed by the same message for the exact same record only for a different field!   At this point I am beyond violence.  Wanna know why GOTO is such a hated flow control statement in the programming world?  It's because it's usually used like this:

10 START LONG_PAINFUL_PROCESS
20    DO_STUFF()
30    IF DO_STUFF() DIDN'T WORK THEN 
35        UNDO_STUFF()
40        GOTO 10
50    ELSE
60        GO_HOME_AND_RELAX
70    END IF
80 END LONG_PAINFUL_PROCESS

At this point in our story, we're at line 40 for the second time. 

We fix the bad data in the record and check ALL the other date fields on that record.  We also discover that the original source file used to correct this particular record had a bad date value in it so we check ALL of the other items in this file and find that it's otherwise clean.  Now we're getting somewhere.

Or not.

So back to LINE 10...20...30...35...CRAP...

...or something to that effect.  It also gave me record and field information but I can't find that right now. 

Now you may be wondering, as I was, WTF?  Navision controls the data that goes into its tables.  It chooses the datatypes of the fields in the new SQL Server database.  How can this possibly happen?  A little investigation reveals that in the object designer, you can specify a SQL datatype for the column even though you are not using SQL right now.  The conversion routine picks this up and uses the information to create the tables in SQL Server.  Pretty convenient n'est pas?  In a word.  No.

In theory this is a pretty handy feature.  Allows you to control the datatype of fields so that if you only have numbers in there, you can ensure that it's an int or whatever when you get to SQL.  The problem arises due to the fact that Navision will not enforce this when using the native C/SIDE database.  Navision has some funky datatypes and for the most part everything is pretty much just text.  If I set the SQL datatype for a column to int, I can still put ONE or Forty-Seven or “My God what are you trying to do to me!!” and Navision won't complain.

SQL, on the other hand, only wants numbers in an int field.  I know.  It's a pretty radical concept but it's true.  :)  Thankfully there was only one field in one table that had the SQL datatype defined.  It was marked as int and we had text data in the field.  Somebody was screwing around.  We change it back to <undefined> and then the conversion will pick the default datatype - whatever that is.

And so, again we go LINE 10...20...30...50...60...70...80   WOO HOO!  It actually worked.

I'm going to spare you the rest of the story but suffice it to say that we are still running on the native C/SIDE database and have abandoned our quest to move to SQL Server.  Believe it or not, performance - our primary motivation for attemping this conversion - was dismal.  Really, really dismall.  I know what you may be thinking but no - there's a SQL Server specific client application.  it's fin.exe for C/SIDE and finsql.exe for SQL 2000 backend use.  So it's not the client.  Our Navision “partner” told us that it requires “tuning”.  Ya, right.  At $1400 a day? No thanks. 

In the last few months - I've been working on this article for a while now - we've managed to optimize a few things including a bunch of fixes to the crap customization code that our “partner” wrote for the original implementation.  Performance is now “acceptable” at best and the work continues.

I hope that my sharing this experience will either convince you not to do this migration or at the very least give you a heads up as the types of challenges you are going to face.

Dave
Just because I can...(which in this case, doesn't mean that we should)