1 / 42

SQL Server and SharePoint

SQL Server and SharePoint. Best Frienemies. Lisa Gardner. Premier Field Engineer LisaG@microsoft.com 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

winona
Download Presentation

SQL Server and SharePoint

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server and SharePoint Best Frienemies Lisa Gardner Premier Field Engineer LisaG@microsoft.com http://blogs.msdn.com/sqlgardner @sqlgardner

  2. 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

  3. Table of Contents • SharePoint Overview • SharePoint Databases • Configuration, Setup, and Maintenance • What to Look Out For

  4. SharePoint Overview Understanding the Application.

  5. SharePoint Glossary • WFE • Web Application • Service Application • Site Collection • ULS Logs • Timer Jobs

  6. SharePoint Web Architecture

  7. Key Attributes

  8. 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

  9. 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

  10. SharePoint Internal Data Logging Database ULS Logs Correlation IDs

  11. 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

  12. Logging Database Table partitioning for ease of use and custom views Tables partitioned for performance Views for usability Custom reports through Excel or Excel web parts Some out-of-the-box reports use this data

  13. ULS Logs Also referred to as trace logs Must be consistent location on all farm servers Over 260 categories of events Nine columns including: Timestamp, Process, Category, EventID, Level, Message, and Correlation

  14. 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 ULS ULS 7d25d051-ca73-43 … ~~~~~~~~~~~~~ ~~~~~~~~~~~~~ 7d25d051-ca73-43 … 7d25d051-ca73-43… 7d25d051-ca73-43 … Application Server Web Front-End Server

  15. SharePoint Databases So Many Databases, So Little Time

  16. 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

  17. 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

  18. 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

  19. Content Database Schema Why SharePoint seems so crazy. Container Tables Sites Webs AllLists Namespace Table Url WebId LeafName DirName DoclibRowId Other Metadata ListId Id SiteId Userdata table 1…64 1...32 1..8 1..16 1..12 1..8 1…16 ~35 float int sql_variant Other metadata datetime bit nvarchar ntext

  20. 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

  21. Service Application DBs

  22. 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

  23. 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)

  24. Database Scale Out Guidance Search Content Content Content Content Content Content Logging Web Analytics Other Admin/ Content

  25. Configuration, Setup, and Maintenance

  26. 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

  27. 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

  28. 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!

  29. 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

  30. 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

  31. 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

  32. 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

  33. 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

  34. What to Look Out For Common issues

  35. 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

  36. Full Crawl Impact • When a full crawl is running – it is a very intensive operation that can have an impact on other dbs hosted on that instance – if asked about overall performance slowdown, ask if a crawl is running • It is common to see deadlocking in the Crawl database during this time. • If size rapidly grows: ask about the depth of crawling links in documents

  37. 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

  38. 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

  39. Others • ASYNC_NETWORK_IO_WAITS • Disk IO • TempDB Bottleneck • Very Large Queries • Logging is the ONLY DB to be queried directly

  40. 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

  41. 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/en-us/download/details.aspx?id=3408

  42. Questions?

More Related