Recommendations for SQL Server Installation Options#

This section describes recommendations based on best practices for a Critical Manufacturing typical installation and workload. Some parameters might have to be adjusted during the lifetime of the application or if this recommendation is updated.

Install Analysis Services in Multidimensional and Data Mining Mode#

Analysis Services provides online analytical processing (OLAP) and data mining functionality for business intelligence applications. When installing SQL Server, the Analysis Services must be installed in Multidimensional mode. This is one of three server modes in which Analysis Services runs in.

Provision Storage For The Operating System and for SQL Server#

The following setup is recommended for each SQL Server Instance:

• Critical Manufacturing databases data files - G:
• Critical Manufacturing databases log files (including tempdb Log) - L:
• Critical Manufacturing databases backup disk - H:
• SQL Server Instance tempdb data file(s) - T:

In addition:

• Transaction log files (L:) should not be in the same disks as data files (G:) due to their random vs sequential way of writing in log files and data files.
• Use RAID 10 (better write performance and reliability). Always make sure write cache is enabled (if applicable).
• TempDB should ideally be in SSD disks. If not possible, make sure it is using different spindles rather than the database files.

Operating System Configuration#

The following configurations are recommended:

• Configure the Windows page file - we typically create a 2GB size page file on the system drive. Page file size can be found in the system properties of Windows Server.
• Set anti-virus exclusions - You need to configure exclusions for all SQL Server files per Microsoft's guidelines: https://support.microsoft.com/en-us/kb/309422 ⧉.
• If you have additional tools that restrict the creation/modification of files, exclusions should also be set.
• Make sure server "Power Options" are set to "High Performance".
• Format the drives with 64K allocation blocks.

This only applies to drives holding SQL Server database and log files (including tempdb). Your C drive / system drive should be separate and 4K block size is appropriate for that logical drive.

Service Accounts and Permission Granting#

Make sure to grant the 'Perform Volume Maintenance Tasks' and 'Page locks in memory' rights to the account that will be used for the SQL Server service (the engine, not the agent). This will enable the instant file initialization (IFI). Please refer to the link https://msdn.microsoft.com/en-us/library/ms175935.aspx ⧉ for more information.

SQL Server Installation and Configuration#

The following configurations are recommended:

• Make sure the TCP/IP Protocol is enabled.
• Configure this in the SQL Server Configuration Manager under "SQL Server Network Configuration." Enabling the TCP/IP protocol will only take effect after the SQL Server instance is restarted.
• Test Instant File Initialization (IFI)
• Create an empty database. Grow the data file by 5GB. If it does not complete immediately, then IFI is not working (revisit the previous step where it was granted.) If you have verified IFI is working, go ahead and drop the empty database.

TempDB Configuration#

By default, the TempDB files are placed on the same drive as the SQL Server binaries. Even if the user chooses a custom install, TempDB still goes on the same drive as the other data files, and that is not advisable. Alternatively, the TempDB data files should be on their own dedicated drive.

Move TempDB to its own drive#

In this example, we put the data file on the T drive and the log file on the L drive. (Important: directory paths must exist beforehand).

use master;
GO
alter database tempdb modify file (name='tempdev', filename='T:\MSSQL\DATA\tempDB.mdf', size = 1MB);
GO
alter database tempdb modify file (name='templog', filename='L:\MSSQL\LOGS\templog.ldf', size = 1MB);
GO

After this code runs, restart the SQL Server. That will create the new TempDB file on the new drive. Manually delete the old TempDB file on the original drive, since SQL Server does not delete it itself.

Grow that file and add additional data files. Now that TempDB is on the right drive, expand it to the full size according to your preferences and then create additional TempDB files.

USE [master];
GO
alter database tempdb modify file (name='tempdev', size = 2GB, FILEGROWTH = 100MB);
GO

The current guidance from Microsoft in KB 2154845 is to use the same number of tempdb files as the number of logical processors up to 8 logical CPUs. Do not add more, unless you observe you have contention.

The code to create one additional TempDB data file can be seen below - you can modify this in order to have more files:

USE [master];
GO
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'T:\MSSQL\DATA\tempdev2.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
GO

