SQL Server Best Practices - PowerPoint PPT Presentation

Loading...

PPT – SQL Server Best Practices PowerPoint presentation | free to download - id: 6dcf9a-M2I0Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

SQL Server Best Practices

Description:

Title: Slide 1 Author: rkokoski Last modified by: rkokoski Created Date: 9/20/2007 5:24:54 PM Document presentation format: On-screen Show (4:3) Company – PowerPoint PPT presentation

Number of Views:36
Avg rating:3.0/5.0
Slides: 40
Provided by: rko51
Category:
Tags: sql | best | practices | server | slide

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: SQL Server Best Practices


1
SQL Server Best Practices
  • Keep Your Database In Top Performance Shape and
    Maintain Effective Backups

Richard Kokoski
September, 2007
2
SQL Server Best Practices
  • Abstract
  • Learn to keep your SQL 2000 and/or SQL 2005
    database optimized with Reindexing. Youll 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 youre a SQL administrator
    (stakeholder) or a technical administrator
    (network and database), youll gain great insight
    and learn best practices necessary for effective
    disaster prevention and recovery.

3
SQL Server Best Practices
  • Richard Kokoski
  • Fox D Tech LLC
  • rkokoski_at_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

4
SQL Server Best Practices
  • Topics of Discussion
  • Backup Database
  • Backup Types
  • Scheduling
  • Notifications
  • Optimize Database
  • Database Integrity Checks
  • Rebuild/Reorganize Indexes
  • Update Statistics
  • Shrinking

5
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

6
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

7
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

8
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

9
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

10
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

11
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)

12
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

13
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
  • Lets SQL Optimizer Make Better Judgments About
    Data Access

14
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

15
SQL Server Best Practices
Demonstration
16
SQL Server Best Practices
  • Session Review
  • Backup Database
  • Backup Types
  • Scheduling
  • Notifications
  • Optimize Database
  • Database Integrity Checks
  • Rebuild/Reorganize Indexes
  • Update Statistics
  • Shrinking

17
SQL Server Best Practices
Questions?
18
SQL Server 2000
SQL Server 2000 Enterprise Manager
19
SQL Server Best Practices
20
SQL Server Best Practices
21
SQL Server Best Practices
22
SQL Server Best Practices
23
SQL Server Best Practices
24
SQL Server Best Practices
25
SQL Server Best Practices
26
SQL Server Best Practices
27
SQL Server Best Practices
28
SQL Server 2005
SQL Server 2005 Management Studio Express Edition
Download MSXML 6.0
(http//www.microsoft.com/downloads/details.aspx?f
amilyid993c0bcf-3bcf-4009-be21-27e85e1857b1displ
aylangen)
Download Microsoft SQL 2005 Management Studio
Express Edition
(http//go.microsoft.com/fwlink/?linkid65110)
29
SQL Server Best Practices
30
SQL Server Best Practices
31
SQL Server Best Practices
32
SQL Server Best Practices
33
SQL Server Best Practices
34
SQL Server Best Practices
35
SQL Server Best Practices
36
SQL Server Best Practices
37
SQL Server Best Practices
38
SQL Server Best Practices
39
SQL Server Best Practices
About PowerShow.com