MS SQL Server Information : Support Portal

Question

What information should I consider when designing and setting up my MS SQL Server install?

Answer

Basic Structure

System databases

1. "Master" – system information about SQL includes the following

a. Database records of system level information

b. Records login accounts

c. Records the existence of all other databases including location of database files

2. "Model" – Used as a template when creating a new database, i.e. change auto growth settings here

3. "MSDB" – Used by SQL server agent

4. "TempDB" - Holds temporary tables, temporary stored procedures and work tables.

Also of note: "SQL Server Agent" – runs maintenance tasks

Summation Databases

1. "ADG" – Summation Case information, stored in different file groups and schemas

(Note: The schema version relates to FTKID located on case details screen [FTKID 1 = ADG_0001])

2. "EDiscovery" – legal matters table is where Summation pulls for UI

3. "Workflow" and "Workflow40"

4. "Infrastructure" – User logins (Security Log), System Logging (System Log) and Work orders

Databases

Every database is made up of mainly two parts, a database file (MDF) and a log file (LDF). The MDF holds all the information that has been committed to the database. The LDF file holds transactions to that database. The transactions saved inside the log file will differ based on the recovery model chosen. The ADG database has an additional feature to it called file groups, where each case is in its own file group, which has its own ndf file.

Recovery Models

FULL – default will log everything to the log file until a log backup is taken. This allows recovery to a specific point in time.

SIMPLE – can recover only to the end of the last backup, no logging for recent changes

Bulk logged – can recover to the end of the last backup, uses minimal logs for bulk operations.

(NOTE: Most law-firms/organizations prefer FULL to allow recovery from data loss or user errors when coding a database. Here is a link to a MS Technet article about such a recommendation. See also links at bottom entitled "Backup Under the Full Recovery Model (strategy)" and "Recovery Models Explained".)

Performance in Order of Importance

1. Processor

2. RAM

3. DISK I/O

a. Database File

b. Log file

c. TempDB

4. Network

Setting up SQL Server Notes

1. Minimum recommendation for locations of SQL Server databases:

a. Drive 1: OS

b. Drive 2: Database

c. Drive 3: Logs

d. Drive 4: TempDB

2. Database drive should be RAID 5 or 10: the database spindle set will require heavy random reads/writes, and this is why RAID 10 is optimal.

3. Logs drive can be RAID 1 or RAID 10: Transactional data stored here. This drive does sequential writes.

4. TempDB should be on RAID 0 or SSD: temp storage for SQL no redundancy needed.

a. In addition to speed of the drives, file i/o performance can be increased by increasing the number of TempDB files.

i. Microsoft suggests a 2GB TempDB per core (see Mircosoft's article Recommendations to reduce allocation contention in SQL Server tempdb database); I have never seen this work well.

ii. We suggest increasing the amount of TempDB (2 GB per) to 4 adding more until the disk queue of the databases/logs can’t keep up.

5. Set Auto growth on the model database to grow by 10% and start at 20 MB and Log 10 MB

a. NOTE: The 10% auto growth should be changed when the database increases to a size to warrant growing at a flat amount of 500 MB. 10 GB is a recommended number to start this.

b. Pre-size database when loading large sets of data

6. Set Max memory on SQL Server leaving room for operating system to breathe.

7. Put database files on the exclusion list for antivirus software.

8. SQL Server Standard 2008 R2 has a maximum 64 GB RAM

9. Fsutil can remove some OS disk overhead, see link below

10. Run SQL as service account

Helpful Links

SQL Server Max Memory Setting

Recovery Models explained

Backup Under the Full Recovery Model (strategy)

Storage Top 10 Best Practices for SQL Server

How to determine SQL Server version (down to service pack installed)