1 / 39

SQL Server Administration 101: Data Recovery, Integrity, and Performance

Learn the fundamentals of SQL Server administration for data recovery, data integrity, and performance optimization. This session covers backup and restore strategies, database integrity checks, and best practices for index maintenance and storage configurations.

breeder
Download Presentation

SQL Server Administration 101: Data Recovery, Integrity, and Performance

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. How to Feed & Care for a SQL Server SharePoint Saturday Virginia Beach 2014 SPSVB, 1/11/2014

  2. Bio • Brian Garraty • Solutions Architect at TPMG • Former DBA, Developer • 15 years with SQL Server SPSVB, 1/11/2014

  3. Platinum Sponsors Welcome to SharePoint Saturday Virginia Beach Jan 11, 2014 Gold Sponsors 7:30 - 8:30 - Registration(Lobby) Breakfast (Sponsor Hall)8:30 - 8:45 - Welcome 9:00 - 10:15 – 1st Sessions10:30 - 11:45 – 2nd Sessions11:45 - 12:30 – Lunch (Sponsor Hall)12:30 - 1:45 - 3rd Sessions2:00 - 3:15 – 4th Sessions3:30 - 4:45 – 5th Sessions5:00 - 5:30 - Closing & Giveaways Silver Sponsors

  4. Itinerary • SQL Server Administration 101 • Free Tools (that make it look easy) • SQL and SharePoint • SQL Community • Q&A SPSVB, 1/11/2014

  5. SQL Server Administration 101 It’s about the data SPSVB, 1/11/2014

  6. Show of Hands SPSVB, 1/11/2014

  7. Pillars • Data Recovery • Data Integrity • Performance SPSVB, 1/11/2014

  8. Recovery - Models • Simple • Full • Bulk Recovery SPSVB, 1/11/2014

  9. Recovery - Backups • Full • Differential • Transaction Log • Snapshot • Third Party SPSVB, 1/11/2014

  10. Recovery - Restores • First - Full • Then - Diff • Then - Log • STOPAT SPSVB, 1/11/2014

  11. Recovery – Post-restore • Reset Database Owner • Remap Users • Reset Recovery Model • Restart Backup Chain SPSVB, 1/11/2014

  12. Recovery - What Can Go Wrong • Permissions • Disk Space • Corrupt Database Backups files • Missing link SPSVB, 1/11/2014

  13. Recovery - Practice • Practice • Often • Tough to learn during an outage SPSVB, 1/11/2014

  14. Database Integrity • DBCC CHECKDB • Regularly • Scheduled • Monitor Results SPSVB, 1/11/2014

  15. Integrity – SQLIOSim • SQLIOSim – tests validity of storage • Download from support.microsoft.com SPSVB, 1/11/2014

  16. What Can Go Wrong • Corruption Detected • REPAIR_REBUILD – no data loss • REPAIR_ALLOW_DATA_LOSS – data loss possible • Don’t be afraid to call Microsoft Support SPSVB, 1/11/2014

  17. Performance • Index Maintenance • Rebuild • Reorganize • Online - Enterprise Edition SPSVB, 1/11/2014

  18. Performance - tempdb • Common bottleneck • Dedicated LUN – RAID 1+0, SSD • Multiple data files • Consistent size and growth settings • Trace Flag 1118 SPSVB, 1/11/2014

  19. Performance - storage • Deep topic • Basics • RAID 1+0 • Multiple LUNs • Dedicated LUNs • SSD for tempdb • Ask your vendor for their SQL best practices SPSVB, 1/11/2014

  20. Free Tools that make it look easy SPSVB, 1/11/2014

  21. ola.hallengren.com • Backup script • Integrity Check script • Index Maintenance script SPSVB, 1/11/2014

  22. sp_whoisactive • Monitoring script • Active connections • Sleeping connections • Blocking • Current query • CPU • Reads • Writes • More • Written and maintained by Adam Machanic SPSVB, 1/11/2014

  23. sp_blitz • Server Health Check • Security • Configuration • Performance • sp_blitz.exe – Gui version • Written and maintained by BrentOzar.com SPSVB, 1/11/2014

  24. SQL and SharePoint Can’t we all just get along? SPSVB, 1/11/2014

  25. Not Always on Same Page SPSVB, 1/11/2014

  26. Alas, a Whitepaper! • Search “Paul Randal SharePoint white paper” • http://technet.microsoft.com/en-us/library/cc262731.aspx SPSVB, 1/11/2014

  27. Do What You Do • Regular DB Maintenance Applies • Backups • DBCC CHECKDB • Index maintenance SPSVB, 1/11/2014

  28. Health Analyzer • Database maintenance rules • Does not cover all databases • I never bothered SPSVB, 1/11/2014

  29. Shrink Not • Cause for earlier disagreement • Should be done sparingly • Should be done fully informed SPSVB, 1/11/2014

  30. Other Best Practices • Windows Only Authentication • Enterprise Edition • MAXDOP 1 • Backup Compression on SPSVB, 1/11/2014

  31. Other – con’t • Tempdb • Dedicated drives • # • Size • Dedicated disk for log files • Autogrowthsettings • Instant File Initialization SPSVB, 1/11/2014

  32. Disclaimer • Actual results may vary SPSVB, 1/11/2014

  33. SQL Community Friends in a Digital Age SPSVB, 1/11/2014

  34. #sqlhelp • Twitter hashtag • Monitored by SQL experts and enthusiasts • Spam at own risk SPSVB, 1/11/2014

  35. SQL Blogosphere • SQLBlog.com • SQLSkills.com • BrentOzar.com SPSVB, 1/11/2014

  36. SQL Publications • Simple-talk.com • SQLServerCentral.com • DatabaseWeekly.com SPSVB, 1/11/2014

  37. User Groups • PASS • PASS Summit • SQL Saturday • Virtual Chapters • Local Chapters • HRSSUG SPSVB, 1/11/2014

  38. Q& A SPSVB, 1/11/2014

  39. Resources • TechNet SharePoint 2010 SQL Configcheck • Paul Randal's Blog • Adam Machanic’s Blog • BrentOzar.com • Microsoft presentation • NULLgarity.wordpress.com SPSVB, 1/11/2014

More Related