Thursday, July 7, 2011

BONUS TIP### - How to be a Good DBA - Strategy and Tactics

sBONUS TIP### - How to be a Good DBA - Strategy and Tactics
Having been a DBA for while, I am frequently asked by some programmers, “I am tired of coding, now I want to switch my career to be a DBA, so where should I start?” I promised that I would write an article to answer these questions based on my experience, so this article is mainly based on my experience as a SQL Server and ORACLE DBA, and I welcome other DBAs to share their experiences on this topic.
Strategies for Junior DBAs
Focusing on the basic DBA tasks: first thing first.
The quickest way to be a qualified junior DBA is to understand the scope of daily DBA tasks and grasp the necessary skills to do these tasks. What you’ll find, most times, is that a junior DBA’s work accounts for at least 40% of daily DBA tasks. The following is a list of tasks essential to every DBA.
·         SQL Server installation. Knowledge needed: SQL Server components (EM/SSMS, Profiler, Query Analyzer, Transaction Log, other tools etc), edition of SQL Server and its environment requirements (hardware, OS, network protocols etc), remote / automatic / manual installation
·         Backup/Restore. Knowledge needed: recovery models, backup / restore tools and SQL statements for backup / restore, and how to adapt your recovery plan to your business needs.
·         Security: roles/login/user accounts. Knowledge needed: authentication modes, types of roles, how to manage security accounts, grant /revoke permission to access data and how to make a security plan matching your business needs.
·         Database Maintenance. Knowledge needed: some DBCC commands (dbcc checkdb / dbreindex / checkcatalog / inputbuffer are among my favorites), sp_who, sp_kill and all the SQL task wizards.
Focusing on programming with SQL: foundation for future growth
T-SQL is a powerful tool that enables DBAs to personate their database management styles by scripting their daily tasks such as auditing, backups / restores, performance tuning etc. Most of application performance issues in real world are related to bad SQL statements. I cannot over emphasize the importance of writing good SQL scripts.
In my humble view, The following are the essential SQL statements (ACRONYM):
a) CRUD (Create, Read, Update, Delete) statements about database, table, view, UDF, triggers, stored procedures.
b) T-SQL programming control statements, such as if…else…, begin…end, while…break…continue and case statement and use of cursor.
c) SQL Server internal functions, such as @@identity(scope_identity for sql server 2005 and above), @@spid, substring(), covert/cast(), etc.
Strategies for intermediate DBAs
·         Focusing on data modeling: beyond data and into business. Knowledge needed: knowledge of business for which the database is going to be built; knowledge of logical / physical model (3rd norm); expertise in the modeling tools, like ERWin, Visio etc. A good data model will not only improve an application performances but also reduce the database maintenance. For example, I once reviewed a data model of a government agency, which stores all employees’ salary information in several tables, the original idea was to facilitate the quick summery reports needed by different departments but later is found too costly to synchronize all the information in terms of time and speed. (Some tables have triggers, which increase delays during synchronizations.)
·         Focusing on performance tuning: fun and challenges. Knowledge/Skills: table/index architecture, Index choice/building; server / database /session options; execution plan; use of profiler to optimize and audit SQL statements, dbcc statements. Performance tuning is where a DBA can shine. Challenges always hide somewhere for a DBA to tackle so that a query can run within 2 seconds instead of two minutes. Believe me, it is fun to do performance tuning!
·         Focusing on advanced management: key to being an expert. Knowledge / Skills: VBScript, DTS, SQL-DMO, replication. I always believe that a DBA who does not know VBScript cannot be creative in managing SQL Server system. Because Microsoft has made VBScript so powerful that it can be used to facilitate the administration of most Window-based applications, SQL Server is no exception. When combining VBScript with DTS and SQL-DMO, I bet almost all administration work can be scripted and automatically run under a schedule.
Strategies for senior DBAs
·         Focusing on soft skills: key to career. As a senior DBA, you spend most time in doing requirements analysis with clients and providing guidance to other team members, here soft skills, such as time management, communication, negotiation and leadership etc, play more important roles than the technical skills. Without soft skills, you can hardly be a valuable DBA. I learnt the lesson very quickly. Assuming I was assigned as a database architect for a project, and had to discuss with clients in different departments regarding their requirements. If from time to time, I release a new version of data model make constant changes to certain columns in the table, by either adding them to different tables or simply getting removed or renamed and also if The problem comes up to be that I forgot to document why I made the change based on my talk with whom. When I get challenged with the differences between version 1.0.04 (four weeks ago) and version 1.0.06 on a specific column, I won’t be able to tell why the column appeared in 10.04 but disappeared in 1.0.05 and then reappeared in 1.0.06. I will be accused of being incapable of my role even though my project manager is assured of my technical capability. Later, I will be removed from my role because the project manager has to satisfy the client’s concern. How stupid I will be right?! Well, your story must not resemble that one.
·         Focusing on innovations: the last step to glory. Innovation is to use the current technology and resources to create new processes / methods that can make better solutions in terms of time or economic efficiency. As a senior DBA, one needs to have a broader view and interests in contributing to your employer’s business bottom line. In the e-business era, more business are relying on data for decision and operation, and this gives all DBAs, esp. the senior DBAs more opportunities to creatively manipulate the data at each stage of the data value chain, from data collecting, processing, mining, to reporting, distributing and storing.
To be a qualified DBA, you have to master the right skills at the right time. You do not have to know everything to start your career as a DBA, but you have to know where to start. There is no distinct line between a junior DBA and an intermediate DBA or an intermediate DBA and a senior DBA. But my personal feeling is that when your rank goes higher, you need more than technical skills to demonstrate your values. My final word to those who are interested in DBA career:
“Do not think of database management as a routine job, but think of it as an art work that you can demonstrate your imagination and creativity.”

No comments:

Post a Comment