1 / 41

SQL Server and SharePoint

SQL Server and SharePoint. Best Frienemies. Lisa Gardner Premier Field Engineer Microsoft. Who am I ? What is PFE?. Lisa Gardner aka SQLGardner Production DBA for 13 years prior to joining Msft Working with SQL since 6.5 http://blogs.msdn.com/sqlgardner @ SQLGardner

coby
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 Microsoft

  2. Who am I ? What is PFE? • Lisa Gardner aka SQLGardner • Production DBA for 13 years prior to joining Msft • 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 • Workshop Delivery • Troubleshooting

  3. Microsoft CSS @ PASS 2012 Breakout Sessions Pre-Con Customer Stories From the Front Line Tues 11/6 8:30am-4:30pm Bob Ward Adam Saxton Inside SQLOS 2012 DBA-500-HD in 618-620 Wed 11/7 1:30-4:30pm Bob Ward SQL Server and SharePoint: Best Frienemies AD-310-C in 6E Wed 11/7 4:45-6pm Lisa Gardner Troubleshooting SQL Server 2012 Performance with Extended Events DBA-407-C in 301-TCC Fri 11/9 8:00-9:15am Rohit Nayak Working with Claims and SQL Server BI Technologies BIA-401-C in 6E Fri 11/9 8:00-9:15am Adam Saxton 4C-3 11/7 – 11/9 10am – 6pm Friday closes at 2pm Early Birds – 7am-8am

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

  5. SharePoint Overview Understanding the Application.

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

  7. SharePoint Web Architecture

  8. Key Attributes

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

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

  11. SharePoint Internal Data Logging Database ULS Logs Correlation IDs

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

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

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

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

  16. SharePoint Databases So Many Databases, So Little Time

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

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

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

  20. Content Database Schema 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 Why SharePoint seems so crazy.

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

  22. Service Application DBs

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

  24. Configuration, Setup, and Maintenance

  25. 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/Update Statistics OFF (content dbs) • 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 • First drops auto created stats • AutoUpdate, AutoCreate – 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

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

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

  40. Questions? Don’t forget to fill out evals!

  41. Thank you for attending this session and the 2012 PASS Summit in Seattle

More Related