Nav native db vs SQL server

nikeman77

Hi guys,

pardon me for asking this questions, but i am still quite unsure of the following:

1.what are the pros & cons of having navision native db & sql db besides bigger storage capacity in sql?
2.how about performance/ security/ backup/ replication?
3.my understanding of navision application server (please correct me if i am wrong presumely), navision house in server where user login remotely (vpn/ remote desktop) to use navision. Database wise it still can be using local db/ sql db.

rhpnt

1. & 2. :The native database is obsolete and is "dying out" - MS enforces the usage of MS SQL Server (NAV versions 4+)
3. NAS is a client without a GUI which main purpose is to automate NAV functionality. It can however be used for other non automated applications such as warehouse-ADCS (wireless data capture)

nikeman77

hi rhpnt,

3. NAS is a client without a GUI which main purpose is to automate NAV functionality.

automate NAV? i dont quite get it, when would be the best place to automate?

rhpnt

There can be lots of tasks in a company that need to be automated for instance - some companies make daily pricelists; because the pricelists are huge and the import has great a impact on the system they need to be imported at a time when the import can have all the resources it needs - night time. So, because there are so little volunteers who are willing to sit at this time for hours in front of the PC to look at the import progress bar slowly moving ahead (except maybe for the security guy) NAS is your ONLY friend. You create let's say a codeunit which imports the sales price file and updates the sales prices in all given items. You set up a job queue task in NAV to run this report every night at a certain time while you get a good night's sleep (or whatever you prefer to do at nighttime). What's best on this story is that people get paid for sleeping. :-$

dans

You can use NAS for job scheduling (Job Queue). If you just need to run the code at specific time, without any GUI, without taking one concurrent user license, then you should use NAS.

rhpnt

dans wrote:

...without taking one concurrent user license,...

That's a bad one. NAS always uses a user session (WIN login).

Sog

rhpnt wrote:

dans wrote:

...without taking one concurrent user license,...

That's a bad one. NAS always uses a user session (WIN login).

yes, but license wise it's a NAS session, not a usersession. (you get one with BRL if I'm not mistaken)

nikeman77

hi guys,

thanks so much for explaning NAS.. bascially we use that to er..say do batch posting scheduling @ times?
back to our main topic, will there be anyone kind enough to help me understand the differences between native and sql db?

dans

rhpnt wrote:

dans wrote:

...without taking one concurrent user license,...

That's a bad one. NAS always uses a user session (WIN login).

It is a NAS session, not a user session. And as Sog said, you'll get one NAS session in BRL.

nikeman77 wrote:

dans wrote:

back to our main topic, will there be anyone kind enough to help me understand the differences between native and sql db?

Scalability wise, SQL is better. The hardware support for SQL is much better than Native (processors, capacity, RAM, etc)

For maintenance, you only need basic maintenance for native db. There is not much you can do in Native. With SQL, there are lots of features you could use to fine tune and maintain it.

Native report is very fast thanks to the SIFT.

Native and SQL way of doing sorting are different.

If the company is small, not many transactions, and with low budget, I usually recommend Native.

nikeman77

dans wrote:

rhpnt wrote:

dans wrote:

...without taking one concurrent user license,...

That's a bad one. NAS always uses a user session (WIN login).

It is a NAS session, not a user session. And as Sog said, you'll get one NAS session in BRL.

nikeman77 wrote:

dans wrote:

back to our main topic, will there be anyone kind enough to help me understand the differences between native and sql db?

Scalability wise, SQL is better. The hardware support for SQL is much better than Native (processors, capacity, RAM, etc)

For maintenance, you only need basic maintenance for native db. There is not much you can do in Native. With SQL, there are lots of features you could use to fine tune and maintain it.

Native report is very fast thanks to the SIFT.

Native and SQL way of doing sorting are different.

If the company is small, not many transactions, and with low budget, I usually recommend Native.

hi dans,

appreciate your detail and patient explanation, thanks. where or how can i find out nav sorting and sql sorting differences. does nav local db support scheduling data replication, backup? :-k

from some articles i read sql had quite a bit of this backup (eg:transactional, snapshot, merge)technologies, but i am not very sure about navision though.

dans

nikeman77 wrote:

hi dans,

appreciate your detail and patient explanation, thanks. where or how can i find out nav sorting and sql sorting differences. does nav local db support scheduling data replication, backup? :-k

