Transcript Slide 1
Understanding SQL Server Database Options for Microsoft Office SharePoint Server 2007 Todd Klindt, WSS MVP Solanite Consulting www.toddklindt.com/blog www.solanite.com WSS MVP since 2006 Speaker, writer, consultant, aquarius Personal Blog www.toddklindt.com/blog Company web site www.solanite.com E-mail [email protected] All around good guy Introduction to how SharePoint uses SQL Comparison of different versions of SQL and which is best The proper care and feeding of SQL Server for the SharePoint administrator Maybe a SQL 2008 demo or two How does SharePoint use SQL? WSS & MOSS Farm configuration is stored in SQL. All SharePoint content is stored in SQL. No exceptions! * A farm may have multiple Content Databases A Site Collection must exist completely in a single Content Database A Content Database may have multiple Site Collections * Except this one. Central Admin is a site collection and is in its own content database Search gets its own database MOSS Only Each SSP gets a database to store settings Each SSP gets a Search database Each SSP gets a Content database 2000? 2005? 2008? 32 bit or 64 bit? Express, Workgroup, Standard or Enterprise 2000 or 2005/2008 is easy, NOT 2000! Reporting Services Scales better with processors and RAM Has native SMTP mail support Supports Database Mirroring Consider SP2 if using SQL 2005 Major improvements in maintenance wizard Data Compression Analysis improvements that benefit Excel Services Improves integration between MOSS 2007 Report Center and SQL Reporting Services Fixes reindex problem Test in test environment first 32 bit advantages Better supported by existing hardware and software Existing SQL servers are likely 32 bit Existing corporate standard is likely 32 bit 64 bit excitement Handles more RAM, up to 16 Exabytes More efficient which means fewer servers, less energy, less space, fewer licenses Can attach 32 bit databases directly Any hardware purchased recently probably supports it It is the wave of the future MOSS also installs on 64 bit The decision ultimately depends on your scalability and availability needs Express Supports 1 CPU Supports up to 1 GB of RAM No native 64 bit support 4 GB Database size limit MOSS uses SQL 2005 Express if you do the single server install. Download Express Edition Toolkit from https://www.microsoft.com/downloads/details.aspx?familyid =3C856B93-369F-4C6F-9357C35384179543&displaylang=en Download Management Studio, https://www.microsoft.com/downloads/details.aspx?familyid =C243A5AE-4BD1-4E3D-94B85A0F62BF7796&displaylang=en Can be upgraded to Standard or Enterprise Install Standard or Enterprise with the following syntax: setup.exe SKUUPGRADE=1 Workgroup Supports 2 CPUs Maximum of 3 GB of RAM No native 64 bit support No database size limit No partitioning Cannot be a member of a cluster Includes Management Studio Standard features Supports up to 4 CPUs (including cores) Supports OS Maximum RAM, 4 GB of RAM on 32 bit OS Failover is manual and restricted to two nodes Supports Database Mirroring No partitioning No Analysis Services Native 64 bit support Enterprise offers the following advantages Full SQL 2005 functionality Supports more than 4 CPUs Support for up to 32 GB of RAM on 32 bit OS. OS limitation, not SQL’s Database Partitioning Online restore Active failover for mirrors KPI and Analysis Server built in Comparison chart of all the versions at http://www.microsoft.com/sql/prodinfo/features/co mpare-features.mspx Improved Maintenance Plan support Can be created manually or with the new wizard Maintenance plans can be easily modified with a graphical interface Maintenance plans can include a variety of operations, including backups Plans can use SMTP to email plan success Check Database Integrity Reindex or Rebuild database Indexes Update Statistics Backups Defrag the file system Check Database Integrity Verifies integrity of databases Uses T-SQL command DBCC checkdb Very disk and CPU intensive Update Statistics Statistics help the DB engine decide the most optimal execution path Updating these statistics improves the efficiency of queries You might trigger it manually if there were a lot of records added or deleted Happens automatically, you should not have to run manually Uses T-SQL command UPDATE STATISTICS Reindex databases Defragments database indexes Uses T-SQL command DBCC INDEXDEFRAG Rebuild Index Completely recreates the database index Not needed as often Can cause problems with SharePoint Fixed in SQL 2005 SP2 Uses T-SQL command ALTER INDEX Shrinking databases fragments your indexes and your data. Backup Can be done as part of maintenance plan. Three types Full Partial Differential Can use built in software or third party. Red-Gate software allow for database compression and encryption. Results in smaller backups Could also result in faster backups, if drive speed is the bottleneck Consider backing up to drive then tape Clean up History Cleans up old information from Maintenance Plans, SQL Agents and Backup and Restore Operations Leave as many jobs as you’d like T-SQL is shown in properties Maintenance plans can be altered via the UI Set up maintenance plans for different intervals; daily, weekly, monthly, quarterly, etc. Defrag file system 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 Should you shrink databases or logs? Database size is reduced by dropping unused space. Uses T SQL command DBCC SHRINKDATABASE Do not shrink databases unless something drastic has happened Massive site or content deletions Removing site collections from v2 databases Abandoning databases Has a heavy impact on the server 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 Properly configure SQL Surface Area for SharePoint Use SP2 if possible, it has improvements for SharePoint Do not change SharePoint databases via SQL Queries. Microsoft hates that. Can use SharePoint farm backups to back up SQL Don’t forget to include your System databases in your maintenance plans. SharePoint Service Pack 1 supports SQL 2008 My Blog http://www.toddklindt.com/blog Real World SharePoint http://www.wrox.com/WileyCDA/WroxTitle/productCd0470168358.html Prepare your database servers for SharePoint http://technet2.microsoft.com/windowsserver/WSS/en/library/f777 2626-cc01-4698-9dd8-958e60f7cb201033.mspx?mfr=true Diskeeper white paper on SQL file defragmentation http://files.diskeeper.com/pdf/SQLdefragmented.pdf Red-Gate Software http://www.red-gate.com/ Database Maintenance for SharePoint white paper by Bill Baer http://go.microsoft.com/fwlink/?LinkId=111531&clcid=0x409 © 2008 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.