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

Romano’s stunt double

• Personal Blog

www.toddklindt.com/blog

• Company web site

www.sharepoint911.com

• E-mail

todd@sharepoint911.com

• Twitter me! @toddklindt

• 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?

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

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

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

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

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

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

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

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

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?

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

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

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

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

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

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

Demo

A little noodling around in SQL

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

Thanks Please fill out your evaluations And turn yourself around