Best Practices for SQL Server for Syspro

These are recommended best practices for configuring SQL Server for use with Syspro.

Basic configuration

• The collation order must be Latin1_General_Bin for Syspro.
• Use Mixed Mode Authentication.
• Store your database on a DIFFERENT disk to your database's log file.
• Store your backups on a third disk.
• Use RAID 5 (recommended by Syspro) or RAID 1+0 for data protection.
• Give your database server plenty of memory. As a point of reference, 10 Gb is very nice for a 20 user site.
• Create at least one user for Syspro to access the database; don't use the system administrator user (sa) for Syspro!

Backup basics

• Copy your backups off your server, ideally daily
• Take a copy of your backup OFF SITE regularly
• Keep backups at several levels: yearly, monthly, weekly and daily,

Really bad backup practices to avoid at all costs

• Backing up to the same disk as the database - if the disk dies, you’ll lose all your data for ever!
• Not backing up at all!

SQL Log file settings

If using the FULL recovery model, the SQL log file should be set so that if it fills up, it will leave a few Gb unused on the file-system so that you don’t crash Windows.

If using the SIMPLE recovery model, the SQL Log file should have its maximum size set to a safe limit so that if it fills up to that limit, it WON’T fill up the hard disk. As a rough guide, that limit should be about 2 to 4 times the normal database size; anything beyond that would need special circumstances.

Other recommended log file settings:

• AUTOGROWTH - Yes
• AUTOSHRINK - NO; definitely not. Setting it to Yes is bad practice; the log file should ideally be a static size, large enough for each day’s transactions.
• Recovery Model - Full Recovery Model is recommended; SIMPLE Recovery Model is OK.

Anti-virus software on your SQL Server?

It sounds like a good thing, but it's the wrong tool for the job. You should be using a host intrusion detection system (H-IDS) such as Tripwire. See this post for details.
However, if you do have anti-virus software on your SQL and/or Syspro server, you should exempt these files from being scanned: your database files including log and backup files (*.mdf, *.ldf, *.bak), and all your Syspro settings, work and data files (C:\Syspro\Work\*, C:\Syspro\Data\*, C:\Syspro\Base\Settings\*).