Scribd
Upload
4 views

2-sqlserver-2012ic-m2-preinstall-os-slides

Uploaded by

etest2272
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views

2-sqlserver-2012ic-m2-preinstall-os-slides

Uploaded by

etest2272
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 20

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

You might also like