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.