SQL Server for the SharePoint Administrator

Document Sample
SQL Server for the SharePoint Administrator Powered By Docstoc
					SQL Server for the SharePoint

Dr Greg Low
Who am I?
 Managing Director and Mentor – SolidQ
   Host of SQL Down Under Podcast
   Microsoft Regional Director
   Microsoft MVP for SQL Server
   Organizer of SQL Down Under CodeCamp
   Author with MS Press, SQL Server Magazine
    and Rational Press
Module Overview

   Installation and versions
   Care and feeding
   High availability options
   Lots of Q&A
Why Does SQL Server Matter?

 SharePoint lives almost entirely in SQL Server
   Remote blob store is an exception but run away !
 Applies to both WSS and MOSS
 Sometimes described as a database in a table
Multiple Databases

 Central admin
 Search
 One or more content databases
Content Databases

 Microsoft suggest a limit of 100GB per content DB
 Each web app has one or more
 Each site collection needs to live entirely in a single
  content DB
 A single content DB may contain multiple site collections
SQL Server Versions

 For new systems: SQL Server 2008
   with SharePoint 2007 SP1 or later
 Potentially implement on SQL Server 2005
 Leave SQL Server 2000 behind now!

 Implement 64 bit instead of 32 bit whenever possible

 WIDS is easy (from Basic Install) but a dead-end
 Express OK for small systems
SQL Server Editions

 Web
 Express
   WID is similar but not the same
 Workgroup
 Standard
 Enterprise
Files and Filegroups

 Databases consist of filegroups
 Filegroups consist of files
    mdf
    ldf
    ndf
 Files need to be appropriately sized
 Empty space is a good thing
 Autogrow should be a last resort!
 Instant File Initialization
Transaction Logs

 Allow for recovery after failure
 Allow for point in time recovery
 Allow for HA via log-shipping
System Databases

 master
 Resource (hidden)
 msdb
 Distribution (only with replication)
 tempdb

 Used as a “scratch” database
   By users to create temporary objects
   By SQL Server to store temporary result sets during query
 One tempdb per instance
   Misbehaving application/session can use up all resources
   Does not persist across SQL Server restarts
   Recreated using model
   No Checkpoint behavior
   Single filegroup for data
SQL I/O Requirements

 mdf & ndf file I/O characteristics
 ldf file I/O characteristics

 Core I/O requirements
    Stable media
    Write ordering
    Torn I/O avoidance

 Stress test I/O subsystems with SQL utilities prior to
SQL I/O Changing Technologies

 SSDs rapidly being deployed
 FusionIO-style drives worth considering
Database Encryption

 SQL Server 2008 Enterprise only
 Easy to turn on but...

 Row compression
 Page compression
 Very dependent on content type

 Counter-intuitive effect on performance?

 Backup compression
   Not a good mix with database encryption
Database and File Properties
Module Overview

   Installation and versions
   Care and feeding
   High availability options
   Lots of Q&A
Maintenance Plans

 Wizard based
 Many options
   Backups
   Index rebuilds/defrags
   Check integrity
   Shrink?
Shrinking Databases

 Autoshrink
 Shrink during maintenance plan

 End result -> filesystem fragmentation

 In general, never do this! Plan database sizes
Avoiding Corruption Issues

 SQL 2000 -> Torn Page Detection
 SQL 2005+ -> Page Checksums

   Fairly intensive
Transaction Log Maintenance

 Cleared via BACKUP LOG
 Unmanaged, will consume your drive
 Allow for point in time recovery
   tempdb and Restart

  • Initial tempdb configuration
         tempdev primary data             Tempdb.mdf            8.0 MB
         templog log                      Templog.ldf           0.5 MB

               DBA sets the              Operations in tempdb            SQL Server
               tempdb size                cause database to                   is
               appropriately                  autogrow                    restarted

     Initial                   DBA set                      Working          After Restart
     State                      State                        State               State

tempdev 8.0 MB            tempdev 200 MB                tempdev 500 MB     tempdev 200 MB
templog 0.5 MB            templog 50 MB                 templog 140 MB     templog 50 MB
tempdb Files

 Multiple tempdb files?
 Microsoft advice
   One file per core
 Paul Randall advice
   Not quite so many
SQL Server Indexes

 Clustered vs Nonclustered
 Fragmentation
 Reindex
   Logical defrag of indexes
 Rebuild
   complete rebuild

 Critical for performance
 Used by the optimizer to choose between indexes
   Row counts
   Selectivity
Moving Databases

 Detach
 Attach
 Potential SID issues on SQL logins when moving
  between servers
   Not an issue for domain accounts
Index Fragmentation & Statistics
Module Overview

   Installation and Versions
   Care and feeding
   High availability options
   Lots of Q&A

 Transaction log backups shipped
 Automated backup, copy, restore
 Can take advantage of compression
 Easy to implement and troubleshoot
 Index operations can cause log size issues
Failover Clustering

 Most commonly implemented HA technology for SQL
   Shared disk subsystem
   Multiple nodes possible
   Entire server is failed over
   Geo-clusters complex to implement
   Much easier to implement in SQL Server 2008
Database Mirroring

 Transaction logs auto-replayed on partner server
 Available since SQL Server 2005 SP1
 Database level technology, not server level
 Server object maintenance is a significant issue
Module Overview

   Installation and Versions
   Care and feeding
   High availability options
   Lots of Q&A
Session Summary

 Some SQL Server knowledge critical for
  SharePoint administrators
 More SQL Server knowledge needed for expert
  SharePoint administrators
Upcoming Melbourne Training
 Advanced T-SQL
   5 days from 6th July
 SQL Server Bootcamp for SharePoint Admins
   3 days from 1st July
 Solving Business Problems with MDX
   3 days from 23rd September
 Designing and Developing Solutions with Analysis Services
   4 days from 14th July
 Working with SQL Server 2008 Integration Services
   2 days from 3rd August
 Working with SQL Server 2008 Reporting Services
   2 days from 21st August
Rushabh Mehta coming to visit

 End to End Business Intelligence
   Melbourne 5th October (5 days)
 PASS Executive Vice President
 Also heading to the SQL Code Camp
Places to be!

 PASS Community Connection
   (SDU Code Camp tab)
   October 10th and 11th Wagga Wagga
 PASS U.S. Summit
   November 2th to 5th Seattle

Shared By: