Administrators Todd Klindt & Shane Young SharePoint911 Who is - - PowerPoint PPT Presentation

administrators
SMART_READER_LITE
LIVE PREVIEW

Administrators Todd Klindt & Shane Young SharePoint911 Who is - - PowerPoint PPT Presentation

402: Taming SQL Server for Administrators Todd Klindt & Shane Young SharePoint911 Who is this Todd guy? WSS MVP since 2006 Speaker, writer, consultant, Aquarius, Ray Romanos stunt double Personal Blog www.toddklindt.com/blog


slide-1
SLIDE 1

402: Taming SQL Server for Administrators

Todd Klindt & Shane Young SharePoint911

slide-2
SLIDE 2

Who is this Todd guy?

  • WSS MVP since 2006
  • Speaker, writer, consultant, Aquarius, Ray

Romano’s stunt double

  • Personal Blog

www.toddklindt.com/blog

  • Company web site

www.sharepoint911.com

  • E-mail

todd@sharepoint911.com

  • Twitter me! @toddklindt
slide-3
SLIDE 3
  • Shane Young
  • Owner of SharePoint911.com
  • Microsoft Office SharePoint Server MVP
  • Consultant, Trainer, Writer, & Speaker

▫ shane@sharepoint911.com ▫ Blog

 http://msmvps.com/shane

▫ SharePoint Consulting

 http://www.sharepoint911.com

▫ http://twitter.com/shanescows

  • I am going to be a professional armpit sniffer when I

grow up.

Who Am I?

slide-4
SLIDE 4

Session Agenda

  • Overview of how SharePoint uses SQL
  • Overview of SQL versions and which you would

use for what

  • Care and feeding Best Practices for your SQL

server

  • A couple of cool SQL 2008 (R2) features to

consider

slide-5
SLIDE 5

SQL? I’m a SharePoint admin!

  • Foundation & SharePoint Server
  • Farm configuration is stored in SQL.
  • All SharePoint content is stored in SQL, unless

you install an RBS or EBS provider

  • Central Admin is a web application and is in its
  • wn content database
  • Search gets its own databases
  • Some service applications have databases
slide-6
SLIDE 6

Content Databases

  • Every web application gets a Content Database
  • A web app may have multiple Content Databases
  • A Site Collection must exist completely in a

single Content Database

  • A Content Database may and probably should

have multiple Site Collections

slide-7
SLIDE 7
slide-8
SLIDE 8

Which version of SQL?

  • Must be 64 bit
  • Supported versions

▫ SQL Server 2008 R2 or later ▫ SQL Server 2008 SP1 CU 2, CU 5 or later ▫ SQL Server 2005 SP3 CU3 or later

  • Can user Express edition
  • SQL Server 2008 R2 Express has 10 GB database limit
slide-9
SLIDE 9

Standard or Enterprise?

  • Standard features

▫ Supports up to 4 CPUs (including cores) ▫ Supports 64 GB of RAM ▫ Failover is manual and restricted to two nodes ▫ Supports Database Mirroring

slide-10
SLIDE 10

Movin’ on up…Enterprise

  • Supports more 8CPUs
  • Support for 2 TB of RAM
  • Active failover for mirrors
  • KPI and Analysis Server built in
  • Snapshots
  • Backup Compression
  • Transparent database encryption
  • Comparison chart of all the versions at
slide-11
SLIDE 11

Keep your SQL server happy

  • Maintenance Plans

▫ Can be created manually or with a wizard ▫ Easily modified with a graphical interface ▫ Can include a variety of operations, including backups ▫ Can use SMTP to email plan success

  • Some things you should consider

▫ Check Database Integrity ▫ Backups

slide-12
SLIDE 12

Things you should do

  • Check Database Integrity

▫ Verifies integrity of databases ▫ Uses T-SQL command DBCC checkdb ▫ Very disk and CPU intensive ▫ Can use PHYSICAL_ONLY to shorten time and impact ▫ Read more at http://msdn.microsoft.com/en- us/library/ms176064.aspx

slide-13
SLIDE 13

Backups

  • Can be part of maintenance plan.
  • Three types

▫ Full ▫ Partial ▫ Differential

  • Transaction logs

▫ What are Transaction logs and why do constantly fill my drives and break my server?

slide-14
SLIDE 14

More Backup recommendations

  • Can use built in software or third party.

▫ Allows for database compression and encryption. ▫ Results in smaller backups ▫ Could also result in faster backups, if drive throughput is the bottleneck

  • SQL 2008 and R2 Enterprise supports

compression and encryption out of the box

slide-15
SLIDE 15

File system fragmentation

▫ SQL will be faster if the database files are contiguous in the file system ▫ Using the built in defrag tool will have performance ramifications ▫ Consider using something like Diskeeper and its intelligent defrag. ▫ Consider stopping SQL if possible

slide-16
SLIDE 16

Shrinking is BAD

  • Database size is reduced by dropping unused

space.

  • Do not shrink databases unless something drastic

has happened

▫ Massive site or content deletions ▫ Abandoning databases ▫ Made to by the devil to get your soul back

  • Forces databases to grow again later
  • Has a heavy impact on the server
slide-17
SLIDE 17

Pre-grow databases

  • Databases grow, it is what they do
  • Grow operations are slow in SQL and will likely

result in a fragmented database file

  • Create database with enough space for one year’s

worth of growth

slide-18
SLIDE 18

Database Best Practices

  • Create multiple TempDB files
  • Put database and corresponding transaction logs
  • n different spindles
  • Arrange databases according to speed
  • Autogrow

▫ Change grow rate to something more intelligent ▫ It’s a last resort

  • Keep your databases under 200 GB
  • Use multiple content databases
slide-19
SLIDE 19

More points to ponder

  • Do not alter databases. Microsoft HATES that
  • Can use SharePoint farm backups to back up SQL
  • Don’t forget your System databases
  • Wss_logging database will have heavy I/O and

will get large, keep your eye on it

slide-20
SLIDE 20

Demo

A little noodling around in SQL

slide-21
SLIDE 21

Some light SQL reading

  • Things you can do

▫ http://support.microsoft.com/kb/932744

  • Things you shouldn’t do

▫ http://support.microsoft.com/kb/841057

  • Database maintenance white paper

▫ http://office.microsoft.com/download/afile.aspx?A ssetID=AM102632301033

slide-22
SLIDE 22

Thanks Please fill out your evaluations And turn yourself around