1 / 14

Tuning SQL Server for SharePoint Jump Start

Tuning SQL Server for SharePoint Jump Start. Bill Baer | Senior Product Marketing Manager, Microsoft Brian Alderman | Chief Executive Officer | MicroTechPoint. Course Topics. Module Agenda. Avoiding Large T-Log Files Backups; Location, Location, Location Enable SQL Server AlwaysOn

lamont
Download Presentation

Tuning SQL Server for SharePoint Jump Start

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Tuning SQL Server for SharePoint Jump Start Bill Baer | Senior Product Marketing Manager, Microsoft Brian Alderman | Chief Executive Officer | MicroTechPoint

  2. Course Topics

  3. Module Agenda • Avoiding Large T-Log Files • Backups; Location, Location, Location • Enable SQL Server AlwaysOn • Verify Integrity of Databases • SQL Server for SharePoint Best Practices

  4. Simple Recovery Model Content Database Located on Hard Drive .MDF .LDF Data Add Content Checkpoint Data Simple Recovery Model

  5. Full Recovery Model Content Database Located on Hard Drive .MDF .LDF Data Data Add Content Checkpoint Data Data Full Recovery Model (Recommended)

  6. Preventing Ginormous T-Logs Tuesday Wednesday Sunday Monday .ldf .ldf .ldf .mdf .ldf .mdf .mdf .mdf Differential Full Backup Differential Differential (3) (2) Backup Log DB_Nameto D:\SQLBackups\Weekly_T_Log.Bak with INIT Backup Log DB_Nameto D:\SQLBackups\TempBackup.Bak (1) (4) .ldf .mdf Lose .mdf file of database on Thursday at 4:00pm

  7. Backups; Location, Location, Location • Local Copy for Quick Recovery • Same Room, Same Floor, Same Building, Same City? • Remote Copy for Large Disasters • Same County, Same State, Same Geographical Location?

  8. SQL Server 2012 AlwaysOn • Requires Windows Server Failover Clustering • Builds on SQL Server Mirroring • Not a Replacement for Database Backups • Components • Availability Groups • Availability Databases • Availability Replicas • Availability Modes • Failover Types

  9. Verify Integrity of Databases • DBCC CheckDB • Check REPAIR_REBUILD Option to Fix Errors (Not Always Possible) • REPAIR_ALLOW_DATA_LOSS Not Supported • Time Consuming Operation, Run During Non-Peak Hours • For Very Large DBs consider using option MAXDOP=1

  10. SQL Server for SharePoint Best Practices

  11. SQL Server for SharePoint Best Practices • Dedicated SQL Server / Instance • Modify Tempdb and Model System Database Settings • Create Multiple TempDB Files on Multiple Disks • Spread Data Files and Transaction Log Files Across Multiple Physical Drives or Locate on RAID 5/10 • Use Database Autogrowth Sparingly (Insurance Policy) • Reduces Fragmentation • Improves Performance

  12. SQL Server for SharePoint Best Practices • Create SQL Server alias • Content Databases < 200 GB • Avoid shrinking database files • Check database integrity with DBCC CHECKDB • Monitor disk seconds per read/write (<20ms) • Defragment SQL Server drives containing content database files • Perform regular backups of database and transaction logs

  13. Summary • Avoid Ginormous T-Logs by Performing Regular T-Log Backups • Have Multiple Copies of Your Backups • Configure AlwaysOn for High Availability • Verify Integrity of SQL Server Databases • SQL Server for SharePoint Best Practices

More Related