for SharePoint 2013 Brian Alderman MCT Consultant MicroTechPoint SPC311 About the Speaker Brian Alderman CEO Founder MCT Consultant MicroTechPoint MTP wwwmicrotechpointcom ID: 458922
Download Presentation The PPT/PDF document "Optimizing SQL Server 2012" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1Slide2
Optimizing SQL Server 2012 for SharePoint 2013
Brian AldermanMCT / ConsultantMicroTechPoint
SPC311Slide3
About the Speaker
Brian Alderman (CEO / Founder / MCT / Consultant)MicroTechPoint (MTP)www.microtechpoint.com
Brian’s Blog
brian@microtechpoint.com
@brianalderman & @microtechpoint
Co-Author SharePoint 2010 Administrator’s Companion
Co-Author SharePoint 2013 Administration Inside Out
Enjoy Travel and GolfSlide4Slide5
Mulletville, VermontSlide6
Arizona GolfSlide7
19th HoleSlide8
Session Agenda
SQL Server IntroductionSharePoint and SQL Server IntegrationDemo: SQL Server Configurations to Optimize SharePoint SQL Server Instance Configurations
Database Configurations
Avoiding
Ginormous Transaction Logs
SQL Server Best Practices for Optimizing SharePointSlide9
SQL Server IntroductionSlide10
SQL Server Introduction
Multiple Instances of SQL Server on One ServerOne Default Instance with Multiple Name InstancesEach Instance Managed Individually Share SQL Server Management Tools
Each Instance Shares Windows Server Resources
Two Types of Databases: System and User
Minimum of Two Files Created Per Database
MDF (Master Data File)
LDF (Transaction Log File)
NDF (Optional for Extending Database)Slide11
SQL Server Transaction Log Process
Modification is sent by
application to SQL Server
1
Data pages are located in,
or read into the buffer cache
and then modified
2
Buffer Cache
Modification is recorded
in transaction log on disk
3
Later, checkpoint writes
dirty pages to database
4
LDF
MDF
CheckpointSlide12
Working with Recovery Models
Recovery Model
Description
Simple
Does
NOT
permit
transaction log (t-log) backups. Automatically truncates log to reduce space requirements
Full
Requires LOG BACKUPS to manage t-log space requirements. Avoids data loss if damaged or missing database file occurs. Permits point-in-time recovery.Bulk LoggedRequires log backups to manage t-log space requirements. Improves performance during bulk copy operations. Reduces t-log space usage by using minimal logging of operations.Slide13
SQL Server Instance System Databases
Master Configuration Database of SQL Server InstanceMsdbStorage of SQL Server Automation Configuration Information
Resource (Hidden)
Read-Only Database Containing All SQL Server System Objects
Tempdb
Temporary Work Storage Area
Model
Template Used to Create All New DatabasesSlide14
SharePoint and SQL Server IntegrationSlide15
SharePoint and SQL Server Integration
All SQL Server Versions and SharePoint VersionsLarge Majority of SharePoint Data Stored in SQL ServerFarm Configuration Information Stored in SharePoint Configuration Database in SQL ServerCentral Administration Content Stored in Own Content Database in SQL Server (Blog on how to rename)
Every Web Application Minimum of One Content Database
Most Service Applications Have at Least One DatabaseSlide16
SharePoint Content Databases
SharePoint Farm Creates Several Databases (>20 DB’s if Spousal Installation and Run Configuration Wizard) Content Database Contains Several Site Collections
Site Collection
Resides
in Only One Content
Database
Prevent RPE’s
Use Full Recovery Model on Production Databases Control Size of Database (Recommended 200GB) Site Collection Quota Templates Maximum Number of Site Collections per DatabaseSlide17
Controlling Size of Content Databases
Web Application
200GB
200GB
2
00GB
200GB
Site Collections
Site Collections
Site Collections
Site Collections
Project
Sites
Department
Sites
HR
Sites
Marketing
Sites
Require 800 Site Collections with 500 MB Quota
750mb X 250 = 187,500mb / 1024 = 183gb
250
250
250
250Slide18
SQL Server Instance Configuration
Default File Locations (Move off C:\ Drive)Minimum and Maximum Memory SettingsMax Degree of Parallelism (MAXDOP) Set to 1 SharePoint
Collation – Latin1_General_CI_AS_KS_WS
During Installation of SQL Server Hosting SharePoint Content
During
Creation of Content Database in SQL ServerSlide19
SQL Server Database Configurations
Model Database File SettingsIncrease Initial Size of Data and Log FilesIncrease Autogrowth Settings (Use MB not %)
Tempdb
Database File Settings
Increase Initial Size of Data and Log Files
Increase
Autogrowth
Settings (Use MB not %)
Use Simple Recovery Model Place Files on Different Drive from Content DatabasesSlide20
DemoSQL Server ConfigurationsSlide21
Avoiding Ginormous Transaction LogsSlide22
Simple Recovery Model Slide23
Full Recovery Model Slide24
Avoiding Ginormous Transaction Logs
mdf
ldf
Sunday
Full Backup
mdf
ldf
Tuesday
Differential
mdf
ldf
Differential
Monday
mdf
ldf
Differential
Wednesday
You Lose
mdf
file of database on Thursday at
4:00pm
(1)
BACKUP
LOG
DB_Name
TO D:\SQLBackups\TempBackup.Bak WITH
NORECOVERY
(2)
Restore Full Backup from Sunday
(3)
Restore
Differential
Backup from
Wednesday
(4)
RESTORE LOG FROM D:\SQLBackups\TempBackup.Bak
BACKUP LOG
DB_Name
TO D:\SQLBackups\Weekly_T_Log.Bak WITH INITSlide25
Best Practices for Optimizing SharePointSlide26
Best Practices for SharePoint’s SQL Server
Dedicated SQL Server Instance / ServerNo Spousal Installations of SQL Server or SharePointDatabase Size Should Not Exceed 200GBMax Degree of Parallelism (MAXDOP) Set to 1
Modify Model System Database Settings
Avoid Auto-Shrinking Databases
Use Database
Autogrowth
Sparingly
Reduces Fragmentation
Improves Data Entry Performance Slide27
Best Practices for SharePoint’s SQL Server
Spread Data Files and Transaction Log Files Across Multiple Drives or Locate them on RAID 5/10Create Multiple
T
empdb
Files on Multiple Drives
Generate Database Maintenance Plans
Defragment Drives Containing Content Database Files
Perform Regular Backups of Database and T-Logs
Perform DBCC CHECKDB Operations RegularlyJust Say NO to Simple Recovery ModelSlide28
Thank You for Attending! Please Complete the Session Evaluation Slide29
Keep in Touch…
www.microtechpoint.com Brian’s Blog
Speaking Events
brian@microtechpoint.com
@brianalderman & @microtechpointSlide30
MySPC
Sponsored by
connect.
reimagine.
transform.
Evaluate sessions
on
MySPC
using your
laptop or mobile device:
m
yspc.sharepointconference.comSlide31
©
2014
Microsoft Corporation. All rights reserved. Microsoft, Windows,
and
other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.