SQL Server system setup recommendations
Server Usage
Ideally, Microsoft SQL Server will be installed on a dedicated server. This server should not be used for other activities or services such as Exchange or acting as a Domain Controller. Patriot services can be installed on the SQL server for smaller stations, but for larger databases a dedicated SQL server is highly recommended.
The instructions below describe the recommended configuration for a single dedicated SQL Server. Alternatively, Patriot also supports connecting to a clustered SQL installation. While the installation and configuration of a clustered SQL system is outside the scope of Patriot support, some Patriot-specific configuration information can be found in the SQL Clustering document.
SQL Server hard disk layout and storage
For optimum performance, the Patriot database files (MDF and LDF) should not be placed on the main operating system drive, but should be placed on separate drives. Backing up to the same drive as the database files will also affect performance.
Ideally, these items should all be spread out onto separate drives, however it is acknowledged that this is not always practical. Suggested layouts are as follows:
| 1 Disk | 2 Disks | 3 Disks | 4 Disks | 6 Disks | |
|---|---|---|---|---|---|
| TempDB | A | A | A | A | A |
| Patriot_Data | A | A | B | B | B |
| Patriot_Log | A | A | C | C | C |
| PatriotArchive_Data | A | A | B | B | D |
| PatriotArchive_Log | A | A | C | C | E |
| OS & Program Files | A | A | A | A | A |
| Backups | A | B | A | D | F |
The database files can be moved on an existing system by temporarily taking the system offline and using SQL tools such as SQL Management Studio.
Single drives should also be replaced with RAID 1 or RAID 10 (preferred) arrays where possible. RAID 5 should be avoided due to the performance overhead when writing.
When creating the partitions on the drives containing the SQL data files, it is recommended to set the File Allocation Size to 64K and the partition offset to 64K (or a multiple of 64K) as well. The default offset on Windows Server 2008 is 1024K which is perfectly fine. If using RAID, the stripe size should also be set to 64K.
Recommended Disk Sizes
Disk space requirements can be estimated by the expected size of the Patriot database.
These estimated sizes assume a standard archiving length of 6 months current history and 2 years archived history. Scheduled archiving and backups must be enabled to keep within this size limits. They are also a rough estimate, so where possible it will be better to install larger disks to ensure adequate storage space. If you are expecting a larger the normal signal volume you should also increase the size of all drives.
The estimates are given for each component. Where more than one component resides on the same physical drive, the estimates need to be combined to give a drive total.
| Site Records up to: | < 1000 Accounts | 5000 Accounts | 10000 Accounts | 20000 Accounts | 40000 Accounts | 80000 Accounts |
|---|---|---|---|---|---|---|
| TempDB (GB) | .5 | 1 | 2 | 3 | 6 | 12 |
| Patriot_Data (GB) | 3 | 7 | 13 | 26 | 52 | 104 |
| Patriot_Log (GB) | 1 | 3 | 6 | 12 | 24 | 48 |
| PatriotArchive_Data (GB) | 3 | 9 | 18 | 36 | 72 | 144 |
| PatriotArchive_Log (GB) | 1 | 4 | 9 | 18 | 36 | 72 |
| OS & Program Files (GB) | 80 | 80 | 80 | 80 | 80 | 80 |
| Backups (7 days) (GB) | 10 | 35 | 70 | 140 | 280 | 560 |
For systems larger than 20,000 accounts, multiply all columns (except OS) for each 20,000 accounts.
TempDB
Microsoft recommends the number of tempdb data files is equal to the number of logical processors on the SQL server, up to 8. Beyond 8, additional data files should only be added where contention is observed. All data files should be of equal size.
Enable Row Versioning
SQL Server has an optional setting READ_COMMITTED_SNAPSHOT which allows SQL to avoid taking locks for some queries, at the cost of additional tempdb usage. This setting can be enabled to improve system performance and avoid deadlocks, especially when running large reports or other heavy workloads.
To enable this setting, stop the Patriot Data Service and ensure nothing is using the Patriot databases. Then, run the following SQL Queries:
ALTER DATABASE Patriot SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE PatriotArchive SET READ_COMMITTED_SNAPSHOT ON;
Patriot can now be restarted and used as normal. To disable these settings, simply repeat the process, executing the following queries instead:
ALTER DATABASE Patriot SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE PatriotArchive SET READ_COMMITTED_SNAPSHOT OFF;
If your system is not using the Archive Database, simply change the setting for the main Patriot database only.
Windows Configuration
Ensure that Windows is up to date and that the latest hardware drivers are installed.
NTFS compression should be disabled for the SQL files as this adds overhead to every read and write.
In the Control Panel, System screen, Advanced, Performance Settings section, change Processor Scheduling/Application Response to prefer Background Services on the Advanced tab.
Check Windows Services and disable unnecessary services and features.
Ensure your network device is prioritising network applications by opening the properties of the Local Area Connection. In the properties of the File And Printer Sharing Microsoft Networks item, ensure Maximize data throughput for network applications is selected under Optimisation.