SQL Server and SharePoint Best Frienemies Lisa Gardner Premier Field Engineer [email protected] http://blogs.msdn.com/sqlgardner @sqlgardner Who am I ? What is PFE? Lisa Gardner aka SQLGardner Central Ohio native Working with SQL since 6.5 http://blogs.msdn.com/sqlgardner @SQLGardner Premier Field Engineering Reactive and Proactive support for Premier customers Architecture/Project Guidance Team Mentoring Deliver workshops Troubleshooting 2 Table of Contents SharePoint Databases Configuration, Setup, and Maintenance What to Look Out For SharePoint Overview Understanding the Application. SharePoint Glossary WFE Web Application Service Application Site Collection ULS Logs Timer Jobs 5 SharePoint Web Architecture Farm Web Application Site Collection Site List Item 6 Key Attributes Web Application Site Collection Site • Different IIS Site • Created on each WFE • Isolates Content • Provides authentication mechanism • Container of Sites • Quotas • Decentralized Content Administration • Also serves as a site • Permission Inheritance • Can Share layout and data with other sites • Can provide unique feature set from other sites 7 Service Applications Provides granular pieces of functionality Some can be tied to a specific server Offers scalability, load balancing, fault tolerance for most services Many to many relationship with web applications and service applications Each web application can have a unique set of service applications 8 Timer Jobs SharePoint equivalent to SQL Agent OWSTimer - Windows service for SharePoint 2010 at a predefined schedule Uses same logging infrastructure as web tier Includes Correlation IDs Jobs can be nested 9 SharePoint Internal Data 10 Logging Database Stores all SharePoint usage and health data ULS trace log data Event log data Blocking SQL Queries Crawl and Query Statistics Feature Usage Page Requests +More 11 Logging Database 12 ULS Logs 13 Correlation IDs Generated for every request Logged from the start of a request through to the end Useful for troubleshooting and tracing On error pages, ULS logs, Windows Logs, SQL Traces 7d25d051-ca73-43 … ~~~~~~~~~~ ~~~ ~~~~~~~~~~ ~~~ 7d25d051-ca73-43…… 7d25d051-ca73-43 7d25d051-ca73-43 … Application Server Web Front-End Server 14 SharePoint Databases So Many Databases, So Little Time Configuration and Admin Content Databases Farm Configuration Store Objects Table – Serialized Objects Binaries Table – Farm Solution Store SiteMap Table – Links a site into the configuration Content Database for Central Admin is a Content DB with very specific templates - considered to be an extension of the configuration database Backup and Recovery It is Supported to back up this database It is Not Supported to restore unless the farm is fully stopped when the backup is taken 16 Configuration and Admin Content Databases General Recommendations Default recovery model is Full but in most cases this database should be run in simple recovery mode Initial Data File Size: 2GB is appropriate for most situations Config databases are typically smaller and do not get much load Mirroring Supported to mirror within the farm (partner on same network as primary) Not Supported to mirror asynchronously or to log ship over WAN 17 Content Database Stores all site data in a site collection Site Metadata Web Part Pages Files uploaded to document libraries List Items Security Solutions It is supported to Mirror in Farm for High Availability It is supported to Mirror Asynchronously or Log Ship over WAN for disaster recovery General Recommendations Run in Full recovery mode only if the site data requires point in time restores 18 Content Database Schema Why SharePoint seems so crazy. Container Tables Sites Id Quota Other Metadata Webs SiteId Id Url Title ScopeId Metadata AllLists WebId Id Title ItemCount ScopeId Fields Metadata Namespace Table Url Other Metadata 1…64 1...32 1..8 1..16 1..12 19 1..8 1…16 ~35 Userdata table Content Database Layout Can contain 1-2000 site collections Scale out at the db level and the instance level. Sizing Guidance <200GB – Maintenance tasks stay manageable – Makes db movement and DR easier – Plan for 2 IOPs per GB data Can have 200GB-4TB if .25 IOPs per GB Size and load depends on the sites they contain Separate very active sites into different site collections/content dbs Can have 32,767 dbs per instance, but recommend 200 per instance as manageability can be an issue 300 DBs per Web Application 20 Service Application DBs Search • Admin • Crawl • Property Logging Reporting Services Profile Web Analytics • Profile • Syncronization • Social Tagging • Reporting • Staging BDC State Secure Store Power Pivot Project Server Performance Point 21 Service Application Databases Performance Considerations The different service application dbs have a wide variety of performance/sizing considerations. Write-Intensive dbs Usage and Health Data Collection database (Logging) Web Analytics Reporting database (during load) Search service application Crawl database (during crawls) Search service application Property database User Profile service application Synchronization database 22 Service Application Databases Performance Considerations Cont’d Read Intensive DB’s Web Analytics Reporting database Search service application Crawl database User Profile service application Profile database User Profile service application Synchronization database User Profile service application Social Tagging database Reporting database (Project Server) 23 Database Scale Out Guidance Search Content Content Content Content Content Content Logging Web Analytics Other Admin/ Content 24 Configuration, Setup, and Maintenance Planning for SharePoint Setup Allow the SharePoint installer to create databases Modify file sizes and growth settings Rename dbs to remove GUIDs SharePoint setup and admin accounts required roles: DB Creator Security Admin Can be removed for the setup account but will need to be added again for any further installs – not recommended Patching/Service packs Adding a new Service Application Add Service Application account logins Requires db_owner role in DB 26 Instance Configuration Follow general Best Practices for SQL Configuration Use Latin1_General_CI_AS_KS_WS collation Configure for heavy TempDB usage Multiple data files Data and log files separated/isolated Pre-size data files Set max degree of parallelism to 1 SharePoint overrides with MAXDOP Set max server memory and use Lock Pages In Mem Consider setting fill factor (%) to 80 27 Database Configuration Do not use Auto Shrink Set Auto Create Statistics OFF Set Page Verify to Checksum Set Auto Grow sizes to MB not Percent Pre-size for growth Monitor utilization and grow manually! 28 Index Maintenance Index Maintenance is extremely important in SharePoint DMV Sys.dm_db_index_physical_stats can be used to report index fragmentation SharePoint 2007 by default would rebuild every index via a Timer Job SharePoint 2010 does a much better job at keeping index fragmentation in check It only rebuilds indexes that are fragmented Updates statistics 29 Health Analyzer Rules Index defragmentation and statistics maintenance address the following databases: Configuration databases Content databases User Profile: Profile databases User Profile: Social databases Web Analytics Reporting databases Web Analytics Staging databases Word Automation Services databases Search Property/Crawl databases These databases contain proc_DefragmentIndices Run daily 30 Health Analyzer Rules Cont’d Search Property database – Proc_MSS_DefragSearchIndexes – Run weekly Crawl database – Proc_MSS_DefragGathererIndexes – Manual – Always report as fragmented – Execute this rule after the first full crawl 31 Statistics Health Analyzer rules rebuild indexes and update statistics AutoUpdate – off in SP 2010 by default Update manually when: Query execution times are slow After maintenance operations such as table truncation or a large batch insert/update/delete 32 Why is Index/Stats Maintenance So Important? GUIDs are used as clustered primary keys Random values = unpredictable insert pattern 16 bytes each Heavy insert/update activity These properties lead to rapid index fragmentation due to many page splits Fillfactor helps delay the inevitable but increases space usage SharePoint rebuilds indexes with fillfactor of 80 33 What to Look Out For Common issues New Content Databases Use DBA created content databases! SharePoint hard codes small file size and growth settings Automation Options: Powershell is a great option to allow SP Administrators to create dbs! Have a number of empty DBs already created Must Do’s Use Latin1_General_CI_AS_KS_WS collation Set appropriate recovery model for your recovery needs Add SP farm setup account and service account with db_owner role 35 Ensure Index Maintenance is Running Health Analyzer Rule Definition Databases used by SharePoint have fragmented indices Databases used by SharePoint have outdated index statistics Health Analysis Job in Logging DB Details in ULS logs 37 Excessive Blocking Common scenario: “The SQL Server is slow” Ask for ULS Log info Blocking/Deadlocks can be common in content DBs Try a manual update stats Inquire about large lists, dbs over threshold, and other capacity limitations being exceeded Ask about list throttling and “happy hour” Read Committed Snapshot Isolation is not supported 38 Others ASYNC_NETWORK_IO_WAITS Disk IO TempDB Bottleneck Very Large Queries Logging is the ONLY DB to be queried directly 39 SP2013 Changes Shredded storage to minimize storage needs with versioning Sparse Columns to support wide lists Web Analytics redesigned – more robust/scalable Profile Sync – tests of a 3 week import for 300k users now only takes 7 hours Stretch farms no longer supported – all databases must now reside in the same data center 40 Helpful Links Know the Limits! http://technet.microsoft.com/en-us/library/cc262787.aspx More info on SharePoint DBs http://technet.microsoft.com/en-us/library/cc678868.aspx http://www.microsoft.com/enus/download/details.aspx?id=3408 41 Questions?