Totzkeeeeee's Blog

Just because I can...

  Home  |   Contact  |   Syndication    |   Login
  216 Posts | 4 Stories | 345 Comments | 321 Trackbacks

News


My blog is worth $14,678.04.
How much is your blog worth?

Tag Cloud


Archives

Post Categories

Blog Roll

Cool Sites

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...

---------------------------
Microsoft Navision Attain
---------------------------
Invalid Value for Cast Specification

---------------------------
OK  
---------------------------

...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)

 

posted on Monday, October 4, 2004 2:15 PM

Feedback

# re: Navision Attain is David's Bane 5/27/2005 3:55 AM Stefano Demiliani
Yes it's not so simple the migration from native database to SQL Server, but SQL Server version is a necessity if you have lots of users and your db is really big.
We're a Navision partner and we work only with the SQL Server version at the moment. Performances are good and we have customers with more than 500 users. Obviously, the code must be checked well and optimizations are a must.

# re: Navision Attain is David's Bane 6/1/2005 8:20 AM D2
Had a laugh at your account of the migration - went through the same thing myself. Those #%#%# dates...I fixed mine the old fashioned way - one at a time, in the offending tables.

Since the migration we have enjoyed the open DB backend to utilise 3rd party reporting tools. Performance is acceptable and blocking has reduced.

# re: Navision Attain is David's Bane 6/12/2005 8:08 PM Mark Rosenberg
I am working with a company with about 200 users on Navision. the VAR has done a poor job of implementation over the past 2 years and the cost has been unacceptable at this point. Poor performance. Nightly routines taking way too long. massive customizaiton with no documentation and very buggy. It is a SQL implementation and we are running a test for c side to see if that helps any of our many issues. It seems Navision comes with a lot of issues, mainly due to its arhitecture and especially if you are running in a large transaction, heavy data volume environment. If you are a skilled Navision professional in California (Southern cal would be best), please email me at mrosenberg@k-a.com. Thank you.

# re: Navision Attain is David's Bane 8/11/2005 10:04 AM Guest
No CRM system is without its faults. As with all software, its impossible to envision the demands 10 years down the road. The end-user will almost always up the ante with requirements. How well the system was designed in the beginning will prove how well it is in the end as it grows beyond the first set of requirements. I must say, Navision, does this quite well. So there may be a few migration hurtles to overcome. Having a well constructed plan the migration helps relieve possible headaches. In the end, there are hundreds of satisfied users of Navision.

# re: Navision Attain is David's Bane 1/27/2006 11:41 AM matt price
Hi David,
I am an IT Manager for a company that is running Navision 3.6 and will soon be upgrading to 4.0 this past summer we had a simular problem with cside to SQL but we did get it finished. Less blocking going on but still a few deadlocks does anyone know of good tools to tweak SQL for Navision?



# re: Navision Attain is David's Bane 5/20/2007 12:56 PM Scott Frappier
David:

I'm sorry to hear about all of the pain that you have gone through in order to convert your database to SQL Server. I thought I'd submit a post here just in case it never did work out to inform you that there are a couple of VARs (Value Added Resellers) in the United States that assist customers with C/SIDE to SQL conversions within the Dynamics NAV environment.

The company I work with, Symbiant Technologies, has been assisting high-end database conversions from C/SIDE to SQL Server for quite some time now. We have custom date-check utilities and other useful tools that makes the migration process less risk intensive. We have focused on this area as we have one of our customers that is the largest in the United States with 500 concurrent users in Dynamics NAV.

Anyways, feel free to contact me if you have any other questions, and if any other posters have questions, feel free to email as well.

Thanks for your time...I hope everything worked out!

Scott Frappier
Vice President, Deployment Operations
Symbiant Technologies, Inc.

651.379.8819 (Direct)
612.834.0533 (Mobile)
sfrappier@symtech.us

# re: Navision Attain is David's Bane 7/9/2007 7:39 PM David Singleton
Hi David,

its always disappointing to hear stories of woe like this. I would love to be able to say that you are wrong, and it never happens, but unfortunately it does.

My company www.go-live.us specializes in fixing Navision implementations that go wrong so I do see enough bad implementations. But I do know that it really is only a small percentage that go bad. Which is little comfort to you when you are one of the ones that went bad.

Its rare that we find a bad implementation due to Navision, but normally due to how it was implemented. As you have found, Navision has its quirks, and many partners get out there and implement without really knowing the right way to do it.

Anyway I just wanted to let you know that there is a large group of Dynamics Freelancers out there that can help.

# re: Navision Attain is David's Bane 11/20/2007 2:52 AM Raji Rasheed
Hi,

I am currently running Navision 3.6 with sql option and my database is 85gb, i am experiencing lock many times, please advice on how to improve performance.

What is the hardware requirement for this.

# re: Navision Attain is David's Bane 5/17/2008 12:25 AM Bill
My question is pretty basic. I am relatively new to Navision and need to know how to important a signature image for the printed checks so every check does not have to be manually signed. Any help is appreciated.

Comments have been closed on this topic.