from some articles i read sql had quite a bit of this backup (eg:transactional, snapshot, merge)technologies, but i am not very sure about navision though.

http://wiki.dynamicsbook.com/index.php? ... NAV_vs_SQL

Native has the 'normal' backup and hotcopy.
http://www.mibuso.com/forum/viewtopic.php?f=23&t=18915

David_Singleton

dans wrote:

http://wiki.dynamicsbook.com/index.php? ... NAV_vs_SQL

Nice link dans,

This one might also help http://wiki.dynamicsbook.com/index.php? ... ase_vs_SQL

nikeman77

hi Dans & David,

:shock: nice one, :thumbsup: ! thank you very much!

Christian_Buehl

where or how can i find out nav sorting and sql sorting differences. does nav local db support scheduling data replication, backup?

Just to answer your main topics (the long version was answered before):

Main sorting difference is that the native DB sorts case sensitive while SQL Server is ignoring upper/lower case (actually this behaves on SQL Server settings).

The native DB is very robust and does not need much maintenance. As it is included to NAV you don't need extra licenses for this database. You don't need to setup anything extra. The database itself contains of a service running in background and a file with extension .fdb (usually it's one file, but you can split into several parts).
You can backup the database via hotcopy (a batch tool) or just by copying the fdb file(s) while not in use. There is no replicaton mechanism available to the native DB.

The SQL Server offers more speed on selections where SIFT technologies can not applied. It's also more scalable by supporting several processors, optimizations, replication technologies and so on.
After updating our Nav 3.70 (on native DB on a very old 1,5 GHz Pentium, 512 GB) ) to NAV2009 on SQL-Server 2005 (Quad Core with 4 GB) we had nearly the same speed impression as before.

On SQL Server you can use the Analysis Services to improve reportings (which was our most benefit and main reason for the migration).
And anytime in future the old native database will be dropped by Microsoft (they already name it deprecated).



However, as mentioned by others

best regards

nikeman77

hi Christian Buehl,

vielen Dank für Ihre ausführliche Erklärung =D> , does the hot copy backup for navision local db takes place using manual (tools>backup or restore)/automatically (eg:scheduler, batch file etc etc)?

seems like from what you mention due to the lack of SIFT features SQL performance wasnt improved at all despite the upgrading of hardware.

rhpnt

dans wrote:

It is a NAS session, not a user session. And as Sog said, you'll get one NAS session in BRL.

Sog wrote:

yes, but license wise it's a NAS session, not a usersession. (you get one with BRL if I'm not mistaken)

To log into a NAV db with NAS you still need a regular NAV user, therefore a user session IS used.

rhpnt

Oh, and one more thing, the ONLY advantage of the native db is that it is free of charge and still available. Part of that everything else is inferior to MS SQL Server like: indexing, backup, capacity, management tools, security, etc...
Once you've used SQL Server you can never switch to native again - and I'm not thinking of technical reasons.

mdPartnerNL

rhpnt wrote:

dans wrote:

It is a NAS session, not a user session. And as Sog said, you'll get one NAS session in BRL.

Sog wrote:

yes, but license wise it's a NAS session, not a usersession. (you get one with BRL if I'm not mistaken)

To log into a NAV db with NAS you still need a regular NAV user, therefore a user session IS used.

If you have a 2 user license. (2 regular users can login and 1 nas user can login).at the same time.

rhpnt

To clear things out:
- NAS can only be used if the granule is included in the license (like BRL) - NAS license session
- if included, NAS still needs a regular NAV user (Windows authentication) to log into the NAV db - NAV user session

If used NAS uses up a NAS license session (multiple NAS can be licensed) AND a regular NAV user session.

David_Singleton

rhpnt wrote:

Oh, and one more thing, the ONLY advantage of the native db is that it is free of charge and still available. Part of that everything else is inferior to MS SQL Server like: indexing, backup, capacity, management tools, security, etc...
Once you've used SQL Server you can never switch to native again - and I'm not thinking of technical reasons.

That's like saying the only advantage of a Volkswagen over a Mercedes is that it is cheaper so no one should ever buy a Volkswagen.

nikeman77

hi rhpnt,
"the ONLY advantage of the native db is that it is free of charge and still available."
thanks for replying, i might be wrong but i read somewhere (cant recall where) that native features of SIFT enables fast calculation of flowfield.

rhpnt

David Singleton wrote:

That's like saying the only advantage of a Volkswagen over a Mercedes is that it is cheaper so no one should ever buy a Volkswagen.

