We are currently still building up the English documentation; not all sections have been translated yet. Please note that there may still be German screenshots or links to German pages even on pages that have already been translated. This guide mostly addresses English speaking users in Germany.
Optimal SQL server setup for JTL-Wawi
Your task/initial situation
JTL-Wawi uses Microsoft SQL databases as the central storage location for all kinds of data. This is where our software stores information on customers and items for the respective company as well as all data concerning sales orders, eBay or Amazon. Since all Wawi installations within the same company must access the same database, the database has a huge impact on overall speed. The higher the number of users on a possibly slow and carelessly configured SQL server, the longer the time it takes the system to process the desired data queries.
Hardware
The right choice of hardware is a decisive factor for quick access to data on an SQL server.
Requirements for all SQL server versions
The computer on which the MS SQL Server is running should have the following hardware specifications:
Set up your system as follows:
While users of the Express Edition already meet the recommended hardware requirements, there are some differences for users of the Standard or Enterprise Edition. With these editions, it is possible to use more than one CPU per query and more RAM. Hence, the following recommendations should be followed:
Once these hardware requirements have been met, the first important step has been completed. The infrastructure for quick database queries is in place.
Configuration
Compatibility level
In addition to the right hardware, you should adjust some basic software settings. For maximum compatibility, an SQL server can be set to different versions. From version 1.4 onwards, JTL-Wawi supports all versions between SQL 2014 and 2019. Always use the highest possible compatibility level.
- To adjust the compatibility settings, open Microsoft SQL Server Management Studio and log in with your login data. In the Object Explorer, define the compatibility level for your SQL server.
- Right-click on the database and select Properties. The dialogue box Database properties opens.
- Open the Options tab. Here you will find the settings for the server’s compatibility level. To guarantee the best possible performance, set your server to the actual level for this version. For example, if you are using SQL 2014, the value should be 120.
Deactivating Auto Close and Auto Shrink
- Deactivate the options Auto Close and Auto Shrink by selecting the option false. Deactivating these features optimises the performance of the SQL server.
An important factor for a better server performance is the deactivation of Auto Close and Auto Shrink in the database. Auto Shrink is constantly trying to reduce the size of the .mdf file. As a result, the server is permanently preoccupied with resizing the file and the fragmentation of the hard drive. Auto Shrink in turn empties the memory every time all connections to the server are closed. That sounds good at first, but unfortunately it is not. If too many important files have to be loaded into memory when a connection is established, server performance will suffer.
- Now switch to the tab Files. Here you can adjust the sizes of the .mdf and .log files. This way, you get a continuous file on the hard drive, while the auto grow function performed by the system generates a fragmented version. This also negatively impacts the database speed.
- In the column Autogrowth/Maxsize, click on the button …. in the MDF file row. The dialogue box Change Autogrowth opens.
- In File Growth , set the size to 500 MB. Ideally, set the initial size to 10 GB (=10,000 MB). This means that your database has a minimum size of 10 GB, which is only increased by another 10 GB once the database has reached that size.
- Adjust the file growth for the .log file in the same way and set it to 100 MB.
Close the database properties of your database.
- Repeat steps 6 to 8 for the TempDB database. Set the initial size to 100 MB for both the .mdf and the .log file.
ServicePacks and Updates
To ensure that the software is always up to date, install available updates and service packs for your SQL Server as soon as possible.
You can find the latest updates here: Microsoft SQL Server updates
Important: Automatic data backup
It is important that you set up an automatic data backup that backs up your databases at least once a day. The backup should be saved on a second drive or another computer!
Read the Microsoft documentation for more information: Data backup for SQL Server.
Special settings for different SQL Server Editions
There are some special settings when it comes to the Standard Edition and the Enterprise Version.
Standard and Enterprise Version
If you are using the Standard or Enterprise Version, adjust these settings after following the steps above:
- For every CPU core, create a new .mdf file in the TempDB with an initial size of 100 MB and a file growth of 100 MB. Since the CPUs can work in parallel, they can access separate TempDB files. They do not all access the same DB, and bottlenecks can be avoided. This can also be adjusted during the active use of JTL-Wawi.
- Limit random access memory to 80 per cent of the available memory. This way, the operating system still has enough resources to run at top performance. We recommend a stronger limitation to 60 or 70 per cent if other applications run on the same computer.
- Limit the maximum number of CPUs working in parallel. If you use more than four, we recommend choosing the option “Use only half of CPUs -1”. If you have eight CPUs, this means that you should only use three at the same time. With four CPUs or fewer, ideally two cores should be used at the same time. This does not mean that the rest of the processors stay idle. By setting these limits, you merely define the number of CPU cores working simultaneously on the same query set. The processors thus have the capacity to process large database queries for more than one processor and process other tasks and queries at the same time.
With these settings, your database should be able to process queries far smoother. To ensure consistent database efficiency, follow the suggestions in the chapter “Tips for SQL server maintenance”.
Further information in the JTL-Blog
You can find even more information in our JTL-Blog on how to optimally set up your SQL database and maintain the servers to ensure optimal Wawi performance:
Optimal setup of your SQL database
Related topics