A guide to installing SQL Server 2008 R2 for a single instance. In addition, post installation steps have been added where appropriate, to ensure a consistent approach is taken to the configuration of the MSSQL instance.

Included are some basic requirements for SQL Server Reporting Services (SSRS) and BizTalk, which are optional components and not always applicable to the installation.

The installation of Service Pack 3 for SQL Server 2008 R2 has also been summarised, which is the latest Service Pack available at the time of writing this document.

Pre-Installation Requirements

Windows Page File

Ensure that the Windows server has been built with a dedicated disk for the Windows Page File (swap space). Generally, this should be sized at 1.5 times the size of the amount of physical RAM installed on the machine, with a “system managed page file size” configuration. For servers with large amounts of physical RAM, the 1.5 times rule may not be appropriate and should be considered on an individual build basis.

Disk Configuration

Whilst not always possible, the recommended disk configuration to use for a dedicated SQL Server database server is as follows:

Disk Letter	Contains	Typical Path
C:\	Operating System	\
D:\	MSSQL software, System DB data/logs	\Program Files\Microsoft SQL Server
E:\	User DB data	\MSSQL\DATA
F:\	User DB logs	\MSSQL\LOGS
G:\	Temp DB data	\MSSQL\Temp_DATA
H:\	Temp DB logs	\MSSQL\Temp_LOGS
I:\	DB backups	\MSSQL\Backups

Using the default Allocation Unit Size for the backup disk provides potential for the backup files (<20GB) to be later compressed to preserve disk space.

The “Bytes Per Cluster” value can be verified per disk, by the running the following command line (with “Administrator” rights):

fsutil fsinfo ntfsinfo L:|find “Bytes Per Cluster”

Bytes Per Cluster : 65536

Further information around disk partition alignment recommendations can be found here:

https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

Service Account and Security Rights

A domain level SQL Server service account should be created in readiness for the installation, as this will be used to run the various SQL Server services.

Add the SQL Server service account to the server’s local “Administrators” group. In addition, add the same service account to the following “Local Security Policies”:

· Replace a process level token

· Lock Pages in Memory (LPIM) (may not be necessary in Windows Server 2008+ builds)

· Perform Volume Maintenance Task

 

If it is not possible to run the services as a domain account, and not a Production environment build, then you may consider using “NT AUTHORITY\Network Service”. This is a low privilege account, but one that retains the computer’s network credentials.

Installation Instructions

From the MSSQL installation kit, run setup.exe and select “Installation” from the side menu. Select “New installation or add features to an existing installation.”

Ensure that the “Setup Support Rules” pass without any issue and continue.

If the product key has been pre-populated, leave as is. Otherwise obtain a valid and appropriate product key and enter it here.

Accept the license terms and continue.

Proceed with the install of the “Setup Support Files” if prompted.

Further “Setup Support Rules” are checked at this stage. The “Windows Firewall” rule will typically flag a warning, which can be ignored. All other checks should pass OK.

Opt for a “SQL Server Feature Installation” and continue.

Feature Selection

Select the features appropriate to the requirements of the installation. Typically the following features would be installed:

· Database Engine Services (no sub-components)

· Reporting Services (optional)

· SQL Server Books Online

· Management Tools – Basic

· Management Tools – Complete

 

DeDetailed descriptions for each of the features can be found Microsoft documentation here:

https://msdn.microsoft.com/en-us/library/ms143786(v=sql.105).aspx

 

Additional “Installation Rules” will perform further pre-requisite checks. Ensure these pass OK and continue.

Instance Configuration

Update the “Instance root directory” path to an appropriate disk (C:\ should not be avoided). If a named instance is required, this should be provided here with an appropriate naming convention.

Disk space requirements will be checked next, and highlight any issues.

Change the “Startup Type” for the “SQL Server Agent” service to Automatic.

An appropriate domain service account should be used if at all possible to run each of the services listed.

If this is not possible, and not a Production environment build, then you may consider using “NT AUTHORITY\Network Service”.

This is a low privilege account, but one that retains the computer’s network credentials.

