Efficient SQL Database Maintenance for Performance

 
Keep your SQL database
clean and performant
Part 2
 
Michael Tobisch
dnnWerk.at
June 26
th
, 2023
 
Please support our valuable sponsors
 
Michael Tobisch, dnnWerk.at
 
2
 
Intro
   Maintenance   Backup/Restore
 
June 26
th
, 2023
Agenda
 
Implementing 
Ola Hallengren's Maintenance Solution 
and
Brent Ozar's First Responder Kit
 on SQL Server Express
Maintenance
Index optimization
Database integrity checks
Health assessment
Backup/Restore
Backup strategies
Restore Models
Automated restore testing
Michael Tobisch, dnnWerk.at
3
Intro
   Maintenance   Backup/Restore
June 26
th
, 2023
 
Award-winning SQL Server Backup, Integrity Check, and Index
and Statistics Maintenance by Ola Hallengren
SQL Server Agent is not available in SQL Server Express
Download from 
https://ola.hallengren.com/downloads.html
Install by running the Script 
MaintenanceSolution.sql
Michael Tobisch, dnnWerk.at
4
Maintenance Solution
Intro   
Maintenance
   Backup/Restore
June 26
th
, 2023
 
A free collection of scripts, worksheets and checklists by Brent
Ozar – helpful for setting up and maintaining SQL Server
Download from
https://downloads.brentozar.com/FirstResponderKit.zip
Install by running the Script 
Install-All-Scripts.sql
Michael Tobisch, dnnWerk.at
5
First Responder Kit
Intro   
Maintenance
   Backup/Restore
June 26
th
, 2023
 
Michael Tobisch, dnnWerk.at
 
6
 
Intro   
Maintenance
   Backup/Restore
 
June 26
th
, 2023
Michael Tobisch, dnnWerk.at
7
Database Integrity Check
Intro   
Maintenance
   Backup/Restore
 
Checks the integrity of
Databases
Tables
Indexes
File groups
Allocation structures
Catalog consitency
etc.
Detailled description of the parameters: 
https://ola.hallengren.com/sql-
server-integrity-check.html
Run this once a week
June 26
th
, 2023
Michael Tobisch, dnnWerk.at
8
Index Optimization
Intro   
Maintenance
   Backup/Restore
 
Rebuilds and reorganizes indexes and statistics
Detailled description of the parameters:
https://ola.hallengren.com/sql-server-index-and-statistics-
maintenance.html
Run this daily on all user databases
June 26
th
, 2023
Michael Tobisch, dnnWerk.at
9
sp_Blitz
Intro   
Maintenance
   Backup/Restore
 
Health Check Assessment that flags common issues and
gives a link to a web page with more in-depth advice
Quick start:
EXEC sp_Blitz @CheckUserDatabaseObjects = 0;
Detailled description: 
https://github.com/BrentOzarULTD/SQL-Server-
First-Responder-Kit/tree/main#sp_blitz-overall-health-check
 
June 26
th
, 2023
 
Michael Tobisch, dnnWerk.at
 
10
 
Intro   
Maintenance
   Backup/Restore
 
June 26
th
, 2023
Michael Tobisch, dnnWerk.at
11
Backup and Restore
Intro   
Maintenance
   
Backup/Restore
 
Important: Backup strategy
How much data loss is acceptable?
What is the maximum time a recovery can take?
Restore Model: Full or Simple?
Backup types:
Full
Differential
Transaction Logs
Backup intervals
 
 
June 26
th
, 2023
Michael Tobisch, dnnWerk.at
12
Examples
Intro   
Maintenance
   
Backup/Restore
June 26
th
, 2023
 
„Normal“ website with not too much change of content, users…
Recovery Model: SIMPLE
Daily FULL Backup 
or
 Weekly FULL Backup and daily DIFF backup
Keep backup files for 1 week
E-Commerce site
Recovery Model: FULL
Weekly FULL Backup
1-2 DIFF Backups/day
LOG backups every 5-10 minutes (even 1 minute!)
Keep backup files for 2 – 3 weeks
 
 
 
Michael Tobisch, dnnWerk.at
 
13
 
Intro   
Maintenance
   
Backup/Restore
 
June 26
th
, 2023
Michael Tobisch, dnnWerk.at
14
Automated Database Restore Tests
June 26
th
, 2023
 
Using Brent Ozar´s sp_DatabaseRestore
@RestoreDatabaseName = 
Something different!
@TestRestore = 1
 – Drops the database after the Restore
@RunCheckDB = 1
 – Performs a Database Integrity Check
 
 
 
Intro   
Maintenance
   
Backup/Restore
 
Michael Tobisch, dnnWerk.at
 
15
 
June 26
th
, 2023
 
Intro   
Maintenance
   
Backup/Restore
 
Michael Tobisch, dnnWerk.at
 
16
 
June 26
th
, 2023
 
Intro   
Maintenance
   Backup/Restore
 
Michael Tobisch, dnnWerk.at
 
