Microsoft SQL Server Recommended Database Settings and Supported Sort Orders with Great Plains

Applies to: Dynamics GP

TechKnowledge Content


Issue


What are the recommended database optionsand supported sort order settings for Microsoft SQL Serveras it relates to Great Plains?


Resolution



Recommended Microsoft SQLServer Database Options

1.
Recursive Triggers

Great Plains does not use recursive triggers, ergo it is unnecessary to check this. If a third party requires that it be checked though, it should not cause a problem.


2.
Torn page detection

SQL Books On-Line suggests little overhead. Therefore Great Plains recommends that you check this option.


3.
Auto close
DO NOT check this option. This closes the database data file and log file every time the last users closes its connection. Could cause performance issues.


4.
Auto shrink

DO NOT check this option.It can cause fragmentation and is automatically enabled if Auto Close is selected.


5.
Auto create statistics
It is recommended to check this option. The System will get smarter as time goes on.


6.
Auto update statistics
It is recommended to check this option.The System will get smarter as time goes on.


7.
Use quoted identifiers
It is recommended to uncheck this option.


8.
Cursor Threshold
Cursor Threshold must be set to -1 (disabled) for Great Plains to post correctly.
9. Guidelines on the percent when auto growing database and log files?

This is a case by case issue. How fast is the company growing? How many transactions are they doing now? For example, a customer with a 10 GB dataset where the auto grow is set to 20% will grow by 2 GB the first time it needs data, and then grow by 2.4 GB the next time, etc. Another customer with 30GB dataset where the auto grow is set to 20% will grow by 6GB the first time, and then grow by 7.2 GB the next time. Both could be good extents, but need to watch available drive space. Also do not want to set it too low as this will cause multiple extents and fragmentation that will affect performance.


Microsoft SQLServer Configuration

Memory Tab

1. Dynamically configure SQL memory


Should a minimum and maximum be set or should this be left alone?

We recommend to have a dedicated server for Microsoft SQL Server, therefore, it is recommended to leavethis option as is. If there are other applications running on this server, then may want to specify a minimum.


2. Reserve physical memory for SQL Server

Should this be marked or unmarked?

See answer above. Only reserve if other applications running on the server are taking memory away from Microsoft SQL Server.


Processor Tab

1. Boost SQL Server priority on Windows NT

It is recommended touncheck this option.


2. Use Windows NT fibers.

It is recommended to leave this optionunchecked.




Microsoft SQL Server 7.0/2000 SQL Code Pages and Sort Orders supported by Great Plains.

1252 - This is most commonly referred to as North American version of NT and SQL Server

Code Pages ...



North America

LocationClient Code PageSQL Code PageSort Order

North American 1252 1252 Case insensitive

North American 1252 1252 Binary



International

Location
Client Code PageSQL Code PageSort Order

Poland 1250 1250 Any

Poland 1252 1252 Any

Czech 1252 1252 Binary

Czech 1252 1252 Czech, case insensitive

Czech 1250 1250 Czech dictionary order, case insensitive

Germany 1250 1250 Binary

Germany 12501250 Dictionary Order Case Insensitive

Portugal 1250 1250 Binary

Portugal1250 1250 Dictionary case Insensitive

UK/Australia/SA1250 1250Binary




This article was TechKnowledge Document ID:6397

DISCLAIMER


MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, THE RELIABILITY OR THE ACCURACY OF THE INFORMATION THAT IS CONTAINED IN THE DOCUMENTS AND THE RELATED GRAPHICS PUBLISHED ON THIS WEB SITE (THE “MATERIALS”) FOR ANY PURPOSE. 

THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE. TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON-INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIALS.