large scale sql considerations for sharepoint deployments
DESCRIPTION
Large Scale SQL Considerations for SharePoint: Considerations for performance, scale, storage, and high availabilityTRANSCRIPT
![Page 1: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/1.jpg)
Joel OlesonSr. Product ArchitectQuest Softwarehttp://www.sharepointjoel.com@joeloleson
Contributions: Mike Watson, Todd Klindt
![Page 2: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/2.jpg)
Audience PollNew to SharePoint?SQL Admins?SharePoint Admins?Large-scale Implementation (+1 TB) experience?How many SQL Admins are freaking out because of the number of SharePoint databases?
![Page 3: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/3.jpg)
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!
![Page 4: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/4.jpg)
SharePoint Databases Overview
![Page 5: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/5.jpg)
SharePoint Containment Hierarchy
![Page 6: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/6.jpg)
Understanding SharePoint Databases
![Page 7: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/7.jpg)
Understanding Configuration DB
![Page 8: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/8.jpg)
Understanding Content DB
![Page 9: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/9.jpg)
Understanding SSP DB - Search
![Page 10: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/10.jpg)
Understanding SSP DB
![Page 11: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/11.jpg)
![Page 12: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/12.jpg)
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.
![Page 13: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/13.jpg)
Database Disk I/O Demand
SearchSearch
Most Demand
ConfigConfig
Medium Demand
+SSP+SSP
*Content..*Content..
Low Demand
* Except during backup and Indexing + Except during Profile Import
TempTemp
MasterMaster
ModelModel
TlogsTlogs
![Page 14: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/14.jpg)
Top Performance Killers1. Indexing/Crawling2. Backup (SQL & Tape)3. Profile Import4. Misc Timer Jobs – User Sync for large #s of Users5. Poor Storage Configuration6. STSADM Backup/Restore7. Large List Operations8. Heavy User Operation List Import/Write9. Network10. Inefficient Queries
![Page 15: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/15.jpg)
![Page 16: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/16.jpg)
Scaling SQL
2.5TB 2.5TB 2.5TB
SCALE OUT
![Page 17: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/17.jpg)
Scalling SQL - Out
More SQL servers = More flexibilityThere aren’t really any physical barriersSharePoint won’t prevent you from placing 100 databases on 100 different SQL instancesThe real barriers are manageability and cost.
More servers = more moneyMore servers = more management$$ + > management = $$$$
![Page 18: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/18.jpg)
Scaling SQL
2.5TB
SCAL
E U
P
2.5TB
2.5TB
![Page 19: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/19.jpg)
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
![Page 20: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/20.jpg)
SQL: Scale Out VS. Scale Up
Scale Out Scale UpAdvantages
Better Performance Easier to Manage
Better Flexibility Cheaper
Disadvantages
More Expensive System Design is Critical
Harder to Manage Single Point of Failure
![Page 21: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/21.jpg)
Walkthrough: Scale Up VS. OutHow to design a 5TBSharePoint SQL Deployment
1TB
1TB
1TB
1TB
1TB
1TB
1TB
1TB
1TB
1TB
![Page 22: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/22.jpg)
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?
![Page 23: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/23.jpg)
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)
![Page 24: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/24.jpg)
![Page 25: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/25.jpg)
Highly Available Deployment?
Redundant SwitchesRedundant Web/Application ServersActive/Passive SQL w/ Redundant HBA’sRedundant SAN FabricRAID 1 StorageRedundant Power Supplies
![Page 26: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/26.jpg)
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/PassiveUse DNS or Load Balancing to direct traffic between frontends.
![Page 27: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/27.jpg)
Mirroring Within Farm
![Page 28: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/28.jpg)
High Availability Between Farms
Can use a variety of methods to ship content between farms/data centers
Log shippingMirroringStorage replication
Longer distances supported* The greater the latency the harder it is to replicate content.
No way to keep configuration or search in sync.
![Page 29: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/29.jpg)
High Availability Between Farms
Bring Databases OnlineAttach Databases to SharePointKickoff a CrawlUpdate DNS/WINSRestore SQL Mirroring
![Page 30: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/30.jpg)
The Two Basic HA/DR Scenarios
Mirroring Within Farm Pros:
Great combo HA/DR solutionCheaper to implementEasier to manage
Cons:Requires closely located datacentersRequires excellent network conditionsNot flexibleContent corruption is replicated immediately.
Mirroring/Log ship Between Farms
Pros:Allows long distance separationCan protect against logical corruptionVery flexible!
Cons:More expensiveHarder to setup and manageFailover is a big decision
![Page 31: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/31.jpg)
Combining Solutions
![Page 32: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/32.jpg)
SQL 2008 - Do you have Enterprise?
![Page 33: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/33.jpg)
![Page 34: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/34.jpg)
Content DB Size Limitation 100GB?
Exceeding 100GB? Keep in mind:Backup/restore/maintenance will be harder.Use differential backup.All sites share the same tables. Isolate large sites.Use multiple data filesDefrag regularly.
* Your experience may vary: H/W and usage profile dependant.
![Page 35: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/35.jpg)
Large Lists – 2000 Items?SharePoint supports large lists, but you must carefully plan how users view the lists to prevent performance impacts. For best performance, do not exceed 2,000 items per folder or viewDefine limits on views. Use indexed columns. Take it easy on column and field counts.
![Page 36: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/36.jpg)
SQL Memory – 4GB Enough?
“4 GB is the minimum required memory, 8 GB is recommended for medium size deployments, and 16 GB and above is recommended for large deployments.”What influences the amount of RAM?
Number and size of Content databases.Number of concurrent requests to SQL.Size and width of commonly used lists.
Remember: Minimum is where we start…
![Page 37: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/37.jpg)
SQL Data files Best Practices:
Allocate TempDB on RAID 1. (or R1 variants)Separate Data and Logs on different LUNSSpread databases on multiple spindlesFor TempDB, Create multiple data files up to the number of CPU cores.Pre-Grow files (Autogrow as safety net)
SharePoint 2010 supports file groups for content databases!
![Page 38: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/38.jpg)
Identifying Disk Bottlenecks
PerfmonMonitor transfer/sec for throughput trends.Monitor Disk sec/Read / Disk sec/Write for bottlenecks.Monitor disk Queue length for bottlenecks.
SQLSelect * from sys.dm_IO_virtual_file_stats(null, null)Solution -http://www.sqlmag.com/Articles/ArticleID/96513/96513.html
![Page 39: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/39.jpg)
![Page 40: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/40.jpg)
Lots of New SharePoint 2010 Databases
![Page 41: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/41.jpg)
Large List Throttling
Configurable List Throttling
And Thresholds
You control when and how
much!
List throttling controls forces end users to create more efficient views with < x number of items.
![Page 42: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/42.jpg)
Web Part Performance Dashboards
![Page 43: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/43.jpg)
Best Practices Analyzer Health Rules Runs on a Timer Job
Create your own!
Repair Auto-
magically!
![Page 44: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/44.jpg)
Logs & Reporting to the DB
Extensibility for reporting and
possibilities are limitless
![Page 45: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/45.jpg)
SummarySQL is extremely important to SharePoint health and Performance
Put SQL on 64bit. (Required for SharePoint 2010)
SQL 2008 Enterprise – Scale, HA, compliance security features
Think IOPS when designing disk arrays.
Always separate work loads with the following priority: temp, log, search, content.
SQL scales up and out. Don’t push the limits upward, but keep manageability and costs in mind when scaling out.
Designing enterprise services with great care. Separate SSP and Search when possible.
SharePoint 2010 brings more databases so strategically plan for 20-50 dbs min…
![Page 46: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/46.jpg)
![Page 47: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/47.jpg)
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
![Page 48: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/48.jpg)
![Page 49: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/49.jpg)
Search Disk Performance Drive IOPs Read (max) IOPs Write
(max)Ratio Read/ Write
Latency Read (sec)
Latency Write (sec)
Search DB Logs
14.67 1,777.29 0.01 0.3060 0.8550
Temp DB 1,110.98 1,492.01 0.74 1.6870 3.5660
Query file group
3,507.26 1,631.96 2.15 3.4360 3.2140
Crawl file group
3,043.93 371.65 8.19 15.0840 15.8720
Reference: http://blogs.msdn.com/enterprisesearch/archive/2008/05/19/sql-monitoring-and-i-o.aspx
![Page 50: Large Scale SQL Considerations for SharePoint Deployments](https://reader035.vdocuments.us/reader035/viewer/2022062617/54c6a5c44a7959cc268b4579/html5/thumbnails/50.jpg)
Applying the Newest Learnings
Add more processor to the backend: 4 cores to 8 coresAdd more RAM: 16GB to 32GBRun profile sync on our terms! Run the jobs as little as possible. Once a week or once a month.Separate SSP SQL instance from Search SQL instance.