Optimizing and Maintaining SQL and SharePoint Databases Part 1

2 minutes read

This is a live blogging post from the International SharePoint Conference London 2012. So don’t expect well-written proza here.

Session by Ben Curry & Wayne Ewington

Disk Subsystem

  • isolate your workloads
  • know what else runs on your storage subsystem
  • sql really needs dedicated spindles

Disk Design

  • anything under 20ms for the first write is unsupported
  • use more smaller disks over fewer larger disks
  • direct attached storage is generally the fastest
  • don’t use virtual disks when virtualizing sql servers
  • san is nice for snapshots
  • say no to nas
  • crawl database is by far the most demanding database
  • try to separate search from the rest of the databases
  • iops is the key requirement to gather
  • you always need an array of disks to meet iops requirements
  • raid 10 is the preferred config for service app databases
  • 2000 iops is more or less the minimal for SharePoint service app databases
  • initial capacity planning is more intuition than science
  • on average, 2.2 versions per document is common
  • on average, 220k per document is common

High Availability & Disaster Recovery

  • ha is about rto, rpo - the business wants 0, but what is feasible?
  • know the sql ha technologies
  • clustering has only one copy of the data
  • mirroring on transactions level, two copies of the data
  • sharepoint is mirroring aware
  • auto failover requires a witness server
  • all databases are supported for mirroring, but not all are recommended (technet guidance on this is wrong)
  • think hard on what problems you try to solve with remote blob storage before using it
  • log shipping works with log backup/restore (one to many)
  • you can combine clustering, mirroring and log shipping together!
  • small rpo/rto: clustering & sync mirroring
  • bigger rpo/rto: log shipping & backup/restore

Deployment

  • use sql aliases
  • database naming standards & no guids
  • who creates your databases?