Microsoft SQL Server best practices

The vast majority of configuration settings for Microsoft SQL Server and your databases will remain unchanged. However, you may want to adhere to the following suggestions for best performance.

In order to interact with the database, NexJ CRM uses a customized version of the open source jTDS driver, jtds-1.2.2-7.jar. This driver is included with the NexJ Studio plugin, in the NEXJ_PLUGIN\ext folder.

To configure your application server to interact with the database, install the jTDS driver into your application server.

The following settings are required for a production environment:

  • Enable TCP/IP connections.
  • Set a non-zero value for TCP Port (the default value is 1433).
  • Disable dynamic ports to prevent a new port number to be selected upon server startup. If enabled, any external incoming connections to the server requiring a fixed port number will be lost.
  • In a production environment, database users should only be members of the njdbuser database role, which provides create, read, update and delete (CRUD) access to the data contained in NexJ tables. No other database roles are required.
The following settings are recommended for a production environment:
  • For enhanced security, use SQL Server and Windows Authentication mode, or a mixed authentication mode.
  • Use a strong password for the sa account.
  • Limit the amount of memory the system uses for file caching in order to optimize use of system memory.
  • Begin with a Default Index Fill Factor value of 90%. This setting reduces fragmentation of data. Monitor the fragmentation over a week and adjust the fill factor accordingly.
The following settings are best practices for development:
  • Limit maximum memory available to the instance.
  • Limit maximum worker threads.
  • Scale maximum file sizes for data files and log files.
Note: Documentation for Microsoft SQL Server can be found here: http://msdn.microsoft.com/en-us/library/default.aspx.