GEM
A Toolkit
To Automate Database Infrastructure Administration
|
|
|
By Ed Barlow |
|
www.edbarlow.com |
What Is GEM
|
|
|
|
GEM is short for “Generic Enterprise
Manager” |
|
A Complete Set of Tools For Sybase and
SQL Server Dba’s (oracle, db2, etc… to come) |
|
An architecture - easily customizable
to your environment |
|
A way to reduce a dba’s workload and
increasing uptime |
|
Built on open source perl |
|
A new way to manage your servers |
|
Based on 15 years of database shareware
software |
|
Designed by and written for DBA’s |
What GEM Does
|
|
|
Routine Maintenance (backups etc…) |
|
Configuration Management |
|
Enterprise Security Management |
|
Monitoring and Alarming |
|
Morning Review Reporting |
|
Common Task Automation |
GEM is a complete
solution
|
|
|
GEM automates systems management as
much as practical |
|
15 years of automating the solving of
real world dba problems |
|
|
Current Solutions
|
|
|
Individually designed and maintained |
|
Incomplete |
|
Non Existent / Unacceptable Vendor
Tools |
|
|
|
You can miss stuff allowing problems to
arise |
GEM is Low Risk
|
|
|
|
GEM uses a simple centralized
architecture |
|
no agents |
|
Open source |
|
you can see exactly what it does |
|
Uses common best practices |
|
Easy to install and use |
|
Low Risk to Try It Out |
GEM Introduction
|
|
|
|
Target Market |
|
Small to Mid Sized Enterprises |
|
3-50 Databases |
|
Low Cost - High functionality |
|
Designed For DBAs |
|
Significant OS functionality |
|
Complete Database functionality |
GEM Design Notes
|
|
|
|
GEM works on NT, Linux, and UNIX |
|
Centralized information collection |
|
System Monitoring Information |
|
System Event/Log Information |
|
System Performance Information |
|
System Maintenance Information |
|
Morning Review Process |
|
Provides static and dynamic views of
this information |
|
Understand exactly what is going on |
GEM Design Notes
|
|
|
Simplicity – GEM is composed of small
simple self documenting command line programs |
|
Open API – the power of GEM is in its
extensive infrastructure libraries (normal perl modules) |
|
Common directory structure |
GEM Design Notes
|
|
|
Error correction – GEM provides
information about potential problems but does not fix them |
|
Expandability – You can easily copy
existing code to cusomize GEM to your environment |
Sample Target
Architecture
|
|
|
|
All Windows Environment |
|
Centralized monitoring of your windows
servers and databases |
|
Significant New Functionality |
|
Complements vendor tools |
|
Much more flexible than vendor tools |
|
New system Stored procedures |
Sample Architecture
|
|
|
|
All Unix / Sybase Environment |
|
Centralized monitoring of your windows
servers and databases |
|
Significant New Functionality |
|
Excellent backup and log shipping
solution |
|
New system Stored procedures |
Sample Architecture
|
|
|
|
Unix and Windows Mixed |
|
All the benefits of both environments |
|
Single common interface |
|
Seamless interface if installed on
samba share (highly recommended) |
The GEM Solution
|
|
|
|
Perl Open Source |
|
Tk and html User interfaces |
|
DBI Database Access |
|
Perl is THE systems administration
language |
|
Tested on windows and unix |
Components of GEM
|
|
|
|
GEM Console: a set of web pages that are frequently updated in place by
batch jobs. These pages contain an
enterprise view of your systems and their state. |
|
GEM Manager: The user interface |
|
GEM Monitor: Dynamic Server Monitor |
|
GEM Event Viewer: Enterprise view of
events and hartbeats of all servers in your enterprise |
|
GEM Installer: The installer for the
other components |
Components of GEM
|
|
|
GEM Batch Scripts: The GEM Installer
creates and schedules a set of custom scripts to collect information and
monitor your environment |
|
GEM Stored Procedure Library for Sybase
and SQL Server expands the vendor provided system procedures |
|
GEM Alarm Database: stores alarms and
events |
|
GEM Utilities: numerous command line
utilities provide you with new capabilities |
|
GEM Sybase & SQL Server Backup
Scripts: a flexible plan based set of server maintenance scripts |
GEM Console
|
|
|
|
|
The console is the reporting interface
for GEM |
|
Information at your fingertips |
|
Output delivered through static web
pages |
|
No web server needed |
|
These pages are updated by scheduled
jobs that survey and monitor your environment |
|
Dynamic Navigation Bar |
GEM Manager
|
|
|
|
|
Flexible plugin driven user interface |
|
Plugins Available: |
|
System Monitor (includes Sybase MDA
tables) |
|
Event Viewer |
|
Several plugins under development |
|
|
Alarm Viewer
|
|
|
|
2 Versions |
|
Web script |
|
Stand alone application |
|
Allow enterprise viewing of alarms |
|
Filter To See only the alarms you care
about |
|
Full Alarm Routing Interface
(operators, schedules, etc) |
GEM Installer
|
|
|
|
|
Stores information in configuration
files |
|
These files may be hand edited |
|
conf subdirectory |
|
gem.xml: store for discovered data |
|
Server password files (*.dat) |
|
configure.cfg: maintenance plans |
|
Sets up the software and batch jobs |
GEM Monitoring Scripts
|
|
|
Customized by installation process |
|
Integrated with windows scheduler |
|
Creates a crontab file on unix |
|
Monitoring Script problems are
monitored by the GEM Console |
Stored Procedure Library
for Sybase and Microsoft Sql Server
|
|
|
|
Extend the system stored procedures |
|
significantly more usable |
|
Start with sp__ (2 underscores) instead
of sp_ |
|
Many common procedures |
|
90 New Stored Procedures |
|
New Formatting, Additional Features,
Extend the internal system procedures |
Database Utilities
Library
|
|
|
A large number of automation scripts. |
|
Command line interface only |
|
Contains numerous simple utilities |
|
Several more complex powerful ones |
|
The basis for planned GEM Manager
plugins |
GEM Libraries
|
|
|
Alarming |
|
Database Connectivity |
|
Persistent Database Connectivity |
|
File Retrieval / Log File Filtering |
|
Password Management |
|
Survey |
|
RosettaStone (generic db
administration) |
GEM style
|
|
|
|
All programs and libraries self
documenting |
|
perldoc program.pl |
|
Program usage summary: program.pl -? |
|
GEM Documentation is built from
embedded perldoc |
|
If a program doesn’t work |
|
use diagnosis mode with -d or -–debug args |
|
program.pl –DEBUG or program.pl -d |
|
|
Sybase & SQL Server
Backup Scripts
|
|
|
|
complete server maintenance toolkit |
|
for Sybase and SQL Server |
|
Use command line scripts |
|
Based on Maintenance Plans |
|
Aim to exceed the Native Backup
facilities for SQL Server by providing a command line interface allowings you
greater flexibility and control. |
Sybase & SQL Server
Backup Scripts
|
|
|
|
All Normal Operations Supported |
|
Full and incremental database backups |
|
Log shipping |
|
update statistics & recompile |
|
audit configuration information |
|
Dbcc |
|
index rebuilds |
Sybase & SQL Server
Backup Scripts
|
|
|
|
backup compression, verification,
removal |
|
transaction logs AND full database
dumps |
|
use internal or external compression |
|
Integrated with monitoring and
reporting |
|
commands run from central system |
|
backup success verification (tripple
check) |
Sybase & SQL Server
Backup Scripts
|
|
|
|
|
Output in standard directory structure |
|
BASE_BACKUP_DIR/SERVER NAME/ |
|
errors (existence of file indicates
error) |
|
raw dbcc output |
|
sessionlogs |
|
dbdump |
|
logdump |
|
|
Sybase & SQL Server
Backup Scripts
|
|
|
|
|
Command line interface |
|
backup.pl –JJOBNAME [-t] |
|
dump_database.pl, load_database.pl etc… |
|
Define Maintenance Plans In
configure.cfg |
|
If your Job is named MYPROD |
|
NUM_BACKUPS_TO_KEEP=1 |
|
MYPROD_NUM_BACKUPS_TO_KEEP=2 |
|
The above keeps 2 backups for Plan
MYPROD |
Sybase & SQL Server
Backup Scripts
|
|
|
|
Summary |
|
Complete Solution for Sybase and Sql
Server |
|
Use instead of sql server native
facilities if you need log shipping or compression |
|
Use for sybase |
GEM Console
|
|
|
|
Morning Review |
|
Checks logs and systems tables |
|
Space monitoring |
|
Allows you to proactively manage
servers |
|
Extensive Configuration Reporting |
|
Statements needed to rebuild servers |
GEM Console
|
|
|
|
Collects Information into a database,
xml, and flat files |
|
Error Logs (all of them) |
|
Backup Server Log Contents (for backup
check) |
|
Server Configuration (from internal
commands) |
|
System Historical Information |
|
Process Issues – blocks, connectivity |
GEM Console
|
|
|
|
Customization |
|
Can add reports via flexible
configuration file |
|
Can add reports by placing their output
in specified directory |
|
Library api’s (log file filter, db
connection, alarming etc) |
|
Everything looks like a single
enterprise |
|
Many reports span systems and servers |
|
Alternate views for QA, Development,
Production |
GEM Console
|
|
|
|
Backup / Nightly Job Monitoring |
|
Backup success report (with history) |
|
DBCC success/fail (with history for
transient errors) |
|
Tripple checks backups and log shipping |
|
Rules based server analyzer |
|
whenever you think of a rule, add it to
a procedure named sp__auditdb and it will appear in the report |
|
Web Based |
GEM Console
|
|
|
|
Individual reports on your systems
& servers |
|
Summary aggregate reports |
|
side by side server comparisons |
|
Space growth details |
GEM Console Screenshot
GEM Console Screenshot
GEM Console Notes
|
|
|
|
|
Audit security & configuration |
|
Logins & roles |
|
Security and Configuration problems |
|
example: trojan horses, more devices than max devices parameter |
|
Creates the DDL required to rebuild
your server |
|
Security – Cross server |
|
Login Failures |
|
Changed Object Report |
GEM Alarming and
Monitoring
|
|
|
|
Uses a central database |
|
Use simple function library |
|
heartbeat |
|
Event |
|
Numerous batch jobs to monitor |
|
Full featured alarming |
|
Simple To Use, Powerful, and Complete |
Ad Hoc GEM Tools
|
|
|
Reverse Engineering |
|
DB Wide Performance Tuning Using
Showplan |
|
Log File Filter |
|
Compare DDL in 2 Servers/Databases |
|
Object Dependency Analyzer |
Where To Start
|
|
|
Documentation at www.edbarlow.com |
|
GEM Documentation: www.edbarlow.com/gem |
|
Read the Getting Started Guide |
|
Contact us for a trial |
Minimum Requirements
|
|
|
|
A Database in SQL Server or Sybase
(preferred) of 100-200MB |
|
Dedicated monitoring servers preferred |
|
Can be done with a single Windows
server and a shared unix server (light unix load) |
|
Perl 5.6 or later |
|
Samba if you are using both unix and
sybase |
Summary
|
|
|
Full Featured, Perl, Open Source |
|
Simple & Practical – designed for
the working dba |
|
Parts distributed as free software for
15 years |
|
Well Designed (obvious directory tree,
self documentation, embedded diagnostics) |
|
Automation For Effective multi-server
administration. |
|
Satisfaction Guaranteed |
Automation
The
Key to Effective Multi-Server Systems Administration.
GEM
The
Key to Automating Your Database Environment