Which Build for SQL2005

ta5

Hi experts

We have to migrate a rather small customer from Navision 4.02 from Native DB to SQL Server 2005.

I think we will also do a technical upgrade to 4.03 because of the security model switch.
What Navision build would you recommend, e.g. is the SP3 alone ok or are there important *technical* hotfixes concerning sql server (2005)?

Thanks in advance
Thomas

DenSter

Update 6, and disable the automatic index hinting.

nunomaia

Yes, I agree with DenSter

Update 6 is one of the best builds for SQL Server. You should read information about index hinting in http://blogs.msdn.com/microsoft_dynamic ... 40718.aspx

ta5

Thanks for your help on this.

Just to be sure:

1) The table $ndo$dbconfig is created and used by update 6 (at least if the database is created by finsql.exe of update 6). Is this correct?

2) To turn off the index hinting just set the field "IndexHint" to No.
Is this correct?

Thanks in advance.

Thomas

nunomaia

1- You must create config. table

CREATE TABLE [$ndo$dbconfig] (config VARCHAR(512) NOT NULL)
GRANT SELECT ON [$ndo$dbconfig] TO public

2 - Yes

INSERT INTO [$ndo$dbconfig]
VALUES ('IndexHint=No')

ta5

Thanks Nuno.
This means that if the table does not exist, indexhinting is on?

A follow up: If we are migrating from a native installation to a sql server 2005 installation, the impact of the indexhinting will probably not be very hard since in the native db we have used setcurrentkey anyway. Is this correct.

Thanks again.

Thomas

DenSter

No that is not a correct assumption. SQL Server does not use indexes for sorting, at least not the way that you are used to in the native database server.

Indexes on SQL Server are used for data retrieval, and the SQL Server engine has logic to determine the most efficient index for each and every query. With update 6, the default behavior is that index hinting is on, which means that NAV is forcing SQL Server to use the sorting order as an index. In many cases this is NOT the most efficient way to retrieve the data, and as a result index hinting slows the system down.

One of my customers is currently testing update 6 on a new SQL Server 2005, and found that it was terribly slow, with people being locked out all over the system. We turned index hinting off, and performance was much better.