SQL Server Installation Notes and Multiple Instances

SQL Server 2008 R2 Installation Procedures

Initial Server Specification
SQL Server 2008 R2 is compatible with Windows Server 2012 so should use this configuration where possible unless a specific reason not to.  It will always be the 64 bit configuration using Server 2012.
RAM to allocate will depend on usage of the database, minimum should be 2Gb but would recommend more for most production databases.
Consider requesting server to be added to the domain.
Disk Configuration
In a physical environment we should be aiming to separate the OS, Data, Logs, TempDb and Backup areas by spindles.
Our fastest disk areas should be Data and Logs and these should be set up as RAID 10 where possible if performance is key. The more disks allocated the more IO we can achieve.
Logs should be kept on their own set of spindles as they write sequentially.  Therefore the heads do not have to travel as far.
If using a VM environment we will not usually reap the same performance benefits by splitting out OS,Data,Logs,TempDb and Backup volumes as we have less control at a physical storage level but it is still useful to still do this to achieve consistency and to prevent one area from filling up another.
We should generally use the following standard on VM and physical:
C: OS (at least 50Gb)
D: Data (depends on estimated database size)
F: Backup (allow enough room for a couple of full backups)
L: Logs (depends on database throughput)
T: TempDb (At least 5 Gb)
In all cases a discussion needs to be had with Tony E regarding multiple instances.  There will be cases where we require two instances to be installed and the above volumes for data,backup,logs and tempdb may need to be doubled up.
Ensure .NET framework installed on windows server 2012 or it will get to the end of install and then fail.  .NET framework is not installed through the roles or this fails.
Workaround is as follows:
mount the windows 2012 installation source as a drive
from command prompt: dism.exe /online /enable-feature /featurename:NetFX3 /Source:<mounted drive letter of windows source>:\sources\sxs /LimitAccess
Now check server features in "Add Roles and Features Wizard" and NET Framework 3.5 should be ticked.

Select New Installation and install the setup support files.

Select "SQL Server Feature Selection"

Feature Selection

Database Engine Services: SQL Server Replication (if replication required), Full Text Search, Analysis Services (if required) and Reporting Services (if reporting services required)  - note that at a minimum Database Engine Services should be selected.
Shared Features: Management Tools - Basic and Complete.
Leave shared feature directories as the default path unless specific reason not to.

Instance Configuration

For the first instance on a server you can select Default or Named instance.
Choose "Named Instance" to give it a specific name ("Default Instance" which will give the instance a name of "MSSQLSERVER" by default and turn off dynamic ports).  If this is an additional instance then default will not be available as an option and "Named Instance" will have to be selected anyway.
You can name the instance by the system "<system><instance number>" if this is for a server named for a specific system.  If it is a generic non system specific server then name the instance <server><instance number>.  Instance number refers to the number of the instance for the system or server. 1 if a one database system or 2 if second instance on server.
Leave instance root directory as the default unless a reason otherwise.

Server Configuration

The default service accounts for SQL Server Agent, SQL Server Database Engine, SQL Server Analysis Services, SQL Server Reporting Services are the NETWORK SERVICE account for non windows 2008 R2 OS.  For windows 2008 R2 refer to the documentation and virtual accounts.
SQL Full Text Filter Deamon and SQL Server Browser are both "LOCAL SERVICE" by default.

Database Engine Configuration

Unless there is a specific requirement set up the instance in mixed authentication mode.  Enter a sa password and it is a requirement to assign a windows account with sysadmin access.  I create a new windows local account and assign this to any instances on the server.  This is the equivalent of sa.  In the example case I created a local windows account called SQLADMIN before assigning it to the instance.

Database Engine Configuration

Click on the data directories tab to change the default locations and folder names of the system and user databases.  Ideally you should have configured your arrays to have separate drives available for the OS, data files, tempdb and log files.  In the example I have amended the default data root directory to D:\data which means the instance name will be under D:\data. On a second instance this can be the same as the first but instance 2 name sitting underneath alongside instance 1.

Analysis Services Configuration

Only applies if Analysis Services were selected in install.  Click on data directories and amend to use the same root folder as the main data directories selected in previous step.  However it will use "OLAP" instead of "MSSQL" as you will see from the screenshot below.

Run SQL configuration manager and go into network configuration / protocols /  TCPIP. Scroll to the bottom and 0 out dynamic ports, put 1433 into the TCP port if default port required.
TCP Dynamic Ports should be off and set to 1433 for the default instance and 1443 for the second instance.  Installing a named instance as the first instance on the server will enable tcp dynamic ports and so may need to be turned off again and set to 1433.
Selected to install reporting services only (not configure) if reporting services included in install.
Using Management Studio set initial tempdb size to be 200Mb and max memory to be 1Gb as a minimum below the total of the OS to give the OS 1Gb. May need to split this in a multi instance config or give more to the OS if the db is also an app server.
Set the autogrowth of the databases and log file to be larger than 1Mb (make default 100Mb) and change from percentage to fixed amount. Set limit of the log file size to be less than volume capacity.
Check autoshrink is switched off on log file.
Set up database mail and operator.  Mail is set up through mssms in managment-database mail and then activated in sql server agent - properties - Alert System. Tick enable mail profile and point to newly set up mail system and profile.
Operator us set up under Agent - Operators
Ensure Service Pack updates are installed before putting into production.