The data file creation should only take a couple of seconds - if it takes more than ten seconds, then instant file initialization is not configured correctly (Revisit the section where IFI is enabled). On a general note: Autogrowing files by at least 100MB for the transaction log is acceptable, but this value may need to be higher to provide enough space to avoid autogrowing again quickly. The best option is to avoid autogrowing in the first place, by correctly sizing the files.

Configuration of SQL Server Max Degree of Parallelism#

Set this value to the number of physical cores in a single NUMA node (processor) socket on your hardware or less.

Example to set the Max Degree of Parallelism to 8:

USE [master];
GO
ALTER DATABASE [tempdb] ADD FILE (NAME = N'tempdev2', FILENAME = N'T:\MSSQL\DATA\tempdev2.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
GO

Configuration of SQL Server Cost Threshold for Parallelism#

USE CriticalManufacturing;  
GO
EXEC sp_configure 'show advanced options', 1;  
GO
RECONFIGURE WITH OVERRIDE;  
GO
EXEC sp_configure 'max degree of parallelism', 8;  
GO
RECONFIGURE WITH OVERRIDE;  
GO

Configuration of SQL Server Max Memory#

By default, SQL Server's maximum memory is 2147483647, i.e. no memory limit. A limit should be defined so that paging to disk is less likely. It is advisable to leave 4GB or 10% of total memory free (whichever is larger on your instance to begin with) and adjust as needed.

Example for a server with 32GB of memory:

EXEC sys.sp_configure 'max server memory (MB)', '29491';
GO
RECONFIGURE WITH OVERRIDE;
GO

To double-check if you are paging to disk, go to Task Manager, open the Performance tab, and look at the free memory metric for Windows 2008. If it is under 200, you are in danger of swapping to disk.

SQL Server Maintenance Setup#

Configure and schedule regular maintenance for all of the following:

• Full (and possibly differential) backups;

• Log backups (every 15 mins);

• CheckDB;

• Index maintenance;

We recommend using free scripts from Ola Hallengren to create customized SQL Server Agent: http://ola.hallengren.com/ ⧉.

Set Compatibility level#

The compatibility level set by Critical Manufacturing MES to its databases is equal to the maximum compatibility level supported by the oldest version of Microsoft SQL Server supported on each version.

For more information, see https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16 ⧉.

Other SQL Server Settings (to check after Critical Manufacturing has been installed)#

Enable the option optimize for ad hoc workloads at the instance level.

EXEC sys.sp_configure 'optimize for ad hoc workloads', 1
GO
RECONFIGURE WITH OVERRIDE;
GO

Enable backup compression default

EXEC sp_configure 'backup compression default', '1';
GO
RECONFIGURE WITH OVERRIDE;
GO

Enable Page_Verify for a high level data-file integrity in our databases.

/*This will script out the command for you, check it and execute the output
*/
SELECT 'ALTER DATABASE ' + QUOTENAME(s.name) + ' SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;'
FROM sys.databases AS s
WHERE s.page_verify_option_desc <> 'CHECKSUM';
GO

This script just creates the TSQL for your change: you still need to copy it and execute it in another window. When you configure your full backups, use the With Checksum option to check the checksums each time a full backup is run.

Manually Enable Backup Jobs#

The SQL Server installation creates the backup jobs but does not enable them by default. In order for the jobs to run, they must be enabled manually.

For more information, see Database Backup and Restore section of the Operations Guide.

Optional Settings#

Enable SQL Trace Flags. Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. To find out more on how to enable SQL Trace flags on startup, visit https://msdn.microsoft.com/en-us/library/ms190737.aspx ⧉.

The trace flags indicated below are the most useful in this context:

• 1117 - Causes files in a filegroup to grow at the same time. Useful with tempdb.
• 1118 - Removes the use of mixed extents. Most often used to help with tempdb contention.
• 2371 - Statistics are recomputed less rarely as tables grow larger.
• 3226 - Stops logging all of your backup success entries to the error log.
• 4199 - To get new query optimizer hot fixes that produce different query plans.
• 8048 - Enables SOFT NUMA when SQL's NUMA-awareness code does not work on larger systems.