Download - SQL Server Best Practices
![Page 1: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/1.jpg)
SQL Server Best Practices
Keep Your Database In Top Performance Shape and Maintain Effective Backups
September, 2007
Richard Kokoski
![Page 2: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/2.jpg)
SQL Server Best Practices
Abstract
Learn to keep your SQL 2000 and/or SQL 2005 database optimized with Reindexing. You’ll learn how do Reindexing, update statistics (for optimized queries) and “shrink” the database to eliminate unused or deleted data. Also learn how to create scheduled backup routines that include notifications (e-mail and paging) so SQL administrators know that their database is properly backed up. If you’re a SQL administrator (stakeholder) or a technical administrator (network and database), you’ll gain great insight and learn best practices necessary for effective disaster prevention and recovery.
![Page 3: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/3.jpg)
SQL Server Best Practices
Richard Kokoski
Fox D Tech LLC
•Microsoft Certified System Engineer (MCSE)•Microsoft Certified Solutions Developer (MCSD)•Microsoft SQL Server Product Specialist•B.S./M.B.A•19 Years Technology Experience•13 Years SQL Server Experience•6 Years Starbuilder Experience/Customized Solutions
![Page 4: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/4.jpg)
SQL Server Best Practices
Topics of Discussion
Backup Database•Backup Types•Scheduling•Notifications
Optimize Database• Database Integrity Checks
• Rebuild/Reorganize Indexes
• Update Statistics
• “Shrinking”
![Page 5: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/5.jpg)
SQL Server Best Practices
• Full Backup
• Complete Backup of Database at Point in Time
• Definitive Restore Point
• Easy Copy of Database to Separate Server
• Most Common for Smaller Size Databases
• Lost Data Minimized to Last Full Backup
Backup Database – Backup Types
![Page 6: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/6.jpg)
SQL Server Best Practices
• Differential Backup
• Backup of Database Data Since Last Full Backup
• Full Backup + Differential Backup Restores to Point in Time
• Faster & Smaller Backup than Full Backup
• Most Common for Larger Size Databases
• Lost Data Minimized to Last Full + Differential Backup
Backup Database- Backup Types
![Page 7: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/7.jpg)
SQL Server Best Practices
• Transaction Log Backup
• Backup of Database Data Since Last Transaction Log Backup
• Full Backup + Transaction Log “Rolling Forward” Restore to Point in Time
• Faster & Smaller Backup than Full Backup and/or Differential Backup
• Most Common for Databases Needing “Real Time” Backups
• Lost Data Minimized to Last Full + Series of Transaction Log Backups
Backup Database- Backup Types
![Page 8: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/8.jpg)
SQL Server Best Practices
• Full & Differential Backups
• Usually Scheduled Every Night
• SQL Backup Files Part of Overall Network Backup
• Ability to “Ad-Hoc” vs. Schedule
• Definitive Restore Point
• May Loose Some Data Because of Scheduling
Backup Database- Scheduling
![Page 9: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/9.jpg)
SQL Server Best Practices
• Transaction Log Backups
• Usually Scheduled More Often then Every Night
• Made for “Real Time”
• Definitive Restore Point
• Best Used to Reduce Lost Data Because of More Frequent Scheduling
Backup Database- Scheduling
![Page 10: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/10.jpg)
SQL Server Best Practices
Backup Database- Notifications
• Notification Types• Email
• “Net Send”
• Pager
• What to Send• Text Messages
• Customized Notes & Logs
• Operators• Usually Network and/or SQL Server Administrators
• Multiple Operators May be Defined
![Page 11: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/11.jpg)
SQL Server Best Practices
• Database Integrity Checks
• Overall “Health” of your database
• Consistency Checks of Data and Indexes
• Structurally and Physically Sound
• Identify Possibly Corruption Issues
• Advance Warning in Order to Fix
• Attempt to Correct “Minor” Issues (Single User Mode)
Optimize Database
![Page 12: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/12.jpg)
SQL Server Best Practices
• Rebuild/Reorganize Indexes
Optimize Database
•What are Indexes?• Descriptors to Data in Tables & Views
•How Are/Who Uses Indexes• Querying Database
• Stored Procedures/Query Plans
•Why Rebuild Indexes• Corruption Issues
• Speed Enhancements
• Adjust “Free Space per Page” to Optimize Data Inserts
![Page 13: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/13.jpg)
SQL Server Best Practices
• Update Statistics
Optimize Database
•What are Statistics?• Information & Distribution of Data for Table/View Indexes
•How Are/Who Uses Statistics?• Query Planning/SQL Server Query Optimizer
•Why Update Statistics?• Auto Update vs. Manual Update
• SPEED - Overall Benefit of Faster Querying for the Database
• Let’s SQL Optimizer Make Better Judgments About Data Access
![Page 14: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/14.jpg)
SQL Server Best Practices
• “Shrinking”
Optimize Database
•What is “Shrinking”?• Free Wasted Disk Space in Database of “Inactive” Data
• Reduce Disk Space for SQL Log Files As well (After Truncate)
•Why “Shrink” The Database?• Improved Database Performance
• Eliminate Unnecessary Disk Space/Backup Space
• Improve Backup Times
• *Note: Shrinking Only Reads To Be Done When A lot of Data Is Purged
![Page 15: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/15.jpg)
SQL Server Best Practices
Demonstration
![Page 16: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/16.jpg)
SQL Server Best Practices
Session Review
Backup Database•Backup Types•Scheduling•Notifications
Optimize Database• Database Integrity Checks
• Rebuild/Reorganize Indexes
• Update Statistics
• “Shrinking”
![Page 17: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/17.jpg)
SQL Server Best Practices
Questions?
![Page 18: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/18.jpg)
SQL Server 2000
SQL Server 2000 Enterprise Manager
![Page 19: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/19.jpg)
SQL Server Best Practices
![Page 20: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/20.jpg)
SQL Server Best Practices
![Page 21: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/21.jpg)
SQL Server Best Practices
![Page 22: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/22.jpg)
SQL Server Best Practices
![Page 23: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/23.jpg)
SQL Server Best Practices
![Page 24: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/24.jpg)
SQL Server Best Practices
![Page 25: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/25.jpg)
SQL Server Best Practices
![Page 26: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/26.jpg)
SQL Server Best Practices
![Page 27: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/27.jpg)
SQL Server Best Practices
![Page 28: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/28.jpg)
SQL Server 2005
Download Microsoft SQL 2005 Management Studio Express Edition (http://go.microsoft.com/fwlink/?linkid=65110)
SQL Server 2005 Management Studio Express Edition
(http://www.microsoft.com/downloads/details.aspx?familyid=993c0bcf-3bcf-4009-be21-27e85e1857b1&displaylang=en)
Download MSXML 6.0
![Page 29: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/29.jpg)
SQL Server Best Practices
![Page 30: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/30.jpg)
SQL Server Best Practices
![Page 31: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/31.jpg)
SQL Server Best Practices
![Page 32: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/32.jpg)
SQL Server Best Practices
![Page 33: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/33.jpg)
SQL Server Best Practices
![Page 34: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/34.jpg)
SQL Server Best Practices
![Page 35: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/35.jpg)
SQL Server Best Practices
![Page 36: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/36.jpg)
SQL Server Best Practices
![Page 37: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/37.jpg)
SQL Server Best Practices
![Page 38: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/38.jpg)
SQL Server Best Practices
![Page 39: SQL Server Best Practices](https://reader035.vdocuments.us/reader035/viewer/2022062315/56814fe3550346895dbdacdf/html5/thumbnails/39.jpg)
SQL Server Best Practices