Presenter: Thomas LaRock
Database Administration Manager
ING Investment Management
In SQL 2008, Maintenance Plans create a workflow of tasks to ensure your database is optimized and backed up on a regular schedule. The Maintenance Plan will create an SSIS package that will be scheduled and run with SQL Agent. Attend this session and learn how Maintenance Plans will ease your administrative overhead.
Common Tasks
- Database backups
- Database Integrity checks
- Index Maintenance
- Automated Cleanup
Creating New Plans
- Maintenance Plan Wizard
- Maintenance Plan Design
- Must be member of sysadmin fixed server role
Important Tasks to Deal With
- Backup Database
- Reorganize Index (fragmentation level < 30%, Reorg)
- Rebuild Index (fragmentation level > 30%, Rebuild)
- Update Statistics
- Check Database Integrity (DBCC CHECKDB)
- Also validates indexes in SQL Server 2008
- History Cleanup
- Cleans information inside information Sys DB’s
- Maintenance Cleanup (MSDB)
- Cleans up after maintenance plans themselves
- Or Backup files, logs, etc…
“Failure to plan for a disaster is inexcusable.” – Thomas LaRock
Schedules will depend upon your organizational needs.
Recommended Practices
- Create separate plans for system versus user databases
- Yes, System DB’s need to be backed up and able to be restored!
- Tasks should be run at non-peak hours (Duh!)Â
- Be sure to check for other running tasks, like reporting or ETL loads.
- Do not automate the Shrink Database task
- Really? Do use it period according to Thomas!
- Perform database backups prior to other maintenance tasks
- Back them up before you start touching anything even in a maintenance plan.
- Always have a “comfortable” restore point.
MultiServer Management
- Configure a SQL Server Agent to be a “Master”
- Specify Target
- Define new Multi-Server jobs or
- Convert existing Local Job to multi-server.
- Right click
- See Target tab. Use it.
FYI, the job only shows on the Master. It does not show in the target server job list.
“Since it’s a demo, we need everything to work.” – Thomas LaRock – Great quote. Too bad it didn’t work.
Additional Resources
Popularity: 5% [?]
