The solution provides multiple benefits for department
users. “We’ve been able to substantially reduce
the personnel hours spent on maintaining separate
data sources, so that [employees can] serve their primary
job function rather than entering redundant
data in numerous application front ends,” says David.
Another benefit is the consistency in data replicated
among the applications. And the functionality that
David added—most notably the stored procedures
that email notifications—made it much easier for
employees to obtain the up-to-date information they
needed on the status of various reports, overtime requests,
and subpoenas.
High Availability for a Critical Hospital System
High availability of essential applications is vital for
any business, but nowhere more so than a hospital,
where patients’ lives depend on systems being available
24 × 7. Although the Oracle-based electronic
medical records system at Spectrum Health had plenty
of redundancy, physicians and staff at the healthcare
center’s six surgical centers wanted further assurance
that surgical-staging data—data that describes how
to set up a surgical room for a specific surgeon and surgery—would always be available on site in the surgical
centers, even if the network went down. “We found
that we needed to have something available, in case
there was a failure with that central system, to keep
surgeries going on and ensure that an OR is staged the
way a surgeon wants it staged, so an operation goes as
smoothly as possible,” says Tim, the senior DBA with
Spectrum Health for about eight years.
To meet this requirement for additional redundancy,
Spectrum’s Oracle DBA originally enlisted the help of
a colleague to develop an extract from the Oracle relational
database management system (RDBMS), which
he bulk-loaded into a standalone instance of Oracle on
a dedicated PC in each surgical center biweekly. The
data was available via a Microsoft Access front end
consisting of two forms and a report. However, the
loading process failed frequently because employees in
the surgical units often shut down the PCs, not knowing
what the dedicated PCs were being used for and
that they were supposed to stay on all the time.
“When I took over support of this process, I
looked into streamlining the solution, knowing that it
had to be scalable because we had plans to bring other
surgical centers online,” Tim says. “I also wanted
to add monitoring functionality and notification on
failure.” Tim opted to use a SQL Server database to
provide the redundancy, along with a DTS package
for exporting the data from Oracle into SQL Server.
The solution is straightforward, as Tim explains.
“We developed extracts out of the main Oracle system
that were specific to each of the surgical centers.
A DTS package ftps the extracts [flat files containing
surgical-staging information] from the Oracle RDBMS
to our SQL Server 2005 Enterprise cluster. A
transformation task loads the data into two tables: a
reference table for the various medical supplies and
the actual surgical preference cards, as they’re referred
to in our system. The data in each surgical center resides
in a SQL Server 2005 Express Edition database
on a dedicated workstation, and the Access file used
for the original Oracle instance was relinked to the
new SQL Express local instance. A second transformation
task pushes data from the central database to
each surgical center’s database. This DTS package is
scheduled to run weekly as a SQL Server Agent job,
which notifies me via email upon job completion.”
Since the staging data changes only about once a
week, the process of extracting the flat files from the
Oracle database and transferring them to SQL Server
occurs at the beginning of each week. Tim’s solution
also monitors the dedicated workstations to make
sure they’re running, using a solution he developed to
track the 70 SQL Server instances hosting all 800-plus
databases across the medical system’s domain. “Using
linked servers and by querying system tables, I track
backup history, SQL Agent history, file space, and other select metadata. SQL Server Reporting Services
(SSRS) then provides me with daily delivered and
on-demand reporting of this metadata. I use SMTP
trapping to notify me if one of the monitored SQL
instances is unresponsive—for instance, if someone
has turned off a server,” Tim says.
The solution meets the need of surgical staff to
have guaranteed uptime of the staging system. Tim
says he’s in the process of converting the monitoring
system to a solution similar to the one that SQL Server
Magazine contributor Rodney Landrum describes
in his recent articles about SQL Server Integration
Services (SSIS) and SSRS. (For more information,
see “SQL Server Integration Services,” May 2007,
InstantDoc 95385 and “SQL Server Reporting Services,”
June 2007, InstantDoc ID 95745.) It’s good to
see one of our authors inspiring an Innovators award
winner to innovate even more!
Database Maintenance
for the Masses
Société GRICS, a company that develops applications
and provides training and consulting services for
school boards and other markets throughout Quebec,
needed a way to enable nontechnical end users to perform
routine SQL Server maintenance. Although the
school boards have part-time DBAs who maintain
their databases, staff outside the school boards didn’t
know how to do any maintenance other than scheduled
backups through SQL Server Agent.
As Société GRICS’ SQL Server specialist, Maurice
knew that the ability to perform routine database
maintenance was essential to ensure that applications
ran smoothly and to avoid data losses.
He also noticed that support-call problems
were increasingly related to inadequate database
maintenance and saw a need for a reliable
database-maintenance solution that all Société
GRICS customers could use. The solution would
need to perform database-integrity testing, selective
index reorganization, statistics updating, regular full database and log backups, cleanup of old backups,
and cleanup of SQL Server logs and history logs,
all without IT involvement. “Being a T-SQL addict
myself and having some knowledge of how database
maintenance works, I envisioned ways to set up all
this in a single T-SQL solution,” says Maurice.
The solution Maurice envisioned would meet the
needs of both end users and IT. The solution’s main
requirement was that it had to run without affecting
system performance or users’ ability to access data.
Additionally, says Maurice, “the tool’s only [prerequisite
for a user to run it] would be a working SQL
Server Management Studio (SSMS) query window.
The tool needed to log all its actions and store the log
in a SQL Server database. The solution should configure
everything necessary to run by itself and require
a minimal set of parameters so it would be easy to
use, even for non-IT people. Finally, it should provide
daily maintenance reporting, disable access to a specific
database in the event integrity problems occur,
and be easy for IT to customize.”
Using SQL Server 2005, Maurice wrote a T-SQL
solution that includes a script that end users download
from the support site, paste into an SSMS query
pane, and launch. The script creates a maintenance
database that includes log tables and a stored procedure
that initiates the various maintenance tasks. The
solution relies heavily on the use of dynamic queries
to perform tasks such as obtaining information about
a particular maintenance process for reporting, job
scheduling, and error handling. Also within the script
are examples that users can customize—one a call to
the setup stored procedure that specifies email recipients
and the mail server to use to send them maintenance
messages, and the other a sample backup directory
for database backups.
“SQL Server 2005 includes decent T-SQL exception
handling, which made it easy to do everything
the solution required,” Maurice says. “Long strings
in varchar(max) datatype ensured that I didn’t have
to worry about the length of dynamic queries needed
for the solution. This also makes it easy to store TSQL
queries and error messages into the log tables.”
By implementing Maurice’s user-friendly databasemaintenance
solution, IT support staff at Société
GRICS can be sure that users will perform database
maintenance setup according to their best practices
wish list, preventing data losses while ensuring that
applications will run properly without performancedegradation
problems related to poor database
maintenance.
End of Article
Prev. page
1
[2]
next page -->
