1 / 69

Planning Large-Scale Microsoft SQL Server 2008 R2 Enterprise Edition Deployments with Microsoft SharePoint Server 2010

Required Slide. SESSION CODE: DAT 313. Planning Large-Scale Microsoft SQL Server 2008 R2 Enterprise Edition Deployments with Microsoft SharePoint Server 2010 . Joel Oleson Sr. Architect & Evangelist Quest Software http:// www.sharepointjoel.com @ joeloleson. Who is SharePoint Joel?.

reegan
Download Presentation

Planning Large-Scale Microsoft SQL Server 2008 R2 Enterprise Edition Deployments with Microsoft SharePoint Server 2010

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. Required Slide SESSION CODE: DAT 313 Planning Large-Scale Microsoft SQL Server 2008 R2 Enterprise Edition Deployments with Microsoft SharePoint Server 2010 Joel Oleson Sr. Architect & Evangelist Quest Software http://www.sharepointjoel.com @joeloleson

  2. Who is SharePoint Joel? ~10 Years SharePoint Veteran SharePoint Product Architect, Evangelist & Strategy @ Quest Twitter: @joeloleson Email: joel.oleson@quest.com

  3. Audience Poll • New to SharePoint 2010? • SQL Admins? SharePoint Admins? • Large-scale Implementation (+1 TB) experience? • How many SQL Admins are freaking out because of the number of SharePoint databases?

  4. Session Objectives And Takeaways • Session Objective(s): • Understand the SQL and storage factors that affect a large scale SharePoint deployment. • SharePoint SQL and storage best practices. • Takeaway: • Proper SQL and Storage design is critical to overall SharePoint health!

  5. UnderstandingSharePoint Databases

  6. SharePoint Containment Hierarchy

  7. Comparing SharePoint Capacity Guidelines

  8. DatabasesBefore and After

  9. Understanding SharePoint Databases

  10. Lots of New 2010 Service Databases* Sample install

  11. I don’t know what to do, so I’ll turn it all on… Nosé que hacer, mejor prendo todo…

  12. SharePoint 2010 Databases in SQL SharePoint Foundation SharePoint Server 2010 Standard Search Administration (holds ACLs Crawl – crawl history and data Property – information associated with crawled results item properties Web Analytics Reporting – Aggregated reports, groups of sites and diagnostics Web Analytics Staging – temporary unaggregated fact data, query batch State – Temporary State information; Required for Infopath Services, chart webpart, Visio Services (cont…) • Configuration • Central Admin Content • Content • Usage & Health Data Collection • Business Data Connectivity (External Content Types) • Application Registry (Used during upgrade for BDC can be deleted after Upgrade) • Subscription Settings (Rare- Only used for hosting configurations, created in powershell) • SharePoint Foundation Search – (for help)

  13. SharePoint 2010 Databases in SQL SharePoint 2010 Standard SharePoint Server 2010 Ent… Word Automation Services Excel Services Visio Services Performance Point Services Web Application Client Services Access Services Requires Report Server if using Access Services (See optional) • Profile – users and their social info • Sync – used for profiles sync to AD • Social Tagging – social tags and notes • Managed Metadata… • Secure Store (Single Sign-on credential mapping) • Optional • *PowerPivot Service Application (R2) – stores additional data in content databases and in Central admin content database • *Report Server • Report Server TempDB

  14. Understanding Configuration DB

  15. Understanding Content DB

  16. Understanding Search

  17. Understanding Profiles

  18. Service App Associations

  19. WAN Friendly Service Apps HTTPS WCF + XML Shared *SharePoint Foundation

  20. Understanding Local Only Service Apps *EntepriseService Apps

  21. Performance

  22. Why is SQL that important?SQL Health = SharePoint Health! • Sub-optimal SQL perf will radiate to other components in the farm. • Slow response from SQL Server will result in queued App requests. • As the app slows down, so does SQL.

  23. Database Disk I/O Demand Most Demand (Write) Medium Demand Low Demand Property *Content.. Config Crawl Model +SSP (2007) Temp Master Transactionlogs Service Apps Usage & Health Data Collection Service Apps Web Analytics * Except during backup and Indexing + Except during Profile Import

  24. Top Performance Killers • Indexing/Crawling • Backup (SQL & Tape) • Profile Import • Misc Timer Jobs – User Sync for large #s of Users • Poor Storage Configuration • STSADM Backup/Restore • Large List Operations • Heavy User Operation List Import/Write • Network • Inefficient Queries

  25. Scaling SQL Up and Out

  26. Scaling SQL 2.5TB 2.5TB 2.5TB SCALE OUT 

  27. Scalling SQL - Out • More SQL servers = More flexibility • There aren’t really any physical barriers • SharePoint won’t prevent you from placing 100 databases on 100 different SQL instances • The real barriers are manageability and cost. • More servers = more money • More servers = more management • $$ + > management = $$$$

  28. Scaling SQL SCALE UP  2.5TB 2.5TB 2.5TB

  29. Scaling SQL - Up • Design is Paramount! • Consider the following: • Overall SQL Throughput (transactions/sec) • Disk throughput (IOPS) • Network throughput (MB/sec) • Disk backup throughput (MB/sec) • Network based backup throughput (MB/sec) • Length of maintenance windows (hours -> minutes) • SharePoint upgrade throughput

  30. SQL: Scale Out VS. Scale Up

  31. Walkthrough: Scale Up VS. OutHow to design a 5TBSharePoint SQL Deployment 1TB 1TB 1TB 1TB 1TB 1TB 1TB 1TB 1TB 1TB

  32. Standard Architectures Schematic Diagram, not to be use as a recommendation for Server Counts • Large Enterprise • Up to 500k users (~500 RPS) • 10-20 TB of data • WFE • Federated • Services App Servers • Common Enterprises • 10-50k users (~50 RPS) • 1-2 TB of data • WFE SQL App Servers • Limited deployments • minimum services • up to 5000 users (~5 RPS) • 50-100 GB of data SQL WFE & App Servers • Demos and Dev Boxes SQL Medium Farm Single Server Small Farm Large Farm

  33. SharePoint 2010 Search Scalability • Multiple Dedicated Web Servers • Multiple Dedicated Query Servers • Multiple Dedicated Crawl Servers, with multiple Crawl DBs to increase parallelization of the crawl process • Multiple distributed Index partitions (max of 10 million items per index partition) • Two query components for each Index partition, spread among servers

  34. Achieving Scale and Cost with SharePoint Virtualization • High-Availability across Hosts • All components virtualized • Uses only two Windows Ent Edition Licenses

  35. Consider the Organization • Will the SharePoint SQL Servers be self managed? • What experience does the team managing SQL have? • Do they have: • Monitoring? • Standard Maintenance Procedures? • Standard Maintenance Windows? • Standard SQL Builds? • What are the break/fix and standard SLA’s?

  36. Scaling SQL – The Bottom Line • Don’t scale SQL instances beyond comfort zones! • Do measure system throughput – Know All of your bottlenecks! • Scaling out is more flexible but scaling up is more cost effective. Find a balance between scaling up and out and stick to it. (1-5TB per instance for example)

  37. SharePoint High Availability in SQL

  38. Highly Available Deployment? Redundant Switches Redundant Web/Application Servers Active/Passive SQL w/ Redundant HBA’s Redundant SAN Fabric RAID 1 Storage Redundant Power Supplies

  39. Mirroring Within a Farm • SQL High Avail or High Protection (sync) mirroring replaces or augments clustering as the SQL HA solution. • Farm components can span closely located datacenters* • Must have LAN like connectivity (1Gbps) • Must have less than 1ms in latency (2ms RTT) • Can be Active/Active or Active/Passive • Use DNS or Load Balancing to direct traffic between frontends.

  40. Mirroring in SharePoint

  41. Mirroring Within Farm

  42. High Availability Between Farms • Can use a variety of methods to ship content between farms/data centers • Log shipping • Mirroring • Storage replication • Longer distances supported* • The greater the latency the harder it is to replicate content. • No way to keep configuration or search in sync.

  43. High Availability Between Farms

  44. The Two Basic HA/DR Scenarios Mirroring Within Farm Mirroring/Log ship Between Farms Pros: Allows long distance separation Can protect against logical corruption Very flexible! Cons: More expensive Harder to setup and manage Failover is a big decision • Pros: • Great combo HA/DR solution • Cheaper to implement • Easier to manage • Cons: • Requires closely located datacenters • Requires excellent network conditions • Not flexible • Content corruption is replicated immediately.

  45. Combining Solutions

  46. SQL 2008 - Do you have Enterprise? http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx

  47. SQL 2008 - Do you have R2 Enterprise? http://www.microsoft.com/sqlserver/2008/en/us/R2-editions.aspx

  48. Disaster Recovery Configuration Based Backup

More Related