Should I move my move Dynamics NAV Server from C/SIDE to SQL

Should I move my move Dynamics NAV Server from C/SIDE to SQL

It used to be quite rare for an NSC so suggest that their client go-live on
SQL. SQL has always had its own issues, mainly performance and cost. In the past
the major drive for SQL has been from a client's specific need. Of course as SQL
gets faster, and with Microsoft's influence a lot of this is changing. Today we
already see a lot of Navision End Users go-live directly on SQL. But what if you
are currently on C/SIDE and you are considering moving to SQL, what influences
that decision.

If you are thinking of switching to SQL, then the first question is "why?". For sure you are going to need not only the new SQL CALs, but also a new server. Unless you planned for it in the
beginning, then its pretty unlikely that your existing Native Navision server will be suitable for SQL.
You are also going to have to re-visit your server maintenance issues.

In the past, the drivers for SQL have been something like :
Connectivity: the client needs to access the Navision data, but don't want to invest in C/FRONT applications, and ODBC is just not going to make the grade. Thus a SQL interface makes sense.

with SQL you can always "throw hardware at the problem" thus its very simple, (costly but simple) to scale up your SQL system for very large databases (100G+) or many users (200+) that is not so easy to do in Navision.
In House Resources: many companies already have SQL developer resources in house, and
believe (often wrongly) that they will thus be able to do all, or at least a major percentage, of their
development in house, thus substantially reducing the cost of the implementation. Of course these "resources" then end out spending most of their time learning the Navision Database structure, and since they can't use the standard Navision code (reports especially) they often don't bother, and start from scratch. In very big
implementations this definitely pays off in the long run, but there needs to be a lot of reporting modifications to justify this.
And most importantly; a well defined long term set of goals for the system.
We use SQL: this was by far the major driver for Navision originally releasing the SQL option. Many clients simply had a tick box on their system spec that said SQL Yes/No, and if NO, then the product just was not even looked at. Navision lost a lot of sales for this reason.
Already have a SQL server: is often quoted by potential Navision clients as a reason. i.e. they will save money because they can use existing hardware and they
already have the CALs. Yes its often a good reason, but generally the cost of an entirely new Navision server will be less than just the cost to beef up the existing SQL server to Navision specs, especially if the server is running a ton of other stuff. (And its probably on a SAN [:'(] )

A few years ago, the Navision Native database was clearly the best Navision solution, unless you had a specific requirement that Navision could not handle, so previously it was very rare for a
client to need to move from Native to SQL. Generally it was because of a company ownership change, or a new requirement to interface to some other software.

Today with the performance improvements in 4.00, the difference becomes less clear. Of course if Microsoft were to rewrite the Base app to take advantage of the new executables, the grey area would be even smaller, but that is some way off

But ignoring even that there are now new reasons for moving to SQL that did not make sense a year ago. Version 5 will
separate Navision more clearly into Native vs SQL, since for the first time in its life, Navision will have
basic functionality that will work on SQL, but not on Native. There is not technical reason for this, just marketing, but
that's how its going to be anyway. Clearly Microsoft want us to move to SQL, and
so long as they can get us a SQL version that is as solid simple and powerful as
C/SIDE, then that will be fine.

Unfortunately the move to SQL needs changes to the application. In the past
this has been done by keeping C/SIDE as it is, and using workarounds for SQL.
Well logically that will start to change. Either the application needs to move
more to a SQL structure, or they need to develop two applications separately.
And when we already have NAV, AX, GP, SL, CRM etc, I can not see them wanting to
support one more code set. So logically further development has to mean a demise
of the Native application. I see this to involve three things:

Firstly the client needs to interface to SQL fully, that means using all the
features of SQL, and getting everything off the client that can be done in SQL
instead. In reality this is more a change to the executables, and dramatic
performance gains can be made without affecting the code you have in your
current system. Hopefully we will see such changes in 5.

Second we need to address locking. ERP applications lock totally different to
airline reservation and stock market systems, which most databases were designed
to handle. ROW locking has very limited benefit in an ERP environment, and
Navision's Version principle with Optimistic Concurrency probably handles the
locking requirements of an ERP system better than most other types of locking.
Unfortunately SQL is not going to be rewritten to support Versioning, it is
designed around a roll back system, and it will stay that way. This means that a
lot of the basic posting principle in Navision need to be changed form their
current Optimistic Concurrency locking to ROW locking, and this means big big
changes to the application. So much that we take for granted in Navision will
just have to be taken away if (WHEN) the Application is redesigned around ROW
locking. As a side note, keep in mind that many new features added to the
base application were never really supposed to work in the Navision app. The way
that locking is built around Dimensions for example looks like it was thought
out with ROW locking in  mind, even though the Database does not support
row locking. So many of the locking issue we currently see in Navision are not
because of the Optimistic Concurrency, but due to poor code design.

Thirdly, and probably the most important thing is Sum Indexes and Flow
Fields. Every Navision developer takes for granted that a user can change
accounting periods on the fly, or report ad hock any data that has been
appropriately indexed. And even if we may have issues with locking during data
entry and posting, we know that with Sum Indexes, we can get any data we want
reported very fast. (Lets not go into Inventory Valuation, because its a very
poorly built report). With the Version principle, there is no locking on tables
to calculate flow fields, and most importantly the maintenance of Indexes is
handled natively, so they are very fast, but we all know that they are a problem
in SQL. So either we see FlowFields and Sum Indexes native to SQL, OR we see the
application rewritten to suit the Server technology that we have. We know which
way that will go.

Basically what I am trying to imply here, is that eventually it will make
sense for most users to move to SQL, and one day we can all expect the end of
the Native C/SIDE server. It will be many many years away, but it will come.

So lets now look at moving, i.e. you are on C/SIDE and you want to decide if
you should switch to SQL.

Well if you are on C/SIDE, the system works fine and you are happy with what
you have, then just stay where you are. Don't just switch over to switch over.
There needs to be a change to your business that would make you want to move.
OK, so lets say you have a reason to move to SQL, but are still not sure if you
should move over, lets look at the move over. Starting with the problems.

If you change to SQL, then by far the biggest internal issue for an end user,
is going to be SQL maintenance. Once Navision Server is up and running that
really is it. You have your daily backups, you do a restore 2 or 3 times a week
to confirm that you have a backup. You have a plan that allows you 24 hours down
time whilst a new server is brought on line if the system fails, and a procedure
to reenter the lost days data, so all is fine. Not so with SQL. Once you move to
SQL, you need to have some one that will maintain the server. In this case, your
best bet (at least for the first 6 months or so) is to contract with your
Dynamics VAR (NSC) to come in on a regular basis (once a week in the beginning)
to do the maintenance for you. But eventually you are probably going to want to
get someone in house SQL certified, other wise it will get very expensive. Don't
get into the idea that you will just have someone come in if you have a problem,
SQL needs regular maintenance.

So you now have addressed the end users biggest issue, i.e. maintenance, and
now we look at the system. The biggest issue in the conversion, and possibly the
biggest cost, will be matching the application to SQL. If you told your NSC from
day one that you eventually plan to move to SQL, then most probably all the
development was done with this in mind, and the code will all be optimized for
SQL. Unfortunately even if this is so, once you actually move to SQL, you are
going to find some code that still needs to be optimized. Now if the coding was
not optimized for SQL, then prepare for a lot of analysis to get it all right.
By the way, if you had one huge system design, that was developed and
installed, and then made very few system code changes since, then your system is
probably going to work well on both databases. Since the NSC will have planned
everything in advance. But if your system was basically built on the fly then it
was most likely optimized to work on the database it was running on. Though
since that method is so much cheaper anyway, your net cost will still be lower.

Be prepared to do a lot of testing of the system on SQL with active users and
identify locking issues that need to be fixed. One piece of advice (which
NSCs/VARs may not like), I would recommend getting a third party analysis of
your system, since its often very difficult politically for an NSC to tell you
that there are chunks of code that need to be re-written.

Now the hardware. It really is not worth even asking your NSC if they can
"beef up" the existing Navision Server to suit SQL. The cost of their time is
more than you would spend on a new server. So just budget that if you move to
SQL you will buy a new server.

SQL tools and reporting. Don't plan on doing any SQL reporting or accessing
the data directly through SQL, until the whole system is converted and running
fine. Keep in mind that if things don't work out you can always switch back to
Native, but once you are reliant on SQL you can't, so make sure it all works
BEFORE you start using SQL reporting services or writing that new interface to
your other system.

New backup procedures. Although you can just use your same recovery model
that you have been using with Native C/SIDE, SQL offers so much more, so it
makes sense to redesign the whole thing with your Dynamics Partner to get the
best out of SQL.

This of course is not a SQL conversion plan, its just to give you something
to think about and discuss with your Dynamics Partner. If you feel that a Three
Tier system is for you, then you will have to get onto SQL, and in that case I
would definitely recommend talking to your Partner now, because when 5 comes out
they are going to be very busy, so get in now.


Get Rich Quick With Microsoft Dynamics

I got hit by my first 419 scam back in 1990, via fax. In those days it was
still relatively new, so I made an effort to find why someone wanted me to share
25% of their 50 million. Without Google, it was quite a task to find out what it
was all about, but we worked it out eventually. In those days it was much
simpler than it is now, then you just arrived at the airport with $50k, and
either gave it to the thugs, or got shot on the spot. Now a days the killing is
too messy, so they use the internet instead.

Of course these days it seems that Nigerian billionaires die in their hundreds
everyday, and forget to write a will magically leaving $125,000,000 (ONE HUNDRED
AND TWENTY FIVE MILLION DOLLARS ONLY). And of course I win countless millions
each week in the Euro Millions lottery. But still I am curious, and if something
comes along with a new twist, then (with Google’s help), I make a bit of an
effort to work it out. I guess its my version of Sudoko Wink

So lets get back on topic.

Over the years, I am sure most people know that I offer help to those that want
it, and I don’t mind if the knowledge I impart is used for commercial purposes,
since in the end that’s what Dynamics NAV is all about. But get rich quick
schemes are just not my thing.

Over the last year of so, just about all the Dynamics related Forums have been
hit by posts asking for answers to exam questions, in fact two new ones started
in the last year or so, that seem to be dedicated to just this. I have been
against these types of posts for some time, and I get a lot of people saying
“but we should help people to learn Dynamics”, and in this I agree, and when the
question is something like:

“I just sat the Dynamics NAV xyz test, and there was a question about Inventory locations, could someone explain the difference between abc and def”

then I feel that the person had
problems, and wants help to a/ pass the test, but also b/ know what is going on.
We used to get posts like that back in the NOLUG days 10 years ago, and the
discussions lead to a healthy learning environment. But the current generation
is something different. I regularly get IMs and Emails where people just start
generally chatting about Navision, then all of a sudden hit me with a series for
very specific questions, that could only be so exact if they were copied during
the test. … Well now its getting worse, I am now getting screen shots from the
tests that look like they are taken with a mobile phone.

So the next thing is to figure out “why would someone go to take a test only for
the purpose of copying down the questions?” surely it makes sense with the free
second shot to go home, learn what you didn’t know, then go back again. After
all, if you don’t know the answer, and you want to be a Dynamics professional,.
then you are going to need to learn it some time. So with some help from Google,
its easy to work out what is going on. So for those of you that are interested
in getting rich quick with Dynamics (or any other product that is certified by
multi choice tests).

Sorry, again I am off topic, so here is one way to get rich quick with Dynamics.

Step 1/ First you need to start a web site, call it something like
set up the site to say you have a
money back guarantee for passing Dynamics tests, and add a page that lists all
the Dynamics certifications, and link each to a new page. Then set up internal
counters, to see how many unique IPs hit each test. On the exam page that opens,
put a message that says : “This exam is currently being processed, please enter
you email address to be notified when it is available, or check back at the site
regularly” it really does not matter, all you are doing right now is getting
stats to see which Exam people are most interested in.

Step 2/ Now you need a company with a few English speaking
people that have basic internet skills, India is the best place to look. Now
once you have some stats from your web site, work out which Exam to do first.
Have one of your “team” enroll in the test. Give them a cell phone with camera,
or some other method of recording the test. Send them to the test, and have them
return the test questions. Don’t have them waste time answering questions, make
sure they just get the list of questions.

Step 3/ Use MSN and Google to find all the forums that would have
people involved in these products. a/ Join up in the forums, b/ get a few basic
posts to make it look like you are interested in Dynamics, and c/ get as many
email addresses and IM names as possible. (b/ is optional by the way).

Step 4/ Put together the questions you now have, and post them
on the forums, email them, and IM everyone you can. You will be surprised at how
many people want to help you in your get rich quick scheme, and will post all
the answers for you.

Step 5/ Now that you have the answers, (using your “second test
for free certificate) send the original person back to the test, this time
answering all the questions, so that you can see how accurate the answers are.
You want 80%-90% results, so if the result is low, have someone else do the
process again to make sure you have the correct questions. And remember you are
only guaranteeing a pass, so really you only need to meet the pass mark, though
your clients will always feel better if they get higher marks.

Step 6/ Update your web site to now reflect that this test is
available. People will be skeptical, so allow them to access a sample of the
Exam Questions and Answers. There are two ways, a/ send them a sample by email,
b/ Have them down load a small piece of spy ware that they install, and that
pops up the information. b/ is the preferred method, since people would other
wise use one off email addresses, and you will loose them. The spy ware method
give you better control. 

Step 7/ St back and watch the money roll in.

Sadly enough, the above is not my idea, and in fact its been going on for
many years, and  is very common these days.

I really don’t like having to make this post, but the emails and PMs and IMs I
am getting very angry, to be honest if a person was asking the
questions for themselves I would not mind so much, but getting cheat notes so
you can sell them is just as bad as the Nigerian 419 scams.

Please next time some one emails you 30 abcd questions and just wants the
answers, consider it as a Euro millions email. and add it to your junk folder.
More importantly, please think twice about answering these threads on forums.

As an MVP its very hard for me to to say “don’t help help people in the
community”, but in this case I think its the correct solution. I do not think
that the community needs this.

Dynamics NAV – my first blog here

Hi to all the MVPs.

I am not sure how interested people are here about the Dynamics products, so I will keep my blogs here less technical than I would normally.

My name is David Singleton, and I have been working with Dynamics products for 16 years now. I know that its thought of by most MVPs as “The New Kid On The Block”, but its actually been around a while now.

Looking forward to joining the community,