Well, anyone who's ever driven a Mercedes would have a real hard time switching to a VW...

rhpnt

nikeman77 wrote:

hi rhpnt,
"the ONLY advantage of the native db is that it is free of charge and still available."
thanks for replying, i might be wrong but i read somewhere (cant recall where) that native features of SIFT enables fast calculation of flowfield.

Same applies for MS SQL Server.
Look nikeman77, if you're deciding for an ERP database, you should focus beyond local technical featurettes. An ERP system lives for and from data so the database is the crucial part of it. For quite some time now (v4+) MS SQL Server is considered THE standard database for new NAV implementations - period. Anyone who claims the opposite should write that in a letter to Mr. Balmer. I don't really know why the native server is still included in the installation DVD.

David_Singleton

rhpnt wrote:

Same applies for MS SQL Server.
Look nikeman77, if you're deciding for an ERP database, you should focus beyond local technical featurettes. An ERP system lives for and from data so the database is the crucial part of it. For quite some time now (v4+) MS SQL Server is considered THE standard database for new NAV implementations - period.

I agree with this in full. Anyone starting a new implementation of Navision as their ERP system should be on SQL.

rhpnt wrote:

Anyone who claims the opposite ...

Did anyone say this, or is it something you dreamt?

nikeman77

hi guys(rhpnt/ david),

till this point i had a rough idea based on the valuable feedback from you guys on the comparison between sql server & native db. thanks a lot of the comments and teachings. i appreciate that, cheers!

David_Singleton

nikeman77 wrote:

hi guys(rhpnt/ david),

till this point i had a rough idea based on the valuable feedback from you guys on the comparison between sql server & native db. thanks a lot of the comments and teachings. i appreciate that, cheers!

You are welcome, I hope it helps.

To simplify things, think of it this way. You should aim if at all possible to use SQL, so if there is a case that you would need native, you will know it and know why, so you are not sure then the answer is to use SQL.

Alex_Chow

rhpnt wrote:

David Singleton wrote:

That's like saying the only advantage of a Volkswagen over a Mercedes is that it is cheaper so no one should ever buy a Volkswagen.

Well, anyone who's ever driven a Mercedes would have a real hard time switching to a VW...

Off topic, I owned a mercedes once and absolutely hated it.

Alex_Chow

If cost is an issue, just use SQL Server Express that comes free in the installation DVD. It allows up to 2 GB worth of data before you need to even need to purchase SQL Server standard.

If we're arguing about technical capabilities, yes native database is faster. Just like DOS is faster than Windows. How many people do you know still use DOS?

David_Singleton

Alex Chow wrote:

If we're arguing about technical capabilities, yes native database is faster. Just like DOS is faster than Windows. How many people do you know still use DOS?

That's a very good analogy. :thumbsup:

dans

rhpnt wrote:

dans wrote:

...without taking one concurrent user license,...

That's a bad one. NAS always uses a user session (WIN login).

rhpnt wrote:

To clear things out:
- NAS can only be used if the granule is included in the license (like BRL) - NAS license session
- if included, NAS still needs a regular NAV user (Windows authentication) to log into the NAV db - NAV user session

If used NAS uses up a NAS license session (multiple NAS can be licensed) AND a regular NAV user session.

That is correct, but I believe we were talking about license in the beginning, and somehow it became session.

David Singleton wrote:

To simplify things, think of it this way. You should aim if at all possible to use SQL, so if there is a case that you would need native, you will know it and know why, so you are not sure then the answer is to use SQL.

:thumbsup:

Alex Chow wrote:

If we're arguing about technical capabilities, yes native database is faster. Just like DOS is faster than Windows. How many people do you know still use DOS?

Nah, I don't think it is the right analogy. You should use Linux and Windows because normally people think Windows as 'standard'.

bstnalm

Hi All,

I am new to this forum and very excited to read all these discussions.

I have a question regarding the same topic. What if we are going to implement NAV in a retail environment, which may require to have HQ and store databases? HQ database will definitely need to use SQL server, but what about the stores?

Currently I think most of the store databases are still using native database for their POS terminal, and I trust that the main reasons for that are because it is faster to setup and less cost required since there is nothing much to do at the POS terminal other than doing sales. After reading this discussion, I wonder whether it is still wiser to use SQL server for the store database in any aspect. Do we have to start to use SQL server just because there will be no native NAV database in the future?

Thanks a lot!