Microsoft SharePoint Server with SQL Server: Now Better Tha - PowerPoint Presentation

Slide1
Slide2

Microsoft SharePoint Server with SQL Server: Now Better Than Ever

Brian AldermanSlide3

About me

Brian Alderman (

MCSE: SharePoint, MCT

/ Author /

Speaker)

CBT Nuggets –

www.cbtnuggets.com

brian.alderman@cbtnuggets.com

@

brianalderman

@

cbtnuggets

www.microtechpoint.com

– Brian’s Blog

Co-Author SharePoint 2010 Administrator’s Companion

Co-Author SharePoint 2013 Administration Inside Out

Enjoy

travel

and

golfSlide4

Agenda

SharePoint and SQL Server integration

Demo: SQL Server

Optimizing Configurations

SQL Server 2014 for

SharePoint 2013

Avoiding ginormous

t

ransaction logs

SharePoint and SQL Server best practices

SQL Server ConfigurationsSlide5

SharePoint and SQL Server integrationSlide6

All SQL Server

versions

and SharePoint v

ersions

M

ajority

of SharePoint data stored in SQL ServerSharePoint farm configuration info stored in

Config_DB Central Administration content stored in own content database (Blog on how to rename)Every web application minimum of one content DB (Blog on how to rename)Most service applications have at least one DBRecommended dedicated instance of SQL for SharePointSlide7

SQL Server Instance Databases

Configuration database

of SQL Server

instance

Storage

of SQL Server automation configuration information

Temporary w

ork storage area Template u

sed to

create all new databases

Master

Msdb

Tempdb

ModelSlide8

SharePoint

farm requires several databases (>20

DB’s if

spousal installation

and

run

Configuration Wizard) Content database contains several site collectionsSite

collection resides in only one content databaseUse Full Recovery model on production databasesControl size of database (Recommended 200GB) Site collection quota templates

Maximum number of

site collections per databaseSlide9

Controlling Size of Content Databases

Web Application

200GB

200GB

200GB

200GB

Site Collections

Site Collections

Site Collections

Site Collections

Project

Sites

Department

Sites

HR

Sites

Marketing

Sites

Require 800 Site Collections with 500 MB

Quota

400 x 500MB = 200GB

750mb X 250 = 187,500mb / 1024 = 183gb

250

250

250

250Slide10

SQL Server configurationsSlide11

Default

file

l

ocations

(Move off C:\ Drive)

Minimum and

maximum

Memory settingsMax Degree of Parallelism (MAXDOP) set to 1

Create SQL Server alias for SharePoint SQL

