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
Who Am I? • 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.
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 own 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 on 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
Recommend
More recommend
