Quick search:eBay DataTransfer JTL-Worker PayPal

JTL-Links

Thank you for using the English version of the JTL-Guide!
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.

Note: On 1 September 2024, JTL introduced new editions that bundle most of the previous individual services into different packages. In order for you to continue working as smoothly as possible, existing customers were automatically switched to the package configuration that best matched their existing contract. The individual packages differ in terms of limitations and/or range of functions. Therefore, some of the features described in the JTL-Guide may not be available to you as part of your booked JTL edition. You can change your package configuration in your JTL customer account. Find out more about the new editions and packages here. Check our FAQ for frequently asked questions about the product editions.

Contents

Optimal SQL server setup for JTL-Wawi

Your task/initial situation

You want to set up your first online shop, so you download JTL-Wawi and install it. You complete the configuration of Microsoft SQL Server and start entering your items and setting up your online shop. It’s not all that difficult, is it? After a few months, however, you have the feeling that JTL-Wawi is running slower and slower. This is neither a bug nor a feature but usually SQL server settings that are not ideal. When your database is set up correctly, the ERP system runs faster and more efficiently.

General information on databases

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:

Powerful processor: The processor permits the system to process queries very quickly.
Four different hard drives: Ideally, these should be SSDs, as they are much more powerful. Since an SQL query involves several writing actions, using several hard drives allows for a significantly faster data throughput. Please note that it is not enough to simply set up four different hard drive petitions.

Set up your system as follows:

On the used computer, do not install anything beside the operating system and the SQL server.
Hard drive 1: Install the SQL server on the same hard drive on which the operating system is installed.
Hard drive 2/3: Save both the .mdf file and the .log file on separate data mediums.
Hard drive 4: For the TempDB, choose another separate hard drive, ideally the fasted one.

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:

CPUs: Depending on your licence type, use the highest possible number of CPUs to improve the general computing power for large databases.
RAM: 32 GB or more. The more RAM, the better. This allows you to quickly retrieve as much data as possible from the cache.

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.

More information on: Auto Shrink and Auto Close

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.

In Microsoft SQL Server Management Studio, open the Object Explorer and go to Databases > System Databases, right-click on the TempDB and select Properties.
Switch to the tab Files.
Click Add.

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.

In Microsoft SQL Server Management Studio, right-click on the server and select Properties.
Go to the tab Memory.
Configure the respective settings under Max Memory.

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.

In Microsoft SQL Server Management Studio, right-click on the server and select Properties.
Go to the tab Advanced.
Enter the desired value into Max Degree of Parallelism. This value should never equal 0. Enter at least 2, but no more than 8.
Under Parallelism , set the Cost Threshold for Parallelism to 75.

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

Tips for server maintenance