25 Things I Wish You Knew About being a SQL Server DBA

I have a few suggestions for people new to using SQL Server. This list is not complete, but it is a start in the right direction. This information is for people new to Information Technology (IT) or are at least new to the Database Administrator (DBA) role. You have to remember that everyone is born with zero knowledge of SQL Server, and learning about databases is a long and wonderful journey. These are random thoughts in no specific order:

  1. Understand what your boss thinks is (or isn’t) your responsibility as the DBA.
  2. Never assume someone else is going to do any part of your job. If you don’t do something, don’t assume someone else will do it for you.
  3. Don’t automatically assume that a problem is caused by SQL Server. Even when someone tells you there is a database issue you must be methodical about understanding a reported problem, troubleshooting the symptoms, and targeting possible solutions.
  4. Test everything, twice as much as you think it needs to be tested, before you make any changes to a production server.
  5. Understand SQL Server security and make sure you understand who can connect to your databases.
  6. Create an server checklist, and complete the checklist on every database instance at least once per year, or before a server is moved to production.
    1. Server configuration settings (Windows version, security settings, CPU and memory, local administrators, etc.)
    2. Database server settings (database settings, paths for each database files, database names, etc.)
    3. Database Logins (script out all logins at least once per week, see item #12)
    4. Enabled and running services (all services and which are enabled, have specific logins, etc.)
    5. Installed utilities or applications (make sure you can recreate the server, if required)
    6. Drive assignments and free space (list of drive letters and the assigned space)
    7. Follow-up with your Windows Administrator if you discover a change. They need to know you are watching those servers and they should check with you before they make changes to your servers.
  7. Create a change management process. There should be a standard process everyone uses when they ask you to make a change to your production databases. You want to include items like who can request a change, the approval process, testing requirements, testing evidence, license requirements, rollback plans, etc.
  8. Always understand any requested change and never make a change unless you fully understand the change, know how to test if the change was good or bad, and how to rollback the change.
  9. Always perform backups and test the restore process. You probably backup your databases every day, but you might only restore your database if things go bad. Backups are useless if you can’t use them to restore the database.
  10. Database backups are your career safety net. Make sure your backups are working correctly, not stored on the database server, and copies of the backups are securely stored off-site. Loss of production databases will cost you your job.
  11. Make sure you test you ability to restore your production databases at least once per year. This will help you document the process, understand how long it takes, and what issues you might encounter during the process.
  12. Script out your database logins at least once per week. They might save your job during an incident.
  13. Do not use the SA login for anything. If you run into a vendor that requires the SA account, you need to find a better vendor.
  14. Try to control the indexes on your tables. Keep the indexes small and don’t create too many indexes on a table.
  15. To reduce network traffic, use SET NOCOUNT ON.
  16. Don’t use SELECT * in a production query.
  17. You need a test server. Test servers are safe places you can make mistakes or try out ideas without risk to the business. If possible, you want a test server that is similar to your production server which will help you evaluate performance changes, verify database restore times, and practice applying vendor updates.
  18. Always comment your code. Comments really aren’t for anyone other than you. Assume that you will have forgotten everything about the code by the time you look at the comments again. Be kind to the future you that will be looking at the code and trying to figure out what it does and why it was needed, usually while stressed out because it is a huge production issue.
  19. Don’t install anything on your database servers unless it is absolutely needed. That includes SSAS, SSIS, SSRS, etc.
  20. Understand how to read a query plan.
  21. Use SQL Server Agent alerts.
  22. Don’t be afraid to search the internet for an answer to your problem. Never take the first answer and always look for a consensus to a solution before you seriously consider that someone knowledgeable on the subject.
  23. With every issue, look for ways to benefit from the solution by learning something new. One of the best ways to learn something new is by dealing with a problem you didn’t know you had until right now. You want to be proactive about issues, but sometimes being reactive is your only option.
  24. Always be learning. Never assume you (or anyone else) knows everything there is is to know about SQL Server.
  25. Understand your TempDB and optimize it for the best performance possible.

If you need more information on these topics, search my site (or the general internet) to investigate possible solutions.

Advertisements

1 thought on “25 Things I Wish You Knew About being a SQL Server DBA”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s