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
LocationClient 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
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
LocationClient 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