{"id":192046,"date":"2018-12-19T09:36:46","date_gmt":"2018-12-19T08:36:46","guid":{"rendered":"https:\/\/guide.jtl-software.com\/optimal-sql-server-setup\/"},"modified":"2023-06-14T14:55:52","modified_gmt":"2023-06-14T12:55:52","slug":"optimal-sql-server-setup","status":"publish","type":"page","link":"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/optimal-sql-server-setup\/","title":{"rendered":"Optimal SQL server setup"},"content":{"rendered":"<div class=\"wpb-content-wrapper\">[vc_row][vc_column width=&#8221;1\/3&#8243; responsive=&#8221;{&#8220;col&#8220;: &#8220;12&#8220;, &#8220;order-md&#8220;: &#8220;1&#8220;, &#8220;col-lg&#8220;: &#8220;4&#8220;, &#8220;order-lg&#8220;: &#8220;12&#8220;}&#8221; advanced=&#8221;{&#8220;class&#8220;: &#8220;&#8220;, &#8220;hash&#8220;: &#8220;&#8220;}&#8221;]<div id=\"toc\">\n\t<div class=\"card border-0 bg-rysywhite mt-0 mt-lg-5 mb-5 w-100\" data-toc-element>\n\t\t<div class=\"d-none d-lg-flex flex-column\">\n\t\t\t<span class=\"fa-layers mx-auto mt--4 fa-fw fa-3x text-shaftblue\">\n\t\t\t\t<span class=\"fas fa-circle\"><\/span>\n\t\t\t\t<span class=\"fas fa-inverse fa-list-ul\" data-fa-transform=\"shrink-8\"><\/span>\n\t\t\t<\/span>\n\t\t\t<span class=\"font-weight-bold text-shaftblue text-center h5 mt-3 mb-0\">Contents<\/span>\n\t\t<\/div>\n\t\t<div class=\"d-block d-lg-none\">\n\t\t\t<a class=\"text-decoration-none card-header-link collapsed\" data-toggle=\"collapse\" href=\"#collapse-toc\" aria-expanded=\"false\" aria-controls=\"collapse-toc\">\n\t\t\t\t<div class=\"card-header d-flex bg-white align-items-center\" role=\"tab\" id=\"heading-toc\">\n\t\t\t\t\t<span class=\"h4 mb-0 d-flex align-items-center\">\n\t\t\t\t\t\t<span class=\"fa-layers fa-fw fa-2x mr-2 text-shaftblue\">\n\t\t\t\t\t\t\t<span class=\"fas fa-circle\"><\/span>\n\t\t\t\t\t\t\t<span class=\"fas fa-inverse fa-list-ul\" data-fa-transform=\"shrink-8\"><\/span>\n\t\t\t\t\t\t<\/span>\n\t\t\t\t\t\t<span>Contents<\/span>\n\t\t\t\t\t<\/span>\n\t\t\t\t\t<span class=\"flex-shrink-0 ml-auto fas fa-caret-down text-gray\"><\/span>\n\t\t\t\t<\/div>\n\t\t\t<\/a>\n\t\t<\/div>\n\t\t<div id=\"collapse-toc\" class=\"collapse show\" data-parent=\"#toc\" role=\"tabpanel\" aria-labelledby=\"heading-toc\">\n\t\t\t<div class=\"card-body p-4 table-of-content\">\n\t\t\t\t<ul data-toc-headings=\"h2,h3\" data-toc class=\"list-wawi\"><\/ul>\n\t\t\t<\/div>\n\t    <\/div>\n\t<\/div>\n<\/div>[\/vc_column][vc_column responsive=&#8221;{&#8220;col&#8220;: &#8220;12&#8220;, &#8220;col-md&#8220;: &#8220;8&#8220;, &#8220;order-md&#8220;: &#8220;12&#8220;, &#8220;order-lg&#8220;: &#8220;1&#8220;}&#8221; width=&#8221;2\/3&#8243; advanced=&#8221;{&#8220;class&#8220;: &#8220;&#8220;, &#8220;hash&#8220;: &#8220;&#8220;}&#8221;]<h1 class=\"d-flex align-items-center\" id=\"optimal-sql-server-setup-for-jtl-wawi\">Optimal SQL server setup for JTL-Wawi <span id='anchor-optimal-sql-server-setup-for-jtl-wawi' data-href='#optimal-sql-server-setup-for-jtl-wawi' class='far fa-link' data-anchor><\/span><\/h1><div class=\"custom-box\">\n\t<div class=\"d-flex align-items-center mb-4\">\n\t\t\t\t\t\t\t<span class=\"mr-3 fa-stack flex-shrink-0 fa-1x\">\n\t\t\t<span class=\"fas fa-circle fa-stack-2x text-shaftblue\"><\/span>\n\t\t\t\t\t<span class=\"flex-shrink-0 fas fa-clipboard-list text-white fa-stack-1x\"><\/span>\n\t\t\t\t\t<\/span>\n\t\t\t\t\n\t\t\n\t\t<h3 class=\"mb-0 \" id=\"your-task-initial-situation\">Your task\/initial situation <span id='anchor-your-task-initial-situation' data-href='#your-task-initial-situation' class='far fa-link' data-anchor><\/span><\/h3>\t<\/div>\n\t<div class=\"box-content\">\n\t\tYou want to set up your first online shop, so you download JTL-Wawi and install it. You complete the configuration of Microsoft\u00a0SQL\u00a0Server and start entering your items and setting up your online shop. It&#8217;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.\t<\/div>\n<\/div>\n<div id=\"parent_69e0dbce071ff\" role=\"tablist\" aria-multiselectable=\"true\" class=\"accordion mb-5 parent_69e0dbce071ff\">\n\t<div class=\"accordion-group\">\n\t<div class=\"accordion-header no-gradient collapsed\" role=\"tab\" id=\"group_69e0dbce0730c\" data-toggle=\"collapse\" data-target=\"#group_69e0dbce0730c_collapse\" aria-expanded=\"true\" aria-controls=\"group_69e0dbce0730c_collapse\">\n\t\t<div class=\"accordion-title\">\n\t\t\tGeneral information on databases\t\t\t<span class=\"fas fa-caret-up indicator\"><\/span>\n\t\t<\/div>\n\t<\/div>\n\t<div id=\"group_69e0dbce0730c_collapse\" class=\"accordion-collapse collapse \" role=\"tabpanel\" aria-labelledby=\"group_69e0dbce0730c\" data-parent=\".parent_69e0dbce071ff\">\n\t\t<div class=\"accordion-body clearfix\">\n\t\t\t\t<p>JTL-Wawi uses Microsoft\u00a0SQL 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.<\/p>\n\t\t<\/div>\n\t<\/div>\n<\/div><\/div>\n<h2 class=\"d-flex align-items-center hl-wawi\" id=\"hardware\">Hardware <span id='anchor-hardware' data-href='#hardware' class='far fa-link' data-anchor><\/span><\/h2>\t<p>The right choice of hardware is a decisive factor for quick access to data on an SQL server.<\/p>\n<h3 class=\"d-flex align-items-center h4\" id=\"requirements-for-all-sql-server-versions\">Requirements for all SQL server versions <span id='anchor-requirements-for-all-sql-server-versions' data-href='#requirements-for-all-sql-server-versions' class='far fa-link' data-anchor><\/span><\/h3>\t<p>The computer on which the MS\u00a0SQL Server is running should have the following hardware specifications:<\/p>\n<ul class=\"list-reset ml-3 list-icons list-custom-square list-wawi\">\n\t<li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\t<b>Powerful processor:<\/b>&nbsp;The processor permits the system to process queries very quickly.\t<\/li><li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\t<b>Four different hard drives:<\/b> Ideally, these should be SSDs, as they are much more powerful.\n\nSince 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.\t<\/li><\/ul>\t<p>Set up your system as follows:<\/p>\n<ul class=\"list-reset ml-3 list-icons list-custom-square list-wawi\">\n\t<li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\tOn the used computer, do not install anything beside the operating system and the SQL server.\t<\/li><li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\t<b>Hard drive 1:<\/b>&nbsp;Install the SQL server on the same hard drive on which the operating system is installed.\t<\/li><li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\t<b>Hard drive 2\/3:<\/b>&nbsp;Save both the .mdf file and the .log file on separate data mediums.\t<\/li><li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\t<b>Hard drive 4:<\/b>&nbsp;For the TempDB, choose another separate hard drive, ideally the fasted one.\t<\/li><\/ul>\t<p>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:<\/p>\n<ul class=\"list-reset ml-3 list-icons list-custom-square list-wawi\">\n\t<li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\t<b>CPUs:<\/b>&nbsp;Depending on your licence type, use the highest possible number of CPUs to improve the general computing power for large databases.\t<\/li><li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\t<b>RAM:<\/b> 32\u00a0GB or more. The more RAM, the better. This allows you to quickly retrieve as much data as possible from the cache.\t<\/li><\/ul>\t<p>Once these hardware requirements have been met, the first important step has been completed. The infrastructure for quick database queries is in place.<\/p>\n<h2 class=\"d-flex align-items-center hl-wawi\" id=\"configuration\">Configuration <span id='anchor-configuration' data-href='#configuration' class='far fa-link' data-anchor><\/span><\/h2><h3 class=\"d-flex align-items-center hl-wawi\" id=\"compatibility-level\">Compatibility level <span id='anchor-compatibility-level' data-href='#compatibility-level' class='far fa-link' data-anchor><\/span><\/h3>\t<p>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.<\/p>\n<ol start=\"1\" class=\"list-reset step mb-5 list-wawi normal-step\">\n\t<li >\n\tTo adjust the compatibility settings, open Microsoft SQL Server Management Studio and log in with your login data.\n\nIn the Object\u00a0Explorer, define the compatibility level for your SQL server.<\/li><li >\n\tRight-click on the database and select&nbsp;<span class=\"highlight\">Properties<\/span>.\n\nThe dialogue box&nbsp;<span class=\"highlight\">Database properties<\/span> opens.<\/li><\/ol>\t\t\t\t\t\t<div data-gallery>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-001-dateiexplorer.png\" itemprop=\"contentUrl\">\n\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-001-dateiexplorer.png\" srcset=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-001-dateiexplorer.png 1600w,https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-001-dateiexplorer.png 1024w,https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-001-dateiexplorer.png 640w\" width=\"418\" sizes=\"auto, (max-width: 20em) 30vw, (max-width: 30em) 60vw, (max-width: 40em) 90vw\" height=\"421\" data-pswp=\"{&quot;src&quot;:&quot;https:\\\/\\\/guide.jtl-software.com\\\/uploads\\\/Jtl-wawi-sql-server-001-dateiexplorer.png&quot;,&quot;w&quot;:418,&quot;h&quot;:421}\" class=\"border-gray border-w-1 border mb-5 img-fluid mb-5\">\n\t\t\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t<ol start=\"3\" class=\"list-reset step mb-5 list-wawi normal-step\">\n\t<li >\n\tOpen the&nbsp;<span class=\"highlight\">Options<\/span> tab.\n\nHere you will find the settings for the server&#8217;s compatibility level.\n\nTo 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.<\/li><\/ol>\t\t\t\t\t\t<div data-gallery>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-001-kompatibilit\u00e4tsgrad.png\" itemprop=\"contentUrl\">\n\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-001-kompatibilit\u00e4tsgrad.png\" srcset=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-001-kompatibilit\u00e4tsgrad.png 1600w,https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-001-kompatibilit\u00e4tsgrad.png 1024w,https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-001-kompatibilit\u00e4tsgrad.png 640w\" width=\"690\" sizes=\"auto, (max-width: 20em) 30vw, (max-width: 30em) 60vw, (max-width: 40em) 90vw\" height=\"625\" data-pswp=\"{&quot;src&quot;:&quot;https:\\\/\\\/guide.jtl-software.com\\\/uploads\\\/Jtl-wawi-sql-server-001-kompatibilit\\u00e4tsgrad.png&quot;,&quot;w&quot;:690,&quot;h&quot;:625}\" class=\"border-gray border-w-1 border mb-5 img-fluid mb-5\">\n\t\t\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t<h3 class=\"d-flex align-items-center hl-wawi\" id=\"deactivating-auto-close-and-auto-shrink\">Deactivating Auto Close and Auto Shrink <span id='anchor-deactivating-auto-close-and-auto-shrink' data-href='#deactivating-auto-close-and-auto-shrink' class='far fa-link' data-anchor><\/span><\/h3><ol start=\"4\" class=\"list-reset step mb-5 list-wawi normal-step\">\n\t<li >\n\tDeactivate the options&nbsp;<span class=\"highlight\">Auto Close<\/span>&nbsp;and&nbsp;<span class=\"highlight\">Auto Shrink<\/span> by selecting the option&nbsp;<span class=\"highlight\">false<\/span>.\n\nDeactivating these features optimises the performance of the SQL server.<\/li><\/ol>\t\t\t\t\t\t<div data-gallery>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-002-autoshrink.png\" itemprop=\"contentUrl\">\n\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-002-autoshrink.png\" srcset=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-002-autoshrink.png 1600w,https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-002-autoshrink.png 1024w,https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-002-autoshrink.png 640w\" width=\"690\" sizes=\"auto, (max-width: 20em) 30vw, (max-width: 30em) 60vw, (max-width: 40em) 90vw\" height=\"625\" data-pswp=\"{&quot;src&quot;:&quot;https:\\\/\\\/guide.jtl-software.com\\\/uploads\\\/Jtl-wawi-sql-server-002-autoshrink.png&quot;,&quot;w&quot;:690,&quot;h&quot;:625}\" class=\"border-gray border-w-1 border mb-5 img-fluid mb-5\">\n\t\t\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t<div id=\"parent_69e0dbce095e9\" role=\"tablist\" aria-multiselectable=\"true\" class=\"accordion mb-5 parent_69e0dbce095e9\">\n\t<div class=\"accordion-group\">\n\t<div class=\"accordion-header no-gradient collapsed\" role=\"tab\" id=\"group_69e0dbce09674\" data-toggle=\"collapse\" data-target=\"#group_69e0dbce09674_collapse\" aria-expanded=\"true\" aria-controls=\"group_69e0dbce09674_collapse\">\n\t\t<div class=\"accordion-title\">\n\t\t\tMore information on: Auto Shrink and Auto Close\t\t\t<span class=\"fas fa-caret-up indicator\"><\/span>\n\t\t<\/div>\n\t<\/div>\n\t<div id=\"group_69e0dbce09674_collapse\" class=\"accordion-collapse collapse \" role=\"tabpanel\" aria-labelledby=\"group_69e0dbce09674\" data-parent=\".parent_69e0dbce095e9\">\n\t\t<div class=\"accordion-body clearfix\">\n\t\t\t\t<p>An important factor for a better server performance is the deactivation of&nbsp;<span class=\"highlight\">Auto Close<\/span>&nbsp;and&nbsp;<span class=\"highlight\">Auto Shrink<\/span>&nbsp;in the database.&nbsp;<span class=\"highlight\">Auto Shrink<\/span>&nbsp;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.&nbsp;<span class=\"highlight\">Auto Shrink<\/span>&nbsp;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.<\/p>\n\t\t<\/div>\n\t<\/div>\n<\/div><\/div>\n<ol start=\"5\" class=\"list-reset step mb-5 list-wawi normal-step\">\n\t<li >\n\tNow switch to the tab&nbsp;<span class=\"highlight\">Files<\/span>.\n\nHere 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.<\/li><li >\n\tIn the column&nbsp;<span class=\"highlight\">Autogrowth\/Maxsize<\/span>, click on the button&nbsp;<b>&#8230;.<\/b> in the MDF file row.\n\nThe dialogue box&nbsp;<span class=\"highlight\">Change Autogrowth<\/span> opens.<\/li><\/ol>\t\t\t\t\t\t<div data-gallery>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-003-Datenbankeigenschaften.png\" itemprop=\"contentUrl\">\n\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-003-Datenbankeigenschaften.png\" srcset=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-003-Datenbankeigenschaften.png 1600w,https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-003-Datenbankeigenschaften.png 1024w,https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-003-Datenbankeigenschaften-768x557.png 640w\" width=\"862\" sizes=\"auto, (max-width: 20em) 30vw, (max-width: 30em) 60vw, (max-width: 40em) 90vw\" height=\"625\" data-pswp=\"{&quot;src&quot;:&quot;https:\\\/\\\/guide.jtl-software.com\\\/uploads\\\/Jtl-wawi-sql-server-003-Datenbankeigenschaften.png&quot;,&quot;w&quot;:862,&quot;h&quot;:625}\" class=\"border-gray border-w-1 border mb-5 img-fluid mb-5\">\n\t\t\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t<ol start=\"7\" class=\"list-reset step mb-5 list-wawi normal-step\">\n\t<li >\n\tIn&nbsp;<span class=\"highlight\">File Growth<\/span>&nbsp;, set the size to 500\u00a0MB. Ideally, set the initial size to 10\u00a0GB (=10,000\u00a0MB).\n\nThis means that your database has a minimum size of 10\u00a0GB, which is only increased by another 10\u00a0GB once the database has reached that size.<\/li><li >\n\tAdjust the file growth for the .log file in the same way and set it to 100\u00a0MB.<\/li><\/ol>\t<p><span class=\"highlight\">Close the database properties of your database.<\/span><\/p>\n<ol start=\"9\" class=\"list-reset step mb-5 list-wawi normal-step\">\n\t<li >\n\tRepeat steps 6 to 8 for the <span class=\"highlight\">TempDB<\/span> database. Set the initial size to 100\u00a0MB for both the .mdf and the .log file.<\/li><\/ol>\t\t\t\t\t\t<div data-gallery>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-004-Temp-DB.jpg\" itemprop=\"contentUrl\">\n\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-004-Temp-DB.jpg\" srcset=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-004-Temp-DB.jpg 1600w,https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-004-Temp-DB.jpg 1024w,https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-004-Temp-DB.jpg 640w\" width=\"584\" sizes=\"auto, (max-width: 20em) 30vw, (max-width: 30em) 60vw, (max-width: 40em) 90vw\" height=\"489\" data-pswp=\"{&quot;src&quot;:&quot;https:\\\/\\\/guide.jtl-software.com\\\/uploads\\\/Jtl-wawi-sql-server-004-Temp-DB.jpg&quot;,&quot;w&quot;:584,&quot;h&quot;:489}\" class=\"border-gray border-w-1 border mb-5 img-fluid mb-5\">\n\t\t\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t<h3 class=\"d-flex align-items-center hl-wawi\" id=\"servicepacks-and-updates\">ServicePacks and Updates <span id='anchor-servicepacks-and-updates' data-href='#servicepacks-and-updates' class='far fa-link' data-anchor><\/span><\/h3>\t<p>To ensure that the software is always up to date, install available updates and service packs for your SQL Server as soon as possible.<\/p>\n<p>You can find the latest updates here: <a href=\"https:\/\/learn.microsoft.com\/en-gb\/sql\/database-engine\/install-windows\/latest-updates-for-microsoft-sql-server?view=sql-server-ver15\">Microsoft SQL Server updates<\/a><\/p>\n<h3 class=\"d-flex align-items-center hl-wawi\" id=\"important-automatic-data-backup\">Important: Automatic data backup <span id='anchor-important-automatic-data-backup' data-href='#important-automatic-data-backup' class='far fa-link' data-anchor><\/span><\/h3>\t<p>It is important that you set up an <strong>automatic data backup<\/strong> that backs up your databases at least once a day. The backup should be saved on a second drive or another computer!<\/p>\n<p>Read the Microsoft documentation for more information: <a href=\"https:\/\/learn.microsoft.com\/en-gb\/sql\/relational-databases\/maintenance-plans\/options-in-the-back-up-database-task-for-maintenance-plan?view=sql-server-ver15\">Data backup for SQL Server<\/a>.<\/p>\n<h2 class=\"d-flex align-items-center\" id=\"special-settings-for-different-sql-server-editions\">Special settings for different SQL Server Editions <span id='anchor-special-settings-for-different-sql-server-editions' data-href='#special-settings-for-different-sql-server-editions' class='far fa-link' data-anchor><\/span><\/h2>\t<p>There are some special settings when it comes to the Standard Edition and the Enterprise Version.<\/p>\n<h3 class=\"d-flex align-items-center hl-wawi\" id=\"standard-and-enterprise-version\">Standard and Enterprise Version <span id='anchor-standard-and-enterprise-version' data-href='#standard-and-enterprise-version' class='far fa-link' data-anchor><\/span><\/h3>\t<p>If you are using the Standard or Enterprise Version, adjust these settings after following the steps above:<\/p>\n<ol start=\"1\" class=\"list-reset step mb-5 list-wawi normal-step\">\n\t<li >\n\tFor every CPU core, create a new .mdf file in the TempDB with an initial size of 100\u00a0MB and a file growth of 100\u00a0MB.\nSince 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.<\/li><\/ol><ul class=\"ml-5 list-reset ml-3 list-icons list-custom-square list-wawi\">\n\t<li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\tIn Microsoft SQL Server Management Studio, open the Object\u00a0Explorer and go to <span class=\"highlight\">Databases &gt; System Databases<\/span>, right-click on the TempDB and select <span class=\"highlight\">Properties<\/span>.\t<\/li><li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\tSwitch to the tab <span class=\"highlight\">Files<\/span>.\t<\/li><li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\tClick <span class=\"highlight\">Add<\/span>.\t<\/li><\/ul><ol start=\"2\" class=\"list-reset step mb-5 list-wawi normal-step\">\n\t<li >\n\tLimit random access memory to 80 per\u00a0cent of the available memory.\n\nThis way, the operating system still has enough resources to run at top performance. We recommend a stronger limitation to 60 or 70 per\u00a0cent if other applications run on the same computer.<\/li><\/ol><ul class=\"ml-5 list-reset ml-3 list-icons list-custom-square list-wawi\">\n\t<li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\tIn Microsoft SQL Server Management Studio, right-click on the server and select <span class=\"highlight\">Properties<\/span>.\t<\/li><li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\tGo to the tab <span class=\"highlight\">Memory<\/span>.\t<\/li><li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\tConfigure the respective settings under <span class=\"highlight\">Max Memory<\/span>.\t<\/li><\/ul>\t\t\t\t\t\t<div data-gallery>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-005-Serverarbeitsspeicher.jpg\" itemprop=\"contentUrl\">\n\t\t\t\t\t\t\t<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-005-Serverarbeitsspeicher.jpg\" srcset=\"https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-005-Serverarbeitsspeicher.jpg 1600w,https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-005-Serverarbeitsspeicher-1024x569.jpg 1024w,https:\/\/guide.jtl-software.com\/uploads\/Jtl-wawi-sql-server-005-Serverarbeitsspeicher-768x427.jpg 640w\" width=\"1127\" sizes=\"auto, (max-width: 20em) 30vw, (max-width: 30em) 60vw, (max-width: 40em) 90vw\" height=\"626\" data-pswp=\"{&quot;src&quot;:&quot;https:\\\/\\\/guide.jtl-software.com\\\/uploads\\\/Jtl-wawi-sql-server-005-Serverarbeitsspeicher.jpg&quot;,&quot;w&quot;:1127,&quot;h&quot;:626}\" class=\"border-gray border-w-1 border mb-5 img-fluid mb-5\">\n\t\t\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t<ol start=\"3\" class=\"list-reset step mb-5 list-wawi normal-step\">\n\t<li >\n\tLimit the maximum number of CPUs working in parallel.\n\nIf you use more than four, we recommend choosing the option \u201cUse only half of CPUs -1\u201d. 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.<\/li><\/ol><ul class=\"ml-5 list-reset ml-3 list-icons list-custom-square list-wawi\">\n\t<li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\tIn Microsoft SQL Server Management Studio, right-click on the server and select <span class=\"highlight\">Properties<\/span>.\t<\/li><li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\tGo to the tab <span class=\"highlight\">Advanced<\/span>.\t<\/li><li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\tEnter the desired value into <span class=\"highlight\">Max Degree of Parallelism<\/span>. This value should never equal 0. Enter at least 2, but no more than 8.\t<\/li><li >\n\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\tUnder <span class=\"highlight\">Parallelism <\/span>, set the <span class=\"highlight\">Cost Threshold for Parallelism<\/span>&nbsp;to 75.\t<\/li><\/ul>\t<p>With these settings, your database should be able to process queries far smoother. To ensure consistent database efficiency, follow the suggestions in the chapter \u201cTips for SQL server maintenance\u201d.<\/p>\n<h2 class=\"d-flex align-items-center hl-wawi\" id=\"further-information-in-the-jtl-blog\">Further information in the JTL-Blog <span id='anchor-further-information-in-the-jtl-blog' data-href='#further-information-in-the-jtl-blog' class='far fa-link' data-anchor><\/span><\/h2>\t<p>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:<\/p>\n<p><span style=\"color: #f68b25;\"><a style=\"color: #f68b25;\" href=\"https:\/\/www.jtl-software.de\/blog\/loesungen-von-jtl\/optimale-einrichtung-der-sql-datenbank\">Optimal setup of your SQL database<\/a><\/span><\/p>\n<p><span style=\"color: #f68b25;\"><a style=\"color: #f68b25;\" href=\"https:\/\/www.jtl-software.de\/blog\/loesungen-von-jtl\/sql-server-wartung-teil-2\">Tips for server maintenance<\/a><\/span><\/p>\n<div class=\"mt-5 card\">\n\t\t\t<span class=\"fa-layers mx-auto mt--4 fa-fw fa-3x text-wawi\">\n\t\t\t<span class=\"fas fa-circle\"><\/span>\n\t\t\t<span data-fa-transform=\"shrink-8\" class=\"fas fa-link fa-inverse\"><\/span>\n\t\t<\/span>\n\t\t\t<div class=\"card-body p-4\">\n\t\t<p class=\"d-flex align-items-center hl-wawi h4\">Related topics<\/p>\n<ul class=\"list-reset list-icons list-custom-square list-wawi\">\n\t\t\t\t\t\t<li>\n\t\t\t\t<a href=\"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/database\/executing-sql-on-the-database\/\" title=\"Executing SQL on the database\">\n\t\t\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\t\t\t\tExecuting SQL on the database\t\t\t\t<\/a>\n\t\t\t<\/li>\n\t\t\t\t\t<li>\n\t\t\t\t<a href=\"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/installation-of-microsoft-sql-server-express\/\" title=\"Installation of Microsoft SQL Server Express\">\n\t\t\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\t\t\t\tInstallation of Microsoft SQL Server Express\t\t\t\t<\/a>\n\t\t\t<\/li>\n\t\t\t\t\t<li>\n\t\t\t\t<a href=\"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/tips-for-sql-server-maintenance\/\" title=\"Tips for SQL server maintenance\">\n\t\t\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\t\t\t\tTips for SQL server maintenance\t\t\t\t<\/a>\n\t\t\t<\/li>\n\t\t\t\t\t<li>\n\t\t\t\t<a href=\"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/securing-the-sql-server-connection-of-jtl-wawi-with-ssl\/\" title=\"Securing the SQL server connection of JTL-Wawi with SSL\">\n\t\t\t\t\t<span class=\"list-icon fas fa-square\"><\/span>\t\t\t\t\tSecuring the SQL server connection of JTL-Wawi with SSL\t\t\t\t<\/a>\n\t\t\t<\/li>\n\t\t\t\t<\/ul>\t<\/div>\n<\/div>[\/vc_column][\/vc_row]<\/div>","protected":false},"excerpt":{"rendered":"[vc_row][vc_column width=&#8221;1\/3&#8243; responsive=&#8221;{&#8220;col&#8220;: &#8220;12&#8220;, &#8220;order-md&#8220;: &#8220;1&#8220;, &#8220;col-lg&#8220;: &#8220;4&#8220;, &#8220;order-lg&#8220;: &#8220;12&#8220;}&#8221; advanced=&#8221;{&#8220;class&#8220;: &#8220;&#8220;, &#8220;hash&#8220;: &#8220;&#8220;}&#8221;][\/vc_column][vc_column responsive=&#8221;{&#8220;col&#8220;: &#8220;12&#8220;, &#8220;col-md&#8220;: &#8220;8&#8220;, &#8220;order-md&#8220;: &#8220;12&#8220;, <a href=\"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/securing-the-sql-server-connection-of-jtl-wawi-with-ssl\/\">[&#8230;]<\/a>","protected":false},"author":2,"featured_media":0,"parent":192322,"menu_order":741,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"_relevanssi_hide_post":"","_relevanssi_hide_content":"","_relevanssi_pin_for_all":"","_relevanssi_pin_keywords":"","_relevanssi_unpin_keywords":"","_relevanssi_related_keywords":"","_relevanssi_related_include_ids":"","_relevanssi_related_exclude_ids":"","_relevanssi_related_no_append":"","_relevanssi_related_not_related":"","_relevanssi_related_posts":"","_relevanssi_noindex_reason":"","footnotes":""},"tags":[],"class_list":["post-192046","page","type-page","status-publish","hentry","pages_category-print-wawi-1-installation-en","pages_category-sql-en"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.8 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Optimal SQL server setup for the ERP JTL-Wawi<\/title>\n<meta name=\"description\" content=\"Get the most out of your Microsoft SQL Server! Learn about the optimal set up of your server for your free ERP system JTL-Wawi!\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/optimal-sql-server-setup\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Optimal SQL server setup for the ERP JTL-Wawi\" \/>\n<meta property=\"og:description\" content=\"Get the most out of your Microsoft SQL Server! Learn about the optimal set up of your server for your free ERP system JTL-Wawi!\" \/>\n<meta property=\"og:url\" content=\"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/optimal-sql-server-setup\/\" \/>\n<meta property=\"og:site_name\" content=\"JTL-Guide\" \/>\n<meta property=\"article:modified_time\" content=\"2023-06-14T12:55:52+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"15 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/optimal-sql-server-setup\/\",\"url\":\"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/optimal-sql-server-setup\/\",\"name\":\"Optimal SQL server setup for the ERP JTL-Wawi\",\"isPartOf\":{\"@id\":\"https:\/\/guide.jtl-software.com\/en\/#website\"},\"datePublished\":\"2018-12-19T08:36:46+00:00\",\"dateModified\":\"2023-06-14T12:55:52+00:00\",\"description\":\"Get the most out of your Microsoft SQL Server! Learn about the optimal set up of your server for your free ERP system JTL-Wawi!\",\"inLanguage\":\"en-en\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/optimal-sql-server-setup\/\"]}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/guide.jtl-software.com\/en\/#website\",\"url\":\"https:\/\/guide.jtl-software.com\/en\/\",\"name\":\"JTL-Guide\",\"description\":\"Dokumentation f\u00fcr die gesamte Produktfamilie von JTL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/guide.jtl-software.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-en\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Optimal SQL server setup for the ERP JTL-Wawi","description":"Get the most out of your Microsoft SQL Server! Learn about the optimal set up of your server for your free ERP system JTL-Wawi!","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/optimal-sql-server-setup\/","og_locale":"en_US","og_type":"article","og_title":"Optimal SQL server setup for the ERP JTL-Wawi","og_description":"Get the most out of your Microsoft SQL Server! Learn about the optimal set up of your server for your free ERP system JTL-Wawi!","og_url":"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/optimal-sql-server-setup\/","og_site_name":"JTL-Guide","article_modified_time":"2023-06-14T12:55:52+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"15 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/optimal-sql-server-setup\/","url":"https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/optimal-sql-server-setup\/","name":"Optimal SQL server setup for the ERP JTL-Wawi","isPartOf":{"@id":"https:\/\/guide.jtl-software.com\/en\/#website"},"datePublished":"2018-12-19T08:36:46+00:00","dateModified":"2023-06-14T12:55:52+00:00","description":"Get the most out of your Microsoft SQL Server! Learn about the optimal set up of your server for your free ERP system JTL-Wawi!","inLanguage":"en-en","potentialAction":[{"@type":"ReadAction","target":["https:\/\/guide.jtl-software.com\/en\/jtl-wawi\/installation\/optimal-sql-server-setup\/"]}]},{"@type":"WebSite","@id":"https:\/\/guide.jtl-software.com\/en\/#website","url":"https:\/\/guide.jtl-software.com\/en\/","name":"JTL-Guide","description":"Dokumentation f\u00fcr die gesamte Produktfamilie von JTL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/guide.jtl-software.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-en"}]}},"_links":{"self":[{"href":"https:\/\/guide.jtl-software.com\/en\/wp-json\/wp\/v2\/pages\/192046","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/guide.jtl-software.com\/en\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/guide.jtl-software.com\/en\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/guide.jtl-software.com\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/guide.jtl-software.com\/en\/wp-json\/wp\/v2\/comments?post=192046"}],"version-history":[{"count":1,"href":"https:\/\/guide.jtl-software.com\/en\/wp-json\/wp\/v2\/pages\/192046\/revisions"}],"predecessor-version":[{"id":192061,"href":"https:\/\/guide.jtl-software.com\/en\/wp-json\/wp\/v2\/pages\/192046\/revisions\/192061"}],"up":[{"embeddable":true,"href":"https:\/\/guide.jtl-software.com\/en\/wp-json\/wp\/v2\/pages\/192322"}],"wp:attachment":[{"href":"https:\/\/guide.jtl-software.com\/en\/wp-json\/wp\/v2\/media?parent=192046"}],"wp:term":[{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/guide.jtl-software.com\/en\/wp-json\/wp\/v2\/tags?post=192046"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}