tuning sql server 2012 for sharepoint 2013 jump start n.
Skip this Video
Loading SlideShow in 5 Seconds..
Tuning SQL Server 2012 for SharePoint 2013 Jump Start PowerPoint Presentation
Download Presentation
Tuning SQL Server 2012 for SharePoint 2013 Jump Start

Loading in 2 Seconds...

play fullscreen
1 / 52

Tuning SQL Server 2012 for SharePoint 2013 Jump Start - PowerPoint PPT Presentation

  • Uploaded on

Tuning SQL Server 2012 for SharePoint 2013 Jump Start. Bill Baer | Senior Product Marketing Manager, Microsoft Brian Alderman | Chief Executive Officer | MicroTechPoint. Introduction. Bill Baer (ˈ bɛər ) Senior Product Marketing Manager SharePoint Microsoft Corporation.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Tuning SQL Server 2012 for SharePoint 2013 Jump Start' - lucia

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
tuning sql server 2012 for sharepoint 2013 jump start
Tuning SQL Server 2012 for SharePoint 2013 Jump Start

Bill Baer | Senior Product Marketing Manager, Microsoft

Brian Alderman | Chief Executive Officer | MicroTechPoint


Bill Baer (ˈbɛər)

Senior Product Marketing ManagerSharePoint Microsoft Corporation

Bill Baer is a Senior Product Marketing Manager and Microsoft Certified Master for SharePoint in the SharePoint product group in Redmond, Washington; having previously worked at Hewlett-Packard Bill Baer has a proven background in infrastructure engineering and enterprise deployments of SharePoint Products and Technologies. While at Hewlett-Packard Bill Baer was awarded the MVP award for his contributions in the Technology Solutions Group, now known as HP Enterprise Business, which encompasses server and storage hardware, technology consulting, and software sales.





Brian Alderman

Chief Executive OfficerFounder of MicroTechPoint

Brian has been focused on helping IT Pros and DBAs better understand core Microsoft technologies for over 25 years. As an industry-recognized consultant, author and conference speaker, Brian’s expertise and designs range across Microsoft operating systems, Active Directory, SQL Server, and SharePoint. A frequent presenter at SharePoint Conferences around the world, he has authored or contributed to several SharePoint and other technical books, and is a MCSE, MCT, MCDBA, and MCITP. Brian has a BS and MS in Computer Information Systems where he graduated summa cum laude from Regis University of Colorado Springs and lives in Scottsdale, AZ where he enjoys playing golf year round and traveling around the world.




setting expectations
Setting Expectations
  • Experienced SharePoint Administrators and/or SQL Server Database Administrators
    • Professional SharePoint experience; working knowledge of SQL Server
  • Suggested Prerequisites/Supporting Material
    • Hands-on experience with a Microsoft Learning Partner recommended
    • Querying Microsoft SQL Server 2012 (course 10774)
    • Administering Microsoft SQL Server 2012 Databases (course 10775)
join the mva community
Join the MVA Community!
  • Microsoft Virtual Academy (MVA)
    • Free Online Learning Tailored for IT Pros and Developers
    • Over 1M Registered Users
    • Up-to-date, Relevant Training on Several Microsoft Products
module agenda
Module Agenda
  • SQL Server Deployment Options & Database Types
  • Understand SQL Server and SharePoint Integration
  • Schema Overview, Database Structure, Schema Restrictions
  • SharePoint Database Descriptions
    • Farm Configuration and Central Administration
    • Content Databases
    • Service Application Databases
  • Multiple Instances of SQL Server on One Physical Server
  • One Default Instance and Multiple Named Instances
  • Create SQL Server Alias for SharePoint SQL Server Instance
  • Each Instance is Managed Individually
    • Share SQL Server Management Tools
  • Each Instance Shares Server Resources (RAM, CPU)
sql server database types
SQL Server Database Types
  • System Databases:
    • Master – Configuration database of SQL Server
    • Msdb – SQL Server automation
    • Tempdb – Temporary storage area
    • Model – Template for all new databases
  • User Databases:
    • All Web app databases
    • All Service app databases
    • All other non-system databases
sql server and sharepoint integration
SQL Server and SharePoint Integration
  • 93.8% of SharePoint content stored in SQL Server
  • Farm Configuration information stored in configuration db
  • Central Administration content stored in own content db
  • Most Service Applications have at least one content db
  • All Web Apps have at least one content db
  • During SQL Server installation, Set Default Collation Setting to Latin1_General_CI_AS_KS_WS
sql server and sharepoint integration1
SQL Server and SharePoint Integration
  • Farm has several databases; >20 if spousal installation
  • Site Collections only reside in one database
  • Content database contains multiple site collections (2,000 Default Setting)
  • If Site Collection > 100GB store in own content database
    • Soft limit maximum size <= 200 GB
  • Use SharePoint to control size of content database
      • Quota Templates
      • Maximum Number of Site Collections
