sql server best practices n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Server Best Practices PowerPoint Presentation
Download Presentation
SQL Server Best Practices

Loading in 2 Seconds...

play fullscreen
1 / 39

SQL Server Best Practices - PowerPoint PPT Presentation


  • 94 Views
  • Uploaded on

SQL Server Best Practices. Keep Your Database In Top Performance Shape and Maintain Effective Backups. Richard Kokoski. September, 2007. SQL Server Best Practices. Abstract

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Server Best Practices' - roman


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql server best practices

SQL Server Best Practices

Keep Your Database In Top Performance Shape and Maintain Effective Backups

Richard Kokoski

September, 2007

sql server best practices1

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.

sql server best practices2

SQL Server Best Practices

Richard Kokoski

Fox D Tech LLC

rkokoski@foxdtechllc.com

  • 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
sql server best practices3

SQL Server Best Practices

Topics of Discussion

  • Backup Database
    • Backup Types
    • Scheduling
    • Notifications
  • Optimize Database
    • Database Integrity Checks
    • Rebuild/Reorganize Indexes
    • Update Statistics
    • “Shrinking”
sql server best practices4

SQL Server Best Practices

Backup Database – Backup Types

  • 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
sql server best practices5

SQL Server Best Practices

Backup Database- Backup Types

  • 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
sql server best practices6

SQL Server Best Practices

Backup Database- Backup Types

  • 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
sql server best practices7

SQL Server Best Practices

Backup Database- Scheduling

  • 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
sql server best practices8

SQL Server Best Practices

Backup Database- Scheduling

  • 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
sql server best practices9

SQL Server Best Practices

Backup Database- Notifications

  • Operators
    • Usually Network and/or SQL Server Administrators
    • Multiple Operators May be Defined
  • Notification Types
    • Email
    • “Net Send”
    • Pager
  • What to Send
    • Text Messages
    • Customized Notes & Logs
sql server best practices10

SQL Server Best Practices

Optimize Database

  • 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)
sql server best practices11

SQL Server Best Practices

Optimize Database

  • Rebuild/Reorganize Indexes
  • 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
sql server best practices12

SQL Server Best Practices

Optimize Database

  • Update Statistics
  • 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
sql server best practices13

SQL Server Best Practices

Optimize Database

  • “Shrinking”
  • 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
sql server best practices15

SQL Server Best Practices

Session Review

  • Backup Database
    • Backup Types
    • Scheduling
    • Notifications
  • Optimize Database
    • Database Integrity Checks
    • Rebuild/Reorganize Indexes
    • Update Statistics
    • “Shrinking”
slide18

SQL Server 2000

SQL Server 2000 Enterprise Manager

slide28

SQL Server 2005

SQL Server 2005 Management Studio Express Edition

Download MSXML 6.0

(http://www.microsoft.com/downloads/details.aspx?familyid=993c0bcf-3bcf-4009-be21-27e85e1857b1&displaylang=en)

Download Microsoft SQL 2005 Management Studio Express Edition

(http://go.microsoft.com/fwlink/?linkid=65110)