| |||||
 |
|
|
|||
|
|
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 and 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 minutiae of administration 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 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. You don't need to ask for complex things. Simply ask them to demonstarte the basic activities that you have determined are key to success in your shop. Not so long ago 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? First is 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
go
use A
go
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')
go
--------------------------------------------------------------------------------
And here's the exam paper (The comments in parentheses are never seen by the applicant.):
--------------------------------------------------------------------------------
Name: ___________________________________________ Date: __________________
Instructions: You may use SQL Server "Books on Line" and all other help files
located on this server as wellas any references you may have brought with you to
the exam. 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
Or
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
Other Related Articles ... to read more DBA articles, visit http://dba.fyicenter.com/article/ |
||||
|
|
|
|||
