A Brief History of Modern RDBMS IT Management Article 4: Under the Hood of the RDBMS Farmer Brown, Falls Creek, OZ Late in the Southern Winter of 2002

The following screen is from the SQLServer RDBMS product, specifically from the GUI Enterprise Manager. This one console reveals the nature of standard RDBMS features and services available in the market place today. Again, let me iterate, as far as this article is concerned this screen shot could have come from the DB2 IBM product, or from the Oracle RDBMS.

While there are obviously going to be subtle differences between each of the products due to their history of development, from the perspective of IT Management, all of the products will do the job. This article is from the perspective of IT Management, and examines the collective series of resources available to IT Management and Engineering .







Under the Hood of a Third Millenium RDBMS

Let's start at the top and work down ....
Server Group: Here multiple servers can be accessed in detail as shown for the server above called IT_LAPTOP2. In each of these RDBMS Servers there is the same list of features as shown in detail for IT_LAPTOP2. That is, from one console, a network of RDBMS Servers can be managed irrespective of their location.

Databases (General): The RDBMS environment will support many separate databases, but the global console view does not show the followng expanded detail for each of the databases:


Diagrams: This permits the production of diagrams of the data tables defined within the database showing the relationships between certain key fields. Handy for generating DBA documentation or in preparation for analysis tasks, etc.

Tables: Here in this tables section of the RDBMS resides the organisational data, table by table and row by row. In any one database there may be a handful or a thousand tables.

Views: A view is essentially a selective means of examining an existent larger set of data, and usually involves a specific subset that is distinguished by adherence to some form of selection criteria. It may span many tables and involve complex selection.

Stored Procedures: The RDBMS is bundled with a whole host of system dedicated stored procedures (with SQLServer usually in the master database) and the RDBMS readily accepts user defined stored procedures. Stored procedures are written in TSQL (The Microsoft version of SQL) and are capable of being called directly from the applications environment. They are essentially program code items capable of being stored in the RDBMS itself. They are a critical component of the generalised RDBMS environment, and the importance of stored procedures will be examined in greater detail in subsequent articles.

Users & Roles: Security administration of the database is handled through a combination of users and user roles. Often, security administration needs to first examine the physical network supporting the communication lines to the RDBMS server, and will require separate security administration at the operating system and network operating system environment.

Rules: These represent a backward compatibility feature that performs some of the same functions as CHECK constraints (used at table creation). Rules are able to be bound to columns, but are usually now classified as a subset of the table (data integrity) constraint(s) specifications.

Defaults: There exists the option to enable the creation of default definitions to be associated with specific tables, at time of creation. The effect of default definitions is to force the default entry of specific data into records in appropriate circumstances.

User Defined Data Types: The RDBMS permits user specified data types based on re-arrangement of the existent system data types already known to the RDBMS. These are often handy in enforcing consistent formatting of specific fields (eg: postcode).




Data Transformation Services (DTS)

In the evolution of any organisation there will always arise the cyclical need to export or import bulk data from and to the established databases. In the early days it was a matter of trying to see what combination of tools would see all such tasks get done. With the modern RDBMS DTS features, such export and import tasks are by comparison a breeze. Over successive releases of the RDBMS software, the large three vendors have accumulated a myriad of combinations and permutations to cater for a large range of formats.

Import formats include dBase, Access, Paradox, FoxPro, Excel (various versions), IBM (DB), Oracle, (nb: OLE and ODBC), other SQLServer RDBMS, and plain text. The complementary data export services also provide for the same rich choice of formats.

Tasks involving both imports and exports, can be queued with other contingent tasks for data processing, and all components can be automated, with enough R&D and QA.




RDBMS Management Services

Clustered under this heading are three separate features: alerts, operators and jobs. Alerts are mechanisms of either logging or notifying specific system operators that there has been some form of problem in operations. Alerts are able to be sent to an operator via email, or by pager, or by various other means. The operators feature permits the registration in summary and detail of system related operators, who are defined as the targets for the alert messages.

