Introduction

“Automation brings velocity, standardization and includes best practices for setting up databases”

In the September issue of The Connection, I introduced NonStop SQL/MX Database Services (DBS) to be included in a pan-HPE solution called HPE Database Platform Automation (DBPA). Only a few months later, the solution was demonstrated at the HPE Discover 2022 user conference in Las Vegas, showing lifecycle management of Oracle, PostgreSQL, and NonStop SQL/MX from the same browser window. An important feature of DBPA, as articulated by the presenter, lies in the automation of the lifecycle process: “Automation brings velocity, standardization and includes best practices for setting up databases”. In addition, it will reduce cost because of that standardization.

In this article, I will describe NonStop SQL/MX DBS considering “Best practices for managing your NonStop database” which is the theme of this issue of The Connection.

Packaged best practices

If you take a closer look at SQL/MX DBS, you will notice that it is built around the capabilities of the SQL/MX Communication Subsystem (MXCS). It adds a set of features that provide better isolation between databases and their users and provides an environment where multiple users can share resources without sacrificing security and confidentiality.

Many features of DBS are implemented best practices that users can apply to an existing MXCS environment, however, by packaging them, you as a customer can use them right away, as standard features of SQL/MX. In case you might ask, yes, DBS is part of the product.

“Which best practices are in the package?”, you may ask. Well, read on.

Separation of duties

Unless specifically enforced in SQL/MX by a system administrator, anyone with a NonStop user-ID can create catalogs on the system and create tables and other database objects on any volume that they have access to. This can lead to database sprawl on the system and the system administrator must monitor which databases are created and by whom and where the data may be located.

With SQL/MX DBS, only a specific set of users can create catalogs, in SQL/MX DBS these catalogs are equivalent to “databases”. One of these specific users is the DBS System Administrator, who creates these databases on behalf of the actual user and then passes the ownership of the database to that user.

The create database task is invoked from a browser-based screen, called the WebDBS GUI, and it requires only a few parameters, the user credentials of the owner and size of the database, being to most important ones, followed by a single mouse-click!

User management

Self-service also means that a database owner can create user-IDs for this database and give access to those users. This includes adding users that act as database owners.

SQL/MX provides the ANSI GRANT/REVOKE access mechanism whereby users can be given specific access to tables in a database. This is a fine-granular way of granting access and places a burden on the time of the busy system administrators. The WebDBS GUI provides a way for the database owner to manage users for their database using four access types. The CREATE, WRITE and READ access types allow this access to the current and future objects in the database. The NONE access type requires that this user needs specific GRANTs to existing objects.

User control of MXCS parameters

Many of the best practices are visible in the Attributes and Control settings of the Datasource. The owner of a database can change some of these parameters: typically, these are the ones that will not impact other users on the system, such as the number of allowed idle servers, or parameters that set the default number of partitions or partition sizes. In traditional SQL/MX, it would require a database administrator to modify these parameters as an MXCS Operator. With SQL/MX DBS however, the database owners only need action from a system administrator if they need a change beyond the self-service capabilities.

Best practices that are worth a specific mention are:

Automatic volume assignments

The CREATE commands for database objects do not require references to the storage locations or volumes. SQL/MX creates hash partitioned tables and indexes on the volumes that are assigned to the database automatically, and as many partitions as defined by the control parameter POS_NUM_OF_PARTNS. ^[1] The value is typically equal to the number of volumes assigned to the database.

Default table sizes to prevent table-full conditions

A common problem for users that are new to NonStop is that they typically create tables and indexes without specifying an initial or maximum size. This can lead to table-full conditions quite easily.

When a database is created, default settings are altered, such that a table or index partition can grow to about 2.5 GB before an error occurs.

These and other defaults can be changed as needed by the database owner. A system administrator can enable other parameters from the rich set of SQL/MX defaults upon request. The user will then find the value in the list of control defaults.

Isolation between databases

With multiple databases, owned by different users on the same system, we need to make sure that these users get the experience of having a dedicated system. SQL/MX DBS, therefore, uses the following principles for isolating users of different databases from each other.

