SQL Server for SharePoint geeks and other involuntary DBA’s
Today I’ve delivered another run of my SQL presentation for non-DBA’s at the Belgian Community Day 2012. It has evolved quite a bit over time, but 60 minutes still seems to be too short to do it properly 🙂
Key Takeaways from my session
- Respect software boundaries & limits, review the support article and read the maintenance whitepaper
- Use PowerShell to create your farm, the farm configuration wizard is bad
- Dedicate a SQL instance to SharePoint
- Differentiate between & prioritize your disks: tempdb, logs, search, other
- Align all partitions correctly (1024K) and format SQL partitions with the right cluster size (64K)
- Test your SQL disks before using them with sqlio
- Only install the features you need (database engine & maintenance tools at minimum)
- Use a domain user for service account
- Enable instant file initialization
- Configure maximum memory
- Fill factor to 80
- MAXDOP to 1
- Consider multiple data files for your content databases
- Tune autogrowth to a fixed number
- Presize your data & log files
- Use SQL aliases to connect SharePoint to SQL
- Check database integrity weekly with dbcc checkdb
- Learn about index fragmentation, how SharePoint deals with it and what you need to do manually
- Don’t shrink databases unless you’re very sure (causes fragmentation)
- Autoshrink is pure evil, never activate it – certainly not with SharePoint
- Learn about the full & simple recovery models and take a look at how logging & recovery works
- In full recovery model you always need to take log backups or your log file will grow indefinitely - no matter how many full backups you run
References
During my session, I made a reference to several resources:
- Brent Ozar’s Blitz! script, which I install on almost every SQL server I manage (it’s a stored procedure)
- Glenn Berry’s diagnostic queries for SQL 2005, 2008 & 2012, which come in handy to diagnose problems
- Performance Analysis of Logs (PAL), which does a tremendous job in analyzing perfmon logs
These are the places I usually go for SQL information: