Upgrading from 3.01 (3.70) to 4.00 SQL Server

Kaetchen

Hi,

We finally intend to upgrade Navision but reading about all this SQL Server performance issues in the forum makes me suspicious.

What are the main benefits from upgrading to SQL Server apart from using "Reporting Services"?

Any experiences you would like to share?

Thanks,
Kaetchen

kriki

Main benefits:
-better backup strategies possible (full-backup,incremental backup, transactionlog-backup)
-faster recovery in case of server-failure (if you have a good backupstrategy and backup server)
-better connection to other programs that want to query the DB



For performance, it is true that Navision must be prepared to use SQL-server.
There are a lot of indexes in Navision that SQL doesn't use but it costs a lot of time to maintain them.
Also SIFT does not exist in SQL, so it has to emulate it with extra tables in which totals are stored. This means that for each record inserted/modified/deleted, another 1 to several hundereds of records must be maintained.
After preparing this, (and doing a dayly SQLStatistics calculation and a weekly indexrebuild) the speed of SQL can be compared with the speed of a Navision DB.

But the biggest disadvantage of SQL is that someone needs to maintain it. It is not an install-and-forget-DB like the Navision DB.

Kaetchen

Thanks for your reply!

How can MS withdraw their support from C/Side if they haven't upgraded (or rewritten) Navision to optimize performance on SQL Server?

If Navision is still running in C/Side migration mode on SQL when will we see the full benefit of a SQL database? Apparently users experience more problems with SQL 2005 than with SQL 2000.
What is the reason for that?

kriki

Kaetchen wrote:

How can MS withdraw their support from C/Side if they haven't upgraded (or rewritten) Navision to optimize performance on SQL Server?

Each version of Navision is better optimized for SQL than it's previous version.
The problem is that the Navision-DB uses some technology that SQL doesn't have. Maybe that in the future SQL will have the technology.
And it is sure the Navision DB will be dropped in the future because Navision, Axapta, CRM, ... will become 1. And they will become 1 under SQL.

Kaetchen wrote:

If Navision is still running in C/Side migration mode on SQL when will we see the full benefit of a SQL database? Apparently users experience more problems with SQL 2005 than with SQL 2000.
What is the reason for that?

With what version of Navision do you work? Only Navision 4.0SP2 hotfix 3 is ready to use SQL2005.

Phenno

Kaetchen wrote:

Thanks for your reply!

How can MS withdraw their support from C/Side if they haven't upgraded (or rewritten) Navision to optimize performance on SQL Server?

If Navision is still running in C/Side migration mode on SQL when will we see the full benefit of a SQL database? Apparently users experience more problems with SQL 2005 than with SQL 2000.
What is the reason for that?

First, they didn't withdraw support for c/side, at least not yet. Normally, they are trying to sell their other products (MS SQL) so ther focus is primary on it.

SQL 2000 => SQL 2005?
SQL 2005 is relatively new, takes time to remove majority of problems & bugs concenrning NAV on SQL 20005. Another issue is that SQL 2005 came After NAV4 is released so NAV4 <=> 2005 compatibility was build in NAV4 SP2.

Kaetchen

The problem is that the Navision-DB uses some technology that SQL doesn't have. Maybe that in the future SQL will have the technology.

What you saying is that SQL has to improve to keep up with C/Side?
Is this not the wrong way around? I thought SQL is the new technology?

We have 3 Options how we going to convert to SQL:

Upgrade now and migrate all the customized software as it is.

Re-design our software after the outcome of BPM and upgrade.

Use the improved version of a customized software from another client (same business) - but they wait for the release of Navision 5.0.

We have no idea why they waiting for 5.0 - is there anything significant happening?

At the moment we favour option 2. Therefore we looking for a Navision Developer on a 6 month contract which is possibly extended to 1 year.
Major focus is Job-Costing.

kriki

Kaetchen wrote:

[What you saying is that SQL has to improve to keep up with C/Side?
Is this not the wrong way around? I thought SQL is the new technology?

SQL will probably the new technology.
But Navision first had it's own DB with SIFT integrated in it even BEFORE getting on SQL. So it does not mean SQL has not to improve to keep up with C/Side, it just means the Navision-DB has it's own technology where it exists.
SQL has no SIFT. So Navision needs to simulate SIFT in SQL by using normal tables.
Maybe in the future also SQL will have it integrated.

kine

1) If you will to use the new Dynamics client in version 5.1, it will run just only with the MS SQL DB. It means - no native for the new client. You can use the old c/side client still, but... ;-)

2) 5.0 is more optimized (yes, each version is better than previous one). Developers are creating the new code and modifications for MS SQL backend.

3) If you want to upgrade to newer version somewhen, it is better to upgrade to MS SQL now, to have time to optimize your processes for that (setting maintenance plans, finding ways how to admin the MS SQL server etc.) and after year or two, when the new version will be released and usable, you can upgrade to newer version, as Mark B. said today to one guy - "make the upgrade as simple as possible". Do not connect more actions into big jump...

Kaetchen

Hi Guys,

thanks for all this information. Maybe this is the only way to learn about Navision anyway.

Unfortunately our customised application doesn't perform very well under C/Side, so it can only get worse.

The table is full of boolean fields. Can this be a reason for the performance problems? Searched a bit in the forum and found this

If you need optimize flowfields, you need to create correct keys on the base table (the table from which are the flowfields calculated) - keys with fields with best selectivity which are used in filter of flowfields. If you create such a key (it can be only one field in the key, but may be more to speedup), and the first field in the key will rapidly reduce the range of data needed to read, it will be faster. But if wrong key design lead to that MS SQL server select for example key begining with field of type option or boolean, which have very bad selectivity, you need read for example 50% of table in case of boolean. But if you create key begining for example with customer no., and you are calculating fields per customers, server will read only records for the one customer for which is calculating field and the performance will be better. And do not forget to disable flowfields on work tables (sales lines etc.) because it is faster let MS SQL server calculate the sum than have big SIFT Tables with empty records because deleted documents etc... (if you are not optimizing tables on regulary base)...

How will SQL Server handle boolean fields in a C/Side simultation?

Apart from the design of the application (Jobs) which is probably not optimal, we might also have to deal with bad coding or database design for those tables.

Therefore we decided to find a freelance Navision developer and not a Navision Partner who has probably only 2 installations in New Zealand.

Does anybody knows the details for getting a developer license and the approximate costs?

It seems that Navision developers often work with Navision Partners because of the costs for the license and we would be prepared to help with this issue.

First we targeting freelance Navision developer in NZ, but also consider a consultant from overseas (newest decision). Business people very reluctant to take this risk here and stick with consultant companies who are highly overpriced. I think their knowledge about Navision is very limited as I never get decent answers and solved most of our problems by myself, reading manuals and searching this forum.

kine

The boolean field is handled as tinyint with 0 or 1. It means just two values, when filtering for that, the index beginning with this field will be never used (if the 0 a 1 are represented 50:50), because the selectivity is just 0.5.

kriki

kine wrote:

The boolean field is handled as tinyint with 0 or 1. It means just two values, when filtering for that, the index beginning with this field will be never used (if the 0 a 1 are represented 50:50), because the selectivity is just 0.5.

There are cases a boolean has a good selectivity.
E.g. Table Customer Ledger Entries : There is an index that starts with the boolean field "Open".
Now if we think about it, with a brandnew DB, there will be very few closed closed entries. But as time passes more and more entries will be applied, thus closed. The no. of open entries will remain +- stable, but the no. of closed entries will increase all the time.
And why is there the index starting with the boolean field Open? Not because we want the closed posts, but the open ones!
So with a brandnew DB, the selectivity of Open entries will be close to 100% (=all records so very bad), but as time passes this % will diminish and thus will get a lot better.
Of course for SQL to use the key in the future of this DB, it is necessary SQL knows of the selectivity of SETRANGE(Open,TRUE);. For this serve the SQLstatistics recalculation.

kine

Yes, as I wrote, will be never used if the 0 a 1 are represented in 50:50. If the ratio is different, you can have situation, when the index will be good... :-)

kriki

kine wrote:

Yes, as I wrote, will be never used if the 0 a 1 are represented in 50:50. If the ratio is different, you can have situation, when the index will be good... :-)

Well, it was not really clear .... at least not in my head.... :?

Kaetchen

Well, it was not really clear .... at least not in my head....

I think it means going back to your example - if the ratio open/closed ledger entries would be 50:50 all the time the field shouldn't be used as an index field. Please correct when wrong.

How can I find out which are the index fields in a table? Keeping in mind I don't have access to the developer tools?

Any suggestions about the license fees, or is this a touchy subject

bbrown

How can I find out which are the index fields in a table? Keeping in mind I don't have access to the developer tools?

Run the table from object designer, and click on the sort button in the toolbar. Remember that the primary key fields are appended to the end of the secondary keys.

You can also look at the tables from SQL.

Kaetchen

Can I identify which ones are primary and secondary keys?

For example, if the primary key includes four fields, the secondary keys can include these four fields and up to 16 additional fields from the table

bbrown

The first one is the primary key.

Kaetchen

... and the secondary keys. How can I see whether they active?

ara3n

in navision there is a column when you design the table and see the window. In sql you only see the active keys. when you run a form. You ca only see the active keys.