SQL Server 2005, Maintenance Plan suggestion

Question

Hi there,

I need some advice on SQL Server 2005 maintenance plan, okay here some question:

1. What task(s) is/are suitable for daily maintenance and what for weekly/monthly maintenance
   
2. Do database need to be offline while in some task, example : reorganize/rebuild index, Shrinking database, etc... (since we need to keep 90% uptime)
   
3. How long can check database integrity, reorganize/rebuild index, cleanup history be?
   
4. Should we do both reorganize and rebuild index?
   
5. Do we need to update statistic after reorganize index? Since rebuild index will auto update statistic

In our case, data is inputed every 1 minute (merely 200 records per minute) 24 hours, 7 days a week.

Can someone suggest me what Maintenance Plan that good for this database?

Thanks,
Dels

Answer 1

Maintenance plans really depend on your database processes. Since the data is inputted every minute, do you have any rollup and etl processes running?

The most important process that I can tell you is the daily backup (both tape and disk)of your data and transaction logs.

Check for any slow running queries using query plan analyzer and you may need to re-index some of your tables either daily or weekly depending on your needs. You can do online re-indexing in SQL Server 2005 enterprise edition, which means you do not need to be offline.

Have a good process by creating a maintenance plan and automate as much as possible by creating scheduled jobs.

Answer 2

Two Words: Disaster Recovery

The best plan is one which you have tested.

Plan for a disaster recovery and see how much you can restore your existing system from this plan. Note any issues and find ways of resolving them, then re-run the disaster recovery. You will find this will help you make decisions and prioritize requirements.

It is best to do this with both an O/S restore as well as an SQL server restore.

Also some advice: Setup an O/S scheduled task to do a file system copy of the master, model, mssqlsystemresource databases. This will save you grief and having to run the SQL server in single-user mode to try and restore your master db from a backup.

Its all well and good having backups, but if you never test recovery, then your backups are worthless.