SANs and LUNs
· SAN configurations affect performance. Nowhere is that more important than with SQL Server. Every document, every list item, every search result, and every user profile is delivered out of a SQL Server database. If those databases are on a slow drive, then SQL Server is slow. If SQL Server is slow, then no amount of configuration trickery can make SharePoint fast. SQL Server really is that important
· The (virtualized) Production farm should have the best-performing LUNs that the SAN can provide
· Ideally, place the tempdb database, content databases, Usage database, search databases, and SQL
Server 2008 transaction logs on separate physical hard disks/LUNs/SAN drive
· Is the search index on a super fast SAN drive? Currently on D:\
· When prioritizing data among faster disks, best practice is to use the following ranking:
1. Tempdb data files and transaction logs
2. Database transaction log files
3. Search databases, except for the Search administration database -
4. Database data files
Optimization
· Limit the content database size to 100GB; use multiple content databases if necessary.
o 100GB is a soft limit imposed by backup times
o DBs are supported up to 200GB
· Create multiple data files and spread them across multiple disks; only create files in the
primary filegroup
· The number of data files should be less than or equal to the number of core CPUs; count dual
core processors as two CPUs; count each processor that supports hyper-threading as one CPU
· Create data files of equal size
Backups of Content DBs
· Governance policy required: e.g. Sites are backed up daily and recoverable up to 14 days
o How far back are sites recoverable now?
o How long do current backups take?
Planning content DBs
Myportal
o Content DB Name: SP_myPortal_CDB
o Read-only: No
o Site collections: 15
o Warning:2000
o Max:5000
Mysite
o Content DB Name: SP_MySite_CDB
o Read-only: No
o Site Collections: 60
o Warning:2000
o Max:5000
· The preferred way for creating databases is to have the DBA pre-create them so that you can just reference the database names when you configure your farm, or create service or web applications.
o The goal is to have the databases available ahead of time, complete with all the settings required by SharePoint
· When SharePoint creates its multitudes of databases, it makes assumptions. E.g. the autogrow settings:
o The database files grow by 1MB at a chunk, almost ensuring that they're going to autogrow with every upload. Not only does this slow down SQL Server (which slows down SharePoint), but it also results in database files that are spread all over your drives in 1MB chunks
o Best practice - Set autogrow to 500MB/1GB
o Autogrow should also be a last resort. Someone, either the SharePoint administrator or a dedicated DBA, should pregrow your databases so that autogrow is unnecessary.
· Recovery model setting needs to be consistent with your disaster recovery plans.
o Do we need transaction logs? If so make sure we're performing routine log backups to keep those .ldf files in check.
o If we don't need transaction logs, switch the databases to the simple recovery model. Doing so will keep your .ldf files from swelling up
· SharePoint only really cares about the Collation, which should be LATIN1_General_CI_AS_KS_WS. Growth settings, file locations and the like are configured at your own discretion. Naturally, you should figure them out when planning your deployment, taking into account the fact that content databases will grow larger (and more quickly) than configuration databases, and that databases for Search can experience some heavy hits in large environments
How is Content DB growth being monitored?
o Web applications are not restricted to a single content database, which means that you can spread site collections within that web application over multiple content databases, keeping them manageable from a backup point of view
o If we have 3 Content DBs - at what point do we need to make another? What is the process for adding a new content DB?
o Limiting DB size will improve manageability, performance, and ease of upgrade for the environment
· Capacity notes
· MS recommends a maximum of 300 content databases per web application in your farm
· MS recommends a maximum of 200 GB of content per content database
· MS recommends site collections be no more than 100 GB in size
· SharePoint's only disk requirement is an 80GB system drive
o SharePoint itself doesn't need much disk space: It uses about 1GB, excluding logs, search index files, and any custom solutions. But that disk also needs to hold Windows and all its associated patches for the next few years. And the disk needs enough space for SharePoint's logs, plus enough space to perform a memory dump in the unlikely event of a problem. Also, NTFS gets fussy when disks are more than 90-percent full, so leave enough space for some overhead, too.
o SharePoint requires at least 80GB, but sometimes that isn't enough. If a SAN is hosting your SharePoint drives, expanding that 80GB system drive to, say, 120GB is painless
