The True Test of a Good DBA

The True Test of a Good DBA

By Bill Wunder

There are a number of reasons why good DBA’s tend to be among the better paid folks in a software development shop and always considered among the least expendable in a any intensive production computing environment. A good DBA is hard to find. The requirements of exceptional professionalism, thoughtfulness, mentoring and business process enforcement abilities, a breadth of savvy across the realms of hardware and software technologies are unlike the requirements of any other person in the shop. A long reaching view of the big picture and an enlarged view of the microscopic details are mandatory skills. A continual ability to learn, innovate, and lead on into the unknown in implicit. With such a breadth of required skills its no wunder that there are far more people calling themselves DBAs than there are true DBA’s in this world. So when it’s time to try to hire a good DBA it is imperative to come up with an extraordinary way to effectively and efficiently identify the real article from the pretenders.

Perhaps there are people in a hiring role that can distinguish between top quality DBA, a diamond in the rough, and a used car sales associate. I don't believe I've met that person, but I have had opportunities to make the acquaintance of many of those latter types as they search for good job in an always tough DBA job market. Relying on any interview process to find a highly specialized technical profession like a DBA will always be a spin of roulette wheel. What’s worse, the house odds will not belong to the organization or the applicant. It is absolutely mandatory to allow a DBA applicant to show what they can do and then evaluate the results. And it is important for all that this is accomplished in an hour rather than in a month or two on the job if at all possible. The best way to accomplish this when hiring a DBA is to set them in front of a database and have them demonstrate some of the key skills that they will need to bring to the organization in order to be effective. Some sort of a test that aids in leveling the playing field and pre-screens applicants as to meeting a minimum standard even before everyone invests larger amounts of time in interviews, discussions, and negotiations.

I’ve used a simplistic written test with only a few questions and I’ve used a more exhaustive evaluation. I’ve tried simply asking for verbal descriptions, writing answers on paper, and hands on - set at a keyboard - do these things tests. Far and away, in my experience, the best way to determine the skill level of an applicant is to give them an 30 minutes to an hour at the keyboard. I suppose it wouldn’t have worked a few years ago – before the bust – but in today’s employment market folks are keen to do what ever it takes to land a good job. Letting them show their stuff on a SQL Server is unbelievably effective.

Recently I found myself looking for an experienced DBA to grow my team of one to a team of two. I sat down for a half an hour and came up with a series of tasks that exemplified what a DBA does in our shop and then I worked at it another half an hour to create a process that I wouldn’t have to reconfigure each time someone “took the test”. I created and populated a couple of small databases. I detached one and I backed up the other. I made a just in case copy of the backup and the detached db but I did not have to use them. Applicants would come in, we’d show them around and then we’d set them at a desk near me, give them a few minutes of explanation and off they’d go solving some SQL problems. Of worthy note, I would also make sure this SQL Server was disconnected from the company's network as a precaution against the unpredictable.

The results of the hiring round that used this exam are a testament to the efficacy of the exam. It’s interesting how many things people will put on a resume that they can’t do. And it’s interesting how hard people will resist admitting they are in over their head. And most of all it’s amazing how well the person that did the best on the hands on evaluation is working out in our shop.

Below is the exam I used with some annotations to give you an idea of what it really took to get to the end. I put in a few surprises because that’s what I needed. Someone that can work through a few surprises. I be interested in hearing your thoughts on this exam. Too Hard? Too Easy? Would you apply for a job that required you to take a test like this?

Bill

first, here's the script I used to set up the test. I then restored a backup of this database with the name of database B, dropped table t1, and fixed the data file and log file size at 10MB to create some problems to solve as the test progresses.

create database A

use A

if object_id('t1') is not null

drop table t1

if object_id('t2') is not null

drop table t2

create table t1

(c1 int not null,

c2 int not null,

c3 varchar(10) not null,

c4 varchar(100) not null)

create table t2

(c1 int not null,

c2 int not null,

c3 varchar(10) not null,

c4 uniqueidentifier not null)

create clustered index ixc_t2__c3__c2 on t2 (c3,c2)

declare @i int, @j int, @k int

set nocount on

set @j = 1

while @j < 1000

begin

set @i = 1

while @i < 92

begin

select @k = floor(rand()*10000000)

insert t1 (c1, c2, c3, c4)

values (@i*@j,

@k,

case when @k&5=5 then 'red'

when @k&3=3 then 'green'

else 'blue'

end,

cast(newid() as varchar(100)))

set @i = @i + 1

end

set @j = @j + 1

end

