MSSQL server configuration

Some fundamental settings are required before an esoffice / eserp database on a newly installed Microsoft SQL server can be put into operation.

 

es2000 does not make any recommendation regarding the suitability of the following information. Errors and exceptions are excluded and no responsibility is accepted for the information provided. The statements are simply descriptions and do not guarantee the consistency of the products. The information may also in part be regarded as an attempt to provide you with assistance for a task even if the product was not actually intended for this special purpose.

 

Some of the settings cannot be applied later to existing databases, so the following configuration of the MSSQL server is required before creation / backup of a database.

SQL Server Configuration Manager

Open the SQL Server Configuration Manager. This is installed along with other configuration tools when the Microsoft SQL server is installed.

Activate the settings for Named Pipes and TCP/IP in the sub item Protocols for [server name] in the SQL server network configuration branch.

 

You must restart the SQL server services after any changes.

SQL server settings

The Microsoft SQL Server Management Studio program (SSMS) is required for these settings. This can be downloaded from the Microsoft website as part of the installation.

 

The following screenshots show an MSSQL server installed in English.

The same English terms are used in the descriptions. -

Right click on the SQL Server entry in the Object Explorer and select the Properties command in the context menu.

Connections

Activate the following options

ansi warnings

Controls abbreviation and NULL in aggregated warnings.

ANSI NULLS

Controls handling when using NULL matching operators.

arithmetic abort

Ends a query if an overflow or divide by zero error occurs during execution of the query.

contact null yields null

Returns NULL wen a null value is concatenated with a character string.

Database Settings

Activate the following options

Compress Backup

Since a compressed backup is smaller than an uncompressed backup of the same data, compression of a backup generally requires less device I/O, which usually means that the backup speed is greatly increased.

The CPU load will increase considerably during compression, which may result in other simultaneous processes due to reduced availability of CPU resources. It may therefore be useful to generate low priority compressed backups during a session when the CPU utilization is restricted by the Resource Governor.

Advanced

Parallelism area

Query Wait

Value: -1

Specifies the time in seconds (from 0 to 2147483647) that a query will wait for resources before a timeout occurs. If the default value -1 is used, the timeout is calculated as 25 times the estimated query cost.

Cost Threshold for Parallelism

Value: 20

Specifies the threshold above which the SQL server creates and executes parallel plans for queries. The cost refers to an estimated elapsed time in seconds that is required to execute the serial plan on a particulae hardware configuration. Only activate this option on symmetrical multiprocessors.

Max Degree of Parallelism

Value: 0

Limits the number of processors (up to a maximum of 64) that are to be used for parallel plan execution. The default value 0 uses all available processors. The value 1 suppresses parallel plan generation. A number greater than 1 limits the maximum number of processors that are used by execution of a single query. If a value greater than the number of available processors is specified, the actual number of available processors will be used.

Locks

Value: 0

Specifies the maximum number of locks available and thus limits the quantity of memory that the SQL server allocates for them. The default value is 0, which allows the SQL server to assign and remove locks dynamically, based on the varying system requirements.

The recommended configuration is to allow the SQL server to use locks dynamically.

Miscellaneous

OPtimize for ad hoc Workloads

Value: True

Allow Triggers to Fire Others

Value: True

Controls whether a trigger can be used to perform an action that triggers a further trigger. Triggers cannot be triggered by another trigger if they are deleted. If you select this option, triggers can be triggered by other triggers, nested up to 32 times.

Memory

Memory options for the server

Rule of thumb = Minimum operating system requirements (4 GB) plus the logical size of all active databases.

esoffice / eserp database settings

The following settings affect all the esoffice / eserp databases (esdata, esmat, and esnls) and must be set when the databases are created.

 

Subsequent changes cannot be made without considerable difficulty.

These settings are accessed through the Microsoft SQL Server Management Studio (SSMS).

 

The Microsoft SQL Server Management Studio (SSMS) can be downloaded and installed as part of the MS SQL server installation.

After installation, start the Microsoft SQL Server Management Studio (SSMS).

Open the Databases branch in the Object explorer.

Right click on the corresponding database and select the Properties command in the context menu.

Collation

Value: Latin1_General_100_CI_AS_SC

Recovery model

Backup and recovery processes for the SQL server are performed within the framework of the database recovery model. Maintenance of the transaction log is controlled by the recovery models.

 

This allows you to specify the recovery model for databases according to your specific backup requirements. The selected recovery model also affects the measures necessary for backup of the productive databases.

 

You must develop and set up a backup concept tailored to the needs of your company yourself or with the help of an IT service provider.

Basic

No backups of the transaction logs are required for this selection.

Changes made since the last backup are not protected. These changes will have to be made again following a restore. Restore is only possible up to the end of a backup.

Complete

Backups of the transaction logs are required for the Complete recovery model.

Recovery up to any given time is possible (for example prior to an application or user error). Recovery up to any given time is possible if the backups up to this time are complete.

Create database user

A database user is needed for basic identification of esoffice / eserp to the SQL server.

Create database user

  1. To create a database user, start the Microsoft SQL Server Management Studio (SSMS).

  2. Open the Security branch in the Object explorer.

  3. Right click on the Login sub item and select the New login... command in the context menu.

    The Login window opens.

  4. Enter the name esoadmin as the Login name in the General area, and set the user account to SQL server authentication.

    Assign a secure password.

    Specify the default language, for example US English.

  5. Select the Server roles item and additionally activate the sysadmin server role.

  6. Open the User assignment item.

     

    This step is only possible when the esoffice / eserp databases (usually named esdata, esmat, and esnls) have lready been created. If these databases are not yet present, click on the OK button to initially create the database user. You can make the user assignment later.

    Select one of the basic esoffice / eserp databases (usually named esdata, esmat, and esnls) each time using the option in the Assign column. In each case assign membership for the database roles db_reader, db_writer, and db_ddladmin. You can leave the assignment to database role set to public. Assign the dbo schema as the default schema.

  7. Click on the OK button.