Platinum
Gold
AU ST R A L I A
SHAREPOINT CONFERENCE
MARCH 8+9 2011
Planning your SQL Server Infrastructure for a SharePoint 2010 Solution
Victor Isakov
MCA | MCM | MCT | MVP
Database Architect / Trainer SQL Server Solutions
Abstract
SharePoint is becoming more ubiquitous in the market as a critical core business application. Consequently it is more important than ever for DBAs to be cognisant of SharePoint's architecture and how to best provision, configure and manage the SQL Server infrastructure . In this session Victor Isakov (MCA, MCM, MCT, MVP) will explore the SharePoint 2010 architecture and considerations that every DBA should know, including capacity planning, performance management, configuration, disaster recovery and high availability.
2010 Victor Isakov www.sqlserversolutions.com.au
Victor Isakov
Victor Isakov is a Database Architect / Trainer who provides consulting and training services to various organizations in the public, private and NGO sectors globally, and been involved in different capacities at various international events and conferences. Victor specializes in:
SQL Server training Performance tuning and optimization Health-checks / Risk Assessments / review of SQL Server infrastructure Architecting / re-factoring database solutions Assessing the effectiveness of your outsourced services / licensing Consolidating / virtualizing / upgrading SQL Server
Blog: Email: Website:
2010 Victor Isakov
www.victorisakov.com victor@sqlserversolutions.com.au www.sqlserversolutions.com.au
www.sqlserversolutions.com.au
Rationale
Industry seems to be focused more on the development of the SharePoint solutions Little focus on infrastructure
SQL Server Storage Capacity / Performance Planning Disaster Recovery High-Availability
Management does not even know SharePoint runs on SQL Server SharePoint Gurus have head start on SQL Server DBAs Little real guidance from various vendors
Myths such as the 100GB content database limit
Lots of experts regurgitating stuff with no deeper understanding
2010 Victor Isakov
www.sqlserversolutions.com.au
SharePoint 2010
Microsoft has re-designed the architecture and database design in SharePoint 2010
Focus on scalability Focus on performance Focus on reliability / robustness
Which means:
BEGIN TRAN Everything I learnt about SharePoint 2007 Everything I was told about SharePoint 2007 ROLLBACK TRAN
Industry will potentially have to re-learn best practices, configuration, deployment, etc
Keep an eye out for Microsoft Whitepapers
2010 Victor Isakov
www.sqlserversolutions.com.au
Outcomes
More awareness of SharePoints architecture and internals at a very high level
Represents starting point for you
Recognise the importance of capacity planning correctly Help design the right solution from the start
Engage your SharePoint IT pros early in the project
Use slide deck as reference material
We are not going to go through every single slide in detail... Phew!
2010 Victor Isakov
www.sqlserversolutions.com.au
Agenda
Basic SharePoint 2010 Architecture Deployment Models SharePoint 2010 Foundation Databases Capacity Planning
Software Boundaries SQL Server Configuration Content Database(s) Remote BLOB Storage Performance Planning
Disaster Recovery High Availability
This is really potentially a one-day workshop
2010 Victor Isakov www.sqlserversolutions.com.au
Basic SharePoint 2010 Architecture
Farm
Tiers WFE Tier Apps Tier SQL Tier
Site Collection SharePoint Service Applications
2010 Victor Isakov
www.sqlserversolutions.com.au
SharePoint Farm - Tiers
2010 Victor Isakov
www.sqlserversolutions.com.au
Site Collection
A SharePoint site collection is a hierarchical set of sites that can be managed together.
Sites within a site collection have common features, such as shared permissions, galleries for templates, content types, and Web Parts, and they often share a common navigation. A site collection contains a single top level site, and any number of sub-sites organized in a hierarchy. A sub-site is a single SharePoint site within a site collection.
Main Enterprise Site IT Site Collection Whitepapers Site KPIs Site HR Site Collection Photos Site Booze Site
For DBAs
Site collection must reside within a single content database Can move site collections between content databases
Party Site
2010 Victor Isakov www.sqlserversolutions.com.au
SharePoint Service Applications
SharePoint Service Applications (SSAs) represent services that represent some functionality Can run on Web, App or SQL tier At least 21 different SSAs Impact on SQL Server:
SSA SharePoint Foundation Service Central Admin Service Logging Service SharePoint Search Service Visio Service CPU Medium Minimal Medium High Minimal IOPS High Minimal High High Minimal Storage High Minimal High High Minimal
2010 Victor Isakov
www.sqlserversolutions.com.au
SharePoint Service Applications
SSA Access Service User Profile Service Managed Metadata Service Web Analytics Service CPU Minimal High Minimal High IOPS Minimal High Minimal High Storage Minimal Medium Medium High
InfoPath Forms Service
Word Conversion Service PerformancePoint Service Application Project Service PowerPivot
Minimal
Minimal Minimal High Medium
Minimal
Minimal Minimal High Medium
Minimal
Minimal Minimal Medium High
2010 Victor Isakov
www.sqlserversolutions.com.au
Deployment Models
Single Server Deployment Small Farm Deployment Medium Farm Deployment Large Farm Deployment
2010 Victor Isakov
www.sqlserversolutions.com.au
Single Server Deployment
One server running everything Small shops Development / Functional testing environments
2010 Victor Isakov
www.sqlserversolutions.com.au
Small Farm Deployment
Separate SQL Server instance
2010 Victor Isakov
www.sqlserversolutions.com.au
Medium Farm Deployment
Separate 3 tiers Potentially multiple SQL Server instances
Separate databases based on activity, importance, etc
2010 Victor Isakov
www.sqlserversolutions.com.au
Large Farm Deployment
You really want a great resume!
2010 Victor Isakov
www.sqlserversolutions.com.au
SharePoint Foundation 2010 Databases
Core Search Service Application User Profile Service Application Web Analytics Service Application
2010 Victor Isakov
www.sqlserversolutions.com.au
Core Databases
Configuration
Stores data about SharePoint databases, Internet Information Services (IIS) Web sites, Web applications, trusted solutions, Web Part packages, site templates, and Web application and farm settings specific to SharePoint 2010 Products, such as default quota settings and blocked file types.
Central Administration Content
A configuration database that stores all site content, including site documents or files in document libraries, list data, and Web Part properties, in addition to user names and rights for the Central Administration site collection.
Content
Store all content for a site collection, including site documents or files in document libraries, list data, Web Part properties, audit logs, and sandboxed solutions, in addition to user names and rights. All the data for a specific site collection resides in one content database on only one server. A content database can be associated with more than one site collection. One or more may be provisioned
2010 Victor Isakov
www.sqlserversolutions.com.au
Core Databases
Usage and Health Data Collection
Stores health monitoring and usage data temporarily, and can be used for reporting and diagnostics
Business Data Connectivity
Stores external content types and related objects
Subscription Settings
Stores features and settings for hosted customers
2010 Victor Isakov
www.sqlserversolutions.com.au
Search Service Application Databases
Search administration
Hosts the Search service application configuration and access control list (ACL), and best bets for the crawl component This database is accessed for every user and administrative action
Crawl
Stores the state of the crawled data and the crawl history One or more may be provisioned
Properties
Stores information that is associated with the crawled data, including properties, history, and crawl queues One or more may be provisioned
2010 Victor Isakov
www.sqlserversolutions.com.au
User Profile Service Application Databases
Profile
Stores and manages users and associated information Also stores information about a user's social network in addition to memberships in distribution lists and sites
Synchronization
Stores configuration and staging data for use when profile data is being synchronized with directory services such as Active Directory
Social tagging
Stores social tags and notes created by users, along with their respective URLs
2010 Victor Isakov
www.sqlserversolutions.com.au
Web Analytics Service Application Databases
Staging
Temporarily stores un-aggregated fact data, asset metadata, and queued batch data for the Web Analytics service application
Reporting
Stores aggregated standard report tables, fact data aggregated by groups of sites, date and asset metadata, and diagnostics information for the Web Analytics service application
Secure Store
Stores and maps credentials, such as account names and passwords
State
Stores temporary state information for InfoPath Forms Services, the chart Web Part, and Visio Services
Managed Metadata
Stores managed metadata and syndicated content types
2010 Victor Isakov
www.sqlserversolutions.com.au
Configuration Database Characteristics
Size / Growth 1GB or less Transaction log files are likely to become large. Microsoft recommends that you back up the transaction log for the configuration database regularly to force truncation. Alternatively, if you are not mirroring system, change the database to run in SIMPLE recovery mode. The database must grow larger because only one Configuration database is supported per farm. Significant growth is unlikely. Microsoft recommends that you switch the configuration database to the simple recovery model to restrict growth of the log file.
I/O Pattern Read-intensive Scaling Strategy Recovery Model Mirroring Support Must scale up. FULL
Supports mirroring within farm Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery
2010 Victor Isakov
www.sqlserversolutions.com.au
Central Administration Content Database Characteristics
Size / Growth Scaling Strategy Recovery Model Mirroring Support 1GB or less I/O Pattern Varies Must scale up. FULL Supports mirroring within farm Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery The database must grow larger because only one Central Administration database is supported per farm. Significant growth is unlikely.
2010 Victor Isakov
www.sqlserversolutions.com.au
Content Database(s) Characteristics
Size / Growth
Microsoft strongly recommends limiting the size of content databases to 200 GB to help ensure system performance. Content database sizes up to 1TB are supported only for large, single-site repositories and archives with noncollaborative I/O and usage patterns, such as Records Centers. Larger database sizes are supported for these scenarios because their I/O patterns and typical data structure formats have been designed for, and tested at, larger scales. The database must be able to grow larger as needed. However, you can create additional site collections that are associated with a Web application and associate the new site collection with a different content database. Also, if a content database is associated with multiple site collections, you can move a site collection to another database.
I/O Pattern Varies by usage Scaling Strategy Must scale up.
Recovery Model Mirroring Support
2010 Victor Isakov
FULL Supports mirroring within farm Supports asynchronous mirroring / log-shipping to another farm for DR
www.sqlserversolutions.com.au
Usage and Health Data Collection Database Characteristics
Size / Growth Up to 1TB Database size depends on the retention factor, number of items enabled for logging and external monitoring, how many Web applications are running in the environment, how many users are currently working, and which features are enabled. The database must grow larger, because only one logging database is supported per farm.
I/O Pattern Very write-heavy Scaling Strategy Recovery Model Mirroring Support Must scale up. SIMPLE Supports mirroring within farm Supports asynchronous mirroring or log-shipping to another farm for disaster recovery Microsoft does not recommend that you do. It is easily re-created in the event of a failure.
Location
As the database is very active it should be put on a separate disk or spindle if possible
www.sqlserversolutions.com.au
2010 Victor Isakov
Business Data Connectivity Database Characteristics
Size / Growth Scaling Strategy Recovery Model Mirroring Support 1GB or less Size is determined by the number of connections I/O Pattern Very read-heavy Must scale up. FULL Supports mirroring within farm. Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery. The database must grow larger, because only one BDC database is supported per farm. (Significant growth is unlikely.)
2010 Victor Isakov
www.sqlserversolutions.com.au
Subscription Settings Database Characteristics
Size / Growth Scaling Strategy 1GB or less Size is determined by the number of tenants, farms, and features supported You can scale out by creating additional instances of the service application. However, the decision to create a separate service application is likely to be based on business, rather than scale, requirements. I/O Pattern Read-heavy Scale up.
Recovery Model Mirroring Support
FULL Supports mirroring within farm. Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery.
2010 Victor Isakov
www.sqlserversolutions.com.au
Search Service Application: Search Administration Database Characteristics
Size / Growth 1GB 100GB
The factors that influence growth include the number of best bets, the number of content sources and crawl rules, the security descriptions for the corpus, and the amount of traffic. The decision to create a separate service application is likely to be based on business, rather than scale, requirements.
I/O Pattern Approximately equal read/write ratio Scaling Strategy Recovery Model Mirroring Support Location Scale up. Can scale out. SIMPLE Supports mirroring within farm. Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery. The Administration database should fit into RAM on the server so that the server can handle the end-user query load most efficiently. Because of this requirement, it is usually best not to have the Administration and Crawl databases located on the same server.
www.sqlserversolutions.com.au
2010 Victor Isakov
Search Service Application: Crawl Database(s) Characteristics
Size / Growth I/O Pattern Scaling Strategy 100GB 1TB Generally starts around 100GB and grows over time, without shrinking. The factors that influence growth are the number of items in the corpus. Associate another Crawl database with the service application instance. Multiple Crawl databases can be placed on the same server, if the server can handle the I/O per second required.
Read-heavy (Read/write ratio is 3:1) Scale out.
Recovery Model Mirroring Support Location
SIMPLE Supports mirroring within farm. Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery. The Crawl database is very I/O intensive, and causes the SQL Server cache to be flushed regularly. In large-scale environments, Microsoft recommends that you locate this database on a server that does not contain the Property database or other databases involved in end-user tasks.
www.sqlserversolutions.com.au
2010 Victor Isakov
Search Service Application: Properties Database(s) Characteristics
Size / Growth Scaling Strategy 1TB or more Factors that influence growth are the number of managed properties and the number of documents. Associate another Property database with the service application instance. Microsoft recommends that you locate each additional Property database on a different server. I/O Pattern Write-heavy (Read/write ratio is 1:2) Scale out.
Recovery Model Mirroring Support Location
SIMPLE Supports mirroring within farm. Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery. At least one-third of the Property database should fit into RAM on the server. In large-scale environments, Microsoft recommends that you put this database on its own server to achieve faster query results.
2010 Victor Isakov
www.sqlserversolutions.com.au
User Profile Service Application: Profile Database Characteristics
Size / Growth 100GB 1TB Growth factors include additional users and the use of news feeds. News feeds grow with user activities. The default is to maintain the last two weeks of activity, after which a timer job deletes the news feed items older than two weeks. Can scale out by creating additional instances of the service application. The decision to create a separate service application is likely to be based on business, rather than scale, requirements.
I/O Pattern Read-heavy Scaling Strategy Scale up. Can scale out.
Recovery Model
Mirroring Support
SIMPLE
Supports mirroring within farm.
2010 Victor Isakov
www.sqlserversolutions.com.au
User Profile Service Application: Synchronization Database Characteristics
Size / Growth Scaling Strategy 100GB 1TB Growth factors include the number of users and groups, and the ratio of users to groups. Can scale out by creating additional instances of the service application. The decision to create a separate service application is likely to be based on business, rather than scale, requirements. I/O Pattern Approximately equal read/write ratio Scale up. Can scale out.
Recovery Model Mirroring Support
SIMPLE Does not support mirroring within farm. Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery.
2010 Victor Isakov
www.sqlserversolutions.com.au
User Profile Service Application: Social Tagging Database Characteristics
Size / Growth Scaling Strategy 1GB 1TB Growth factors include the number of tags, ratings and notes that have been created and used. Can scale out by creating additional instances of the service application. The decision to create a separate service application is likely to be based on business, rather than scale, requirements. I/O Pattern Read-heavy (Read/write ratio is approximately 50:1) Scale up. Can scale out.
Recovery Model Mirroring Support
SIMPLE Does not support mirroring within farm. Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery.
2010 Victor Isakov
www.sqlserversolutions.com.au
Web Analytics Service Application: Staging Database Characteristics
Size / Growth Scaling Strategy Recovery Model Mirroring Support Up to 100GB Size varies based on the number of reports being generated Associate another Web Analytics Staging database with the service application instance. I/O Pattern Varies Scale out FULL Does not support mirroring within farm. Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery.
2010 Victor Isakov
www.sqlserversolutions.com.au
Web Analytics Service Application: Reporting Database Characteristics
Size / Growth Scaling Strategy 1TB or more Size varies based on retention policy. I/O Pattern Varies Scale up. Can Scale out. You can scale out by creating additional instances of the service application. The decision to create a separate service application is likely to be based on business, rather than scale, requirements.
Recovery Model Mirroring Support
FULL Supports mirroring within farm. Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery.
2010 Victor Isakov
www.sqlserversolutions.com.au
Web Analytics Service Application: Secure Store Database Characteristics
Size / Growth Up to 100GB Size and growth are determined by the number of target applications, number of credential fields per target application, and the number of users stored in each target application. If auditing is turned on, the number of read/write operations performed against a given target application also affects size. You can scale out by creating additional instances of the service application, however, the decision to create a separate service application is likely to be based on business, rather than scale, requirements.
I/O Pattern Scaling Strategy
Equal read/write ratio Scale up. Can Scale out.
Recovery Model
FULL
Mirroring Support
Location
Supports mirroring within farm. Supports asynchronous mirroring or log-shipping to another farm for disaster recovery.
For secure credential storage, Microsoft recommends that the secure store database be hosted on a separate database instance or database server that has access limited to one administrator.
www.sqlserversolutions.com.au
2010 Victor Isakov
Web Analytics Service Application: State Database Characteristics
Size / Growth Scaling Strategy Recovery Model Mirroring Support 100GB 1TB Size is determined by the use of InfoPath Forms services and Visio Services. I/O Pattern Varies Scale out FULL Supports mirroring within farm. Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery.
2010 Victor Isakov
www.sqlserversolutions.com.au
Web Analytics Service Application: Managed Metadata Database Characteristics
Size / Growth Scaling Strategy Up to 100GB Growth factors include the amount of managed metadata. You can scale out by creating additional instances of the service application. The decision to create a separate service application is likely to be based on business, rather than scale, requirements. I/O Pattern Read-heavy. (Read/write ratio is approximately 1,000:1) Scale up. Can scale out.
Recovery Model Mirroring Support
FULL Supports mirroring within farm. Does not support asynchronous mirroring or log-shipping to another farm for disaster recovery.
2010 Victor Isakov
www.sqlserversolutions.com.au
Capacity Planning
Software Boundaries SQL Server Configuration
Processor Memory Storage
Content Database(s)
Purpose Considerations Remote BLOB Storage
Performance
2010 Victor Isakov
www.sqlserversolutions.com.au
Software Boundaries
Boundaries
Absolute limit Example: 2GB document size limit
Thresholds
A default value that cannot be exceeded unless the value is modified Exceeding threshold may impact performance Example: Document size limit of 50MB by default
Supported Limit
Defined by testing and represent a known limitation of the product Exceeding supported limit may cause unexpected results, significant performance degradation or other detrimental effects Example: Support 500,000 site collections per web app.
Whitepaper: SharePoint Server 2010 Capacity Management: Software Boundaries and Limitations
2010 Victor Isakov www.sqlserversolutions.com.au
Microsofts Recommendations
SharePoint 2007 Items per view Documents per library Database size Simultaneous Doc Editors 2000 5 million 100GB SharePoint 2010 5000 10 million 200GB (Up to 1TB for workloads) 10 (max at 99)
1 (no Multi user editing of Word, Excel, PPT) Column 2000 per doc lib, 4096 per list New Row Wrapping with (rowOrdinal) (8,000 bytes) Content Databases per Web 100 300 App App Pools per web server 8 10 Indexed (Crawl Count) 50 Million items per SSP
Site Collections per Web App 50,000
2010 Victor Isakov
100 Million per Search Application 500,000
www.sqlserversolutions.com.au
SQL Server Configuration
Dedicated SQL Server
Assume lots of open connections, concurrent users threads Assume SharePoint is being used for document management
Configuration Options
max degree of parallelism
2010 Victor Isakov
www.sqlserversolutions.com.au
tempdb System Database
SharePoint 2010 heavily utilises the tempdb system database Dont forget that tempdb is used for other purposes, some of which are I/O intensive
DBCC CHECKDB Index Rebuilds
Best practices:
Multiple data files On separate LUN Equal in size Auto-growth in MB (decent size) Log file on separate LUN
2010 Victor Isakov
www.sqlserversolutions.com.au
Processor
Can only run on 64-bit SQL Server SharePoint consumes a lot of connections / SPIDs / threads to SQL Server Ideally want as many logical cores as possible
Socket Core Hyper-threading
Each scheduler consumes memory
Architecture Memory Consumption
x86
x64 IA64
512KB
2MB 4MB
2010 Victor Isakov
www.sqlserversolutions.com.au
Processor
Each Worker Thread consumes memory:
Number of CPUs <= 4 8 32-bit 256 288 64-bit 512 576
16
32
352
480
704
960
IMPORTANT TO CAPACITY PLAN MEMORY CORRECTLY IF YOU HAVE A LARGE NUMBER OF CPUS Rule of thumb: 4-6GB per core Example: 4 x Xeon E5540: 128 -192GB!
2010 Victor Isakov
www.sqlserversolutions.com.au
sp_who - Virgin SharePoint Site
2010 Victor Isakov
www.sqlserversolutions.com.au
Memory
Your SharePoint environment will almost certainly will be I/O bound, not compute bound Memory is critical to performance
Impact of BLOBs on Buffer Pool Impact of BLOBs on Windows
Alleviate disk sub-system problems Dont forget, more logical cores means more memory!
2010 Victor Isakov
www.sqlserversolutions.com.au
Task Manager Virgin SharePoint Site
2010 Victor Isakov
www.sqlserversolutions.com.au
Storage
Separate I/O intensive databases
Content database(s) Search database(s)
Especially important for Enterprise Content Management (ECM) environments
BLOBs Storage Versioning Recycle Bin policy
2010 Victor Isakov
www.sqlserversolutions.com.au
Content is King!
Ultimately most organisations are going to use SharePoint to store content
Various lists Document Libraries BLOBs
All content is stored in one or more content databases
All eggs are in one basket Disaster recovery is critical Availability is important Performance of content database(s) is key to the user experience Capacity planning Performance planning
2010 Victor Isakov
www.sqlserversolutions.com.au
Content Database(s)
Purpose Considerations Remote BLOB Storage
SharePoint Web FE
SQL Server
Content Database
Config Database
2010 Victor Isakov
www.sqlserversolutions.com.au
ContentDB Purpose
Used to store all documents that reside in Document Library
Default is to store as VARBINARY(MAX)
Used to store all lists Site collection has to reside within a single ContentDB Can create multiple ContentDBs
SharePoint will round-robin the creation of site collections between multiple ContentDBs
Can move site collections between ContentDBs
Only if on the same SQL Server
2010 Victor Isakov
www.sqlserversolutions.com.au
ContentDB Provisioning
Different approaches:
Create ContentDBs as required Define ContentDB size limit Create new ContentDB when limit reached Will need to potentially move site collections when you hit the size limit Create multiple ContentDBs Let SharePoint round-robin the creation of site collections Create ContentDBs as required and control which site collections go in them No substitute for knowledge about how users will use the solution
2010 Victor Isakov
www.sqlserversolutions.com.au
ContentDB Considerations
Serialization of the transaction log for document inserts and updates
My major concern for ECM solutions
AllDocs set of tables can contain a large volume of records
Wide records
Need to take into account:
Versioning Major Minor Comments Recycle Bin
Dont forget
SharePoint Foundation 2010 uses SQL Server 2008 Express which has a maximum size of 4 GB per database SQL Server 2008 R2 Express supports databases up to 10 GB in size
2010 Victor Isakov www.sqlserversolutions.com.au
ContentDB Capacity Planning
Microsoft: Use the following formula to estimate the size of your content databases: Database size = ((D V) S) + (10 KB (L + (V D)))
The value of 10 KB in the formula is a constant that roughly estimates the amount of metadata required by SharePoint Server 2010. If your system requires significant use of metadata, you may want to increase this constant.
Example: Number of Docs (D)
Average Size of Docs (S) List Items (L) Number of non-current versions (V)
200,000 250KB 600,000 2
(100,000 users)
(Assume max 10 allowed)
Database size = (((200,000 x 2)) 250) + ((10 KB (600,000 + (200,000 x 2))) = 110,000,000 KB or 105 GB
2010 Victor Isakov
www.sqlserversolutions.com.au
Content Tables - AllDocs
The AllDocs Table stores data for all documents in the content database Very wide table:
Does not store the actual BLOB Watch out for [MetaInfo] column
2010 Victor Isakov
www.sqlserversolutions.com.au
Content Tables - AllDocStreams
The AllDocStreams Table stores the document stream and related data for documents with content streams stored in the content database [Content] stores the BLOB [RbsId] used by RBS
2010 Victor Isakov
www.sqlserversolutions.com.au
AllDocs Disk Usage (Major & Minor Versioning Enabled)
Virgin:
Insert 1024KB Document:
Check-out, modify, check-in as minor version, no comments:
2010 Victor Isakov
www.sqlserversolutions.com.au
AllDocs Disk Usage (Major & Minor Versioning Enabled)
Check-out, modify, check-in as major version, no comments:
Check-out, modify, check-in as major version, comments:
Then I forgot what I did:
2010 Victor Isakov
www.sqlserversolutions.com.au
AllDocs Disk Usage (Major & Minor Versioning Enabled)
SharePoints Version History of the mess I created:
2010 Victor Isakov
www.sqlserversolutions.com.au
What Are You Thinking Right Now?
These tables are going to be potentially massive...
We could have 100s of millions of records there... I certainly hope SharePoint isnt doing table scans all the time. I wonder if the SharePoint Development team have indexed the tables correctly? Can I create my own indexes on the tables? How badly can these tables get fragmented? What about rebuilding the clustered index on these tables? Do they have a clustered index? What about LOB compaction? If these tables take up the majority of space in my 200GB content database how much free space to I need to rebuild the clustered index? Have they used partitioning? Can I implement partitioning?
Crickey...
2010 Victor Isakov www.sqlserversolutions.com.au
Remote BLOB Storage
RBS is the promised land Do not use EBS
Deprecated / Dead technology
Store BLOBS outside the ContentDB
Transaction logs no longer hammered Buffer Pool no longer hammered Content database(s) size can be substantially reduced in size No longer have to store content in Tier 1 storage
Two Options
Local FILESTREAM External Provider (Vendor Specific) Cloud SAN NAS
2010 Victor Isakov
www.sqlserversolutions.com.au
RBS Architecture
SharePoint Web FE
SQL Server
Content Database
Config Database
BLOB Store X
BLOB Store Y
BLOB Store Z
Downloadable, add-in component Store all BLOBs external to ContentDB when Provider is active
2010 Victor Isakov www.sqlserversolutions.com.au
RBS Workflow Save Document
SharePoint Web FE 7. Back to User 1. Save Request
2. Enforce Biz Logic
SharePoint Object Model
RBS Client Library
Relational Access
SQL Server
3. Save BLOB 5. Return BLOB Id
BLOB Store Provider Library
6. Save Metadata & BLOB ID
Content Database Config Database
4. Write BLOB
BLOB Store
2010 Victor Isakov
www.sqlserversolutions.com.au
RBS Workflow Read Document
SharePoint Web FE 7. BLOB Data to User 1. Open Document
2. Enforce Biz Logic
SharePoint Object Model
RBS Client Library
Relational Access
SQL Server
4. Read BLOB 6. Return BLOB
BLOB Store Provider Library
3. Get BLOB Id
Content Database Config Database
5. Read BLOB
BLOB Store
2010 Victor Isakov
www.sqlserversolutions.com.au
FILESTREAM Performance Considerations
Yes, databases are not necessarily the best place to store BLOBs But it does depend on the size of the BLOB
2010 Victor Isakov
www.sqlserversolutions.com.au
Potential RBS Solution
SQL Server
Content Database
Config Database
Tier 3 Store
Tier 2 Store
Tier 1 Store
After 1 year
After 3 months
Tiered Storage Approach
Move older content to cheaper storage as required
2010 Victor Isakov www.sqlserversolutions.com.au
RBS Vendor Considerations / Questions
The technology is still in its infancy
Version 1.0 product Expect improvements Functionality Performance
Some questions you should be asking:
When do you deleted BLOBs? How do you handle orphaned BLOBs? If I want to move BLOBs from one provider to the other how does your solution do it? Directly? Through a content database? Do you support storing BLOBs through an RBS Provider based on: Size? Type? Do you support tiered archiving?
2010 Victor Isakov
www.sqlserversolutions.com.au
Performance Capacity Planning
Its all about Requests Per Second (RPS) SharePoint IT Pros always concerned about whether SQL Server will handle the expected load
Tell them: Go away!!! Cool down... Go back to them and work together with them!
Idea is learn how much you can scale out upper tiers before SQL Server becomes the bottleneck
Then can scale up / scale out SQL Server
Set realistic expectations for all stakeholders
Too many moving pieces otherwise
2010 Victor Isakov
www.sqlserversolutions.com.au
Performance Planning Methodology
Define Dataset
ContentDBs Site Collections Document Libraries / Site Collection Populate document libraries
Define anticipated workload
Number of Users Expected mix of operations
Define target RPS (Optional) Use Visual Studio web tests
Run against 1 WFE Record metrics Scale out more WFEs Repeat test run
Determine at what point you are either:
Maxing out SQL Server Meeting target RPS
2010 Victor Isakov www.sqlserversolutions.com.au
Impact on SQL Server 1 WFE
2010 Victor Isakov
www.sqlserversolutions.com.au
Impact on SQL Server 2 WFEs
2010 Victor Isakov
www.sqlserversolutions.com.au
Impact on SQL Server 3 WFEs
2010 Victor Isakov
www.sqlserversolutions.com.au
RPS based on WFEs
2010 Victor Isakov
www.sqlserversolutions.com.au
Disaster Recovery
SharePoint has its own backup process But
2010 Victor Isakov
www.sqlserversolutions.com.au
Disaster Recovery Considerations
Assume content is most important Sharepoint 2010 supports backup size < 1 1.5 TB Other products out there
Data Protection Manager Support backup size up to 80TB
Usual suspects
RTO RPO Potential to quiesce SharePoint environment / databases Crash consistent backups VSS Backups
2010 Victor Isakov
www.sqlserversolutions.com.au
High-Availability
There might be no native solution with SQL Servers technology stack
Might need to engage hardware vendors / ISVs What are you going to do with RBS? Test! Test! TEST!
Clustering is easy SharePoint 2010 now supports Database Mirroring natively
But
2010 Victor Isakov www.sqlserversolutions.com.au
Database Mirroring Considerations
Not all the databases support database mirroring within the SharePoint Farm Only a few database support asynchronous database (or log-shipping) mirroring outside farm Some database can be rebuilt or dont require database mirroring
The search database(s) can be rebuilt How long will that take? Can the business afford that downtime?
Perhaps we can get away with just mirroring the content databases. Most SharePoint databases can be mirrored through GUI
Rest done through PowerShell
2010 Victor Isakov
www.sqlserversolutions.com.au
Database Mirroring Considerations
You might not want to have automatic failover
Other dependencies: WFE tier Apps Tier
Remember the difference(s) between Standard Edition and Enterprise Edition
Yes we know Victor, the price!!! No... I mean the other difference(s) Asynchronous versus synchronous mirroring Number of redo threads
So capacity plan for database mirroring!
2010 Victor Isakov
www.sqlserversolutions.com.au
Database Mirroring Capacity Planning
More mirrored databases consume more threads Need more logical processors
Role Thread Function Event processing Log send Mirror Database mirroring communication Number of Threads For Function 1 per instance 1 per mirrored database 1 per mirrored database 1 per instance
Principal Database mirroring communication
Event processing
Log hardening Redo manager Redo threads
1 per mirrored database
1 per mirrored database 1 per mirrored database FLOOR ((# logical procs + 3) / 4)
2010 Victor Isakov
www.sqlserversolutions.com.au
Summary
Important to understand SharePoint architecture / topology Important to capacity plan SharePoint from the start
Do not let it get out of control
There are a large number of excellent whitepapers and other resources being released by Microsoft Engage with management, SharePoint IT Pros and other stakeholders Critical to test your DR and HA strategy
As I say: It always works on the whiteboard...
Its just another database solution
Leverage your knowledge and experience! Dont be afraid to poke around in the database in a development environment to learn more about the internals of how SharePoint works
2010 Victor Isakov
www.sqlserversolutions.com.au
Questions?
Email me at victor@sqlserversolutions.com.au I would like to you to gather some basic statistics about your SharePoint 2010 sites, collate them, and report those statistics back to the community.
2010 Victor Isakov
www.sqlserversolutions.com.au
Gold Sponsors
Silver Sponsors
Bronze Sponsors
