Dgarratt: Updated by push_wiki.py

2026-09-22T20:13:28Z

Updated by push_wiki.py

New page

'''Install Database''' is step 2 of the [[Install Guide]]. Commander4j keeps all of its data in a database that you install first. This page walks through '''Microsoft SQL Server Express on Windows''' in full, then covers MySQL, MariaDB and Oracle.

You only do this on the computer that will hold the data: the server, or the single PC if you are trying Commander4j out. Workstation PCs skip this page.

== Which database? ==

{| class="wikitable"
! You are on !! Use !! Why
|-
| '''Windows''' || '''Microsoft SQL Server Express''' (free) || Best supported, free, and the same product every live Commander4j site runs. This is the path described in full below.
|-
| macOS or Linux || MySQL (free) || Simple to install. MariaDB also works with two settings changed.
|-
| A site that already runs Oracle || Oracle || Fully supported, but not recommended for a first evaluation because it is much more work to set up.
|}

Whichever you choose, the end result is the same three things: a database server that accepts '''TCP/IP''' connections, an empty database called <code>Commander4j</code>, and a way for Commander4j to log in to it.

== Microsoft SQL Server Express (Windows) ==

Allow twenty minutes. You will install two programs: SQL Server Express itself, and SQL Server Management Studio (SSMS), the tool used to look after it.

=== 1. Install SQL Server Express ===

# Go to the Microsoft [https://www.microsoft.com/en-gb/sql-server/sql-server-downloads SQL Server downloads] page, find '''Express''' and click ''Download now''. The file is small (about 6 MB) because it downloads the rest as it runs.
# Run the downloaded file. Choose the '''Basic''' installation type.
# Accept the licence terms and leave the install location as it is. Click '''Install'''. The download and install take five to ten minutes.
# The final screen shows ''Installation has completed successfully''. Make a note of the '''Instance name''', which is <code>SQLEXPRESS</code>. Click the '''Install SSMS''' button; it opens the SSMS download page in your browser. Leave this window open until SSMS is installed, then click ''Close''.

[Screenshot to add: SQL Server Express installer, ''Basic'' option]
[Screenshot to add: SQL Server Express final screen showing ''Instance name: SQLEXPRESS'' and the ''Install SSMS'' button]

=== 2. Install SQL Server Management Studio (SSMS) ===

# On the page that opened, click ''Download SQL Server Management Studio''. It is a separate 1 GB download.
# Run it and click '''Install'''. Accept the defaults. Restart the PC if it asks.
# Open '''SQL Server Management Studio''' from the Start menu. A ''Connect to Server'' box appears. Enter:
#* Server name: <code>localhost\SQLEXPRESS</code>
#* Authentication: ''Windows Authentication''
# Click '''Connect'''. On the left you now see ''Object Explorer'' with your server at the top.

[Screenshot to add: SSMS ''Connect to Server'' box filled in]

=== 3. Create the Commander4j database ===

# In Object Explorer, right-click '''Databases''' and choose '''New Database…'''.
# Database name: <code>Commander4j</code>. Leave everything else as it is.
# Click '''OK'''. <code>Commander4j</code> now appears under Databases.

[Screenshot to add: SSMS ''New Database'' dialog with the name filled in]

=== 4. Decide how Commander4j will log in ===

There are two ways. Pick one.

'''Option A: Windows login (simplest, one PC).''' Commander4j logs in as whoever is logged in to Windows. Nothing to create: the Windows user who installed SQL Server is already an administrator of it. In [[Setup4j]] you will tick ''Integrated Security'' and leave the username and password blank. Use this for trying Commander4j out on a single PC.

'''Option B: SQL Server login (recommended for a server with several PCs).''' A database username and password that work from any PC. Three steps in SSMS:

# '''Allow SQL logins.''' Right-click the server name at the top of Object Explorer and choose '''Properties'''. Click the '''Security''' page. Under ''Server authentication'' select '''SQL Server and Windows Authentication mode''' and click OK. SSMS warns that the change needs a restart: right-click the server name again and choose '''Restart''', then ''Yes''.
# '''Create the login.''' Expand '''Security''', right-click '''Logins''' and choose '''New Login…'''. Fill in:
#* Login name: <code>commander4j</code>
#* Select '''SQL Server authentication''' and type a password twice. Write it down; you will type it into Setup4j.
#* Untick '''Enforce password expiration''' (and ''User must change password at next login'' if it is ticked), otherwise Commander4j will stop working when the password expires.
#* Default database: <code>Commander4j</code>
# '''Give it the database.''' Still in the New Login window, click the '''User Mapping''' page on the left. Tick '''Commander4j''' in the top list, then tick '''db_owner''' in the bottom list. Click '''OK'''.

[Screenshot to add: Server Properties, Security page, ''SQL Server and Windows Authentication mode'']
[Screenshot to add: New Login, General page]
[Screenshot to add: New Login, User Mapping page with Commander4j and db_owner ticked]

=== 5. Turn on TCP/IP ===

