Top 25 DBA Worst Practices

For those that don't know, I've spent the last 15 months or so writing SQL Server 2008 Administration in Action, due to be published by Manning in May 2009. Writing a book whilst working 5-6 days a week is intense; add a young family to the mix, and it's arguably impossible. For those interested in such a process, I'll document my personal experiences with it in an upcoming blog.

With the writing phase now complete, I'm discovering life again, and apart from spending some desperately needed time with my family, one of the things I'll be doing is blogging. For this, the first post, I wanted to talk about Best Practices, specicifically DBA best practices.

One of the things I was very careful to do in the book was avoid making broad sweeping best practice statements; as we all know, there are always exceptions to almost every rule. Having said that, I think there are a number of things that we all agree are worst practices, and with that in mind, I give you, in no particular order, my Top 25 DBA Worst Practices;

1. Not considering service level agreements (SLAs) when designing a database environment and/or not considering the need for scheduled downtime for service pack/hotfix application,

2. Defining "disaster" too narrowly and not simulating/practicing a disaster recovery (DR) plan. Having a DR plan is fine, but how do you know it will work (and several people can follow it) when required?

3. Designing a storage system from a capacity perspective alone,

4. Assuming a SAN will meet/exceed performance requirements. Just because SANs are (typically) expensive does not mean the storage design process can be skipped,

5. Failing to track align disk partitions and formatting them with the default Windows allocation unit size,

6. Using RAID 5 volumes for write intensive applications,

7. Failing to validate an I/O subsystem for performance and validity before production implementation. The SQLIO/SIM tools are free; there's no reason not to use them,

8. Virtualizing/consolidating SQL Server instances and databases without consideration of the scalability, licensing, support, administration and performance profile implications,

9. Installing service packs, cumulative updates or hot fixes without reading the release notes and/or not installing them in a test environment first,

10. Installing all SQL Server features on the off chance they may be needed at some point in the future. Doing so increases the attack surface area and results in unnecessary running services that may reduce performance,

11. Installing multi-instance clusters without considering the resource implications of failover situations,

12. Creating logins/jobs with elevated privileges. Implementing least privilege can be tough work, but is essential in locking down a system for maximum security,

13. Changing configuration values from their default settings without adequate research and/or a detailed change log,

14. Placing data and transaction logs on the same physical disk(s),

15. Storing backups on the same disk as the database files,

16. Relying on Autogrow for file sizing, and leaving the TempDB database at its default size,

17. Not taking backups and/or not checking their validity and/or not practicing and documenting various recovery situations. All of these are equally as bad,

18. Leaving the database in the full recovery model without taking transaction log backups. A 2GB database with a 200GB transasction log ... Yes, we've all seen that before,

19. Implementing database mirroring in high safety (synchronous) mode without considering network latency and/or transaction log usage from index maintenance,

20. Not running regular DBCC checks,

21. Running REPAIR_ALLOW_DATA_LOSS as the primary/default recovery response, and not following up corruption events with a root cause analysis,

22. Not evaluating index usage and/or fragmentation levels as part of an index maintenance routine,

23. Updating statistics using the default sampling rate after a full index rebuild,

24. Using SQL Profiler in place of server-side traces, and using it as the primary performance analysis/tuning technique,

25. Manual administration with SQL Server Management Studio. For maximum efficiency and minimal errors, tasks should be scripted and automated along with appropriate monitoring and alerting mechanisms such as MOM or SQL Agent operators & alerts

I'm sure there's (lots) more that could be added to this list. Let me know if i've missed any common and obvious ones.

Cheers