In this short article, we’ll look at how you can improve SQL Server performance for Sage integrations to help you get the most out of Sage, regardless of which version you use in your business.
As with many applications, the various versions of Sage, such as Sage 50 or Sage 200, depend greatly on the database system, SQL Server. As a result, it’s important to make sure that your database is optimised. Otherwise, as the workload increases, performance will suffer, potentially to the point that the database becomes completely unusable.
This in turn can have an impact on other daily operations, increasing waiting times, delaying relatively simple processes and even affecting recovery times in the event of a failure.
In this article, we’ll look at some of the things you can do to ensure that you enjoy the best performance.
How to Improve SQL Server Performance for Sage Integrations
Reorganise Stored Data
On databases where there is a high movement of data, it’s not for all this writing, deleting and rewriting to cause some issues. To address this, you can perform some maintenance tasks to reorganise your data.
This is similar to disk fragmentation on a PC. This is where small fragments of data get spread out randomly all over the disk, and this means that it takes longer to read data. By defragmenting the disk, you can relocate the data so that it is easier to locate.
You can perform a similar procedure with SQL Server. All you need to do is schedule maintenance tasks designed to reorganise the data stored.
To do this, launch the SQL Server Management Server console and click on Management > Maintenance Plans > Maintenance Wizard. Then, select the following from the list that appears:
- Check Database Integrity
- Shrink Database
- Reorganize Index
- Update Statistics
- Clean Up History
Troubleshoot SQL Server Memory Issues
Another common problem when integrating SQL Server and Sage is the memory allocation for the database.
Whether you have allocated too much or not enough, you might find yourself having the change the amount of memory available for the database.
You can do this by changing the database properties in the SQL Server Management Studio console.
For more detailed instructions, check out the “Setting RAM Thresholds” section in our blog article How to Improve the Performance of SQL Server 2019.
NOTE: As always, even though the aforementioned article is written for SQL Server 2019, the process is still more or less the same for other versions of SQL Server.
Important Points for Sage Clients Connected Via VPN
For those installations that require a VPN connection, you’ll need to bear in mind some other important points.
Firstly, you will need to make sure that you have a stable connection with enough bandwidth, not one that regularly disconnects or suffers from annoying “micro-cuts” in connection.
To resolve any potential connection issues, we recommend modifying the Sage configuration file (config.ini) to increase the connection time to 360 ([ENLACE_NET] 360). This should help prevent any drops in connection.
Using the SQL Server Tuning Advisor for Sage
We should also mention the SQL Server optimisation tool called Tuning Advisor. This tool is included in SQL Server and is available on the paid and Developer versions. It provides a way of checking the state of your database and make some improvements so that it operates more efficiently.
You can find more information in the “Using SQL Server Tuning Advisor” section in our blog article How to Improve the Performance of SQL Server 2019.
Make Sure Your Machines Meet Sage Requirements
A really important thing to check is that your machines satisfy the minimum requirements for Sage 50 to function correctly.
In reality, this is the very first thing you should think about, and this section should probably go at the beginning of this article. However, we didn’t want to draw attention away from the other points, which are just as important, so we’ve put this section at the end as a little reminder.
Now, your minimum requirements will depend on whether you have a single installation on a local machine or a server installation with multiple users.
If you have a Sage installation on a local machine, the minimum requirements (remember, minimum) will be:
- OS: Windows 7 SP1 or later, including Windows 8.1, Windows 10 and Windows 11. We recommend using the Professional, Enterprise or Ultimate version.
- Processor: Intel Core i3 or superior, including other processors with similar ranges and performance levels.
- RAM: You will require a minimum of 4GB, but we highly recommend having more.
- Hard Drive: You will require a minimum of 10GB free on the system, but we recommend having more and preferably an entire disk which is separate to the system disk. Similarly, we recommend using SSD drives.
- Connections: You’ll need an Ethernet 100/1000 BT network card or superior.
- Database: To install Sage 50, you’ll need a Microsoft SQL Server 2014 database or later, whether it’s 2014, 2016, 2017, 2019 or 2022. The Express version is supported.
- Other recommended software: We recommend having Microsoft Office 2010 or later in order to make the most of the all the Sage 50 features. Microsoft 365 is supported.
- For those devices that use remote access via Citrix or Terminal Server (ICA, RDP), you will need a minimum of 2GB of RAM and network access.
If you have an server installation with various users, you’ll have different requirements, which are as follows:
- OS: Windows 2012 or later, 64-bit versions, with both Standard or Datacenter versions supported.
- Processor: We recommend Intel Xeon, but other processors with similar ranges and performance levels are supported.
- RAM: You will require a minimum of 8GB. If you have more than 10 users, you’ll need a minimum of 16GB of RAM, but we highly recommend having more.
- Hard Drive: You will require a minimum of 10GB free on the system, but we recommend having more and preferably an entire disk which is separate to the system disk. Similarly, we recommend using SSD drives.
- Connections: You’ll need an Ethernet 100/1000 BT network card or superior.
- Database: To install Sage 50, you’ll need a Microsoft SQL Server 2014 database or later, whether it’s 2014, 2016, 2017, 2019 or 2022. The Express version is supported but we recommend using the Standard version as a minimum in order to get the best performance.
- We recommend keeping the server separate from the access terminal, but if it is all the same machine, the requirements will be greater. Similarly, where you have multiple roles on a single server, we recommend having Microsoft Office 2010 or later in order to enjoy all the features of Sage 50. Microsoft 365 is supported.
Conclusion
In this article, we’ve taken a brief look at some steps you can take to improve SQL Server performance for Sage integrations so that you get the most out of your hardware and OS.
However, it’s important to stress that these aren’t tasks that you carry out just once. Ideally, you should review all of these things on a regular basis to ensure that everything is OK and that you’re getting the very best performance.
If you would like to find out more about this topic, we have a number of articles and tutorials on our blog. And of course, there’s also the Sage installation guide.
We hope that you’ve found this brief article useful in getting the best performance out of SQL Server and Sage. However, if you do encounter any issues, don’t hesitate to contact us so that we can help you.
Thanks for choosing Jotelulu!
