Transcript ~10 Years SharePoint Veteran SharePoint Product Architect, Evangelist & Strategy @ Quest Twitter: @joeloleson Email: [email protected].

~10 Years SharePoint Veteran
SharePoint Product Architect,
Evangelist & Strategy @ Quest
Twitter: @joeloleson
Email: [email protected]
Proper SQL and Storage design is critical to
overall SharePoint health!
Farm
Servers
Web Front End, APP, SQL
Web Applications
Central Admin, SPWeb Services, FastAdminWebServices, Content
Databases
Content, Config, Service Apps
Site Collections
Internet, Intranet Portal, Wikis, Blogs, Team, etc…
Sites
Wikis, Blogs, Team, Doc, Records, Search ctr, Doc Center, Group work site
Lists
Documents, Contacts, Pages, Events, Discussions, Surveys, etc…
Items
Files, Calendar items, Contacts, Images, links, Videos, Images, etc…
SharePoint 2007
SharePoint 2010
Items per List view
2000
5000
Docs per Library
5 Million
10 Million
DB Sizing
100 GB
200 GB
Doc Editors
1
99 Max
Content Db per Web App
100
300
Indexed Items
50 Million
100 Per Search Application
Site Collections per Web App
50,000
500,000
Farm
Web App
• Config
• Servers
• Web Apps
• Solutions
• Global Config
• Subscription
Settings (Hosters)
• Content 1..2
• Site Collections
• Sites
• Lists
• Pages
• Documents
• DWPs
Service Apps
•
•
•
•
•
•
Excel Services
Profiles
Access
Visio
Metadata
Search
• Properties
• Catalog
• Web Analytics
WSS_Content_GUID
Admin_Content
Crawlstore
WSS_Content_GUID1…
Application Registry Service
SearchDB
WSS_Content_GUID2…
Secure_Store_Service
PropertyStore
StateService
SharePointFoundation_Search
Web Analytics Web Service
• WSS_Usage
• Reporting_DB
• Staging DB
ProfileDB
SocialDB
ManagedMetadata
SyncDB
Sync
BDC_Service_DB
Word Conversion Service
Application
Performance_Point
Content
Search_Service_Application
Services
Farm
Config
I don’t know what to do, so I’ll turn it all on…
Nosé que hacer, mejor prendo todo…
Config
Database
Sites
(Sp_sites)
Servers
Web Apps
Content
Database
Sites
Webs
Doc
(sp_site)
(sp_web)
Stream
RBS Provider
* R2 EE
Search Admin
Search
Properties1
Search Crawl1
Search
Properties2
Search Crawl2
Lotus Notes
Connector
(optional)
Profile Service
App
Synchronization
(AD to SP & SP
to AD)
State
Social Tagging
Managed
Metadata
Service
Proxy Group
default
http://my
custom
http://team
CentralAdmin
Finance
Globally Consumable
Business Data
Connectivity*
Managed Metadata
Service
Search
User Profiles
Secure Store
(Single Sign-On)
Web Analytics*
S
h
a
r
e
d
Local Only
Access Services
Excel Services
Visio Services
Word Auto
Conversion
Word Viewing
Word
Lotus Notes
Connector
Office Web Apps
Excel
PPT
PerformancePoint
OneNote
SQL Health = SharePoint Health!
Slow
App
Slow
SQL
Database Disk I/O Demand
Most Demand (Write)
Medium Demand
Low Demand
Property
Config
*Content..
+SSP (2007)
Model
Master
Service Apps
Crawl
Temp
Transaction
logs
Usage & Health
Data Collection
Service Apps
Web Analytics
* Except during backup and Indexing + Except during Profile Import
2.5TB
2.5TB
SCALE OUT 
2.5TB
2.5TB
2.5TB
SCALE UP 
2.5TB
Scale Out
Scale Up
Advantages
More Performance
Less to Manage
More Flexibility
Cheaper per GHz
Disadvantages
More Expensive
System Design Flaws become Critical
More to Manage
More Single Points of Failure
How to design a 5TB
SharePoint SQL
Deployment
1TB
1TB
1TB
1TB
1TB
1TB
1TB
1TB
1TB
1TB
Schematic Diagram,
not to be use as a
recommendation for Server
Counts
Large Enterprise
Up
to 500k
users (~500 RPS)
Common
Enterprises
10-20
of data
10-50kTB
users
(~50 RPS)
1-2 TB of data
WFE
App Servers
Limited deployments
minimum services
up to 5000 users (~5 RPS)
50-100 GB of data
SQL
Demos and Dev Boxes
Single
Server
Small
Farm
Medium
Farm
Large
Farm



High-Availability
across Hosts
All components
virtualized
Uses only two
Windows Ent
Edition Licenses
Redundant Switches
Redundant Web/Application Servers
Active/Passive SQL w/ Redundant HBA’s
Redundant SAN Fabric
RAID 1 Storage
Redundant Power Supplies
Kickoff
a Crawl
Attach
Databases
to
SharePoint
Bring
Databases
Online
Restore
SQL
Mirroring
Update DNS/WINS
SQL
Enterprise
• Asynchronous Mirroring with compression (Multithreaded/Full)
• Transparent Database Encryption
• Resource Governor
• Table Partitioning (Web Analytics Recommended)
• Data Compression (2010 Property Db Search)
• Mirrored Backups
• Database Snapshots
SQL Standard
• Synchronous Mirroring (Yes (Single Threaded, Safety Full Only)
• 2 Node Clustering
• Log Shipping with compression
• Restore Compressed Backup
SQL Express
• FREE!
• Both SharePoint Foundation 2010 and SharePoint 2010 use…
• Up to 4 GB Max Storage
• Use as a Witness in Mirroring
• New Management Studio Express
• Advanced Services (FTS & Reporting Services)
http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx
SQL Enterprise
2008 R2
SQL Standard
2008 R2
SQL Express
2008 R2
• PowerPivot for SharePoint to support the hosting and management of PowerPivot applications in
SharePoint
• Application and Multi-Server Management for enrolling, gaining insights and managing up to 25 instances
• Master Data Services for data consistency across heterogeneous systems
• Data Compression now enabled with UCS-2 Unicode support
• High-scale complex event processing with SQL Server StreamInsight™
• Backup Compression to reduce data backups and reduce backup time
• Instance for Application and Multi-Server Management capabilities
• High-scale complex event processing with SQL Server StreamInsight™
• FREE!
• From 4 to Now 10 GB!
• Mirroring Witness
• (Limited to 1GB of RAM and 1 CPU)
• http://blogs.msdn.com/b/sqlexpress/archive/2010/04/21/database-size-limit-increased-to-10gb-in-sqlserver-2008-r2-express.aspx
http://www.microsoft.com/sqlserver/2008/en/us/R2-editions.aspx
SQL Reporting Built in for
Proactive Reporting
www.microsoft.com/teched
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn
Sign up for Tech·Ed 2011 and save $500
starting June 8 – June 31st
http://northamerica.msteched.com/registration
You can also register at the
North America 2011 kiosk located at registration
Join us in Atlanta next year