Everyone makes mistakes, but a Database Administrator (DBA) shouldn’t make these common mistakes more than once.
- Memory Management – You can run into serious trouble if you are not managing your MIN and MAX database settings. You will also have different configurations for 32-bit and 64-bit versions of SQL Server.
- Poor Database Disk Usage – You should not place your database files in the default location and hope for the best performance. Investigate your drive configurations and performance and relocate the files to drive locations that provide the best performance while reducing contention, hot spots, and throughput issues. Make sure the drives are formatted and configured properly, and the database, TempDB, and log files are on separate drives.
- Auto Shrink – If you have your database files set to Auto Shrink Enabled, you are not properly managing your database. This setting causes disk fragmentation and unwanted overhead that should be avoided in any production environment.
- Bulk Imports – Do not perform bulk inserts during normal production hours. If you have to import large amounts of data and you don’t have an available maintenance window, then determine the lowest usage times and perform the import during those times, if possible.
- No Backup Strategy – As a Database Administrator you are responsible for the data, yet many times you rely on someone else to make sure backups are scheduled and complete successfully. This is a mistake that will haunt you the first time you have a disaster and are asked to restore the databases. Always verify the backup jobs are running and complete successfully. Practice your database restores at least one every calendar year.
- No Server Monitoring – You should measure database and server performance, even if everything is working great. If you are taking periodic measurements, you should be able to detect issues long before users start complaining about degraded performance. Don’t wait for users to start complaining before you look for issues.
- No Performance Tuning – At least 10% of your time should be spent looking at database and server performance, looking for ways to improve performance using existing hardware and software. If you can’t improve performance with improvements to stored procedures, moving of log and data files, or improved indexing then you should be looking at faster hard drives, more server memory, or faster CPUs.
- No Automation – This is a really simple idea. If you are doing anything more than once, you should be asking yourself if it can be automated. Spend some of your time looking at facts, analyzing data, improving services. Do not spend you time repeatedly figuring out how to gather facts, where is the data located, how to query performance statistics, etc. Use automation to improve your productivity. Look at scripts, scheduled jobs, etc.
- Using Poor SQL Code – Allowing developers to run poorly formatted or poorly designed Transact-SQL code to run on your production servers will cause serious performance issues. If you don’t have the resources to adequately check code before it goes live in production, at least monitor the performance on the production servers and provide immediate feedback to the development group.
- No Security – You should limit access to production data to just the specific users that require access. Each user should have their own account, and they should not be logging into the data with one single shared account for all users. If everyone uses the same account and it has elevated privileges – you’re asking for trouble and it is just a matter of days before disaster strikes.
- Bonus: Lack of Business Awareness – This bonus mistake is more career oriented than just focusing on the technical systems. You have to be aware of what is important to the business and understand when things change. This will help you focus on the critical processes and be more proactive in targeting those systems that are critical to the business.
Do you have additional mistakes that you think should be added to this short list?