The job of DBA

– “What does the job of DBA consist of?”

The DataBase Administrator or DBA is responsible for the design, implementation and management of DataBase Management Systems (DBMS).
DBMS, which are system software designed to create and manage databases, make it possible to read, write, modify, sort or restore information from these databases, depending on requests made by the user.

The DBA ensures the operation, but also the maintenance of these DBMS by adapting them according to technological developments and the needs of the company.
It is thus guarantor of the accessibility, quality and security of the data.

But the DBMS are numerous and varied and most of the time, they are of different designs, hence the difficulty of the DBA’s job and the extent of his task if he has to work on several DBMS.

Our CEO, Michel Payan, who has been a DBA for 25 years, has often noted that the companies in which he worked were faced with the same problem of setting up and maintaining the operating scripts of their DBMS.
With always the same need to guarantee the homogeneity of scripts across the fleet, the quality of the code, the management of errors and return codes.
This is why he had the idea and the desire to create multi-DBMS operation and maintenance software that could make the daily life of DBAs easier.

The dbSQWare software has now existed for 17 years and it is the only software in France capable of operating and maintaining, in a completely homogeneous manner, 12 different types of database engines.

V2024.01

A new major version of dbSQWare has just been released!

This first version of the year was a little later than expected, because it includes 2 major new features:

– the possibility of running jobs on demand via the graphical interface:
–> creation of jobs by administrators,
–> delegation of the right to execute these jobs to certain groups of users,
–> launching jobs by authorized users,
–> monitoring the execution of these jobs.

– the end boundary for exceptions:
–> possibility of putting an end date on a specific setting (check backups, thresholds, etc.)

Constantly evolving, dbSQWare adapts to the expectations of our users to facilitate their daily lives and gain in efficiency and time, on the daily tasks of operating database engines.

INSTALL dbSQware

Unique in its kind, the dbSQWare software allows its users to manage all of their DBMS in a completely homogeneous manner, despite their heterogeneity.

Its installation takes place in 4 essential phases:
๐Ÿ”Ž  the definition and study of the architecture in place,
๐Ÿ’ป  the configuration and customization of dbSQWare according to the environment,
๐Ÿš€  software deployment,
๐Ÿค  and finally, the passage of knowledge on its daily use.

Installing dbSQWare means choosing reliability, agility and peace of mind, through global management and vision of your databases.

12 DBMS SUPPORTED

dbSQWare is a multi-DBMS operating and maintenance software that supports 12 types of database engines which are among the most widespread and most used.
Supported versions have a range of 30 years, from the early 90s for some DBMS (Oracle 7, SQL Server 6.5) until today.

dbSQWare supports 9 RDBMS (Relational Database Management System):
 ๐Ÿ‘‰   Oracle, most popular on UNIX/Linux
 ๐Ÿ‘‰   SQLServer, developed by Microsoft and mainly deployed on Windows
 ๐Ÿ‘‰   Teradata, a historical RDBMS with colossal storage capacities
 ๐Ÿ‘‰   Sybase, acquired by SAP and former market leader
 ๐Ÿ‘‰   DB2 LUW, the distributed system version of the Mainframe engine
 ๐Ÿ‘‰   Ingres, dating from the 80s and in Open Source since 2004
 ๐Ÿ‘‰   MySQL, the most popular Open Source RDBMS
 ๐Ÿ‘‰   MariaDB, a fork of MySQL
 ๐Ÿ‘‰   PostgreSQL, the most advanced Open Source RDBMS

and 3 NoSQL:
 ๐Ÿ‘‰   Cassandra and her column-oriented organization
 ๐Ÿ‘‰   MongoDB, the most popular NoSQL, with a document-oriented organization
 ๐Ÿ‘‰   Adabas Linux version, accessible by the natural language.

Despite the diversity of these database engines and their heterogeneity, dbSQWare makes it possible to have a common approach via centralized and homogeneous exploitation.

V2023.01

The new version V2023.01 of dbSQWare has just been released!

This first version of the year includes several major new features, such as:

– the addition of the “tee” command and the removal of the “tail” command at the log management level, in all scripts,
(the custom scripts in place will have to be adapted)
– the management of a locking file for SQL Server, in order to avoid having two backups of the same type at the same time on the same database,
– use of pigz (if installed) instead of gzip for archive backups, PITRs and dumps in PostgreSQL.

Constantly evolving, dbSQWare adapts to the demands of our users to make their daily life easier and to gain in efficiency and time, on the daily tasks of operating database engines.

DBMS: Update Statistics

A statistic (on a table, a column / a set of columns or on an index) provides information on:
– the table (number of lines, average size of a line, number of blocks/page, density),
– the distribution of values โ€‹โ€‹for the column(s) (number of distinct values, number of duplicates, existence of null values, histogram of distribution of values),
– the composition of the index (depth, number of blocks, cluster ratio).

These statistics are used by the database optimizer to study the different execution plans that can be used for a query.
The optimizer (based on costs / CBO) chooses the best execution plan (least expensive) from the data calculated from these statistics on, in particular, the number of rows and blocks of data to be shuffled at each stage of these plans (access strategies).

Statistics are essential for the proper calculation of query execution plans. It is therefore essential that they are up to date to take into account changes to the data and therefore to the indexes, columns and tables.
Thanks to update statistics, the optimizer has reliable data to choose the best performing execution plan to serve data to applications.

DBMS: Reorganize

An optimized database improves query performance, reduces processing times and reduces resource consumption (CPU, RAM, Storage, etc.). This is why it is essential to maintain an efficient organization of databases.

Hence the need for reorganization when this is not the case.
This task consists of physically reorganizing indexes and/or tables with the aim of optimizing the use of disk space and improving access to data (through their organization), recovering unused disk space, left by deleted records and/or data updates.

The operation to be carried out depends on the degree of dispersion of the index (or table) information in the database (called fragmentation):
– compaction (REORGANIZE or COMPACT) is often necessary for fragmentation between 10 and 30%
– beyond that, a reconstruction (REBUILD) must be carried out.
A “side effect” of rebuilding a table is rebuilding all of its indexes.

In any case, a successful database is a database that is monitored and maintained.

V2022.04

dbSQWare version 2022.04 has just been released!

This latest version includes several major new features expected by our users:
– the possibility of managing repository instances by API:
         –> in consultation, creation and update
– the ability to generate indicators, by API, for:
         –> backups and storage.
The management of indicators for the Job part is being finalized and will be available in the next patch.

Constantly evolving, dbSQWare adapts to the demands of our users to make their daily life easier and to gain in efficiency and time, on the daily tasks of operating database engines.

V2022.03

dbSQWare version 2022.03 has just been released!

With a novelty that should please our users:
– the possibility of delegating the authentication, to connect to the software, to an external application, AD style.

Not to mention that dbSQWare is the only DBMS management and maintenance software capable of managing 12 types of database engines in a consistent manner.

DBMS: Environment copy

– “The environment copy, what is it for?”

The environment copy allows you to duplicate an environment and thus have 2 identical environments (same structure, same tables, same data) on 2 different servers.

The interest is to have, for example, a development environment identical to that of production (in the case where the prod environment is copied in dev) and to provide developers with a working base reliable and up-to-date before they start coding (Caution, it may be necessary to anonymize the data!).
In other cases, the environment copy can make it possible to put a test environment into production, if it has successfully passed all the validation stages, or to set up a DRP (Disaster Recovery Plan) on D-1.

Finally, if the duplication is done through backups, this makes it possible to validate their viability.