Managed SQL Database Servers
A managed SQL database server is a server instance of the PostgreSQL® relational database management system controlled by the Model Manager server itself. The Managed SQL Database Servers page, opened by clicking SQL Database Servers in the System navigation sidebar, shows a table with the configurations of all such managed SQL database servers. A single configuration — Default managed PostgreSQL® server — is initially shown in the table. This default managed SQL database server is automatically created by the Model Manager server when the latter is started for the first time — see also The Default Managed Database.
Links to the Managed SQL Database Servers page are hidden in the web interface if you cleared the Managed PostgreSQL® check box in the Products step of the Model Manager server installation.
Click on the label of a managed SQL database server in the table to show more details for that managed server. Click the Add button to add a new managed SQL database server.
Adding Managed SQL Database Servers
You can add a new managed SQL database server to Model Manager server. This is useful if, for example, you want to use different options than the default ones for the automatically added managed SQL database server but, at the same time, want to keep the default one around as a reference.
To add a new managed SQL database server:
1
Write a label for the managed SQL database server in the Label field.
2
Write the path on the file system where the data directory for the managed SQL database server will be created in the Data directory field.
The data directory must be located on a physical disk unless running on Linux® using NFS mounted in hard mode. Other network disk configurations are not supported.
3
Select when and how the managed SQL database server is started in the Start mode list. Select Automatic if the server should start when the Model Manager server is started or when the SQL database server is used by a Model Manager database. Select Manual if the server must be manually started on The Managed SQL Database Server Page.
4
Select the Enable backup check box to enable backups of the data directory. Write the path on the file system where backups will be written to in the Backup root directory field. The backup root directory will be automatically created if it does not already exist on the file system. See also Backup of a Managed SQL Database Server.
5
In the Trigger mode list under Restore, select Automatic (most recent) if the data directory should be automatically restored from the most recent backup if it is missing on disk, or if a previous restore has not yet finished, when the managed SQL database server starts. Select Manual if a restore requires manual triggering on The Managed SQL Database Server Page. See also Restore of a Managed SQL Database Server.
6
Click Save to add the new configuration. The data directory and the backup root directory will be automatically created as needed the next time the managed SQL database server is started.
The specified data directory must either not exist at all on the file system or, if it exists, be empty if you want to create a new managed SQL database server. A nonempty data directory is assumed to belong to an existing managed SQL database server that you want to add a configuration for in the Model Manager server.
The parent directory to the data directory must already exist on the file system for the server to automatically start after saving. If not, you can create the directory from The Managed SQL Database Server Page and then manually start the server.
Placing the data directory of a managed SQL database server on a network file system is not supported unless running on Linux® using NFS mounted in hard mode — see also Installation Planning.
Prefer placing the backup directory on a physical disk different than the disk containing the data directory. Otherwise, you might risk complete data loss in case of a server disk crash.
At least one SQL database needs to be added to the managed SQL database server to use it for a Model Manager server database — see SQL Databases.
The Managed SQL Database Server Page
The Managed SQL Database Server page contains configuration details for a managed SQL database server that is added to the Model Manager server — see Adding Managed SQL Database Servers. The configuration fields are:
•
Platform. The database system platform used for the managed SQL database server.
•
Label. The label of the managed SQL database server. Used for display purposes in the Model Manager server web interface.
•
Data directory. The file system path to the data directory of the managed SQL database server.
The currently available disk space on the file storage containing the data directory is shown below the path.
You can see the current status of the data directory in its Status field. If the data directory does not exist on the file system, click the Create button to create it.
•
Start mode. The start mode of the managed SQL database server — Automatic if the server starts when the Model Manager server is started or when the SQL database server is used by a Model Manager database, Manual if the server must be manually started.
The adjacent Status field shows, for example, if the server is currently started or if the server has failed to start due to some configuration issue. For the latter case, read the nested error information to look for hints to the underlying cause.
Click the Stop button to stop a running managed SQL database server. Click Start to start it.
To edit the configuration for a managed SQL database server, proceed as follows:
1
Deactivate all Model Manager server databases using SQL Databases in the managed SQL database server. See also The Database Configuration Page.
2
Click the Stop button to stop the managed SQL database server.
3
Click the Edit button.
4
Enter the new configuration settings. If you change any directory paths, manually move the corresponding directories on the file system via, for example, a system file explorer before saving.
5
Click the Save button.
6
Start the managed SQL database server if not automatically started.
7
Activate all databases deactivated in the first step.
Deleting a Managed SQL Database Server Configuration
Click the Delete Permanently button to permanently delete the configuration.
Clicking Delete Permanently will only delete the configuration for the managed SQL database server; it will not delete its data directory or backup root directory. You can add an existing SQL database server again by adding a new configuration that points to its data directory and backup root directory.
SQL Databases
The version control management system for simulation models and other data are stored in SQL databases in the managed SQL database server. A single Model Manager server database uses exactly one SQL database. The SQL databases are stored inside the data directory for the managed SQL database server.
The Databases section shows a table with all SQL databases in the SQL database server. The table columns are:
•
The Name column — the name of the SQL database.
•
The Used by column — the Model Manager database using the SQL database, if any.
•
The permanently delete column — click the delete button to permanently delete the SQL Database.
Permanently deleting a SQL database cannot be undone unless you restore the managed SQL database server from a backup.
To add a new SQL database, write the name of the database and click the Add button under the Databases section on the Managed SQL Database Server page. The name may only contain the letters a–z and digits 0–9 and must start with a letter. Some specific names are explicitly forbidden — either because these databases already exist internally in the database server or because they are reserved names. The Model Manager server will show an error message if such a forbidden name is entered. A suggested name is modelmanager — the same name used by The Default Managed Database.
The managed SQL database server must be running in order to add new databases.
Backup of a Managed SQL Database Server
When you enable backup for a managed SQL database server, it will continuously back up any data written to its data directory to a subdirectory inside the backup root directory. This backup is incremental in the sense that it records all changes made in the server’s SQL databases as a transactional log. When you restore a managed SQL database server, including all its SQL databases, this transactional log is replayed.
Since the time it takes to replay the transactional log scales with the running time of the managed SQL database server, it is wise to periodically create a new base backup. This is a complete point-in-time snapshot of the managed SQL database server. Once you have created such a base backup, the incremental backup will instead record changes using that backup as a new baseline — thereby speeding up any future restore.
On the Managed SQL Database Server page, click the Trigger Base Backup button to create a new base backup. The Backup root directory field shows the root directory path in which backups are stored.
A new base backup is automatically created when you enable backup for a managed SQL database server.
Restore of a Managed SQL Database Server
If you have configured a managed SQL database server with Trigger mode for restoring set to Automatic (most recent), a missing data directory will be automatically restored using the most recently backed-up state in the backup root directory when the managed SQL database server is started.
You can also manually restore the data directory of a managed SQL database server by clicking the Trigger Restore button on the Managed SQL Database Server page. A confirmation dialog box is shown, informing you that the following steps will be taken by the Model Manager server:
1
The SQL database server is stopped if currently running.
2
Any existing data directory is renamed to avoid overwriting it with the restored data.
3
The most recent base backup is copied from the backup root directory to the data directory.
4
The SQL database server is launched in recovery mode and automatically restores itself using the most recently found backed-up state — see Backup of a Managed SQL Database Server. Once finished, the SQL database server is made available again.
You are strongly recommended to also recreate any search indexes whose indexed data is populated from SQL databases in the restored SQL database server — see the manual steps in Restore of a Managed Search Index Server. Otherwise, search results in the Model Manager may not reflect what is stored in the SQL database.
You are recommended to deactivate any Model Manager server database using SQL Databases in the managed SQL database server before triggering a restore. See The Database Configuration Page.