SharePoint Data Management and Scalability on Microsoft SQL Server

(1)

SharePoint Data Management and Scalability on

Microsoft SQL Server

Kevin Kline

(2)

2

(3)

Kevin Kline

• SQL Server Expert for Quest

Software

• Former developer, DBA, and

enterprise architect since „86

• Former president of PASS

(www.sqlpass.org)

• Microsoft MVP since „04

• Author of SQL in a Nutshell

and 9 other books

(4)

4

Audience Poll

• New to SharePoint?

• SQL Admins?

• SharePoint Admins?

• Large-scale Implementation (+1 TB) experience?

(5)

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.

• Key Takeaway:

(6)

6

SharePoint Containment Hierarchy

Items

Files, calendar items, contacts, customers, images, custom

Lists

Doc Lib, Pages, Events, Discussions, Surveys, etc…

Sites

Wikis, Blogs, Team, Doc, Mtg

Site Collections

Internet, Intranet Portal, Wikis, Blogs, Team, Doc, Mtg

Databases

Content, Config, SSP, Search

Web Applications

Central Admin, SSP Admin, Content

Servers

Web Front End, APP, SQL

(7)

(8)

(9)

(10)

(11)

Understanding SSP DB

SSP

Database

MySite Host

Config

Profiles

BDC Config

(12)

(13)

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.

Slow

SQL

Slow

(14)

14

Database Disk I/O Demand

Search

Most Demand

Config

Medium Demand

+SSP

*Content..

Low Demand

* Except during backup, indexing, and during Profile Import

(15)

Top Performance Killers

• Indexing/Crawling

• Backup (SQL & Tape)

• Profile Import

• Misc Timer Jobs – User Sync for large #s of Users

• Poor Storage Configuration

• STSADM Backup/Restore

• Large List Operations

• Heavy User Operation List Import/Write

• Network

(16)

(17)

Scaling SQL

(18)

18

Scalling SQL - Out

• More SQL servers = More flexibility

• There aren‟t really any physical barriers

• SharePoint won‟t prevent you from placing 100 databases

on 100 different SQL instances

• The real barriers are manageability and cost.

– More servers = more money

(19)

(20)

20

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)

(21)

SQL: Scale Out VS. Scale Up

Scale Out

Scale Up

Advantages

Better Performance Easier to Manage

Better Flexibility Cheaper

Disadvantages

(22)

22

Walkthrough: Scale Up VS. Out Deployment:

How to Design a 5TB SharePoint SQL

(23)

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?

(24)

24

Scaling SQL – The Bottom Line

• Don‟t scale SQL instances beyond comfort zones!

• Do measure system throughput – Know All of your

bottlenecks!

(25)

(26)

26

Highly Available Deployment?

Redundant Switches

Redundant Web/Application Servers

Active/Passive SQL w/ Redundant HBA’s

Redundant SAN Fabric RAID 1 Storage

(27)

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/Passive

• Use DNS or Load Balancing to direct traffic

(28)

28

(29)

High Availability Between Farms

• Can use a variety of methods to ship content

between farms/data centers:

– Log shipping

– Mirroring

– Storage replication

• Longer distances supported*

– The greater the latency the harder it is to replicate content

(30)

31

The Two Basic HA/DR Scenarios

Mirroring Within Farm

• Pros:

– Great combo HA/DR solution

– Cheaper to implement – Easier to manage

• Cons:

– Requires closely located datacenters – Requires excellent network conditions – Not flexible – Content corruption is replicated immediately.

Mirroring/Log ship

Between Farms

• Pros:

– Allows long distance

separation

– Can protect against

logical corruption

– Very flexible!

• Cons:

– More expensive

– Harder to setup and

manage

(31)

(32)

33

SQL 2008 - Do you have Enterprise?

• Asynchronous Mirroring with compression • Transparent Database Encryption

• Backup Compression • Resource Governor

SQL

Enterprise

• Synchronous Mirroring • 2 Node Clustering

• Log Shipping with compression • Restore Compressed Backup

SQL

Standard

• FREE!

• Both SharePoint Foundation 2010 and SharePoint 2010 use…

• Up to 4 GB Max Storage • Use as a Witness in Mirroring

(33)

(34)

35

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 files

– Defrag regularly

(35)

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

view

(36)

37

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.

(37)

SQL Data files

• Best Practices:

– TempDB:

• Create multiple data files up to the ½ or even ¼ number of CPU cores • Allocate TempDB on RAID 1 (or R1 variants)

• RAID5 sucks at writes!

– Separate Data and Logs on different LUNS – Spread databases on multiple spindles

– Pre-allocate files and use autogrow only as safety net

(38)

39

Identifying Disk Bottlenecks

• Perfmon

– Monitor transfer/sec for throughput trends.

– Monitor Disk sec/Read / Disk sec/Write for bottlenecks. – Monitor disk Queue length for bottlenecks.

• SQL

– Select * from sys.dm_IO_virtual_file_stats(null, null) – Solution –

(39)

(40)

41

Lots of New SharePoint 2010 Databases

Farm

Config

Admin_Content

Application Registry Service StateService

Web Analytics Web Service WSS_Usage Reporting_DB Staging DB

Services

Search_Service_Application Crawlstore SearchDB PropertyStore WSS_Search SocialDB ProfileDB (was SSP db) SyncDB BDC_Service_DB

(41)

(42)

43

(43)

(44)

45

Logs & Reporting to the DB

Extensibility for reporting and possibilities are

(45)

Applying the Newest Learnings

Add more processor to the backend: 4 cores to 8 cores

Add more RAM: 16GB to 32GB

Run profile sync on our terms! Run the jobs as little as possible. Once

a week or once a month.

(46)

47

Summary

SQL 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.

(47)

(48)

(49)

Search Disk Performance

Drive IOPs Read