SQL Server 2012:
Installation and Configuration
Module 5: Post-Installation Configuration
Tasks for SQL Server 2012
Glenn Berry
[email protected] Introduction
How SQL Server 2012 is updated
The importance of updating SQL Server 2012
Obtaining SQL Server 2012 Service Packs
Installing SQL Server 2012 Service Packs
Obtaining SQL Server 2012 Cumulative Updates
Installing SQL Server 2012 Cumulative Updates
Setting instance properties with SSMS
Setting instance properties with T-SQL
Configuring tempdb files
How SQL Server 2012 Is Updated
Microsoft has three primary ways to update SQL Server
Hotfixes
Cumulative Updates (CU)
Service Packs (SP)
Hotfixes are designed to fix one specific defect
You must contact Microsoft Support (CSS) to get a hotfix
Cumulative Updates are rollups of hotfixes (10-50 defects)
They are released every eight weeks, and are not fully regression tested
You must request and download CUs from Microsoft
Service Packs are designed to fix a larger number of defects
They are released about every 12 months
They are fully regression tested
You can manually download Service Packs
They are also available from Microsoft Update
Optional Update in Microsoft Update
The Importance of Updating SQL Server 2012
SQL Server Service Packs are required in order to stay in a fully
supported configuration
Microsoft “retires” branches of code after certain milestones
RTM branch is retired one year after SP1 is released
SP1 branch is retired one year after SP2 is released
If you are on a “retired” branch, you will get limited CSS support
Basic troubleshooting until you upgrade to a supported Service Pack
Service Packs and Cumulative Updates fix many defects
You are less likely to run into problems if you stay up-to-date
You should test Service Packs and Cumulative Updates
Testing and applying SPs and CUs is good practice for your organization
It forces you to exercise your testing and high-availability plans and infrastructure
Obtaining SQL Server 2012 Service Packs
Use this Microsoft Knowledge Base article to find the latest SP
http://bit.ly/R1VRdQ
You can also use your favorite search engine
There is a separate download for SQL Server 2012 Express Edition
There are now separate, full slipstream installs for SQL Server 2012
Make sure to download the correct Service Pack version
Run SELECT @@VERSION to check the version on your instance
x64 is 64-bit version for Intel/AMD systems
Most servers should be using this version
x86 is 32-bit version for Intel/AMD systems
Most useful for older workstations and legacy support
You should not run the x86 version on a brand new server
SQL Server 2012 Service Pack 1
Installing SQL Server 2012 Service Packs
Confirm that you have the right version of the Service Pack
x64 or x86, Express Edition or not, full slipstream or not
Read the release notes
SQL Server 2012 SP1 http://bit.ly/RIDRDb
Schedule the installation during a maintenance window
You can use a rolling upgrade technique to minimize downtime
You must have some high-availability technology in place to do this
Don’t have any pending reboots of Windows
The setup program will prevent the installation from running if you do
Don’t have SQL Server Management Studio running locally
This decreases the chance of the Service Pack requiring a reboot
Obtaining SQL Server 2012 Cumulative Updates
Use these Microsoft KB articles to find the latest CU
The SQL Server 2012 builds that were released after SQL Server 2012 was
released
http://bit.ly/Imox6j
The SQL Server 2012 builds that were released after SQL Server 2012 Service
Pack 1 was released
http://bit.ly/SHe25n
You must obtain the latest CU for your branch of the code
Examples
Release to Manufacturing (RTM) branch
Service Pack 1 (SP1) branch
An RTM branch CU will not work on an instance that has SP1 installed
An x86 CU will not work on an x64 instance of SQL Server
Requesting a Specific Cumulative Update
After you have identified the correct CU, you must request it from
Microsoft from a link in a KB article
Example: SQL Server 2012 SP1 CU1
http://bit.ly/UTClRI
You must select the correct CU package
This can be somewhat confusing
You also must request the CU package from the web page
You give Microsoft a valid e-mail address and they will send you a link
You must enter a CAPTCHA code
These are sometimes difficult to read
You will receive an e-mail with a download link in a few minutes
You must download and extract the CU installation file
Requesting a Cumulative Update
Installing a SQL Server 2012 Cumulative Update
After you have extracted the CU setup file you can run it
Schedule the installation during a maintenance window
You can use a rolling upgrade technique to minimize downtime
You must have some HA technology in place to do this
Don’t have any pending reboots of Windows
The setup program will prevent the installation from running if you do
Don’t have SQL Server Management Studio running locally
This decreases the chance of the CU requiring a reboot
Cumulative Updates usually install faster than Service Packs
Installation time depends on your hardware and what SQL Server features
are installed on the instance
SQL Server 2012 Cumulative Update
Install the Latest Service Pack and CU
For a brand new instance, you should install the latest SQL Server
2012 Service Pack and Cumulative Update
You do not want to be running the original RTM build in Production
It will require some sort of outage to install them after you are in
Production
It is better to install them now and fully test your applications before you go
into Production
You may not be able to easily install those updates later
Setting Instance Properties with SSMS
You can right-click on the instance in Object Explorer
Select Properties
This will open the Server Properties dialog
There are eight properties pages (shown in the left-hand pane)
You can make a change and then use the Script button
This is much safer than simply clicking the OK button
It will also help you learn the T-SQL commands to change properties
It will save you the time and trouble of typing T-SQL code
This will prevent many syntax mistakes
Server Properties - Memory
Server Properties - Processors
Server Properties – Database Settings
Server Properties - Advanced
Setting Instance Properties with T-SQL
Configuring tempdb Files
SQL Server 2012 only has one very small tempdb data file
It is a common best practice to create additional data files to reduce
the chances of seeing PAGELATCH contention in tempdb
These additional data files can all be on the same LUN
They should all be the same initial size
Good starting point for number of tempdb data files
Less than eight processor cores: # of files = # of cores
More than eight processor cores: Start with eight tempdb data files
Monitor tempdb for signs of PAGELATCH contention
Add more tempdb files in groups of four if contention exists
See this Jonathan Keheyias article for more details
http://bit.ly/NKs6c3
Summary
It is important to maintain your SQL Server instance
Service Packs
Cumulative Updates
Hotfixes
This makes the instance easier to maintain in the future
Selected instance level settings should be changed
Optimize for ad hoc workloads
Max server memory
Default backup compression
Additional tempdb data files should be added
Use my guidance for the number of data files to start with
What is Next?
Module 6 will cover automating common maintenance tasks for SQL
Server 2012
Confirming network connectivity
Confirming SQL Server connectivity
Enabling Database Mail
Creating a New SQL Server Operator
Setting up SQL Server Agent Alerts
Adding Ola Hallengren’s Maintenance Solution
Configuring the Maintenance Solution