database structure
Database Structure



Data File

.MDF (1)

.NDF (0-n)

Log File

.LDF (1-n)

  • Fundamental Unit of Data Storage in SQL Server
  • 8 KB of Data Which Can be Index or Data Related, Large Object Binary (LOB’s, e.g. BLOB) etc...
pages c ont
Pages cont...

Data rows are inserted serially immediately following the header.


Page Header

Data Row 1

A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page.

Data Row 2

Data Row 3




  • Group of Eight (8) Consecutive Pages
  • Aligned on (8) Eight Page Boundaries or 64KB
schema implications perceptions
Schema Implications (Perceptions)
  • SharePoint Tables Too Wide, Wraps Rows
  • SharePoint Manages Own (NVP) Indexes
  • SharePoint Adds Force-Order, Query Hints
  • Missing Indexes for Common Operations
  • Excessive Use of Dynamic Queries
  • No SQL Referential Integrity OR Key Constraints
  • DBCC with Data Loss Not Supported
  • Missing Integration of Back-up/Restore
supportability constraints on schema modifications
Supportability Constraints on Schema Modifications
  • Some Examples of Such Database Changes Include:
    • Adding Database Triggers
    • Adding Indexes or Modifying Existing Indexes Within Tables
    • Adding, Modifying, or Deleting Primary or Foreign Key Relationships
    • Modifying or Deleting Existing Stored Procedures
    • Adding New Stored Procedures
    • Making Modification to Database Schema
    • Adding Tables to a Database of Products Listed in the "Applies to" Section
    • Changing the Database Collation
  • More Information: http://support.microsoft.com/kb/841057
understanding support policies and imposed limitations
Understanding Support Policies and Imposed Limitations
  • Single Data Platform
    • Web Content Management (WCM): Predominantly READ / Structured Queries and Search
    • Enterprise Content Management (ECM): 80/20 READ/WRITE Distribution / Ad-hoc Queries
  • Upgrade and Patch Management
      • Requires Consistency and Integrity
  • Application Logic Expectations on Schema
  • Enforced Integrity and Constraints
database descriptions
Database Descriptions
  • 23 Unique Databases Created in a Complete SharePoint Server 2013 Installation
  • Distributed Light > Heavy IO and Scale Up/Out Options
configuration database
Configuration Database
  • Single Database: Stores Farm Configuration Data, Solutions, and Farm Specific Settings
central administration content
Central Administration Content
  • Content Database for Central Administration
content databases
Content Databases
  • Stores all Site Content, Documents, Files, and Data
upa profile database
UPA: Profile Database
  • Stores and Manages Users and Social Information
upa synchronization database
UPA: Synchronization Database
  • Stores Configuration and Staging Data Used During Profile Synchronization
upa social tagging database
UPA: Social Tagging Database
  • Stores Social Tags, Notes, and Ratings
search administration database
Search: Administration Database
  • Stores Search Application Configuration and ACL for Crawl Component
search analytics reporting database
Search: Analytics Reporting Database
  • Stores Results for Usage Analysis Reports
search crawl database
Search: Crawl Database
  • Stores State of Crawled Data and Crawl History
search link database
Search: Link Database
  • Stores Information Extracted by Content Processing and Click-Through Information
  • SharePoint Deployment Should Have Dedicated Instance of SQL Server
  • Unique SharePoint Database Schema Does Not Support Modification
  • Several Databases Created for Web Apps and Service Apps with Different IO Impact
apps app management database
Apps: App Management Database
  • Stores App Licenses and Permissions
apps apps for sharepoint database
Apps: Apps for SharePoint Database
  • Stores Information About Apps for SharePoint and Access Apps
secure store service database
Secure Store Service Database
  • Stores App Licenses and Permissions
usage database
Usage Database
  • Stores Health Monitoring and Usage Data
subscription settings service database
Subscription Settings Service Database
  • Stores Features and Settings for Hosted Customers
business data connectivity database
Business Data Connectivity Database
  • Stores External Content Types and Objects
project server 2013 database
Project Server 2013 Database
  • Stores Data for Project Web App Sites
powerpivot service database
PowerPivot Service Database
  • Stores Data Refresh Schedules and PowerPivot Usage Data
performancepoint services database
PerformancePoint Services Database
  • Stores Temporary Objects and Persisted User Comments and Settings
state service database
State Service Database
  • Stores Temporary State Information for InfoPath Forms Services, Exchange, Visio Services, and Chart Web Part
word automation services database
Word Automation Services Database
  • Stores Information About Pending and Completed Document Conversions and Updates
mms managed metadata database
MMS: Managed Metadata Database
  • Stores Managed Metadata and Syndicated Content Types
mms taxonomy database
MMS: Taxonomy Database
  • Stores Hierarchical Structure of Terms Used for Tagging Content and Building Site Collections