Jump to content

Install Database

From Commander4j

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?

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 Commander4j, 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

  1. Go to the Microsoft 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.
  2. Run the downloaded file. Choose the Basic installation type.
  3. Accept the licence terms and leave the install location as it is. Click Install. The download and install take five to ten minutes.
  4. The final screen shows Installation has completed successfully. Make a note of the Instance name, which is SQLEXPRESS. 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)

  1. On the page that opened, click Download SQL Server Management Studio. It is a separate 1 GB download.
  2. Run it and click Install. Accept the defaults. Restart the PC if it asks.
  3. Open SQL Server Management Studio from the Start menu. A Connect to Server box appears. Enter:
    • Server name: localhost\SQLEXPRESS
    • Authentication: Windows Authentication
  4. 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

  1. In Object Explorer, right-click Databases and choose New Database….
  2. Database name: Commander4j. Leave everything else as it is.
  3. Click OK. Commander4j 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:

  1. 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.
  2. Create the login. Expand Security, right-click Logins and choose New Login…. Fill in:
    • Login name: commander4j
    • 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: Commander4j
  3. 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.

  1. 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 SQLServerManager16.msc (use SQLServerManager15.msc for 2019) and press Enter.
  2. On the left expand SQL Server Network Configuration and click Protocols for SQLEXPRESS.
  3. On the right, right-click TCP/IP and choose Enable. Click OK on the warning.
  4. 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 1433 into TCP Port. Click OK.
  5. 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:

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

What Setup4j will need

Keep these to hand for Setup4j:

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

MySQL (macOS, Linux, or Windows)

  1. Install MySQL Community Server from dev.mysql.com. On a Mac the .dmg installer adds a MySQL pane to System Settings where you start and stop the server. On Ubuntu or Debian, sudo apt install mysql-server. On Windows use the MySQL Installer and choose Server only.
  2. During install you set a password for the root user. Write it down.
  3. Open a terminal (or MySQL Workbench if you installed it) and log in: mysql -u root -p. 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 '%' lets the user connect from any PC. Use 'localhost' instead if only this computer will connect.

  1. MySQL listens on TCP port 3306 by default. If other PCs will connect, check that bind-address 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 localhost (or the server name), Port 3306, Database Commander4j, Username commander4j, Collation utf8mb4_0900_ai_ci, CharSet utf8mb4.

MariaDB

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

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 RESOURCE and CONNECT roles plus UNLIMITED TABLESPACE 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.

Retrieved from "https://wiki.commander4j.com/index.php?title=Install_Database&oldid=1925"