17
 
June 26
th
, 2023
 
Intro   
Maintenance
   Backup/Restore
Slide Note

Keep your SQL database clean and performant

Embed
Share

Learn to effectively clean and optimize your SQL database for optimal performance. Discover tools like Ola Hallengren's Maintenance Solution and Brent Ozar's First Responder Kit, along with best practices for backup, restore, index optimization, and database integrity checks. Ensure a healthy database environment for improved efficiency.


Uploaded on Sep 07, 2024 | 0 Views


Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

E N D

Presentation Transcript


  1. Keep your SQL database clean and performant Part 2 Michael Tobisch dnnWerk.at June 26th, 2023

  2. Intro Maintenance Backup/Restore Please support our valuable sponsors June 26th, 2023 Michael Tobisch, dnnWerk.at 2

  3. Intro Maintenance Backup/Restore Agenda Implementing Ola Hallengren's Maintenance Solution and Brent Ozar's First Responder Kit on SQL Server Express Maintenance Index optimization Database integrity checks Health assessment Backup/Restore Backup strategies Restore Models Automated restore testing June 26th, 2023 Michael Tobisch, dnnWerk.at 3

  4. Intro Maintenance Backup/Restore Maintenance Solution Award-winning SQL Server Backup, Integrity Check, and Index and Statistics Maintenance by Ola Hallengren SQL Server Agent is not available in SQL Server Express Download from https://ola.hallengren.com/downloads.html Install by running the Script MaintenanceSolution.sql June 26th, 2023 Michael Tobisch, dnnWerk.at 4

  5. Intro Maintenance Backup/Restore First Responder Kit A free collection of scripts, worksheets and checklists by Brent Ozar helpful for setting up and maintaining SQL Server Download from https://downloads.brentozar.com/FirstResponderKit.zip Install by running the Script Install-All-Scripts.sql June 26th, 2023 Michael Tobisch, dnnWerk.at 5

  6. Intro Maintenance Backup/Restore Demo June 26th, 2023 Michael Tobisch, dnnWerk.at 6

  7. Intro Maintenance Backup/Restore Database Integrity Check Checks the integrity of Databases Tables Indexes File groups Allocation structures Catalog consitency etc. Detailled description of the parameters: https://ola.hallengren.com/sql- server-integrity-check.html Run this once a week June 26th, 2023 Michael Tobisch, dnnWerk.at 7

  8. Intro Maintenance Backup/Restore Index Optimization Rebuilds and reorganizes indexes and statistics Detailled description of the parameters: https://ola.hallengren.com/sql-server-index-and-statistics- maintenance.html Run this daily on all user databases June 26th, 2023 Michael Tobisch, dnnWerk.at 8

  9. Intro Maintenance Backup/Restore sp_Blitz Health Check Assessment that flags common issues and gives a link to a web page with more in-depth advice Quick start: EXEC sp_Blitz @CheckUserDatabaseObjects = 0; Detailled description: https://github.com/BrentOzarULTD/SQL-Server- First-Responder-Kit/tree/main#sp_blitz-overall-health-check June 26th, 2023 Michael Tobisch, dnnWerk.at 9

  10. Intro Maintenance Backup/Restore Demo June 26th, 2023 Michael Tobisch, dnnWerk.at 10

  11. Intro Maintenance Backup/Restore Backup and Restore Important: Backup strategy How much data loss is acceptable? What is the maximum time a recovery can take? Restore Model: Full or Simple? Backup types: Full Differential Transaction Logs Backup intervals June 26th, 2023 Michael Tobisch, dnnWerk.at 11

  12. Intro Maintenance Backup/Restore Examples Normal website with not too much change of content, users Recovery Model: SIMPLE Daily FULL Backup or Weekly FULL Backup and daily DIFF backup Keep backup files for 1 week E-Commerce site Recovery Model: FULL Weekly FULL Backup 1-2 DIFF Backups/day LOG backups every 5-10 minutes (even 1 minute!) Keep backup files for 2 3 weeks June 26th, 2023 Michael Tobisch, dnnWerk.at 12

  13. Intro Maintenance Backup/Restore Demo June 26th, 2023 Michael Tobisch, dnnWerk.at 13

  14. Intro Maintenance Backup/Restore Automated Database Restore Tests Using Brent Ozar s sp_DatabaseRestore @RestoreDatabaseName = Something different! @TestRestore = 1 Drops the database after the Restore @RunCheckDB = 1 Performs a Database Integrity Check June 26th, 2023 Michael Tobisch, dnnWerk.at 14

  15. Intro Maintenance Backup/Restore Demo June 26th, 2023 Michael Tobisch, dnnWerk.at 15

  16. Intro Maintenance Backup/Restore Questions? June 26th, 2023 Michael Tobisch, dnnWerk.at 16

  17. Intro Maintenance Backup/Restore Thank you! June 26th, 2023 Michael Tobisch, dnnWerk.at 17

More Related Content

giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#giItT1WQy@!-/#