mibuso.com
Home› NAV/Navision Classic Client

Nav native db vs SQL server

nikeman77nikeman77 Member Posts: 517
edited 2011-10-28 in NAV/Navision Classic Client
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.
«1

Comments

  • rhpntrhpnt Member Posts: 688
    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)
  • nikeman77nikeman77 Member Posts: 517
    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?
  • rhpntrhpnt Member Posts: 688
    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. :-$
  • dansdans Member Posts: 148
    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.
    Microsoft Certified IT Professional for Microsoft Dynamics NAV

    Just a happy frood who knows where his towel is
  • rhpntrhpnt Member Posts: 688
    dans wrote:
    ...without taking one concurrent user license,...
    That's a bad one. NAS always uses a user session (WIN login).
  • SogSog Member Posts: 1,023
    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)
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • nikeman77nikeman77 Member Posts: 517
    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?
  • dansdans Member Posts: 148
    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.
    Microsoft Certified IT Professional for Microsoft Dynamics NAV

    Just a happy frood who knows where his towel is
  • nikeman77nikeman77 Member Posts: 517
    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.
  • dansdans Member Posts: 148
    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
    Microsoft Certified IT Professional for Microsoft Dynamics NAV

    Just a happy frood who knows where his towel is
  • David_SingletonDavid_Singleton Member Posts: 5,479
    dans wrote:
    http://wiki.dynamicsbook.com/index.php? ... NAV_vs_SQL


    Nice link dans, :mrgreen:

    This one might also help http://wiki.dynamicsbook.com/index.php? ... ase_vs_SQL
    David Singleton
  • nikeman77nikeman77 Member Posts: 517
    hi Dans & David,

    :shock: nice one, :thumbsup: ! thank you very much! :D
  • Christian_BuehlChristian_Buehl Member Posts: 145
    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
  • nikeman77nikeman77 Member Posts: 517
    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.
  • rhpntrhpnt Member Posts: 688
    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.
  • rhpntrhpnt Member Posts: 688
    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.
  • mdPartnerNLmdPartnerNL Member Posts: 802
    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.
  • rhpntrhpnt Member Posts: 688
    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_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • nikeman77nikeman77 Member Posts: 517
    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.
  • rhpntrhpnt Member Posts: 688
    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...
  • rhpntrhpnt Member Posts: 688
    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_SingletonDavid_Singleton Member Posts: 5,479
    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?
    David Singleton
  • nikeman77nikeman77 Member Posts: 517
    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_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • Alex_ChowAlex_Chow Member Posts: 5,063
    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.
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • Alex_ChowAlex_Chow Member Posts: 5,063
    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?
    Confessions of a Dynamics NAV Consultant = my blog
    AP Commerce, Inc. = where I work

    Getting Started with Dynamics NAV 2013 Application Development = my book
    Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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:
    David Singleton
  • dansdans Member Posts: 148
    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'.
    Microsoft Certified IT Professional for Microsoft Dynamics NAV

    Just a happy frood who knows where his towel is
  • bstnalmbstnalm Member Posts: 25
    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!
Sign In or Register to comment.