Scribd
Upload
20 views

5-sqlserver-2012ic-m5-postinstall-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
20 views

5-sqlserver-2012ic-m5-postinstall-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/ 23

SQL Server 2012:

Installation and Configuration

Module 5: Post-Installation Configuration


Tasks for SQL Server 2012
Glenn Berry
Glenn@SQLskills.com
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

You might also like