SQL Server and SharePoint

makeshiftklipInternet και Εφαρμογές Web

31 Οκτ 2013 (πριν από 4 χρόνια και 7 μέρες)

341 εμφανίσεις

SQL Server and SharePoint

Best
Frienemies

Lisa Gardner

Premier Field Engineer

LisaG@microsoft.com

http://blogs.msdn.com/sqlgardner

@sqlgardner

2

Lisa Gardner aka
SQLGardner

Central Ohio native

Working with SQL since 6.5

http://blogs.msdn.com/sqlgardner

@
SQLGardner

Premier Field Engineering

Reactive and Proactive support for Premier customers

Architecture/Project Guidance

Team Mentoring

Deliver workshops

Troubleshooting



Who am I ? What is PFE?

Table of Contents

SharePoint Databases

Configuration, Setup, and
Maintenance

What to Look Out For

SharePoint Overview

Understanding the Application.

5

WFE

Web Application

Service Application

Site Collection

ULS Logs

Timer Jobs



SharePoint Glossary

6

SharePoint Web Architecture

Farm

Web
Application

Site
Collection

Site

List

Item

7

Web Application


Different IIS Site


Created on
each WFE


Isolates Content


Provides
authentication
mechanism


Site Collection


Container of
Sites


Quotas


Decentralized
Content
Administration


Also serves as a
site

Site


Permission
Inheritance


Can Share
layout and data
with other sites


Can provide
unique feature
set from other
sites

Key Attributes

8

Provides granular pieces of functionality

Some
c
an be tied to a specific server

Offers scalability, load balancing, fault tolerance for most
services

Many to many relationship with web applications and
service applications

Each web application can have a unique set of service
applications

Service Applications

9

SharePoint equivalent to SQL Agent

OWSTimer

-

Windows
service for SharePoint 2010 at a
predefined schedule

Uses same logging infrastructure as web tier

Includes Correlation IDs

Jobs can be nested

Timer Jobs

10

SharePoint Internal Data

11

Stores all SharePoint usage and health data

ULS trace log data

Event log data

Blocking SQL Queries

Crawl and Query Statistics

Feature Usage

Page Requests

+More





Logging Database

12

Logging Database


13

ULS Logs

14

Correlation IDs

Web
Front
-
End
Server

Application
Server

~~~~~~~~~~
~~~

~~~~~~~~~~
~~~

7d25d051
-
ca73
-
43…

7d25d051
-
ca73
-
43 …

7d25d051
-
ca73
-
43 …

7d25d051
-
ca73
-
43 …

Generated for every request

Logged from the start of a request through to the end

Useful for troubleshooting and tracing

On error pages, ULS logs, Windows Logs, SQL Traces

SharePoint Databases

So Many Databases, So Little Time

16

Farm Configuration Store

Objects Table


Serialized Objects

Binaries Table


Farm Solution Store

SiteMap

Table


Links a site into the configuration

Content Database for Central Admin
is a Content DB with
very specific templates
-

considered
to
be an extension of the
configuration database

Backup and Recovery

It is Supported to back up this database

It is Not Supported to restore unless the farm is fully
stopped when the
backup is taken


Configuration and Admin Content Databases

17

General Recommendations

Default recovery model is Full but in most cases this database should be
run in simple recovery mode

Initial Data
File Size:
2GB is appropriate for most situations

Config

databases are typically smaller and do not get much load

Mirroring

Supported to mirror within the farm (partner on same network as
primary)

Not Supported to mirror asynchronously or to log ship over WAN


Configuration and Admin Content Databases

18

Stores all site data in a site collection

Site Metadata

Web Part Pages

Files uploaded to document libraries

List
Items

Security

Solutions

It is supported to Mirror in Farm for High Availability

It is supported to Mirror Asynchronously or Log Ship
over WAN for disaster recovery

General Recommendations

Run in Full recovery mode only if the site data requires point in
time restores


Content Database

19

Content Database Schema

Why SharePoint seems so crazy.

Userdata table

Namespace Table

Container Tables

Id

Quota

Other Metadata

Sites

SiteId

Id

Url

Title

ScopeId

Metadata

Webs

WebId

Id

Title

ItemCount

ScopeId

Fields

Metadata

AllLists

Other
Metadata


1…64

1...32


1..8


1..16


1..12

1..8

1…16

~35

Url

20

Can contain 1
-
2000 site collections

Scale
out at the
db

level and the
instance level
.

Sizing Guidance <200GB


Maintenance tasks stay manageable


Makes
db

movement and DR
easier


Plan for 2 IOPs per GB data

Can have 200GB
-
4TB if .25 IOPs per GB

Size
and load
depends
on the sites they contain

Separate very active sites into different site
collections/content
dbs

Can have 32,767
dbs

per instance, but recommend
200 per instance as manageability can be an issue

300 DBs per Web Application


Content Database Layout

21

Search


Admin


Crawl


Property

Profile


Profile


Syncronization


Social Tagging

Web Analytics


Reporting


Staging

Service Application DBs

Logging

BDC

State

Secure Store

Reporting
Services

Power Pivot

Project
Server

Performance
Point

22

The different service application
dbs