This is the step everyone misses. SQL Server Express installs with network connections '''switched off''', and Commander4j needs them even when it runs on the same PC.

# Open '''SQL Server 2022 Configuration Manager''' (or the 2019 version). It is not always in the Start menu: if you cannot find it, press ''Windows key + R'', type <code>SQLServerManager16.msc</code> (use <code>SQLServerManager15.msc</code> for 2019) and press Enter.
# On the left expand '''SQL Server Network Configuration''' and click '''Protocols for SQLEXPRESS'''.
# On the right, right-click '''TCP/IP''' and choose '''Enable'''. Click OK on the warning.
# Right-click '''TCP/IP''' again and choose '''Properties'''. Click the '''IP Addresses''' tab and scroll to the very bottom, to the section called '''IPAll'''. Clear the '''TCP Dynamic Ports''' box (delete the number in it) and type <code>1433</code> into '''TCP Port'''. Click OK.
# On the left click '''SQL Server Services'''. Right-click '''SQL Server (SQLEXPRESS)''' and choose '''Restart'''.

[Screenshot to add: Configuration Manager, Protocols for SQLEXPRESS, TCP/IP enabled]
[Screenshot to add: TCP/IP Properties, IP Addresses tab, IPAll section with TCP Port 1433]

Setting the port to 1433 means every PC connects the same way. If you prefer to leave the dynamic port as it is, then instead start the '''SQL Server Browser''' service (same window, set its Start Mode to Automatic) and open UDP port 1434 in the firewall as well as the TCP port.

=== 6. Windows Firewall (only if other PCs will connect) ===

Skip this on a single PC. On a server that Workstation PCs connect to:

# Open '''Windows Defender Firewall with Advanced Security''' from the Start menu.
# Click '''Inbound Rules''', then '''New Rule…''' on the right.
# Rule type '''Port''', next. '''TCP''', ''Specific local ports'' <code>1433</code>, next. '''Allow the connection''', next. Leave all three profiles ticked, next. Name it <code>SQL Server Commander4j</code> and finish.

=== What Setup4j will need ===

Keep these to hand for [[Setup4j]]:

{| class="wikitable"
! Setup4j field !! Value
|-
| Database Type || SQL Server
|-
| Server || <code>localhost</code> on the same PC, or the server's name or IP address from another PC
|-
| SID || <code>SQLEXPRESS</code> (the instance name)
|-
| Port || <code>1433</code>
|-
| Database || <code>Commander4j</code>
|-
| Integrated Security || Ticked for Option A. Unticked for Option B, with Username <code>commander4j</code> and the password you chose.
|-
| Trust Server Cert || Ticked (Express uses a self-signed certificate)
|}

== MySQL (macOS, Linux, or Windows) ==

# Install MySQL Community Server from [https://dev.mysql.com/downloads/mysql/ dev.mysql.com]. On a Mac the <code>.dmg</code> installer adds a MySQL pane to System Settings where you start and stop the server. On Ubuntu or Debian, <code>sudo apt install mysql-server</code>. On Windows use the MySQL Installer and choose ''Server only''.
# During install you set a password for the <code>root</code> user. Write it down.
# Open a terminal (or MySQL Workbench if you installed it) and log in: <code>mysql -u root -p</code>. Then run:
CREATE DATABASE Commander4j CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'commander4j'@'%' IDENTIFIED BY 'choose-a-password';
GRANT ALL PRIVILEGES ON Commander4j.* TO 'commander4j'@'%';
FLUSH PRIVILEGES;
The <code>'%'</code> lets the user connect from any PC. Use <code>'localhost'</code> instead if only this computer will connect.
# MySQL listens on TCP port 3306 by default. If other PCs will connect, check that <code>bind-address</code> in the MySQL configuration is not limited to 127.0.0.1, and allow port 3306 through the firewall.

For Setup4j: Database Type ''mySQL'', Server <code>localhost</code> (or the server name), Port <code>3306</code>, Database <code>Commander4j</code>, Username <code>commander4j</code>, Collation <code>utf8mb4_0900_ai_ci</code>, CharSet <code>utf8mb4</code>.

== MariaDB ==

Install MariaDB and create the user exactly as for MySQL, but create the database with MariaDB's own collation (it does not have <code>utf8mb4_0900_ai_ci</code>):

CREATE DATABASE Commander4j CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

In Setup4j select ''mySQL'' as the Database Type and set:

Collation utf8mb4_general_ci
Char Set utf8mb4

== Oracle ==

Only recommended if you already have an Oracle database administrator. Ask them to:

* Create a schema user for Commander4j with rights to create tables, views, indexes, sequences and functions (granting the <code>RESOURCE</code> and <code>CONNECT</code> roles plus <code>UNLIMITED TABLESPACE</code> is simplest to start with).
* Confirm the listener is running on TCP (port 1521 by default) and accepts remote connections.
* Give you the server name, port, service name or SID, username and password for Setup4j.

'''Next step:''' [[Install Commander4j]].

[[Category:Commander4j]]

Install Database - Revision history

Dgarratt: Updated by push_wiki.py