Transcript presentation - Ramblings of a Crafty DBA

Designing Custom
Maintenance Plans with
TSQL
By John Miner
Why implement plans?
• Prevent loss of data.
– Hardware failure
– Unintentional software action
– Database corruption
• Provide good server performance to users.
– Ordered Indexes
– Up-to-date statistics
Surprising how many systems do not have these plans in place
correctly.
What’s not covered!
• Maintenance Plan Wizard – a wizard that steps
the user through the process of setting up
basic plans with limited options.
• Maintenance Plan Designer – a drag-and-drop
GUI interface in SSMS that facilitates the
design and creation of somewhat more
customizable plans.
• Powershell – extremely advanced scripting
performing tasks against more than one
database at a time.
Core Maintenance Plan Tasks
This presentation will review how to build a
custom maintenance plans from the ground
up using TSQL commands.
1.
2.
3.
4.
5.
6.
7.
Verify integrity of databases.
Backup databases (full versus differential).
Backup logs
Maintain database indexes.
Maintain index/column statistics.
Remove older data from [msdb].
Remove older backups from file system.
Before Creating Plans
• Database Mail has to be setup to send
notifications.
• At least on Operator needs to be configured to
receive the messages.
• Active Directory distribution lists should
assigned to the Operator.
Check Database Integrity
• Database corruption might not show up right away
and sometimes require full restore from old backup
to fix.
• Use DBCC CHECKDB (‘db name’) for small
databases.
• Adding the NOINDEX option for larger databases
to reduce time by not checking non-clustered
indexes.
• Considering executing as off-line operation due to
intense resource usage.
With modern hard disk systems, this task is largely
overlooked.
Recovery Models
• Simple Recovery – transaction log is automatically
truncated during periodic checkpoints and can’t be
used for recovery.
• Full Recovery – transaction log is not automatically
truncated during periodic checkpoints and can be
used for recovery.
• Bulk-Logged Recovery - Same as Full Recovery
except for bulk operations are minimally logged
saving significantly on processing time, but
preventing point-in-time restore options.
Full Backup – TSQL Example
BACKUP DATABASE @VAR_NAME
TO DISK = @VAR_FILE
WITH
FORMAT,
INIT,
NAME = @VAR_DESC,
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10;
Full Backup Described
• Provides a full copy of the data so that it can
be used to restore a database after a given
event.
• The database name, backup file name, and a
description must be supplied.
• Using the FORMAT and INIT options, any
previous backups are over-written.
• The NOFORMAT and NOINIT options allow for
appending data.
Do not append data since backup files can become
quite large.
Differential Backup – TSQL
Example
BACKUP DATABASE @VAR_NAME
TO DISK = @VAR_FILE
WITH DIFFERENTIAL,
FORMAT,
INIT,
NAME = @VAR_DESC,
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10;
Differential Backup
Described
• Provides a copy of the data changes since the
last full backup.
• The database name, backup file name, and a
description must be supplied.
• Using the FORMAT and INIT options, any
previous backups are over-written.
• The NOFORMAT and NOINIT options allow for
appending data.
Must use recent full backup, recent differential
backup, and all transaction logs to restore to a
given point-in-time.
Log Backup – TSQL Example
BACKUP LOG @VAR_NAME
TO DISK = @VAR_FILE
WITH
NOFORMAT,
NOINIT,
NAME = @VAR_DESC,
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10;
Log Backup Described
• Provides a copy of the data changes recorded
in the log file.
• The log file is truncated after this action. Log
files can grow uncontrollably if not done.
• Appending log backups in the same file allows
for easier restoring. Do this if the database
size is small.
Chose a periodic daily execution time that balances
business needs versus acceptable data loss (15, 30
or 60 minutes).
Maintain Indexes
• REBUILD versus REORGANIZE options of ALTER
INDEX command.
• Both commands reduce the amount of
fragmentation in Indexes which increases database
performance.
• REBUILD is a very server intensive command but is
quick. Only perform during off-line hours.
• REORGANIZE can be performed during on-line
hours but is slower.
• One must run the UPDATE STATISTICS command
on the object after a REORGANIZE operation.
Maintain Statistics
• The ‘sp_updatestats’ stored procedure can be
executed in the current database.
• Updates only the statistics that require
updating based on the rowmodctr information
in the sys.sysindexes catalog view.
• Can cause stored code to be recompiled.
• Gives Query plan optimizer better information
to work with.
Remove data from [msdb]
• The stored procedures below are used to clear
[msdb] data by oldest date pertaining to
backup sets, job history, and maintenance
plans.
‘msdb.dbo.sp_delete_backuphistory’
‘msdb.dbo.sp_purge_jobhistory’
‘msdb.dbo.sp_maintplan_delete_log’
• While this is not necessary, having two year
old information is sometimes useless.
Remove older backups
• The undocumented ‘master.dbo.xp_delete_file’
stored procedure can be used.
• Deletes backup (*.bak), transaction (*.trn)
and report files (*.txt).
• Physically examines the contents of the file
before deleting.
• Must specify last file date to keep on disk.
Tape Rotation (GFS)
• The Grand-Father-Son approach uses three
levels of tape rotation.
• Level 1 - daily backups
• Level 2 - weekly backups
• Level 3 - monthly backups
Tape Rotation (TOH)
• The Tower of Hanoi approach uses five levels
of tape rotation.
• Level A every 2 days; Level B every 4 days
• Level C every 8 days; Level D every 16 days
• Level E every 32 days
Best Practices (Daily)
• Full or Differential backups at the start of the
day.
• Transactional Log backups every hour.
• Copy backups from database server to archive
server.
• Swipe backups from archive server to tape
daily.
• Rotate tapes daily using either the GFS or
Tower of Hanoi schemes.
• Store tapes off site.
Best Practices (Weekly)
•
•
•
•
•
•
Verify the integrity of each database.
Remove old data from [msdb].
Remove old backups from server.
Rebuild or Reorganize Indexes *
Update statistics on changed tables *
Perform full backups on large databases. Use
daily scheme to secure backup.
* - Move to daily if time permits in over night
schedule.
Best Practices (Monthly)
• Send full tape backup of databases to parent
site.
• Restore backup from tape to test validity.
Biography
•
Has twenty years of data processing and proven project
management experience, specializing in the banking, health
care, and government areas.
•
His credentials include a Masters degree in Computer Science
from the University of Rhode Island; and Microsoft Certificates
(MCDBA & MCSA).
•
John is currently a Developing DBA at Sensata working with
SQL Server 2008 silo of products.
•
When he is not busy working, he spends time with his wife,
daughter and dog enjoying outdoor activities
Questions & Answers
• References
– “Brad's Sure Guide to SQL Server Maintenance
Plans”, Brad McGehee, Simple Talk Publishing
– "Best Practices for Backup and Restore in SQL
Server 2005", Javier Loria, Penton Media Inc.
– SQL Server Books Online http://msdn.microsoft.com/en-us/library/ms130214.aspx
• Please ask about the presentation.
• If you have any questions, you can contact me
at [email protected]