Runner-Up
Michael Metcalf, Database
Administration Team Manager
Wilmington Trust,
Wilmington, Delaware
madmike@mmetcalf.com
Self-Service Development Testing
For Mike Metcalf, database administration
team manager for a Delaware financial services company, keeping his databases secure
and available is the highest priority. Mike
and his four-DBA team manage more than
60 servers (including 45 production servers)
running more than 270 SQL Server, Oracle,
and IBM DB2 databases. Because of the
sensitive nature of the data his company
works with, his team doesn’t allow developers who work for the organization to run
SQL Server jobs in any of the organization’s
database environments—not even on development servers. This policy lets Mike and
his team keep a tight rein on data access, but
it can be frustrating for developers, especially
when they need to run SQL Server jobs to
test the applications they’re writing.
Mike explains that about half of the
applications that his company uses are developed in house, so developers are constantly
writing scripts and stored procedures that
they need to test. “When the developers
needed to run ad hoc jobs, they had to go
through one of the DBAs. My team and I
weren’t always at their beck and call to run
a job, which sometimes led to frustration for
the developers, plus time wasted waiting and
frequent demands for elevated rights.”
To solve the problem, Mike created
the dbaSelfService database, which contains permitted users, the validated jobs (with ID
numbers) that they’re allowed to run, and
stored procedures to make it work. When a
developer wants to run a job, he logs in to
the database and executes a stored procedure
against Query Analyzer to see a list of the jobs
that he’s allowed to run. The stored procedure
matches the logged-in user’s ID to its list of
allowable IDs, then matches the user’s ID to
the list of jobs that user is allowed to run.
When the developer chooses to run a job, the job name is put into a holding
queue table. An sa-owned job runs every
minute and queries the holding queue table
for any waiting jobs. If the sa job finds any
entries, it runs them. The user who executed
the job gets an email notification when the
job starts. The user can also see job information that’s written to a log table.
Because Mike created the tool strictly for
use by his team, he trained developers to use
it over time. When a developer would bring
a job to Mike’s team to run, the DBA would
show the developer the new tool and walk
him through using it. The response from
developers has been positive—so much so,
that a couple of months ago, the team started
using it for a production application. Mike
explains, “One of our Web apps needed to
be able to kick off a stored procedure that
performs a user-initiated data load. The self-service database gave us the functionality to automate the data load.”
One of the keys to the solution’s success,
says Mike, is its built-in security. “All our
developers have to log in with Windows
credentials, so people can log in and get
access to run their own jobs but not anyone
else’s jobs. To keep everyone honest, we also
have an audit table to keep track of what
jobs each person executes and to show that
the jobs actually get executed.”
Mike enjoys his work and appreciates his
team members, so he’s glad he could create a
solution that makes their jobs easier. “I don’t
necessarily consider myself a guru about all
aspects of SQL Server, but I’m pretty handy. I
try to solve things first and figure out what I
need before I go looking for it. Occasionally I
get lucky and find a better way to do whatever
I’m trying to do.” (For more about the creativity that makes Mike an innovator, see the
sidebar “Last Rites for a Beloved Server.”)
Prev. page
1
[2]
3
4
5
next page 