• Users should only see data that they need to. This includes the metadata of a database, and with SQL/MX DBS, users of a database can only see the metadata of their database, not that of any other database, unless they have explicit permission from the owner of that other database.
• All data of a database is placed on one or more volumes dedicated to only that database. The volumes have Safeguard ACLs to protect them from access by other users of the NonStop system.
• Each database has a dedicated MXCS Datasource that is created and started automatically when a database is created. Only users that are assigned to the database can access this Datasource. All other users are denied access. This is a significant difference from the standard MXCS, which allows anyone with a valid NonStop user ID to connect to any Datasource. SQL/MX DBS Datasources and standard SQL/MX Datasources can co-exist on the same system, each serving its own types of users, from different TCP/IP ports.

Single points of access

There are two types of APIs available to interact with a SQL/MX DBS database. One is for administrative tasks such as the aforementioned create database, but also user tasks to manage the database such as taking a backup and starting and stopping the Datasources. These tasks are called from the WebDBS GUI. The name of the user who executes these administrative tasks and the outcome is logged in the system EMS log.

The other type of API provides the standard ODBC and JDBC calls to interact with the database. These include DML and DDL commands but also most utility commands, such as updating statistics and explaining execution plan functionality. These APIs allow users to take advantage of commonly available, often free versions of tools like DBeaver and DBVisualizer. These tools are often used to access other databases in the enterprise, thus adding to a common user experience, regardless of the database that is used.

Convenient metadata views

SQL/MX metadata is stored in regular SQL tables and can therefore be queried with regular SQL DML. However, navigating through these tables takes some effort because these queries can become quite complex. Users of ODBC and JDBC GUI tools can access this data from those GUI tools, and this is very useful. However, for some tasks, like selecting the names of the tables that have no or old statistics, tailored queries must be written. This is where the metadata views are useful. For example, there is a view that shows table information, such as the last time statistics were gathered. A list of all views is contained in the view ZZVIEWS in the current schema.

User support for backup and restore

SQL/MX DBS databases are TMF-protected, and users do have to manage the database log files or TMF-audit trails, as they are also called. It is common practice that this management, including file recovery, is provided by the NonStop System administrator or service provider. Still, users may want to create a backup of their data and restore it when needed. A typical use case is where a standard set of data is used in a development or QA environment. It can be saved on disk from where it can be restored quickly as part of a new development or test cycle.

Backup and restore to disk of SQL/MX objects has been possible for a long time, using the pak2 utility, which in turn invokes Backup/Restore2 (BR2). BR2 includes many options, so many that it is not easy to select the ones you need for a specific action for a user’s situation.

With SQL/MX DBS, there is a clear definition of a database (which is equivalent to a catalog) that contains data organized in schemas. Backup and restore are tasks that are started from the WebDBS GUI. A user can back up all the objects and their data of a schema and store it on the system in a place that is assigned to that database. The user can restore the complete schema from that backup. Backup/restore by schema keeps a consistent set of data, unless, of course, there are data relationships that cross the boundaries of schemas or even catalogs. NonStop SQL/MX allows this; however, it makes database management not an easy task.

Dropping databases

The system administrator can remove databases, however, cleaning up the resources is also a self-service user task. By now, you have seen that SQL/MX DBS puts a lot of power into the hands of a database owner, and while doing her work, SQL/MX may have created quite a bit of extra data in the system. A backup, users and their credentials in Safeguard, Safeguard ACLs, database data, and so on. But because of the standardized setup of a database environment, just a single click is required to remove the database (a confirmation to really drop the database is of course required) and all the other objects that have been created on the system are removed and resources are returned to the pool of free resources.

Conclusion

NonStop SQL/MX Database Services aims to bring a “cloud experience” to both existing users of NonStop and users who are new to the platform and need a reliable SQL database that can scale. The cloud operation model is all about ease-of-use, pay for what you use, and using the resources that you pay for sensibly. SQL/MX DBS provides this by sharing the NonStop resources while separating the users (referred to as the “tenants”) and their data in a multi-tenant environment.

Ease-of-use is a requirement for self-service, and this is facilitated by not only a comprehensive web-based user interface, but also by the implementation of a way of working, organizing data by database, allowing access via JDBC and ODBC, but also by a set of best practices.

If you are interested to experience the end-user side, HPE education and NonStop Academy offer free access to a SQL/MX database on a system that is accessible on the public internet. A request form is located at: https://www.nonstop-academy.com/dbs-access-request/

All you need is to leave your name and email address and in short what you would like to achieve.

For good reasons, you cannot get access as an administrator. However, I will be happy to guide you through the administrator menu items in an online demo. You can contact me for such a demo at [email protected].

1. Currently, this is only the case for hash-partitioned tables. Additional partitions of range-partitioned tables need a volume name to be specified. ↑