Make sure the collation is set to “Latin1_General_CI_AS” and continue.

On the “Account Provisioning” tab, ensure that the “Authentication Mode” is set to “Mixed Mode”. Additionally, add in accounts for other DBA logins/groups. These will be granted “sysadmin” rights and therefore have unrestricted access to the SQL Server instance.

 

You will also need to supply a password for the “SA” account (“System Administrator”) which has the same “sysadmin” rights as the listed users, but uses SQL Server authentication. Make sure the password conforms to the current password complexity standard, and is recorded securely.

Update the “Data root directory” to match the location used for the “Instance root directory” path earlier on. In addition, update the remaining Data, Log and Backup directory paths as appropriate.

The drive letters may differ slightly per installation, but if possible, the following directory structures should be used:

Directory	Path
MSSQL software	D:\Program Files\Microsoft SQL Server
User DB data	E:\MSSQL\DATA
User DB logs	F:\MSSQL\LOGS
Temp DB data	G:\MSSQL\Temp_DATA
Temp DB logs	H:\MSSQL\Temp_LOGS
DB backups	I:\MSSQL\Backups

 

 

Leave the FILESTREAM feature disabled, and continue.

If “Reporting Services” was selected from the “Feature Selection”, you’ll see the following screen. Continue using the “Install the native mode default configuration” option.

Ensure that the “Send Windows and SQL Server Error Reports” option is unticked and continue.

Ensure that the “Installation Configuration Rules” completed OK and continue.

Finally, check the installation summary, specifically the directory paths, features, and edition of SQL Server (usually Standard) to make sure everything is as expected, then click Install.

Once installed, check the log file for any issues, and click Close.

Service Pack 3 Installation

Accept the license terms and continue.

Leave all of the features select, so that all components are patched to the same level.

Wait for the “Check Files In Use” check to complete. Everything should be stopped that needs to be at this point, but you may need to explicitly stop applications/services depending on the output here, before the patching can continue.

Double check the correct features are listed for patching, then click Update. The “Patch Level” listed below indicates the current version installed. In this example, it is 10.50.1600.1 which is SQL Server 2008 R2 RTM (base release).

Once the update has completed, click Close.

To verify the Service Pack has been applied, you can log into SQL Server Management Studio (SSMS) and the version number will be displayed to the right of the instance name. Alternatively, you can run the following T-SQL:

SELECT CAST(SUBSTRING(@@version ,0, CHARINDEX(CHAR(10), @@version )) AS CHAR(70)) AS [Version]

Version

———————————————————————

Microsoft SQL Server 2008 R2 (SP3) – 10.50.6000.34 (X64)

Further information around SQL Server version information can be found here in the Microsoft KB:

http://support.microsoft.com/en-us/kb/321185

A similar process to the above is followed when applying Cumulative Updates.

NOTE: Some patches may require server restarts before/after update process.

Post Installation Steps

Max Server Memory Settings

By default this is effectively set to unlimited, which can lead to the Operating System being starved of physical memory at times, or other SQL Server instance, and so should always be adjusted.

The guide below assumes an x64 bit installation is running with either SQL Server 2005/2008/2008 R2/2012 versions:

Physical RAM Installed		SQL Max Server Memory Setting	Physical RAM Remaining
Total GB	Total MB		Free MB	Free GB	Remaining %
2	2,048	1,450	598	0.6	29.2
4	4,096	3,000	1,096	1.1	26.8
6	6,144	4,800	1,344	1.3	21.9
8	8,192	6,400	1,792	1.8	21.9
12	12,288	10,000	2,288	2.2	18.6
16	16,384	13,500	2,884	2.8	17.6
24	24,576	21,500	3,076	3	12.5
32	32,768	29,000	3,768	3.7	11.5
48	49,152	44,000	5,152	5	10.5
64	65,536	60,000	5,536	5.4	8.4
72	73,728	68,000	5,728	5.6	7.8
96	98,304	92,000	6,304	6.2	6.4
128	131,072	124,000	7,072	6.9	5.4
256	262,144	250,000	12,144	11.9	4.6
1024	1,048,576	1,029,000	19,576	19.1	1.9

