SQL Server 2012:
Installation and Configuration
Module 2: Pre-installation Tasks
for the Operating System
Glenn Berry
Glenn@SQLskills.com
Introduction
There are several OS-level tasks that you must complete first
These affect security, performance, scalability, and maintainability
Getting domain accounts provisioned
Needed for SQL Server Service accounts
Getting Windows Server 2012 installed and patched
Updating the main BIOS and other firmware
Updating device drivers
Checking power management settings
Granting Windows rights to the SQL Server service account
Getting a static IP address for your database server
Getting your database server added to the domain
Getting Domain Accounts Provisioned
You should have a Windows Domain account for each service
Some organizations also have one domain account for each SQL instance
This will depend on which components are installed
SQL Server Service
SQL Server Agent Service
SQL Server Reporting Service
SQL Server Analysis Service
SQL Server Integration Service
These should be regular Domain User accounts
The setup program will grant the necessary rights for each account
They do not need Local Administrator rights on the machine
You will need the name and password for these accounts
This information is required during SQL Server installation
Install and Patch Windows Server 2012
Windows Server 2012 Standard Edition is a good choice
It does not have the 32GB memory limit from previous versions
It allows you to install the Failover Clustering feature if needed
This is required for AlwaysOn Availability Groups
Install the operating system on a hardware RAID 1 array
This gives you some basic protection from a single disk failure
Make sure to install Microsoft Update
This is a superset of Windows Update
Configure Microsoft Update properly
Make sure it only notifies you of new updates
You do not want it to automatically download and install updates
Check for available updates and install them
You may have to do this several times to get every update
BIOS and Firmware Updates
Make sure you have the latest main system BIOS installed
Brand new servers often have old BIOS versions
Check the system vendor’s support site for the latest version
Make sure you have the latest firmware for all of your components
This will depend on what components are in your server
RAID controllers
Host Bus Adapters (HBAs)
PCI-E storage cards
Network Interface Cards (NICs)
Vendor system management tools can detect out-of-date firmware
Dell Open Management Systems Administrator
HP System Insight Manager
IBM Director
Checking For Firmware and Driver Updates
Install or Update Your Device Drivers
Prefer the vendor supplied, system-specific drivers instead of the
generic Windows drivers
What you need will depend on what components are in your server
RAID controllers, Host Bus Adapters (HBAs)
PCI-E storage cards
Network Interface Cards (NICs)
Video drivers
Install the chipset drivers from the vendor first
Install storage related and NIC drivers next
Install video drivers last
Checking Your Power Management Settings
BIOS power management in the BIOS setup application
Should be set to OS control or disabled
Windows Power Plan should be set to “High Performance”
Default setting is “Balanced”
This has a measurable negative effect on database server performance
Check your processor speed values to confirm the settings
Windows Server 2012 Task Manager
CPU-Z utility from www.cpuid.com
Main BIOS Power Management Settings
Windows Power Options
Using Task Manager to Check CPU Speed
Using CPU-Z to Check CPU Core Speed
Grant Windows Rights to the SQL Server Service
Two Windows rights should be granted on the machine to the SQL
Server Service account
Perform volume maintenance tasks
Lock pages in memory
The Windows domain account for the service must exist first
You should do this before you install SQL Server 2012
Use the Local Group Policy Editor to do this
Type gpedit.msc at a command prompt
Perform Volume Maintenance Tasks
This right enables Windows instant file initialization (IFI)
This lets SQL Server avoid having to “zero-out” data files after they are
created or grown
This only affects SQL Server data files, not SQL Server log files
Huge reduction in data file creation and growth times
Especially important for larger database data files
This dramatically reduces database restore time
Makes it easier to initialize high-availability secondary copies of databases
(such as database mirror, replication subscription database, or AlwaysOn
Availability Group replica)
Dramatically reduces downtime during disaster-recovery restore
Very slight security risk with this setting
A DBA could possibly access previously deleted files that they don’t have
normal rights to access
Not an issue if storage is dedicated to SQL Server
Lock Pages in Memory (LPIM)
Prevents OS from trimming the SQL Server working set
Operating system forces SQL Server to release some memory
This can happen when the OS is under severe memory pressure
It is often caused by memory leaks in device drivers
Trimming the working set has an extreme negative effect on SQL Server
Used to only be available in SQL Server Enterprise Edition
SQL Server 2012 Standard Edition now supports LPIM
No trace flag is required with SQL Server 2012 Standard Edition
Very important to set sp_configure ‘max server memory’
You want to limit how much memory SQL Server can use
You want to make sure the OS is never under memory pressure
Jonathan Keheyias post at http://bit.ly/Nn1RtQ
Local Group Policy Editor
Final OS Configuration Tasks
Change network settings to use a static IP address
Make sure your server has been added to a Windows domain
Enable Remote Desktop in Windows
Allows you to logon remotely when needed
Configure anti-virus to skip .MDF, .NDF, and .LDF files
Manually optimize your C: drive
Make sure there are no pending reboots in Windows
If you will be using the FILESTREAM feature:
Disable 8.3 filename generation
fsutil behavior set disable8dot3 1
Disable updating of last file access time
fsutil behavior set disablelastaccess 1
Read Paul Randal’s whitepaper for more details about FILESTREAM:
http://bit.ly/10QqTqt
Optimize Drives Dialog
Summary
Proper OS installation and configuration is very important
Provides better performance, scalability and security
Makes the instance easier to maintain in the future
Hardware configuration is very important
Power management and hyperthreading settings
BIOS and firmware updates
Make sure all of these tasks are completed before you install SQL
Server 2012
What is Next?
Module 3 will cover pre-installation tasks for SQL Server 2012
Using a standardized naming scheme for disks and directories
Considering your workload for storage provisioning
RAID level and SQL Server workloads
Provisioning your logical drives
Testing your logical drive performance with CrystalDiskMark
Testing your logical drive performance with SQLIO
