Get Better Hold over Your DB

A database is the backbone of any enterprise application. And most of these

applications use database management systems (DBMS) to create, store and manage

data. These DBMS software, like Oracle, IBM DB2, Sybase, Microsoft SQL Server or

MySql are responsible for creation, updation, retrieval and storage of

information in a structured format. Plus, it must also maintain data integrity,

access control and security. Since there's so much to do in a database, the

organization must dedicate a database administrator for the job, who has to

ensure efficient and proper design of data in the database. If anything goes

wrong with an application, the DBA has to use his skills to check whether the

problem arose because of a fault in the database design or due to the

application's logic. This is not an easy task, which is why (fortunately) there

are database management tools that help a DBA keep a real-time check on the

performance of DBMS. Not only do these tools provide for performance optimality

but also help in the design and development of database on various DBMS

platforms. The database management tools can broadly be classified into two

categories: Production, and Development tools.

DBManager's Query Builder can be used to create queries by selecting the appropriate data object from the Objects panel. The result is displayed in the Output panel

Production DBA tools



A database administrator is required to maintain the production and test

environments of an application development project. The tools used to manage the

production environment are used to sort issues related with database

performance, administration and database recovery.

The DBA can tune and improve the performance of a database by using such

tools. The performance-monitoring tool helps a DBA gauge the efficiency and

responsiveness of SQL queries, system parameters and database structures. Such

performance monitoring tools run in the background and maintain a log of the

database performance statistics and alert the DBA on occurrence of any problem.

Some advanced tools can also take proactive steps to counter the problem so that

database performance is not hindered at all.

The performance enhancement tools should monitor three aspects of a database

application, which are database structure, the system itself and the

application. These tools help manage the system resources perform optimally,

while allowing you to analyze and optimize queries and run applications at top

speed.

On a day-to-day basis a DBA requires some administration tools that help him

in simplifying the tasks ranging from database object creation to database

synchronization. In the absence of such administrative tools, the DBA would be

required to write complex scripts for such intricate tasks that would consume

time and would sometimes be error-prone too. Such administrative tools help DBA

by automating some of the complex tasks and also help him in saving time.

Most of the DBMS applications provide basic backup and recovery features.

However, these can't satiate the requirements of a DBA. He needs tools that can

automate processes of backup and recovery and can also implement procedures for

disaster recovery. The recovery tools help the DBA in such tasks. Some advanced

recovery tools are also available that examine the database logs and then

perform online SQL-based recoveries of the crashed or corrupted databases.

Adoption of such tools by DBAs that have automation capabilities, results in

great improvement of the data availability and integrity, and hence improved

performance of the database.

Development DBA tools



During the development of application database, the DBA focuses on building

an effective database environment to support applications running in an

organization. As the programs for an application are built, the DBA also has to

look toward providing proper database structures that would be required by the

application.

DBA tools step in here to support the development of database by providing

features for designing E-R diagrams of the database and translating the logical

diagram into physical implementation. Such tools also provide auto generated

test data that would be used on the developed database to check primarily for

data integrity and also for other purposes. The database developers can also use

such tools for creating the procedures, functions or triggers for the database,

by using their SQL editors.

In this article, we have covered some of the DBA tools that might help a DBA

to develop databases and also for the maintenance of the production database.

These tools are available as a package having a host of in-built

functionalities. We also talk about a few which are available as individual

function tool and help in database management or creation.

DBManager Pro



A company named DBTools Softwares offers a complete database management tool

called DBManager that is compatible with various databases like Oracle, MS SQL

Server, Sybase, MS Access and MySql. This tool is a comprehensive package of

features that a DBA can use to properly manage the database and can also help in

efficient database development.

DBManager is designed to support multiple database engines. As applications

are being built on different DBMS platforms, tools like DBManager have to help a

DBA to have a single interface for various databases that he monitors or is in

charge of. DBManager does that with perfection. The tool provides easy

management of databases and their tables, procedures, triggers and functions.

The interface has default database server connections made for SQLite and MS

Access. A new database server connection can be easily made with the Server

Manager.

Many a times it is required to import database from one DBMS to another. This

requires the DBA to configure various import/export settings and then import the

database in a particular format, which can be CSV or XML. DBManager makes the

task of the DBA easy by providing DAO import of database from MS Access; also

the database can be imported from an XML-based file. For this DBManager has easy

to use wizards that allow users the freedom to create the database or to copy

imported database tables into an existing database schema.

For development support, the tool has wizard-based table creation features

that can provide constraints and relationship management for a table. You can

easily create forms and reports for a database table using the Form or Report

designers. The Query Builder can be used to create and test queries. Through the

Objects panel when you select SQL/DDL templates, you can have a list of

templates for various queries, which can be modified to create your own query.

Through the management options a DBA can view the status of the server,

database and table activities. The Tools menu also has an option for Task

Builder through which the DBA can automate some of the processes like import and

export, data manipulation, etc.

SQL Manager has a detailed interface for database browsing. A DB structure can easily be manipulated by clicking on its object in Explorer pane

EMS SQL Manager



EMS has introduced SQL Manager that provides administration and development

support to specific databases. The package is available for DBMS like Oracle,

PostgreSql, MySql, SQL Server and DBISAM. Here we would be trying SQL Manager

for MySql.

The SQL Manager for MySql has full support for MySql server. It is a tool

when used for development can help in rapid database creation and efficient

designing of the database objects. This tool when in a DBA's arsenal will

empower him and his development team to easily create, manipulate and manage the

database and its objects.

When installed, the tool gets easily configured with the MySql server

instance. The databases on the server get displayed on the database panel. By

expanding a database node the structure of the database can be browsed for

viewing tables, views, functions, procedures, etc. The interface is easy to get

familiar with for a developer, as the panels for data object creation and

browsing are well laid out. The tables can be edited and their properties

changed by directly double clicking on them.

You can create a new database through Database menu option. The Report

designer can be used to create reports in a diagrammatical manner. The interface

has tabs to switch between Code and Design view. For a DBA, it's always a

concern to keep the check on data integrity, data consistency and data

structure, while designing a database. With a tool like SQL manager the work of

managing a database for such intricate tasks has become easier.

DTM database tools



DTM database tools unlike other tools are not a package, but a series of small
tools that have specific database functionality. These tools can be downloaded

from www. sqledit.com. A DBA can choose the functionality he needs a tool for,

and can download that in isolation rather than having the whole package.

One such tool that a DBA would need is Data Modeler that can be used by

database developers for forward and reverse engineering. This tool can convert a

data model into the corresponding database by creating necessary SQL scripts.

Sometimes, it is required to compare two databases, one from production and

the other from development environment; or in case of active production data,

and its backup. For such cases DTM Data Comparer is used that will compare the

two data objects of same or different databases and indicate the differences

between them. It can also be used for synchronization of the database.

Once the development of a database has been completed, it has to be populated

with some test data so that it can be checked with the application for

consistency. DTM Data Generator is a tool specifically catering to such need.

Like other packages it also contains an SQL editor. A DBA can use this tool with

his developer team.

Thus, depending on his requirements a DBA can choose for package based

database management tools or can use tools that are available for specific

functionalities of database management.