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

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.

Comment List
  • 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.

  • Thanks David, that was very informative.
    I would like to mention a couple of things.
    I from the SQL side with Nav, GP & RS experience and who has been nagging a client with Nav 2.5 to switch to SQL version for years. I'm a SQL / RS developer and Database analyst but not a Nav or GP developer.

    Firstly, Row Level locking is available in SQL 2005 - whether Navision uses it is another matter!
    Secondly, I think the need for SQL certified staff falls more to the reseller rather than the customer. Having said that most medium sized companies will probably have SQL-capable staff already, especially as Reporting Services picks up momentum.
    Thirdly, I think you have over played the maintenance issue on SQL. Partly because I think SQL 2000 or 2005 does not need a great deal of maintenance if backup routines and maintenance plans are set up properly by the reseller.
    I also think you've missed out the influence of Great Plains on what Microsoft does in the future. GP was built around SQL as far as I'm aware although like Navision, it has it's own development language. But GP uses SQL much more than Navision seems to. For instance with Stored Procedures and Triggers. As there seems to be some cross-fertilisation of ideas between the two camps as they slowly come together, it's obvious that GP's more SQL-centric view will prevail.

    Despite these comments, I thought you presented a well balanced piece even though you are obviously a big fan of the Navision native DB.

    If you want a topic for another article, I'd be grateful for some in-depth clarification on what is the full position on SIFT Flow Fields in SQL. If you have already, I apologise, I'm going to start googling that topic now.

    Chris McGuigan
  • To those following this Blog, a lot of new information has come up from Convergence.

    As soon as I get time, I will write a new version of this post.

    In summary though, the reasons for moving to SQL are now gettng stronger.
  • Thanks.

    Actually its been a work in progress for some time, not really finished, but better posted as is that sitting on my desktop ;)
  • Nice piece of work David!