(PC) Support Tip: Setup MS SQL Server for ALM PC
Setting up and tuning the MS SQL server for database backend of your ALM PC environment is a task that is best to be performed by a database administrator (DBA). This is not always possible, however it is best at least the planning, installation and initial tuning of SQL server to be performed by a DBA.
Below are some basic steps, which can significantly improve availability, maintainability and performance.
Consider the SQL Server version and edition is supported for the planed ALM PC installation
- Consider choosing the correct default collation for the MS SQL server, which matches ALM PC language requirements
- Never setup the default databases directory to be on the system drive (usually drive C:\). MS SQL server intensively uses the database files and this will lead to competition with the OS for disk resources. Additionally, the normal behavior of databases is to grow, when the data are inserted, so SQL databases might consume all of the system drive disk space causing the entire platform to fail, because of insufficient disk space.
- If you have more physical drives in your system it is best to have database file *.mdf and *.ldf files on different drives. This will significantly improve both - the read and write performance. Using hardware RAID 10 or RAID 5 might additionally improve the performance
- MS SQL server works best when the underling disk subsystem is formatted with 64 Kbit OS cluster size. If the OS is older than Windows 2008, the disks needs also to be aligned
- It is good to have a dedicated MS SQL server database instance for ALM PCs. This will allow you to easily isolate the issues.
- By design MS SQL uses all of the available physical memory when it is highly utilized. This usually leads to performance degradation, because the OS might runs out of memory and intensively uses the swap file. It is good to limit the server max memory, that MS SQL can use
- The default installation for ALM PC uses full recovery model for the databases. This allows the database to be recovered to a specific point of time. However having this advantage require database maintenance task for having regular database backup strategy.
- SQL server uses intensively an internal database - temp db for some operations. Putting the temp dB on separate or fast drive can lead to additional performance improvement.
- If MS SQL server is performing some administrative task the performance usually decrease. If at the same time there is running a performance test it can fail because of slow DB response. However, creating a maintenance slots in ALM will prevent running the performance tests during the database maintenance.
- Do not leave the defaults for database and log file growing. For example, according to performance it is best to have a single file grow with 100 MB instead of 100 grows by 1 MB