Databases – Administrator Guide

StackRegent hosts MariaDB and Microsoft SQL Server databases for subscriptions. The database servers can be on this server or on other servers. Server-wide settings are under Tools & Settings › Applications & Databases. A subscription's own databases are on its Databases page.

Install a database server#

Go to Tools & Settings › Server Components.
Install MariaDB Server, Microsoft SQL Server Express, or both. SQL Server Express runs as the instance SQLEXPRESS on port 1433.
To manage MariaDB databases in the browser, also install phpMyAdmin.

Locally installed servers register under Tools & Settings › Database Servers by themselves.

Database Servers#

Tools & Settings › Database Servers lists every server with its type, version, status (Online or Offline) and number of databases. A new database goes to the default server of its type, unless the plan names another one.

Action	How
Add a remote server	Click Add remote server, choose MariaDB or SQL Server, then enter the Host, Port, Administrator login and Password. The login needs rights to create databases and logins. For SQL Server with a self-signed certificate, tick Trust the server's own certificate (self-signed). Click Test and add.
Change the admin login	Click Credentials. Leave the password empty to keep the current one.
Set the default	Click Make default.
Allow remote connections	For a local server, tick Open port N in the Remote connections column. Clients can then connect from anywhere (SQL Server Management Studio, HeidiSQL and similar) with their database login. Tools & Settings › IP Address Banning blocks addresses that keep failing to log in (its Microsoft SQL Server and MariaDB sources). To allow only some addresses, set the server to Allow from listed addresses under Tools & Settings › Firewall; a server set to Deny there cannot be opened here.
Hide other clients' databases	For a SQL Server, tick Hide other clients' databases in the Remote connections column (on by default for the server StackRegent installed). A task makes each database's first read-write user its owner and denies VIEW ANY DATABASE to everyone, so in SQL Server Management Studio a client lists only the databases its login owns. Other users of a database still connect by typing its name under Options › Connection Properties › Connect to database. Empty schemas the user owned (Plesk gives each user one of its own name) pass to `dbo`. A user whose own schema holds tables (often a restored application that uses it) keeps its user and roles instead, and connects by typing the database name. The task log says how many databases got their client as owner. The panel's own login must be in the sysadmin role. Untick to show every database name again.
See connections	Click Processes.
Remove	Click the bin icon. This works only for a server with no databases. StackRegent forgets the server; the server and its data are not touched.

Database Hosting Preferences#

Tools & Settings › Database Hosting Preferences sets two things:

Name prefix. New databases and users start with No prefix, The subscription's system user name (for example shop_), The subscription ID (for example s12_), or A fixed text.
Remote access to MariaDB. By default, database users connect from This server only, Any host, or These hosts (a comma-separated list, for example 203.0.113.5, 192.168.1.%). Each user can override this. Remote connections also need port 3306 open (see Database Servers above).

Add a database to a subscription#

Open the subscription and click Databases.
On the Databases tab, click Add database.
Choose the Type. If the plan allows Database server selection and there are several servers, choose the Server.
Enter the Database name (letters, digits and underscores). The prefix is added in front.
You can choose a Related website. This only helps you keep track; it has no other effect.
Leave Create a database user ticked. Enter the User name and Password, and choose the access: Read and write, Read only or Write only. Tick Access to every … database of the subscription to give the user access to all of them.
Click Create.

The toolbar shows how many databases of each type the plan allows. Click Refresh sizes to measure them again.

Database actions#

Each database row has a ⋯ menu:

Action	MariaDB	SQL Server
Export	Export dump	Back up to the webspace
Import	Import dump (`.sql`, `.gz` or `.zip`)	Restore from a backup (`.bak` or `.zip`)
Check	Check	Check
Repair	Repair tables	—
Copy	Copy to a new database on the same server, with all tables and data. Users are not copied.	same
Remove	Remove deletes the database, its data and its own users.	same

Exports are saved in /private/db_dumps in the webspace. The import dialog lists the files there, newest first. To import a file from somewhere else, upload it with the File Manager first, then choose Another file…. An import replaces tables with the same name, and an SQL Server restore replaces the whole database.

Database users#

The Users tab lists every database user with its server, database, access and remote access. Click Add database user to create one, or Change to edit one. In the dialog:

Database: one database, or All … databases on a server.
Access: Read and write, Read only or Write only.
Remote access (MariaDB only): Server default (Database Hosting Preferences), This server only, Any host, or These hosts. Local connections from this server always work.

When you remove a user, applications that log in as that user stop working. The data stays.

SQL console and SQL Server Web Admin#

Click SQL (MariaDB) or Web Admin (SQL Server) on a database row. The page connects as the database's user, so statements run with that user's rights.

Click a table on the left to show its first rows.
Type one statement in the editor and click Run, or press Ctrl+S.
Results show up to 1000 rows. Statements that change data show the number of rows affected.

phpMyAdmin with single sign-on#

When phpMyAdmin is installed, MariaDB rows have a phpMyAdmin button. It opens phpMyAdmin already signed in as the database user, so nobody needs to type a password. The button needs the database to have at least one user. phpMyAdmin runs on its own HTTPS site on port 9445. The port is shown at the bottom of Database Servers.

ODBC data sources#

Server-wide: Tools & Settings › ODBC Data Sources holds 64-bit system DSNs that every website can use.
Per subscription: the ODBC Data Sources tab on the subscription's Databases page. The plan limits how many DSNs a subscription may have.

Click Add data source, enter a Name, choose the Driver and fill in the Settings (driver keywords such as Server and Database). Passwords are not stored in data sources. Click Test to try a connection with a user and password. Leave the user empty for Windows authentication. Applications connect with DSN=<name> in their connection string.

Database Process List#

Tools & Settings › Database Process List (under Assistance and Troubleshooting) shows the connections and running queries of one server and refreshes every 10 seconds. Choose the server at the top. Click Kill to end a connection and its query.

Troubleshooting#

No database server is available yet: install a server under Server Components, or add a remote one.
A server shows Offline: the reason is shown under the badge. Check that its service is running in Services Management, and that the login under Credentials is still valid.
An application on another host cannot connect: add its address to the database server's list under Firewall, open the port under Database Servers, and set the user's Remote access to These hosts (MariaDB). Failed logins from the network are banned by IP Address Banning (sources Microsoft SQL Server and MariaDB).
The phpMyAdmin button is greyed out: add a user to the database first.