Advice for becoming SQL Server DBA for newbies - Bulleted Points

Rajnilari2015
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 1082 red flag
Rating: 5 out of 5  
 1 vote(s)

SQL Server DBA is a kind of dream for many SQL Server professionals.However, for the aspiring newbies, it's quite confusing as which topics to focus on more and what to learn since it's again another vast ocean of knowledge. In this article, we will provide some of the bulleted points in no particular order for the same with relevant references that will act as a detail reference point.
Recommendation
Read The Art of Stored Procedure Optimization - Part 1 before this article.

Introduction

SQL Server DBA is a kind of dream for many SQL Server professionals.However, for the aspiring newbies, it's quite confusing as which topics to focus on more and what to learn since it's again another vast ocean of knowledge. In this article, we will provide some of the bulleted points in no particular order for the same with relevant references that will act as a detail reference point.

The Bulleted Points

  1. Database installation is a must skill. One should have the hands on skill for installation of latest version(s) of SQL Servers.
  2. Adding users/roles in SQL Server.
  3. Detach and attach the database in order to move it from one location to another.
  4. Moving bulks of data from one database to another, or make a copy of the data e.g. Import/Export Wizard, Linked Server, bulk copy and bulk import and export,bcp
  5. Generating SQL Server Script using Publish Script Wizard (Please read our article for the same.)
  6. Index maintenance for performance improvement
  7. Scheduling SQL Jobs.
  8. Preparing a Maintenance plan.. A Maintenance Plan helps at least for the below activities -
    1. Backup - All Tables, Full Backup (daily)
    2. Backup - Selected Tables, Full Backup (hourly/daily/weekly/monthly)
    3. Backup - Transaction Logs (Every 15/30 minutes/hourly/daily)
    4. Check database integrity (daily)
    5. Reorganize index (daily)
    6. Rebuilding index (weekly/monthly)
    7. Update database statistics (daily)
    8. Shrink database (weekly)
    9. Rebuild index (weekly)
    10. Maintenance cleanup (daily)
    11. Performing an operator notification
    12. Executing a SQL Server Agent job
    13. Executing a Transact-SQL statement
    14. Clean up the database histories
  9. Backup,Restore and Recovery of database for failure.
  10. SQL Profiler and SQL Trace for performance bottlenecks/other monitoring tasks.
  11. DMVs/DMFs are a must for identifying performance bottle necks in the system.Some more guidelines on the same are available here
  12. Learning about SQL Server Extended Events will always remain as a great asset under the belt. Here and here are some more resources on the same.
  13. Health monitoring of the server by using Activity Monitoring (also worth reading this), Database Engine Tuning Advisor
  14. Learning SQL Server Transaction Log will be an indispensable asset in case of system failure.
  15. DB auditing. A comprehensive knowledge of Change Data Capture will be very handy.
  16. Central Management of Servers for distributed systems.
  17. Proper planning for better hardware resources.
  18. SQL Server Replication
  19. DB Clustering.
  20. Database Configurations including DBmail configuration.
  21. Knowledge of Powershell will be helpful.(Please read our articles from here, here and here for the same to get a flavor of the same.)
  22. Disaster recovery using AlwaysOn High Availability..
  23. Good amount of knowledge about locking and blocking.
  24. Good hold of Wait Stats/Wait Types/Wait Queues
  25. Log shipping.
  26. How to Restore Master DB in case it crash.
  27. Sufficient amount of knowledge about Temporary DB and how to keep an optimal one.
  28. Deployment in the Cloud Environment (Azure platform).(Please read our article on the same
  29. Extensive amount of knowledge about query optimization.(Please read our articles presented here and here)
  30. File Stream experience will be a good asset
  31. Internals of SQL Server
  32. Data security
  33. Excellent trouble shooting skill.
  34. Knowledge of ETL Process (SSRS/SSIS/SSAS)(Please read our articles presented here,here, here,here,here,here,here for the same)
  35. Good amount of knowledge about Data Warehousing and Data Mining
  36. An effective communication skill(written/verbal), presentation skill and initiatives.
  37. Training to the fellow members
  38. Writing blogs for sharing the knowledge and open to learn new technologies help always.

Apart from the Bulleted Points

  1. NOSQL knowledge(theory and Practical) with optimization of the same will be a great asset. Should add about MongoDB and Neo4J in the hat.
  2. Also it is good to know about other databases like GreenPlume(Parallel DB), Oracle, PostgreSQL, MySQL, EyeDB (OODB) etc. and it's functionality.

Certifications,Certifications and Certifications - A must for everyone

Certifications are must to measure the skill(s). Some of them are listed below

  1. MCSA: SQL Server
  2. MCSD: Azure Solutions Architect
  3. MongoDB Professional Certification
  4. Neo4j Certification

Books recommended

  1. DBA Survivor: Become a Rock Star DBA, by Thomas LaRock
  2. Microsoft SQL Server 2012 Internals, by Kalen Delaney
  3. SQL Server Hardware, by Glenn Berry
  4. Inside the SQL Server Query Optimizer, by Benjamin Nevarez
  5. Troubleshooting SQL Server - A Guide for the Accidental DBA, by Jonathan Kehayias and Ted Krueger
  6. Fundamentals of SQL Server 2012 Replication
  7. High Availability and Disaster Recovery (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions
  8. Performance Tuning With SQL Server Dynamic Management Views

Reference(s)

SQL Server DBA Tips and Tricks

Conclusion

Having said all the above, nothing can beat hard work/dedication/patience/an eye for detail/inquisitiveness and above all a smile under high work pressure makes a lot of difference - everywhere and anywhere. To conclude, we must quote Sir Isaac Newton -

" I know not what I appear to the world, but to myself I seem to have been only like a boy playing on the sea-shore, and diverting myself in now and then finding a smoother pebble or a prettier shell, whilest the great ocean of truth lay all undiscovered before me. " - Isaac Newton

Hope this article will be helpful for many SQL DBA newbies and will serve as a point of reference for them.

Thanks for reading.

Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)