How should I configure my disks for my new GP/SQL installation?

 It has been a while since I posted anything on disk setup for SQL Server and since technology marches on, below is my recommendation for a Great Plains/SQL installation.

Important considerations here are:

• GP databases are categorized as a high IO databases.
• GP databases are mission critical and depend on high reliability storage.

SQL Setup Footprint

• SQL Server has .mdb files, which are the main data files, and .ldb files, which are the transactional log files. These files should be sized with at least 10% empty space to prevent auto-growth.
• SQL Server has 4 system databases that are required for any SQL instance to work
• Master
• Holds the information that is global to the SQL instance.
• This is not a high use database and should not grow much over time.
• Typical size is less than 100MB.
• Best practice is to place its files in the same location as the user database files, but it installs on the OS drive by default and would need to be moved immediately after the SQL installation completes.
• These files should be backed up monthly or any time major changes are made, such as adding or removing databases.
• Model
• This is a template that is used for creating user databases.
• Configuring it to properly reflect your most common desired settings can save much configuration time of databases later.
• Typical size is less than 10MB.
• Best practice is to place its files in the same location as the user database files, but it installs on the OS drive by default and would need to be moved immediately after the SQL installation completes.
• These files should be backed up monthly.
• Msdb
• Holds the objects used by maintenance plans and SQL Agent objects (such as jobs and dbmail requests).
• Typical size is under 2GB.
• It will grow progressively larger due to job history records if maintenance is not regularly performed to clear those records. If this database gets too large, the SQL agent jobs may start failing due to poor performance.
• Best practice is to place its files in the same location as the user database files, but it installs on the OS drive by default and would need to be moved immediately after the SQL installation completes.
• Tempdb
• This is used by the SQL instance to process data reads and writes.
• Depending on the size of the databases being queried and the size of the data sets being returned, this database can grow quite large. The total size all files for tempdb in a typical GP instance is around 10GB, but month end processes can cause it to temporarily grow up to 100GB on a large system.
• The files for tempdb are deleted and rebuilt each time the SQL Services are restarted, so there is no need to back them up.
• It should be configured with the same number of files as there are active processors available for the SQL service.
• Because of its high IO load and its large effect on overall system performance, it is highly recommended that the tempdb files (data and log) be stored on a dedicated SSD drive.
• To discourage storage of other files on the drive and to prevent the need for file growth, best practice is to pre-size tempdb so that it takes up 80% of the drive space by default.
• A GP/Signature install will contain the following user databases 
• DYNAMICS
• This is the main database for the GP instance.
• Typical size is around 2GB on a large system and it does not typically grow very fast.
• Should be stored on user database and user log drives.
• Company database(s)
• There will be one database for each GP company. It will have a maximum of 5 characters in the name.
• Initial size with no testing data is around 2GB. Once the size hits 100GB, it is considered a large company database and steps should be taken to put procedures in place to archive or purge data regularly for performance.
• Some of these databases will have a high IO load during peak usage hours and would benefit from being stored on a dedicated drive, otherwise they should be stored on user database and user log drives.
• RESCOXRM_CONFIG
• Holds configuration data for Mobiletech.
• Not a high use database.
• Typical size if less than 10MB
• Should be stored on user database and user log drive
• {company}_RESCOXRM
• There will be one database for each company that MobileTech is installed on.
• Initial size is less than 100MB. Size will vary based on the amount of transactional data being exchanged with the mobile devices, but should not exceed the size of the company database.
• Should be stored on user database and user log drive
• ReportServer and ReportServerTempDB
• Holds SSRS reports and cached report data
• Typical size is less than 1GB.
• This database is high use for read operations.
• If possible, Report Server should be installed on a completed separate SQL Instance with separate storage. Otherwise, the files should be stored on the user database and user log drive.
• Other user databases
• Should be stored on the user data and user log drives.

SQL Backups

• Whether Maintenance plans or SQL Jobs are used, a SQL instance will not run for very long without regular maintenance.
• Backups are a part of that maintenance. There are routines that are run that allow the log file to write transactional data to the data file and clear out old transactional data that will only run when a backup is taken of the database.
• SQL needs a drive to write the backup files to.
• Best practice is to store these on a separate drive and to regularly make a copy of the backups and move off-site.

Drive setup

Drives should be sized to at least 25% larger than the initial size of the data. 

• Drive 1
• OS
• SQL binaries
• GP Test client
• Drive 2
• SSD if possible and physically located inside the server
• Data and log files for Temp DB
• Drive 3
• User database data files
• System database data files
• Drive 4
• User database log files
• System database log files
• Drive 5
• Backup files

Types of disks

• HDD
• Spinning hard drive.
• Today's 15k HDD drives can transfer data at around 130MB/s
• A drive in the drive setup represents a separate physical HDD if that is what is being used.
• SDD
• Solid State Drive
• A drive in the drive setup above represents a separate physical SDD if that is what is being used.
• SAN
• While the speed of the drives in the SAN can affect the performance of SQL Server, the bigger bottleneck is usually the Fibre Channel connection between the server and the SAN.
• Specs aside, a 4GB Fibre Channel will perform in a real production environment about the same as a high speed enterprise local HDD. Adding more HBA’s may help, depending on your SAN brand.
• SAN monitoring software can help diagnose pathing issues, but different SAN vendors have different definitions for active/active, so you need to make sure you do some research into how the arrays must be set up and where the SQL files must be stored to take advantage of it.
• Most virtual environments cannot truly take advantage of multipathing, which can severely limit SQL performance.
• A drive in the drive setup above represents a separate LUN, preferably each LUN representing a drive group or RAID array.
• RAID
• May use SDD or HDD
• If RAID is used locally, RAID 10 is recommended with a separate array for each drive in the drive setup.
• RAID recommendations when part of a SAN generally follow the San vendors recommendations.