The final entry labelled "Jobs" is extremely useful in IT Managenement. This is the job queue, or schedule of automated tasks for the entire database system, and as such it is capable of achieving a great deal without human intervention. Within this entry, a DBA or IT Manager can create new scheduled tasks (ie: Jobs) by a variety of means. All database backup tasks, and all database maintenance tasks defined will exist in this area as an entry on the job queue. Tasks can be automated from this area of the RDBMS, in a surprisingly simple fashion.

Backup

Unlike the old days, when a database could only be backed up after all users and all tasks were isolated and logged off, the modern RDBMS is able to take a snapshot backup "on the fly". Backups can be scheduled and automated for ever with little effort here. Often however, RDBMS backup strategies involve the use of performing what is termed incremental backups and additionally retaining a log of transaction for the RDBMS that may be applied against an older version of the original database, for recovery purposes.

Current Activity

Essentially lists all users and tasks logged on to the RDBMS and shows a certain range of information about each task/user including the process id, user name, status, number of open transactions, memory, CPU and disk IO parameters, login information, etc, etc. Also covered here are mechanisms to view and manage database locking.

Database Maintenance Plans

Basically this represents the tasks that physically reorganise the data and index pages, remove unused space or allocate growth percentages. These can be specified for one or all databases, or constituent tables, and may be placed as tasks on the scheduled tasks queue. (See Jobs, above). A number of routine database integrity exception checks are optional with these tasks, for the data and for the data indexes.

Server Logs

The RDBMS logs its own component activity, confirmation of start and end times for system scheduled tasks, errors which are either system defined or user defined (See Alerts above), messages written to the RDBMS from other applications, and messages written by the RDBMS kernel itself. As such, reference to these logs can be an invaluable assistance in the task of problem resolution.

Web Publishing

In only these recent years web publishing, and internet enablement have characterised much of the media's attention. It is therefore not surprising that these modern RDBMS have internal web publishing services that provide means of publishing direct to HTML from either one or more tables, a given TSQL statement, or some predefined stored procedure.

Security

This section of the Enterprise Manager GUI Console covers the security of logins, server roles and remote servers. It is here that specific users can be allocated a default database, and a default language.

Support Services

Additional support services shown above include Distributed Transaction Coordinator and a final entry SQL Mail. The Distributed Transaction Coordinator enables the processing of transactions that span two or more physical servers. The SQL Mail service consists of a number of stored procedures that handle system generated, and system received email messages.

From the IT Management perspective there are services and utilities geared towards automation and integration of all RDBMS features under the bonnet of most modern RDBMS products. There is no doubt that great effort has been expended, millions of man hours in fact, towards delivering a great package of resources.

As these specialised RDBMS products began to evolve and capture the marketplace at the large end, and then at the midrange, and now filtering down into the smaller orgainsations, their services improved dramatically over what was available one or two decades earlier. There is simply little comparison. Under the hood of the modern RDBMS everything is capable of running on well greased bearings for an unlimited time.

At this stage in the evolution of Information Technology, the RDBMS is leading the field in the delivery of an extremely efficient data processing environment that is the main and primary asset of practically every large international organisation on the planet. Investment in this environment in terms of machine hardware, and processors and memory, etc, is at an optimum.

Unfortunately, as we have explained in earlier articles, this RDBMS environment itself resides in a dynamic environment of its own. In the next article we will examine the more expanded computer systems environment that support an RDBMS product, and its corresponding set of database application software products, its corresponding set of network and PC operating system software, and its base hardware and physical communications link.

From my perspective at least, let me iterate this is IT Management, all these RDBMS products are the top of the range of database systems software for most tasks required of them, given sufficient resources, time and planning.

The large volume problematics in IT Management are not really sourced from within the RDBMS environment, once it has been properly established, and properly benchmarked for the tasks contemplated, and the environment has been stabilised so that automation of tasks can commence in earnest.

The large volume problematics in respect of IT Management are in fact sourced dominantly from another environmental process altogether. This other process is known simply as Change Management. And it is in the attempt at managing change that IT Management professionals earn their hardest won dollars, as we shall clearly outline in the next article.

Index/Intro | Article 1 | Article 2 | Article 3 | Article 4| Article 5 | Article 6 | Article 7 | Article 8 | E-Mail

A Brief History of Modern RDBMS IT Management