have a wide variety
of performance/sizing considerations.

Write
-
Intensive
dbs

Usage and Health Data Collection
database
(Logging)

Web Analytics Reporting
database (during load)

Search service application Crawl database (during
crawls)

Search service application Property database

User Profile service application Synchronization
database


Service Application Databases

Performance Considerations

23

Read Intensive DB’s

Web Analytics Reporting database

Search service application Crawl database

User Profile service application Profile database

User Profile service application Synchronization
database

User Profile service application Social Tagging
database

Reporting database (Project Server)


Service Application Databases

Performance Considerations Cont’d

24

Database Scale Out Guidance

Search

Content

Logging

Web
Analytics

Other

Content

Content

Admin/
Content

Content

Content

Content

Configuration, Setup, and Maintenance

26

Allow the SharePoint installer to create databases

Modify file sizes and growth settings

Rename
dbs

to remove GUIDs

SharePoint setup and admin accounts required roles:

DB Creator

Security Admin

Can be removed for the setup account but will need to be
added again for any further installs


not recommended

Patching/Service packs

Adding a new Service Application

Add Service Application account logins

Requires
db_owner

role in DB

Planning for SharePoint Setup

27

Follow general Best Practices for SQL Configuration

Use Latin1_General_CI_AS_KS_WS collation

Configure for heavy
TempDB

usage

Multiple data files

Data and log files separated/isolated

Pre
-
size data files

Set max degree of parallelism to 1

SharePoint overrides with MAXDOP

Set max server memory and use Lock Pages In
Mem

Consider setting fill factor (%) to 80


Instance Configuration

28

Do not use Auto Shrink

Set Auto Create Statistics OFF

Set Page Verify to Checksum

Set Auto Grow sizes to MB not Percent

Pre
-
size for growth

Monitor utilization and grow manually!


Database Configuration

29

Index Maintenance is extremely important in SharePoint


DMV
Sys.dm_db_index_physical_stats

can be used to
report index fragmentation


SharePoint 2007 by default would rebuild every index via
a Timer Job


SharePoint 2010 does a much better job at keeping
index fragmentation in check

It only rebuilds indexes that are fragmented

Updates statistics

Index Maintenance

30

Index defragmentation and statistics maintenance
address the following
databases:

Configuration databases

Content databases

User
Profile: Profile
databases

User
Profile: Social
databases

Web Analytics
Reporting
databases

Web Analytics
Staging
databases

Word Automation Services databases

Search Property/Crawl databases

These databases contain
proc_DefragmentIndices

Run daily


Health Analyzer Rules

31

Search

Property database


Proc_MSS_DefragSearchIndexes


Run weekly

Crawl database


Proc_MSS_DefragGathererIndexes


Manual


Always report as fragmented


Execute this rule after the first full crawl


Health Analyzer Rules Cont’d

32

Health Analyzer rules rebuild indexes and update
statistics

AutoUpdate



off in SP 2010 by default

Update manually when:

Query execution times are slow

After
maintenance
operations such as table truncation
or a large batch insert/update/delete


Statistics


33

GUIDs are used as clustered primary keys

Random values = unpredictable insert pattern

16 bytes each

Heavy insert/update activity


These properties lead to rapid index fragmentation due
to many page splits


Fillfactor

helps delay the inevitable but increases space
usage

SharePoint rebuilds indexes with
fillfactor

of 80

Why is Index/Stats Maintenance So Important?

What to Look Out For

Common issues

35

Use DBA created content databases!

SharePoint hard codes small file size and growth settings

Automation Options:


Powershell

is a great option to allow SP
Administrators to create
dbs
!

Have a number of empty DBs already created

Must Do’s

Use Latin1_General_CI_AS_KS_WS collation

Set appropriate recovery model for your recovery
needs

Add SP farm setup account and service account with
db_owner

role


New Content Databases

37

Health Analyzer Rule Definition

Databases used by SharePoint have fragmented indices

Databases used by SharePoint have outdated index
statistics


Health Analysis Job in Logging DB

Details in ULS logs

Ensure Index Maintenance is Running


38

Common scenario: “The SQL Server is slow”

Ask for ULS Log info

Blocking/Deadlocks can be common in content DBs


Try a manual update stats


Inquire about large lists,
dbs

over threshold, and other
capacity limitations being exceeded

Ask about list throttling and “happy hour”


Read Committed Snapshot Isolation is
not

supported


Excessive Blocking

39

ASYNC_NETWORK_IO_WAITS

Disk IO

TempDB

Bottleneck

Very Large Queries

Logging is the ONLY DB to be queried directly

Others

40

Shredded storage to minimize storage needs with
versioning

Sparse Columns to support wide lists

Web Analytics redesigned


more robust/scalable

Profile Sync


tests of a 3 week import for 300k users
now only takes 7 hours

Stretch farms no longer supported


all databases must
now reside in the same data center


SP2013 Changes

41

Know the Limits!

http://
technet.microsoft.com/en
-
us/library/cc262787.aspx


More info on SharePoint DBs

http://
technet.microsoft.com/en
-
us/library/cc678868.aspx

http://www.microsoft.com/en
-
us/download/details.aspx?id=3408

Helpful Links

Questions?