Sep 17
The Do's and Don’ts of SQL Server and SharePoint

SQL SERVER STORAGE CONSIDERATIONS


DB Naming Conventions

  • Rename the SharePoint_Content DB to remove the GUID that follows the name.  This will reduce fragmentation.
  • Install the Server Bits
    • Before continuing through the installation wizard to configure the farm you will want to run a psconfig command to create the SharePoint_Config DB and set Farm credentials using stsadm.
    • You can do this using a script that Jim Grabinski created
      • psconfig -cmd configdb -create -server ALIAS-XXX -database SharePoint_Config -user DEMO\spfarm -password -admincontentdatabase SharePoint_Admin_Content

   

DB Sizing

  • Size the databases ahead of time
    • Content 60mb
    • Config 10mb
  • The max upload size of the webapp should match the auto grow size for the DB
    • DB properties>File>
  • Ideally you should size the DB and not allow auto growth
    • This will reduce and prevent fragmentation of the DB and
    • Keep the disk contiguous.   

   

Host Bus Adapter

  • Settings that need to be configured
    • Driver mismatch
    • Queue Depth 34 or higher


Configure correct NTFS Allocation size

  • 64k best; default is 4k which results in a 30% performance hit
  • (4k) is great for Exchange and File Servers
  • Run chkdsk <drive Letter> to verify Allocation size
    • To set: format E: /Q /FS:NTFS /A:64K /V:Data1 /Y


Ensure correct Windows "Sector Alignment"

  • Up to a 50% performance hit if incorrectly set!
  • Use a WMI script (ReportOffset.vbs) - Default in Server 2008

 

Separate LUNS

  • Data and Logs should be placed on different disk spindles and SAN arrays
  • Quick view locations for data and logs
    • C:\OS
    • D:\SQL Data Files
    • E:\SQL Log Files
    • F:\tempdb
    • G:\SQL Backup
  • Set the default locations for the data and log files to a location that is extensible. (Meaning logs can grow large!)
  • RAID Level - Latency should be less than 10ms for data and less than 5 ms for transaction logs
    • Use sqlio.exe to measure I/O performance
  • Try to use RAID 1+0 for best DB performance


Fastest drives to slowest drive in this order should be considered

  • The Temp DB should be on the fastest drive
  • The transaction log files
  • Search DB
  • Content DB


For Enterprise Deployments

  • Temp DB, Content DB, and Transaction logs should be on separate LUNS
  • Keep SQL Server auto grow on, but try to set it to an appropriate size
  • Use dedicated DB's for large site collections >50GB
  • Configure the Temp DB to be at least 10% of total content DB size or the size of the largest table
  • Increase DB memory to 16GB!!!
  • Make certain the Processor L2 cache > 2MB
  • Disk Latencies of < 10msec
  • SQL Max Memory
  • Use Dedicated SQL Server
  • Service Pack updates
  • You can theoretically scale out beyond 4 TB of data – beyond the 100 gig recommended threshold, however you should use DPM or SQL backups instead of other systems such as Backup Exec and Tivoli systems to do backup on these systems
  • Use SQL Server connection Alias!!!
    • This simplifies redirecting WFE's to a different db instance
  • Use Transparent Data Encryption (TDE)
  • Monitor performance regularly
    • Use DMV's Dynamic Microsoft
    • Check Integrity regularly (daily if possible)
      • DBCC CHECKDB
        • Do not use Reapir_Allow_Data_Loss - Not Supported!
    • Data Collection and Management Data Warehouse
    • Timeline of data captured
    • PERFMON
    • Wait Statistics
    • SQL Server Activity
    • Fragmentation
    • Rebuild and Reorganize Indexes to eliminate fragmentation daily
      • Between 10-70% do nothing
      • If its greater than 70% perform the operations
    • Shrink DB Size - Do not do!!!
      • Only shrink the content DB if free space is greater than 50%
      • Do not perform this as a maintenance plan
        • This is not a supported state according to Microsoft


High Availability

  • Failover Clustering
  • DB mirroring
    • Synchronous and Asynchronous Mirroring
    • Asynchronous Mirroring only supported for content DB
    • Synchronous Mirroring only supports single WFE
    • Only Mirror 50 or less DB's
    • When using auto failover, verify and tune appropriately to avoid false triggers
    • Failover all mirrored DB's together

Log Shipping

  • Enable backup compression for SQL server 2008
  • When using full backup model for point in time recovery choose to truncate transaction logs file!!!


Perform multiple differentials or log backup's multiples times a day

Setup Alerts!!!

Comments

There are no comments for this post.

 ‭(Hidden)‬ Blog Tools