SQL Server 2005 Best Practices

[ryrae]

We are looking to overhaul our current SQL environment. We have over 15 different servers with either SQL 2000 or 2005. Some servers host multiple DBs, but the majority of the servers are built specifically for the application it hosts. We want to make sure we are doing everything possible to maintain our databases properly. We currently have manintenance plans, which include Full DB backups, T log backups, and some have integrity checks and/or shrink jobs that run. Does Microsoft have a "Best practices" for maintenance plans/jobs or can anyone offer and suggestions that would help? Thanks for any help provided.

 

[SQLSister]

I would add that you should regularly practice restoring those backups. You will also want to periodically rebuild indexes and update statistics. How often would depend heavily on the need for your particular dbs and tables.

You can set up notifications to tell you that the disk is starting to get full,etc.

I think a report that identifies the top 10 or so poorly perfoming queries (or sps) and then scheduling them for performance tuning woudl be a good thing to have.

"NOTHING is more important in a database than integrity." ESquared

 

[philhege]

Also, run the Best Practices Analyzer against your instances. It will tell you a lot about security and configuration improvments.

Go to

http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63

for the 2005 download.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[ryrae]

Thanks SQLSister and philhege for the help. What would you suggest that I have in my maintenance plans as far as task? ie, history cleanup task, rebuild index task, shrink DB, etc. We are a 24/7 org that relies heavily on the availability of the DBs. Most maint plans only have a full backup of DBs and T-logs. thanks agian.