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? ~10 Years SharePoint Veteran SharePoint Product Architect, Evangelist & Strategy @ Quest Twitter: @joeloleson Email: email@example.com
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?
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!
I don’t know what to do, so I’ll turn it all on… Nosé que hacer, mejor prendo todo…
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)
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
WAN Friendly Service Apps HTTPS WCF + XML Shared *SharePoint Foundation
Understanding Local Only Service Apps *EntepriseService Apps
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.
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
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
Scaling SQL 2.5TB 2.5TB 2.5TB SCALE OUT
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 = $$$$
Scaling SQL SCALE UP 2.5TB 2.5TB 2.5TB
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
Walkthrough: Scale Up VS. OutHow to design a 5TBSharePoint SQL Deployment 1TB 1TB 1TB 1TB 1TB 1TB 1TB 1TB 1TB 1TB
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
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
Achieving Scale and Cost with SharePoint Virtualization • High-Availability across Hosts • All components virtualized • Uses only two Windows Ent Edition Licenses
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?
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)
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
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.
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.
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.
SQL 2008 - Do you have Enterprise? http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx
SQL 2008 - Do you have R2 Enterprise? http://www.microsoft.com/sqlserver/2008/en/us/R2-editions.aspx
Disaster Recovery Configuration Based Backup