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.
Scalability: 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
yet.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.
Hi Chris, sorry I did not see this reply from you.
Anyway maybe its late, but..
Firstly no way i would ever recommend a client to go to SQL on NAV 2.50. Get them first onto either 4.00 SP3 or 5.00 executables.
On the topic of maintenance, we now have a set of tools available from SQL Perform that mean we can now do most of that work in SQL maintenance plans, so you are correct, this is no longer as big an issue as it once was.
In terms of ROW locking, there are very few cases where Navision can take advantage of ROW locking. It was designed around the Version principle and optimistic concurrency, and the Basic Application is designed around that.