![Page 1: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/1.jpg)
Tuning Microsoft SQL Server for SharePointDaniel Glenn
![Page 2: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/2.jpg)
Daniel Glenn
@DanielGlenn
http://KnowSP.com
SharePoint and Collaboration Practice Leader @ InfoWorks,
Inc. – www.InfoWorks-tn.com
PASS Nashville Business Intelligence (Nash BI) User Group
SharePoint User Group President and Speaker
![Page 3: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/3.jpg)
Business Process Analysis, Analysis, Architecture and
Design
Fresh perspective to create and align your strategy and
goals
We will help you respond to your
business needs and get more done in
less time
Improve the impact of information on
your business
Trusted partner for creating and
maintaining a sustainable organization
PMP-certified Project Managers,
PMO standards and procedures
InfoWorkswww.InfoWorks-TN.com
![Page 4: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/4.jpg)
About you
Database administrators
Business users
Developers
SharePoint administrators
SharePoint experience?
![Page 5: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/5.jpg)
About you
![Page 6: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/6.jpg)
Agenda
SharePoint install demo
What is SharePoint?
SharePoint Databases
SQL Configuration
SQL Server Maintenance
SharePoint Configuration
![Page 7: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/7.jpg)
SharePoint Install Demo
![Page 8: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/8.jpg)
What is SharePoint?
![Page 9: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/9.jpg)
SharePoint
SharePoint is a valuable tool that delivers enterprise document management, business intelligence, web content management, search, and social capabilities
There are different versions and deployment options
Foundation, Standard, Enterprise; on-premises, cloud, hybrid
MS SQL is SharePoint’s data store for almost all components
What is a ‘SharePoint farm?’
A Site Collection?
![Page 10: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/10.jpg)
SharePoint
![Page 11: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/11.jpg)
![Page 12: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/12.jpg)
![Page 13: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/13.jpg)
![Page 14: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/14.jpg)
![Page 15: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/15.jpg)
SharePoint Databases
![Page 16: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/16.jpg)
SharePoint Databases
For a full install of SharePoint 2013 Enterprise on-premises, there are over 20 databases
Types:ConfigurationCentral Admin ContentContentService Apps (some have more than one
DB)
One content database per Site Collection
Content databases can have many site collections, but a large (>100GB) site collection should have a dedicated database
![Page 17: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/17.jpg)
SharePoint Databases
![Page 18: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/18.jpg)
SharePoint Configuration
![Page 19: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/19.jpg)
SharePoint Configuration
Dedicated SQL instanceWatch those resources!
DNS CNAME or SQL Alias for use with SharePoint
Before SharePoint install
Keep your collaboration site content databases < 200GB
Monitor SharePoint content database I/O
![Page 20: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/20.jpg)
SQL Configuration
![Page 21: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/21.jpg)
SQL Configuration
New Instance Not on the SharePoint server!
Latin1_General_CI_AS_KS_WS
File placement
Model database Increase initial size, and set to do MB growth not %
Full recovery
Tempdb Data files = # of processor cores*
Simple recovery
Server memory (RAM) usage
Max Degree of Parallelism
![Page 22: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/22.jpg)
SQL Configuration
![Page 23: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/23.jpg)
SQL Configuration
Permissions for SharePoint administrators & service accounts
SharePoint admins themselves do NOT need permissions within SQL
The service account used to install SharePoint needs:
securityadmin and dbcreator SQL Server roles during setup and configuration
![Page 24: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/24.jpg)
SQL Configuration
Permissions for SharePoint administrators & service accounts (cont)
The service account that is the Farm service account will be provisioned automatically during farm creation to have:
dbcreator and securityadmin fixed server roles.
It will also be the db_owner for all SharePoint databases.
Other service accounts will be added to new roles on SharePoint databases
To learn more: http://technet.microsoft.com/en-us/library/cc678863(v=office.15).aspx
![Page 25: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/25.jpg)
SQL Server Maintenance
![Page 26: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/26.jpg)
SQL Server Maintenance
Database growthPlan and pre-grow
Quotas, # site collections per database
Low vs. High I/O databasesLower I/O databases can be larger without
performance issues
BackupsLocation
Test them!
Simple vs. full recovery
![Page 27: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/27.jpg)
SQL Server Maintenance
KB841057 (et al.) For SharePoint production databases, don’t:
Open
Edit
Query
Etc.
FragmentationDatabase
Tables
Indexes
File system
![Page 28: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/28.jpg)
SQL Server Maintenance
KB841057 (et al.) For SharePoint production databases, don’t:
Open
Edit
Query
Etc.
FragmentationDatabase
Tables
Indexes
File system
![Page 29: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/29.jpg)
Summary
![Page 30: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/30.jpg)
Best Practices & Summary
Use SQL Server best practice guidance
Use RAID disks and spread data and trans log files on multiple physical drives
Auto growth and ‘shrinking’ causes fragmentation
Defragment SQL drives that have SharePoint content databases
Backup (with multiple copies) and test your backups
Dedicated SQL Server or SQL Instance
Create SQL Server alias or CNAME
Content databases < ~200GB
Monitor SharePoint content database I/O
MaxDOP = 1
Set dbcreator and securityadmin for SharePoint install account only
![Page 31: Tuning Microsoft SQL Server for SharePoint - Daniel Glenn · 2015-10-26 · SQL Configuration Permissions for SharePoint administrators & service accounts (cont) The service account](https://reader034.vdocuments.us/reader034/viewer/2022042113/5e8edab2d3c1685a05059499/html5/thumbnails/31.jpg)
SharePoint Install Demo