SQL Server or Native? Which is better?

vasoov

:?: Good day, I am new to Navision. I have a client with 40 concurrent users. on Version 4.01 Size of data is estimated to be 10GB. Which is better to use the native database or SQL server?

Thanks for ideas..

kriki

There are a lot of other questions to ask:
1) those 40 concurrent users, are they all working in the same company? All they all entering sales orders at the same moment? Or are they working in different tables?
2) How fast is the DB growing per day/week/month/year
3) Do you need a garanteed uptime of 99.999%?

Probably others will find other questions to ask you.

kriki

Remembered an other question.

Do you want to read the DB with external tools?

vasoov

Currently the 40 users are on a JDEdwards XE platform and we plan on migrating them to Navision. All of the 40 users are working in the same company. We plan on using a terminal server setup, with maximum availability (clustered hardware) if possible.

Does navision support clustering?

8 users will be entering sales orders at a rate of 150 SO per day. We expect to start off with 10GB of data and grow at 10GB per year. Modules which will be used are Distribution, Financials and Job Costing. Our strengths are in SQL database.

kriki

Does navision support clustering?

You mean that Navision is running on 1 server with a RAID-pack. If the server fails, the second server kicks in and continues, using the same RAID-pack as server 1? Yes

8 users will be entering sales orders at a rate of 150 SO per day.

- How many lines per order?
- And what are the other 32 users doing?

If you want to have a maximum uptime, in SQL, you can make transactional backups, and move them to another disk-system and restore it in the SQL-DB on that disk-system. In case of complete failure of the first server (server+disks), with some luck you still have the transaction-log and can also restore that on the second server, losing only the uncommited transaction and getting back online after a few minutes.
This is not possible with a Navision-DB. With Navision you would have to do a hot-copy, but this backups (=copies) the whole DB.

vasoov

I have found some info on a similar site which has 100 users running on native.

Kowa

vasoov wrote:

I have found some info on a similar site which has 100 users running on native.

It depends on what these users are doing. If most of them perform reading operations on the database there won't be much of a problem, but if lots of users try to start a posting process simultaneously the table locking used in the native database will block all others users until the process of the first user in the queue is finished.

Even the No.Series table is locked during postings, so when batch postings take place no new orders can be created because the document no. is not generated by the system ( the Last No. Used has to be written to the table)

fl

Hi,

Seen the info you mentioned I would prefer SQL. Why:

You say you have already sql knowledge, so this would be no reason to not choosing the sql.
Sql is using record locking mechanisme, while native is using table locking. Like Kowa also mentioned, this depends what you are doing. But keep in mind that batches, posting documents lock several tables like no. series, document dimensions, ...
You ask max up time: sql can use multiple processors in the server, native can't. Yes, you will get a lower performance if you run the the sql database on the same hardware as an native database. But more users, can ask more performance and then depending on your application, you can't make use of multi-processor servers like sql.
A sql database is better to tune then an native. In sql you can spit files like in native, but in sql you can define which tables in which file (or read here physical drive). This last one, you can't do in native.
A sql backup is also much faster to take then a native one.
Also think about future: how long will Microsoft go on with native database

Francois

Marije_Brummel

fl wrote:

Hi,

In sql you can spit files like in native, but in sql you can define which tables in which file (or read here physical drive). This last one, you can't do in native.

Francois

Is this true? Can this be set up in SQL without Navision giving errors the table is changed in SQL? I always thought you cannot change a Navision Table in SQL...

I am not much of a SQL guy, but if this works you can solve a lot of performance issues I think.

fl

Hi Mark,

Yes you can split the tables of Navision on different fysical files like you want or need.

Even MBS is doing this at installation. They create 2 filegroups (so you get 3 files, 2 data and 1 transactional log file). They place the basic objects of SQL in the default filegroup (this is the reason that this filegroup practical doesn't grow). The "Navision" tables or al placed on the second data file. But you can make more filegroups and change the filegroup on which you want the to place tables and keys. You can even place all the images seperatly on an other filegroup than the other fields of the table.

If you are more interested I can send you a document there about, specifically sql and navision. Just send me your mail address in that case.

Indeed you can not change the structure of a table from SQL manager, otherwise Navision won't see the fields or gives errors. But the place where the tabledata resides has nothing to do with this point. Navision just askes sql to pass data or indexes. SQL knows where it has to look for.

SQL is a very performant database that can be used from a few users till really thousands. But keep in mind, performance tunibg of that database (when needed) is a job that askes knowledge from SQL as Navision application.

Kind regards

Francois

kriki

Kowa wrote:

It depends on what these users are doing. If most of them perform reading operations on the database there won't be much of a problem, but if lots of users try to start a posting process simultaneously the table locking used in the native database will block all others users until the process of the first user in the queue is finished.

The fact that SQL uses record-locking, will not help in case all users are posting at the same moment. Even with SQL, Navision will lock some tables (the Entry-tables) to get the last entry no. and then add 1 to it, before writing. Because of this, also in SQL posting will happen sequentially and not parallelly.

Kowa

kriki wrote:

The fact that SQL uses record-locking, will not help in case all users are posting at the same moment. Even with SQL, Navision will lock some tables (the Entry-tables) to get the last entry no. and then add 1 to it, before writing. Because of this, also in SQL posting will happen sequentially and not parallelly.

That is true, but the SQL server decides by itself, whether a table lock is necessary, or a page lock or recordlevel lock is sufficient, so the posting process on the whole is potentially less prone for users getting in the way of each other.

Marije_Brummel

@Francois

Yes please send me this document.

I normaly never do SQL issues or Navision Installations, but this is realy interesting.

I am very curious how you can change the location of a navision table on a SQL installation.

It must also be something that varies from one installation to another.

Email marq@hakkinen.com

bbrown

Yes it is possible to place the various SQL objects of a Navision SQL database into different data files. This is only an advantage if the different files are placed on separate physical disk arrays. Tghe only restriction is that a table and its primary key must be in the same file, but secondary keys can be move to other files.

Partitioning an SQL database requires an understanding of how the application use the database. The idea is to split the different objects in order to spread the disk i/o across different drives. Sometimes you may be better off just adding extra disk to your RAID 10 array. When building arrays remember that more drives is better than larger.

Dakkon

If anyone could send me materials about Native vs SQL, I would be very appreciative. I work for a company that plans to upgrade to a new version of Navision soon. Specifically we are looking at 4.1. We have not decided whether to use the Native db or MSSQL and I'm trying to get all the resources I can to help in making this decision.