Tips for SQL server maintenance

Setting up a database backup

It is always possible that a database server cannot be restarted due to a problem. To prevent a loss of data relating to sales orders, customers and items, you should regularly back up your server. We recommend backing up your server at least once a day. The storage location for the backup should be different from the server location to prevent the backup from being destroyed along with the original, e.g. in a fire.

Manual backup

This is how you perform a manual backup:

1. In JTL-Wawi, open the database administration under Start > Datenbank (Start > Database).
2. Log in to the database administration and choose the Mandant (Tenant) for which you want to perform a backup.
3. Click DB-Export.
4. If necessary, select a Speicherort  (Storage location) and a Dateiname  (File name) and click Start. The database backup will now be created.

Automated backup

Users of the Standard, Enterprise or Web Editions can download a script on https://ola.hallengren.com/ that, automates backup generation and reviews the backup if configured properly. However, we recommend random manual checks of the backups because it is better to be safe than sorry.

Users of the Express version of the SQL server can create a workaround with the external tool SQLBackupandFTP .

Regular updating of indices

Imagine the database indices like a phone book. They provide the phone numbers for each name, i.e. the entries in the database. The problem is that the data changes regularly—some moves, some becomes redundant and new data is added. The SQL server generates new pages for the phone book based on the new information or moves certain entries to a different place. This results in chaos and a lot of useless information. The fragmented database causes long computing times as not all information is accessible in compressed form but is spread over several, often partially filled pages. This means that the number of accesses increases substantially.

This is how you launch the reorganisation and re-creation of the indices:

1. Open the database administration of JTL-Wawi via the menu Start > Datenbank (Start > Database).
2. Log in to the database and select the desired Mandant (Tenant).
3. Select Performanceoptimierung (Performance optimisation). This will take you to Mandanten optimieren (Optimise tenants).

4. Check the option Indizes neuorganisieren und neuerstellen (Reorganise and newly generate indices).

5. Click Starten (Start).

For users of the Standard, Enterprise or Web Editions, there is also a script provided on the Ola Hallengren site that allows you to automate this process. Users of the Express Edition need to perform a manual check.

Updating statistics

Statistics are useful when it comes to generating execution plans for the SQL server. Unfortunately, the server only performs a database update when more than 20 per cent of the content of a table have changed. Large databases do not often change to such a high extent, but there are still many individual entries in a table that may have moved to a different place.

Let us take a look at a more practical example: Imagine that the server is your store. The content of your database is the warehouse. The statistics are the list of what is currently in stock. A customer (i.e. the database query) arrives and wants to buy all the goods that you have received in the last 30 days. The inventory list (i.e. the statistics) has not been updated yet as the stock did not change by 20 per cent in the last 30 days. This means the salespersons has to go to the warehouse to check whether new goods have arrived. They take a basket, go to the warehouse and start searching. When they find a new item, they take it and bring it to the store. They then return to the warehouse and continue searching. They have to repeat this process for 100 new items. This takes a lot more time than if the customer knew that there were 100 new items and could have picked them all at the same time.

The following paragraph explains how to update the statistics of your database. We recommend performing the update once a week. However, make sure to carry out the update in the evening or at the weekend so as not to disrupt ongoing operation.

1. Open the database administration of JTL-Wawi via the menu Start > Datenbank (Start > Database).
2. Log in to the database and select the desired Mandant (Tenant).
3. Select Performanceoptimierung (Performance optimisation). This will take you to Mandanten optimieren (Optimise tenants).

4. Check the option Aktualisierung der Statistiken (Update statistics).

5. Click Starten (Start).

Less is more

Even though you now know how to optimise the software, you should not overdo it. Do not independently access the deeper functions of the database. Experience shows that this often leads to negative consequences. There are also a lot of database optimisation tools that can become a problem. Compared to the database’s own tool and the auxiliary tools recommended by JTL, there are others that may prevent JTL-Wawi from accessing the SQL server. It makes more sense to take the time to install the regular security updates for the server in order to protect yourself against external threats.

Video

Printer problem: No item labels are printed

In this video you will learn how to improve the performance of your SQL Server with the DB ConfigTool.