If more than one SQL Server instance (MSSQL) is running on the server, split the allocated SQL Max Server Memory across the instances, appropriate to demands of each environment.

For example, a server with 16GB of physical memory installed running MSSQL and SSRS would typically result in the MSSQL instance being configured to use 12,500MB (minus 1,000MB for SSRS).

This update can either be performed using the GUI or via T-SQL.

From SSMS, right click on the instance and open up the “Properties”. Click on the “Memory” page on the left hand menu, and adjust the “Maximum server memory (in MB)” value as required. Leave the “Minimum server memory (in MB)” value as zero, and accept the changes (no restart is required).

T-SQL:

— Adjust Max Server Memory

EXEC sys.sp_configure ‘show advanced options’, ‘1’ RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure ‘max server memory (MB)’, ‘16500’

RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure ‘show advanced options’, ‘0’ RECONFIGURE WITH OVERRIDE

Database Settings

From SSMS, right click on the instance and open up the “Properties”. Click on the “Database Settings” page on the left hand menu, and tick the “Compress backup” option. Also check that “Database default locations” paths for the data and log files are also correct, although they should already be set from the install.

T-SQL:

— Enable compression for backups

EXEC sys.sp_configure ‘backup compression default’, ‘1’

RECONFIGURE WITH OVERRIDE

Advanced Optimization Settings

From the same instance properties window, click on the “Advanced” page on the left hand menu, and change the “Set Optimize for Ad hoc Workloads” option to “True”. This helps optimize ad-hoc queries by reducing plan cache bloat, and reducing memory pressure.

T-SQL:

— Set Optimize for Ad hoc Workloads to True

EXEC sys.sp_configure ‘show advanced options’, ‘1’ RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure ‘optimize for ad hoc workloads’, ‘1’

RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure ‘show advanced options’, ‘0’ RECONFIGURE WITH OVERRIDE

Database Mail Configuration

There is no current requirement to configure database mail, but this URL explains how to do so:

http://www.snapdba.com/2013/04/enabling-and-configuring-database-mail-in-sql-server-using-t-sql/

Error Logs Retention

From SSMS, navigate to “Management” and then right click on “SQL Server Logs” then “Configure”.

Tick the “Limit the number of error log files before they are recycled” box and change the “Maximum number of error log files” to 62, allowing for 2 months’ worth of logs.

T-SQL:

–Configure SQL Error Log retention for 62 days (2 months)

EXEC xp_instance_regwrite ‘HKEY_LOCAL_MACHINE’, ‘Software\Microsoft\MSSQLServer\MSSQLServer’,

‘NumErrorLogs’, REG_DWORD, 62

SQL Server Agent Job History

From SSMS, right click on “SQL Server Agent” then “Properties”. Click on the “History” page on the left hand menu and make sure the “Limit size of job history log” box is ticked. Change the “Maximum job history log size (in rows)” to 10000 and the “Maximum job history rows per job” to 100.

T-SQL:

–Change the SQL agent history retention

EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=10000,

@jobhistory_max_rows_per_job=100

TempDB configuration

The default TempDB sizing and configuration is usually sufficient for most installations. However, some environments may benefit from splitting the data file into multiple files of equal size, especially if latch contention issues are experienced and TempDB usage is quite high.

Creating multiple TempDB files enables MSSQL to split the I/O transactions across each of the data files, optimizing performance when multiple CPUs perform the SQL operations simultaneously. This can also prevent or alleviate blocking activity.

The advice from Microsoft is such that:

“As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.”

However, previous experience has shown that using 1 data file per logical core, can sometimes lead to further contention and performance issues. From experience, the best approach to take is to create equally sized TempDB data files based on ½ the number of logical processor cores, with a maximum of 8 files.

Further details can be found in the Microsoft KB here:

http://support.microsoft.com/en-us/kb/2154845

Additional information about working with TempDB can be found here:

https://technet.microsoft.com/en-us/library/cc966545.aspx