|
So you want to be a DBA...
2009-12-17 00:39
After spending 8 years working on DB2 and SQL Server, I am frequently asked by many about the “DBA responsibilities and career opportunities in DBA” Finally, I have the opportunity to write this down. This is based on my experience and naturally welcome other DBAs to share their experience.
First, DBA is not a degree offered by college or university! Of course, you can take classes in database structures, database theory and design, or a few classes on a specific databases product, but there is no comprehensive college program you can enroll to earn your degree in DBA!
You may not aware of this but there are many types of DBA. Their specialties or categories I used here are:
DBA (Database Administrator): Generally responsible for day-to-day administrative operation and databases.
Database Developer (Transact-SQL): Writes T-SQL applications, using scripts and stored procedures.
Database Modeler/Designer: Model and design databases
Data Warehouse/Special Product: Administrate, design and develop data warehousing applications or use special SQL related products.
This article will focus on becoming a DBA. We will examine each category and discuss what specific skills required. For simplicity, we will primarily focus on the MS SQL world. Can I just take exam 70-228 and exam 70-229 with the proper prerequisite so I can obtain my MCDBA certification? Yes, you can, but that is only the beginning!
The DBA is a well-round individual in regard not only to SQL Server but the operating environment as well. You must know not just SQL Server but be familiar with all aspects of SQL Server including but not limited to database designs, development, data warehousing and security. Do you know Windows 2000, Windows 2003, networking and security? Do you have to be expert in each sub-category? No, but the more you know the better you can perform your job! In a nutshell, you are jack-of-all-trades. Depending on the installation size and work load, you may be the only DBA, Database Developer, Database Designer or Data Warehouse staff and without any clear line of demarcation. To become a DBA is a major undertaking not something to be taken lightly. Plan to invest adequate amount of time to accomplish this career objective.
What you need to learn
The best way to approach this is to examine what daily tasks a DBA performs. I compiled some essential skills or basic tasks for DBA:
Server Installation: Skills needed here: SQL Server components (EM, profiler, Query Analyzer, other tools. Know your SQL Server editions and their limitations with their operating environment such as disk drive configuration, Windows NT, Windows 2000 or Windows 2003, steps involved in remote, manual and automatic installations.
Server Maintenance: Develop a systemic approach to test and apply patches from Microsoft or other vendors
Backup/Recovery: Know your database recovery models, and SQL statements for backup/restore. How to adapt your recovery plan to your business need? Any offsite backup need? Did you test that recovery procedure in a simulated disaster recovery scenario?
Security Concerns: Know your system, database, application and user roles. What authentication mode, Windows or Mixed? How to grant, revoke data base permission, security level of your SQL login in id. Make sure this security plan match your business model!
Database Maintenance: know your DBCC commands inside out. A few of my favorites are DBCC CHECKDB/DBREINDEX/CHECKCATLAOG. Know your sp_who, sp_kill and SQL task wizards well. Setup your database maintenance plan before you get that performance degradation!
Know your T-SQL: Use this powerful tool to your advantage. Write scripts to automate your daily task as much as possible. This not only help you to gain a greater appreciation of SQL, but in the real world, many performance issues traced back to poorly written T-SQL statements.
A working knowledge not only on create, read, update, delete database, tables, views, UDF, triggers and stored procedures.
SQL server functions such as @@identify, @@SPID, convert or cast, coalesce and others.
Know when to use cursor and or temporary table or define a data type as table.
If you think the list goes on and on, you get the idea now!
For those who are fortunate enough to be in the database designer, the following skills are essential:
Data Modeling: You are beyond data type and server operation now; you have to know the business of your company and built the appropriate databases. There are database modeling tools such as ERWIN, Visio or other commercial data modeling tools. A good database design will not only improve application throughput but reduce database maintenance. Know your logical and physical data base models. An experienced DBA once told me, normalize your database until it hurts and de-normalize it until it works! It is more than just create indexes, constraints, and triggers but also know when to normalize and de-normalize.
Performance Tuning: This is the fun and challenging part of job. Know your table/index architecture and placement, index choice, server, database, and table options, execution plan and the internal working of SQL server. I got a great satisfaction when I reduced the response time from 90 seconds to 8 seconds on a transaction. This is the pride and joy of a good DBA!
Advanced Scripting Functionalities: I am a strong believer in using scripting to automate the routine tasks. Know your script, DTS, SQL-DMO, replications and VBScript. Microsoft made a significant improvement in VBScript. Once you master the VBScript combination with DTS and SQL-DMO, you will have an automated data, hands free environment. Otherwise go and buy a third-party software package. Don’t try to monitor your SQL system manually!
Data Warehousing/Special Product – Step to glory
At this stage, technical skill is not as important but your soft skills will be a major factor. As a senior DBA working on data warehousing project, you will spend time in project definitions, requirements and deliverables. Interaction with other team members, communication, time management, negotiation and leadership skill will make or break your project or career. Innovations and creativities are more on order now. How can you use the current database technology to improve your business process and to make a contribution in the company’s bottom line? In the current e-business environment, a tremendous opportunity exists for DBA to manipulate data creatively along each step of data collecting, processing, mining and reporting step. Here you may build and link to multiple databases in order to complete your objectives.
A new feature called Reporting Services is now available in SQL Server 2000. It encompass several areas:
Development environment-Using Visual Studio 2003 to define and design your reports.
SQL Server- Report depository to house the reports.
Report Process and Distribution-Using Windows Services to render reports, schedule execution, and distribution by using Email or file share.
Web Service based Management and Report Viewing Tools-An ASP.NET application to define security, scheduling, managing, and report viewing. It touches IIS, ASP.NET, Windows Services, Web Services and finally SQL Server. If you want to use this product, you are expecting to create process flow to tie them together and ultimately deliver the reports to your users via email, file share or Web pages.
Where do I start?
As you can see, to be a good DBA, it is a tall order. One thing I learned from MCSE World.com is this: "Prepare well and you will not be surprised, opportunity is around every corner, the real question is whether or not you have prepared yourself to be ready to turn the corner." Quote from Ztech’s Dad.
The key here is preparation. Fortunately, relational technology has been around for more than 20 years, so the approach is start to build a small library with top-notch books. Some times I read the same book over and over again, each time picking up more information, insight and inspiration. My recommendation is start off with MS Press Microsoft SQL Server 2000 Exam 70-228 and Exam 70-229 Training Kits to get a general understanding of the SQL Server setup. Once that is done, you can home in any area of special interest to you, such as T-SQL, Stored procedure, XML, Database Design, DTS, English Query or Analysis Services. Once that is done, you can now take the certification exam. With enough hands on experience, that MCDBA certification is now more meaningful to you!
Here are the books I find useful. They are not in any particular order or interest.
Microsoft SQL Server 2000 System Administration Exam 70-228
Microsoft SQL Server 2000 Database Design and Implementation exam 70-229
Microsoft SQL Server 2000 unleashed
Microsoft SQL Server 2000 Technical Reference
SQL Server Books On Line
Database Design for Mere Mortals
Inside SQL Server 2000
Microsoft SQL Server 2000 Performance Tuning Technical Reference
SQL Server 2000 Analysis Services Step by Step
Beginning SQL Server 2000 Programming
Professional SQL Server 2000 Programming
Beginning SQL Server 2000 for Visual Basic Developers
Professional SQL Server 2000 DTS
MCSE Microsoft Windows 2000 Advanced Server Clustering Services
SQL Queries for Mere Mortals
SQL Server Stored Procedures Handbook
The Guru’s Guide to SQL Server Stored Procedures, XML, and HTML
While reading books is one of best way of learning SQL Server, you can surf the Web for additional information. Some of my favorite sites are
MSDN SQL Server 2000
dbforums
SQL Server Magazine
SQL Server Central
SQL-Server-Performance.com
On the other hand, you can join a local SQL User Group if there is one available. It is a great place to establish networking with professional DBA!
Get some real world experience
What good is a well read DBA without real world experience? This is a hardest part of the preparation! Try to create data base to keep track of your own books, CDs, stamp collection, or what ever hobby you have. The best one is to volunteer to develop a SQL Server database application for your friend or civic organization. I was a system administrator. The opportunity arose to become a DBA in my project and I took that challenge to become a full time DBA.
It is not easy and very challenging to become a DBA but I am glad I did. If you want to become a DBA, you need time and patience. The task is not insurmountable. As Ztech’s Dad said, “Are you prepared to be ready to turn the corner?”
http://www.mcseworld.com/forums/showthread.php?t=3126
http://www.canadastop100.com/national/
|
|
