SQL Server Hardware and Operating System Configuration
The information in this section is intended for advanced users or users with Database Administrator experience.
If you are using a dedicated SQL server (that is separate from the system where Service Center will be installed), it is recommended that:
- other database applications are not using the SQL instance or system;
- you do not install other SQL instances on the same machine.
Additionally, you should ensure the following:
- Because SQL Server relies heavily on RAM for efficiency, it is recommended to provide as much RAM as possible.
- At a minimum, use a gigabit Ethernet LAN connection between the SQL Server and the application server and SQL Report server.
- The Windows Page file should be enabled, located on a storage system that is separate from the SQL data and transaction volumes, and explicitly set to at least the same size as the system’s physical memory.
- To maximize performance and reduce potential configuration complexities, install SQL server on a physical machine and not in a virtual environment. An example of a performance issue that can occur is if you are running on hardware-based NUMA and the virtualization technology is not configured properly.
- RAID 10 is recommended for all data volumes to maximize performance and minimize downtime. In particular, it is not recommended to use RAID 5 for transaction logs. For example, if there is a hard drive failure and you have to rebuild the array, you might be prevented from doing so due to reduced performance and incomplete parity data or data damage. Additionally, Barracuda RMM is a write-intensive application, and RAID 5 is less efficient at performing writes.
- For large deployments, it is recommended to have an Active/Passive SQL Failover Cluster to minimize outages should hardware issues occur.
SQL Server Configuration
The following SQL Server configuration settings are recommended:
- Use the Simple Recovery model for the databases, which includes changing the system model database to SIMPLE. Set the model database data file to 2 GB, the model log file to 2 GB, and set the growth in increments of 1 GB for both data and log files.
- Pre-allocate the TempDB data files and TempDB transaction log file to at least 2 GB.
System with high speed IO and multiple CPUs may benefit from multiple TempDB data files. For more information, see Microsoft’s guidelines for multiple TempDB files.
- When setting the maximum memory that SQL Server can use, leave at least 2 to 10 GB of RAM for the operating system.
SQL Server Operational Maintenance
Barracuda RMM has a built-in database maintenance feature that includes all the necessary procedures to ensure the database is optimized and cleans up the old data which includes index maintenance and defragmentation, statistics updating, and data cleanup based on retention settings. You are not required to configure maintenance plans in SQL Management Studio or to implement your own maintenance for the MW SQL databases.
Additionally, do not shrink database data files unless there is critically low space situation. Shrinking data files causes high index fragmentation, requires a lot of CPU and IO, and generates a lot of transaction log activity.
SQL Server Back Ups
When backing up SQL databases, use either native SQL Server backups, which are configurable through SQL Management Studio using maintenance plans, or use a third party back up solution that interacts with the SQL database engine, causing a checkpoint. Back up technologies that freeze or lock the database files from SQL Server are not supported.
The following database backup practices are recommended:
- File system snapshots cause IO stalls, therefore it is not recommended that file system snapshots be used. IO stalls can cause user latency in Service Center, and can cause expected internal operations run by Service Center to encounter an unexpected error if it times out, which could leave the application in a degraded state.
- For quick recovery, store a copy of the SQL database backups locally on the SQL server. It is also recommended to store copies of the SQL database backups on another storage device (such as another computer or an external hard drive) that is separate from the SQL server’s main storage. This ensures that backup files can be recovered if there is a hardware failure on the SQL Server.
- It is not recommended that you back up the databases by making copies of the MDF or LDF files.
- Run back ups outside of business hours.