Understanding SQL Server Database Options for Microsoft Office

Understanding SQL Server Database Options for Microsoft Office Share. Point Server 2007 Todd Klindt, WSS MVP Solanite Consulting www. toddklindt. com/blog www. solanite. com

Who is this Todd guy? WSS MVP since 2006 Speaker, writer, consultant, aquarius Personal Blog www. toddklindt. com/blog Company web site www. solanite. com E-mail todd@solanite. com All around good guy

Session Objectives And Agenda Introduction to how Share. Point uses SQL Comparison of different versions of SQL and which is best The proper care and feeding of SQL Server for the Share. Point administrator Maybe a SQL 2008 demo or two

SQL? I’m a Share. Point Admin! How does Share. Point use SQL? WSS & MOSS Farm configuration is stored in SQL. All Share. Point 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

How Share. Point uses SQL MOSS Only Each SSP gets a database to store settings Each SSP gets a Search database Each SSP gets a Content database

Which version of SQL should I use? Decisions, Decisions. . 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

SQL 2005 SP 2 Consider SP 2 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

How many bits, 32 or 64? 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

Express, Workgroup, Standard or Enterprise? 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.

SQL Express options Download Express Edition Toolkit from https: //www. microsoft. com/downloads/details. aspx? familyi d=3 C 856 B 93 -369 F-4 C 6 F-9357 C 35384179543&displaylang=en Download Management Studio, https: //www. microsoft. com/downloads/details. aspx? familyi d=C 243 A 5 AE-4 BD 1 -4 E 3 D-94 B 85 A 0 F 62 BF 7796&displaylang=en Can be upgraded to Standard or Enterprise Install Standard or Enterprise with the following syntax: setup. exe SKUUPGRADE=1

SQL 2005 Workgroup Edition 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

SQL 2005 Standard Edition 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

SQL 2005 Enterprise Edition 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

How to keep your SQL server happy 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

Maintain Databases Check Database Integrity Verifies integrity of databases Uses T-SQL command DBCC checkdb Very disk and CPU intensive Update 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

Working with Indexes 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 Share. Point Fixed in SQL 2005 SP 2 Uses T-SQL command ALTER INDEX Shrinking databases fragments your indexes and your data.

Doing Backups in your maintenance 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 and maintenance notes 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.

History Cleanup Screenshots

Defragment File System 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

To Shrink or not to shrink 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 v 2 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

Random SQL and Share. Point notes Properly configure SQL Surface Area for Share. Point Use SP 2 if possible, it has improvements for Share. Point Do not change Share. Point databases via SQL Queries. Microsoft hates that. Can use Share. Point farm backups to back up SQL Don’t forget to include your System databases in your maintenance plans. Share. Point Service Pack 1 supports SQL 2008

Resources My Blog http: //www. toddklindt. com/blog Real World Share. Point http: //www. wrox. com/Wiley. CDA/Wrox. Title/product. Cd 0470168358. html Prepare your database servers for Share. Point http: //technet 2. microsoft. com/windowsserver/WSS/en/library/f 777 2626 -cc 01 -4698 -9 dd 8 -958 e 60 f 7 cb 201033. 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 Share. Point white paper by Bill Baer http: //go. microsoft. com/fwlink/? Link. Id=111531&clcid=0 x 409

Questions?

© 2008 Microsoft Corporation. All rights reserved. Microsoft, 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.