Be Proactive: A Good DBA Goes Looking for Signs of Trouble

Database Administrators (DBAs) are busier than ever these days, so fighting fires is the last thing they want to spend their time doing. A proactive approach to database performance and maintenance helps keep DBAs ahead of trouble and prevent little problems from turning into full-blown emergencies.

routine database maintenance
o Most DBAs take full backups at regular intervals
o DBAs also need to consider implications of backups concerning recovery
o Backup frequency determines potential data loss from recovery

backups
o RPO
- Recovery point objective (RPO): Maximum amount of data organization is willing to lose after recovery
- RPO dictates backup schedule rather than other way around
- Cost of achieving zero data loss is usually prohibitively high
o backup all databases
- Backup system databases as welll as user databases
- Without system database backups, new user databases will not have any agent jobs, logins, and permissions
o verification
- Use CHECKSUM option during backup to perform corruption checks
- Use RESTORE VERIFYONLY to ensure that backups are complete and readable without restoring database
o backup types
- Full backup: Contains all data in database
- Differential backup: Contains only data that has changed since last full backup
. Recovery: Need to restore most recent full backup before recovery point, followed by most recent differential backup
- Log backup: Latest full backup plus all transactions that occurred since previous log backup.
. Recovery: Restore log backups in chronological order to reach recovery point
- Full copy only backup: Independent of regular backup sequence since it does not modify log sequence number (LSN)

recovery
o Be prepared for database failures
o Restore backups regularly
o Determine time needed to retrieve backup files and restore backups including log files
o Test restore scripts beforehand so they are ready for use

integrity checks
o Some database servers like SQL Server allow backing up corrupt databases but it will note restore that backup
o Try to run DBCC CHECKDB before every backup for full corruption check on entire database
o However, CHECKDB is resource intensive
o Frequency of integrity checks depends on amount of data organization can afford to lose

agent alerts
o Set up alerting to provide notification when database failures occur
o SQL Server agent sends notifications when it finds qualifying conditions
o Database engine errors, resource errors, and corruption errors are important

query performance
o Proper indexing is one of best performance enhancements you can make to your database
o Database developers often use tools to create tables without knowing much about good database design
o Look for highly-used, unused, fragmented, and missing indexes