Runner-Up
Ayad Shammout,
Lead Technical DBA
CareGroup Healthcare System,
Boston
ashammou@caregroup.harvard.edu
Comprehensive Server
Monitoring
Although he’s a DBA, 18-year IT veteran
Ayad Shammout has an all-inclusive perspective on Windows technology. “I don’t want to
isolate myself only on the SQL Server database, because I know that SQL Server relies
on Windows, Windows relies on networking,
and so on. I’ve forced myself to understand
other technologies, background processes, and
dependencies, so if I encounter a problem, I
can easily isolate or diagnose it,” says Ayad.
Ayad’s big-picture approach to technical
problem-solving served him well in developing a solution that reports a plethora of
system metrics. IT staff at CareGroup use
such information to gauge the health of
SQL Server 2005, SQL Server 2000, and
Windows servers across the organization as
well as for capacity planning and auditing.
Initially, Ayad developed his system to
monitor the status of CareGroup’s approximately 400 SQL Server databases and 30
SQL Server instances. “I want to go to one
place and access specific information about
all my databases, rather than logging on to
individual servers,” Ayad says. To obtain such
data, IT used to retrieve data from SQL Server system objects, such as database data
and log-file sizes on all SQL Server instances,
then output that data into a Microsoft Excel
spreadsheet. “It took IT a long time to [create
the spreadsheet report], and if in a month or
two, we needed to get an update, we had to
do that work all over again.”
Ayad began building his centralized
reporting system by creating a master repository database on a SQL Server instance. The
repository would store data about every
SQL Server instance, database, object, and
user in CareGroup’s environment. Ayad
created a linked server connection to all the
SQL Server instances so that the stored procedures could pull data from them and put it
in the repository database. Then he extended
the system to collect Windows system
information from all Windows servers via
Windows Management Instrumentation
(WMI) and Microsoft Systems Management
Server (SMS). The data-collection jobs run
nightly or weekly on the servers, depending
on which data they’re gathering.
After data is in the repository, IT can use
Reporting Services to view the data. “We
can get a complete overview of a specific
user,” says Ayad. “For example, you can look
up one user, and from that user drill down
and see that the user has access to this particular server and database, has read-only
permissions to one table, and has read-write
permissions to another table.”
IT has come to depend so heavily on Ayad’s
server-reporting system that he’s moving the
database to a cluster server to ensure that it’s
available 24 X 7, even during planned outages
for maintenance. Ayad says that CareGroup’s
IT has investigated third-party tools that perform similar system-reporting functions but
found none as complete as his. For example,
“a tool might report all the server properties
but doesn’t work as comprehensively with the
SQL Server databases. So we might end up
running different [third-party tools] to get all
this collected information. My solution saves
money and the hassle of working with different tools. We own it and control it, so we can
keep developing and adding.” In fact, Ayad is
currently customizing the solution to collect
data from Oracle databases on CareGroup’s
UNIX servers and network data from Windows servers and network switches.
Honorable Mention
Edward Bond,
Database
Analyst
Baylor Health Care System,
Dallas
edwardb@baylorhealth.edu
Automated Medical Coding
After developing an automated procedure-coding system for Baylor Health Care
System, veteran DBA Ed Bond probably
knows more than many healthcare professionals about invasive-cardiac– and peripheral-vascular–procedure codes. Traditionally,
medical coders manually derive the correct
codes for more than 300 distinct cardiac
procedures from documentation entered
in a patient’s chart. Missing information
often prevents coders from entering the
correct codes. The hospital asked Ed to
assess whether automating the coding and
enhancing electronic charting could eliminate such errors.
Ed first researched charge coding,
which took several months, then developed
algorithm specifications for all the codes
and wrote the application. Data entered
into a patient’s chart via the hospital’s
computerized system is sent to the server
via FTP. ParserQC, the Visual Basic (VB)
utility that Ed wrote, monitors the FTP
folder on the server and retrieves data as it
appears. ParserQC parses the information
necessary to derive codes from the charted
notes and temporarily stores the data in
a Microsoft Access database. The utility
checks for required information, derives
the charge codes from information stored
in a SQL Server database, produces the patient reports for the medical record, and
stores the derived data in the SQL Server
database.
After using the new system for more
than 18 months, Baylor Health Care System
has greatly improved the accuracy of complex cardiac procedure coding. Doctors like
the system because they no longer need to
dictate procedure reports; ParserQC derives reports from the charted information. Lab
staff spend less time manually deriving
charge information and have less paperwork
to process. “The system has decreased costs
associated with charge coding, increased revenue [by reducing the number of rejected
insurance claims] and accuracy of patient
billing, and enabled clinical staff to spend
more time on patient care,” says Ed.
Prev. page
1
2
3
[4]
5
next page 