instance (Not u

sed by FAST search SKU (SharePoint 2010, use DNS alias)SharePoint collation – Latin1_General_CI_AS_KS_WSDuring

installation of SQL Server

hosting SharePoint contentDuring

creation of content database in SQL ServerSlide12

Model d

atabase file

s

ettings

Increase

initial

s

ize of data and log files

Increase Autogrowth

settings (Use MB not %) Tempdb d

atabase file settings Increase initial size of data and log

files

Increase Autogrowth settings (Use MB not %)

Use Simple Recovery model Place files on different drive from

content databasesSlide13

Steps for

AlwaysOn

Setup

Create SharePoint farm

Prepare databases for

AlwaysOn

Be sure set to Full Recovery model

Backup all SharePoint databases

Prepare SQL Server cluster

Use created

AlwaysOn

cluster with SharePoint

Prepare SharePoint 2013 with SP1

Create SQL alias and point to SQL node

Create new farm with DB’s & SA’s using SQL alias

Create new

AlwaysOn

Availability Group

Create listener

Update SQL alias on each server to use listener

Test SQL Server failover with SharePointSlide14

Demo: SQL Server Configurations

Brian AldermanSlide15

SQL Server 2014 for

SharePoint 2013Slide16

Performance

Standard edition supports up to 128GB of RAM opposed to

64 in SQL Server 2012

AlwaysOn

Availability Groups Enhancements Supports 8 secondary replicas as opposed to 4 Read-only secondary’s still available if lose primary or quorum

Azure support for hosting availability group data files, entire replica, and backing up to a URL to store in Azure Blob StorageSecurity Support for native backup encryptionSlide17

SQL Server 2014 with SharePoint 2013, must have May CU

Access Services 2013 not supported on SQL Server 2014 CTP

New deployments should specify Availability Group Listener name that will host the CA and configuration DB’s

AppFabric

1.1 installation

https://www.microsoft.com/en-us/server-cloud/products/sql-server/Comparison.aspx

SharePoint 2016 will require SQL Server 2014Slide18

Avoiding ginormous transaction logsSlide19

SQL Server Transaction Log Process

Modification is sent by

application to SQL Server

1

Data pages are located in,

or read into the buffer cache

and then modified

2

Buffer Cache

Modification is recorded

in transaction log on disk

3

Later,

CHECKPOINT

writes

dirty pages to database

4

LDF

MDFSlide20

Avoiding Ginormous Transaction Logs

mdf

ldf

Sunday

Full

Backup

mdf

ldf

Tuesday

Differential

mdf

ldf

Differential

Monday

mdf

ldf

Differential

Wednesday

You

lose

mdf

file of database on Thursday at 4:00pm

(1)

BACKUP LOG

DB_Name

TO D:\SQLBackups\TempBackup.Bak WITH NORECOVERY

(2)

Restore Full Backup from Sunday

(3)

Restore Differential Backup from Wednesday

(4) RESTORE LOG FROM D:\SQLBackups\TempBackup.Bak

BACKUP LOG

DB_Name

TO D:\SQLBackups\Weekly_T_Log.Bak WITH INITSlide21

Best practicesSlide22

Dedicated SQL

Server 2014 instance

/

server

No

spousal

i

nstallations of SQL Server or SharePointDatabase size should not exceed 200GBMax Degree of Parallelism (MAXDOP) set

to 1

Create SQL alias for SQL Server SharePoint instanceModify Model

and Tempdb system database settings Don’t enable Auto_Create or

Auto_Update Statistics

Avoid auto-shrinking databasesUse database Autogrowth

sparinglyReduces fragmentationImproves data entry performance Slide23

Spread

data

f

iles

and

transaction

log

files across multiple drives or place on RAID 5/10Create

multiple Tempdb

files (.ndf) on m

ultiple drives or RAID 10 for drives hosting Tempdb filesGenerate Database Maintenance PlansDefragment drives c

ontaining c

ontent database filesDon’t rebuild or

manage indexes of SharePoint DB’sPerform regular backups of database and t-logs

Monitor SQL Server

storage and performancePerform DBCC CHECKDB o

perations regularlySay NO to Simple Recovery modelSlide24

Review

SharePoint and SQL Server integration

Demo: SQL Server

Optimizing Configurations

SQL Server 2014 for

SharePoint 2013

Avoiding ginormous

t

ransaction logs

SharePoint and SQL Server best practices

SQL Server ConfigurationsSlide25
Slide26

Slides below this are the Microsoft template slidesSlide27

Agenda

SharePoint and SQL Server integration

SQL Server configurations

Demo: Optimizing SQL Server for SharePoint

SQL Server instance configurations Database configurations

SQL Server 2014 for SharePoint 2013Avoiding ginormous transaction logsSQL Server best practicesSlide28

Review

SharePoint and SQL Server integration

SQL Server configurations

Demo: Optimizing SQL Server for SharePoint

SQL Server instance configurations Database configurations

SQL Server 2014 for SharePoint 2013Avoiding ginormous transaction logsSQL Server best practicesSlide29

ATTENTION: PLEASE READ

Photography and copyright infringement.

Using imagery from movies, television, music and pop-culture is illegal, unless purchased, licensed or by express written consent from the owner.

Please

do not include

unlicensed imagery

in

your presentation.

Sessions with questionable imagery

will not be published

until resolved. The speaker will

be

contacted via email for immediate resolution

. If additional costs

are incurred

in

editing, an

I/O

Code will

be

required.

If you have any questions regarding your imagery please contact

Media

Acquisitions Inquiries:

mediaacq@microsoft.com

.

The

PPT

deck and Recording

will not

be published

until the

editing is complete.Slide30

If an image is on the Internet/Bing it is in the public domain.

If there is no copyright notice on the image,

I

don’t need permission.

If I don’t profit from the use, I don’t need permission.

If I remove the image after notice, I don’t owe any money to the copyright.

If I alter the image X

%,

I don’t need permission.

If I only use a part of the

image,

I

don’t need permission.

If I only include a hyperlink in my presentation and click it to show the

image,

I

don’t need permission

.

Common Copyright MythsSlide31

Resources for Microsoft employees

Mediaacq

Microsoft Brand Tools

Presentation guidelines

Microsoft photography

Microsoft illustrations

Can’t find the image that you have in mind for your presentation? The Media Acquisitions team can help you find and license imagery through our stock

photography providers. You will also find imagery and information via these links:

For information on the use of third-party copyrighted content:

http://lcaweb/CTP/Copyrights/Third-Party-Content-Use/Pages/default.aspx

(Media Acquisitions alias)

www.superstock.com/

www.gettyimages.com/creativeimages/royaltyfree

www.corbisimages.com/stock-photo/royalty-free

There are agreements in place with these providers.Slide32

Preferred text layout (no bullets)

Main topic 1: size 40pt

Size 20pt for the subtopics

Size 20pt for the subtopics

Main topic 2: size 40pt

Size 20pt for the subtopics

Size 20pt for the subtopicsMain topic 3: size 40pt

Size 20pt for the subtopicsSize 20pt for the subtopicsSlide33

Preferred text layout (no bullets)

Main topic 1: size 40pt

Size 20pt for the subtopics

Size 20pt for the subtopics

Main topic 2: size 40pt

Size 20pt for the subtopics

Size 20pt for the subtopicsMain topic 3: size 40ptSize 20pt for the subtopics

Size 20pt for the subtopicsSlide34

Adjusting list levels

Main topic 1: size 40pt

Size 20pt for the subtopics

Size 20pt for the subtopics

Main topic 2: size 40pt

Size 20pt for the subtopics

Size 20pt for the subtopicsMain topic 3: size 40pt

Size 20pt for the subtopicsSize 20pt for the subtopics

Use the “Decrease List Level” and “Increase List Level” tools on the Home Menu to change text levels.

Try this:

Place your cursor in any row of text to the left that says “Size 20pt for subtopics”

Next click the Home tab, and then on the “

Decrease List level

” tool. Notice how the line moves up one level.Now try placing your cursor in one of the “Main topic…” lines of text. Click the “

Increase List Level

” tool and see how the text is pushed in one levelUse these 2 tools to adjust your text levels as you workSlide35

Example of a bulleted slide with a subhead

Set the slide title to “Sentence case”

Set subheads to “Sentence case”

Hyperlink style

www.microsoft.com

Bullet points layout with subtitle

SubtitleSlide36

Slide

palette info

Accent 3

Accent 2

Accent 1

Accent 6

Accent 5

Accent 4

Text

Light

2

Accent colors 1-6 – (6 Theme Colors to the far right)

The PowerPoint palette for this template has been built for you and is shown below. Avoid using too many colors in your presentation.

Use

Accent 1

as the main accent color. Use

Accent 2

and

Accent 3

when

additional colors are needed.

Use

Accents 4-6

sparingly – only when more colors are necessary. Slide37

VideoSlide38

Demo

Speaker nameSlide39

Photo layout 1Slide40

Microsoft brand guidelines

Looking for more slide resources?

Brand guidelines

for PowerPoint templates is a separate slide deck that provides an overview of the Microsoft brand, guidelines, resources, tips and much more.

A few of the slides are shown

at right.

Download from:

https://brandtools.microsoft.com/Resources/Presentations/Pages/StoryBoard.aspx?section=Elements1Slide41

Section titleSlide42

Section titleSlide43

Section titleSlide44

Section titleSlide45

Section titleSlide46

Software code slide

This slide layout uses Consolas, a monotype font which is ideal for showing software code. Slide47