select c1, count(c1) from t1 group by c1 having count(c1) > 1

select c2, count(c2) from t1 group by c2 having count(c2) > 1

select c3, count(c3) from t1 group by c3 having count(c3) > 1

select c4, count(c4) from t1 group by c4 having count(c4) > 1

insert t2

select * from t1

order by c1

dbcc showcontig ('t1')

dbcc showcontig ('t2')

And here's the exam paper:

Name: ___________________________________________ Date: __________________

Instructions: You may use SQL Server "Books on Line" and all other help files located on this server. Use Query Analyzer when possible. Save all work completed with Query Analyzer to a file using the question number as the name and .sql as the extension. (for example: 1.sql, 2.sql, etc). Save all results from work completed with Query Analyzer to a file using the question number as the name and .txt as the extension. (for example: 1.txt, 2.txt, etc) If you do not use Query Analyzer to complete a task, please indicate what tool you used. Partial credit may be given for all problems attempted. You may ask questions at any time.

Watch the applicant complete 1 and 2 then leave them to it

1. Start the SQL Server service and the SQL Agent service (5 points)

Service Manager 5 points

EM 5 points

Services console 2 points

2. Detach database A (2.5 points)

A bit of a set up here. Detaching will cause problem for restore in 4

3. Drop database B (2.5 points)

reverse set up, the statement needed is the question

4. Restore database A from the backup file c:\back\A.bak (10 points – 5

points extra credit for using SQL Litespeed restore and backup file

c:\back.A.sls)

- 3 if not done by QA

will have to delete or move detached files first

5. Attach Database B (10 points)

Copy file c:\save\B_Log.ldf from to c:\mssql\Log\B_Log.ldf

Copy file c:\save\B_Data.mdf to c:\mssql\data\B_Data.mdf

Attach database B to the SQL Server using the files c:\mssql\Log\B_Log.ldf and c:\mssql\data\B_Data.mdf

6. Check for table t1 in database B and drop if found. (5 points)

There is no table t1 in database B

7. Load a table from a flat file. (10 points)

Create table t1 as defined below in database B and load data from file c:\save\data.csv into table t1 in database B

table name : t1

column name data type required

------------ ------------------------ ---------

c1 integer yes

c2 integer yes

c3 from 1 to 10 characters no

c4 from 1 to 100 characters yes

Can do 6 and 7 all in the DTS package

use B

create table B.dbo.t1

(c1 int not null,

c2 int not null,

c3 varchar(10) not null,

c4 varchar(100) not null)

something along the lines of this necessary to get it to load:

truncate table b.dbo.t1

backup log b with truncate_only

alter database B

modify file ( NAME = 'B_Data',

MAXSIZE = UNLIMITED)

alter database B

modify file ( NAME = 'B_Log',

MAXSIZE = UNLIMITED)

8. Add a primary key to table t1 in database B on the unique column. (5 points)

alter table b.dbo.t1

add constraint pk_t1__c4

primary key (c4)

9. Index maintenance (10 points)

Determine the fragmentation of table t2 in database A.

Remove the fragmentation from table t2 in database A.

Confirm that the fragmentation in table t2 of database A has been removed.

Explain as necessary (use back of this page if necessary).

Before image will document that database A was restored in 4

10. Server Configuration (5 points)

Select @@version

Determine the service pack level of the operating system _________

Determine the service pack level of the SQL Server _________

Compaq Array Configuration Utility

Determine the number of physical discs, and the RAID level for the drive where the transaction log files of database A reside.

Number of Discs _________

RAID level _________

11. T-SQL (15 points - 5 points extra credit for using the "Create Procedure

Basic Template")

Create a stored procedure in database A owned by dbo named “get_results” that returns the min and max value for column c2 of table t1 in database B and the name of the user executing the procedure in a single row.

Example output:

min max user

---------- ---------- --------------------

12 147 Bill

select min(c1), max(c1), suser_sname() from B.dbo.t1

12. List the stored procedures in database A. (5 points)

select Name from a.dbo.sysobjects where type = ‘P’

13.Security (10 points)

Add a SQL user named with your first name. Give this user the correct

permissions necessary to execute the procedure “get_results”.

sp_helprotect ‘dbo.get_results’

14. Log in as this user in Query Analyzer and execute the stored procedure

“get_results”. (2.5 points)

output should be :

min max user

----------- ----------- --------------

1 90909 <name>

15.Stop the SQL Agent Service. (2.5 points)

Important to make sure it was started in 1. If it;s off when they leave you know they did both.