Database Server Setup for SQLServer on Linux¶

This wiki discusses the setup and configuration of SQL Server and the setup of the FWD server and the environment to be able to run backed by the SQL Server instance. This is focused on general procedure and, if needed, the hotel_gui is given as example. Customer specific wikis may refer this document to avoid duplicating information.

Installation of sqlcmd (Microsoft SQL Server Client)¶

curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg
sudo chmod 644 /usr/share/keyrings/microsoft-prod.gpg
curl https://packages.microsoft.com/config/ubuntu/24.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo apt-get install mssql-tools18 unixodbc-dev

echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bash_profile
source ~/.bash_profile
ln -s /opt/mssql-tools18/bin /usr/bin/

Installation of Microsoft SQL Server 2025¶

This guide shows the installation steps of SQL Server 2025 on Ubuntu 24.04 in two ways: locally or using official image.

Installing and running SQL Server 2025 using docker¶

Install docker in your system (if is not already installed):

# Add Docker's official GPG key:
sudo apt update
sudo apt install ca-certificates curl
sudo install -m 0755 -d /etc/apt/keyrings
sudo curl -fsSL https://download.docker.com/linux/ubuntu/gpg -o /etc/apt/keyrings/docker.asc
sudo chmod a+r /etc/apt/keyrings/docker.asc

# Add the repository to Apt sources:
sudo tee /etc/apt/sources.list.d/docker.sources <<EOF
Types: deb
URIs: https://download.docker.com/linux/ubuntu
Suites: $(. /etc/os-release && echo "${UBUNTU_CODENAME:-$VERSION_CODENAME}")
Components: stable
Signed-By: /etc/apt/keyrings/docker.asc
EOF

sudo apt update
sudo apt install docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin

Pull the official SQL Server docker container:

docker pull mcr.microsoft.com/mssql/server:2025-latest

Create the docker image (make sure to replace the parameters with your needs):

docker run -e 'ACCEPT_EULA=Y' \
-e 'MSSQL_SA_PASSWORD=$PASSWORD' \
-e 'MSSQL_COLLATION=$DB_COLLATION' \
-e 'MSSQL_AUTHENTICATION_MODE=$AUTH_MODE' \
-p $SRC_PORT:$DST_PORT \
-v $VOL_NAME:$SAVE_DIR \
--name $CONTAINER_NAME \
-d mcr.microsoft.com/mssql/server:latest

Example of usage:

docker run -e 'ACCEPT_EULA=Y' \
-e 'MSSQL_SA_PASSWORD=StrongPassword!' \
-e 'MSSQL_COLLATION=Latin1_General_100_CS_AI_SC_UTF8' \
-e 'MSSQL_AUTHENTICATION_MODE=sql_and_windows' \
-p 1433:1433 \
-v sqlvolume:/var/opt/mssql \
--name sqlserver_container \
-d mcr.microsoft.com/mssql/server:latest

The command provided above creates and starts a docker image with SQL Server 2025, sqlserver_container, with a new docker volume, sqlvolume, which persists the data into /var/opt/mssql.
Please note that the docker container will not start automatically on system boot. To start the container, use the following command (replace $CONTAINER_NAME with your container name):

docker start $CONTAINER_NAME

Installing and running SQL Server 2025 locally¶

Section work in progress. Use the docker setup instead.

Creating Users¶

Using the SQL Server Management Studio or the sqlcmd CLI execute the following statements:

      CREATE LOGIN fwd_user WITH PASSWORD = 'user';
      CREATE USER fwd_user FOR LOGIN fwd_user;
      ALTER ROLE db_owner ADD MEMBER fwd_user;

      CREATE LOGIN fwd_admin WITH PASSWORD = 'admin';
      ALTER SERVER ROLE [dbcreator] ADD MEMBER [fwd_admin]
      CREATE USER fwd_admin FOR LOGIN fwd_admin;
      ALTER ROLE db_owner ADD MEMBER fwd_admin;

Project Configurations for Conversion and Import of the Database(s)¶

I am putting these here only as a reference. Please update the values according to your machine.

Static conversion:

./cfg/p2j.cfg.xml. make sure sqlserver2012 is appended to the list of ddl-dialects for the namespaces:

      <namespace name="hotel" importFile="data/hotel.df" default="true" >
         <parameter name="ddl-dialects" value="h2,postgresql,mariadb,sqlserver2012" />
         [...]
      </namespace>

There are no specific settings for this dialect.

Import:

./build.properties.

# db dialects
db.h2=false
db.postgresql=false
db.mariadb=false
db.sqlserver=true
 
#db connection info
db.host=localhost
db.port=1433
db.instance=\\FWDCLUSTER

Configurations for Running your Project¶

deploy/server/directory.xml (runtime). Make sure the url uses the same IP / port as above.

    <node class="container" name="server">
      <node class="container" name="standard">
        <node class="container" name="database">
          <node class="container" name="hotel">
            <node class="container" name="orm">
              <node class="string" name="dialect">
                <node-attribute name="value" value="com.goldencode.p2j.persist.dialect.P2JSQLServer2012Dialect"/>
              </node>
              <node class="container" name="connection">
                <node class="string" name="driver_class">
                  <node-attribute name="value" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
                </node>
                <node class="string" name="url">
                  <node-attribute name="value" value="jdbc:sqlserver://localhost\\FWDCLUSTER:1433;databaseName=hotel;encrypt=true;trustServerCertificate=true;"/>
                </node>

Setting up the MSSQL database manually¶

The above paragraphs describe the configurations needed to be performed when using a provided ant script. But what happens behind the scene? Or if you want to manually create and configure the MSSQL database to be used with FWD server? Use the following statements, from SQL Management Studio or sqlcmd. In the examples, hotel database is used. Please replace with your desired database name.

create the database:
```
create database [hotel]
```

set the collation:

ALTER DATABASE [hotel] COLLATE Latin1_General_100_CS_AI_SC_UTF8

apply schema. It can be found in ddl/ directory in the conversion project. Execute the ddl/schema_table_hotel_sqlserver2012.sql to get the tables and necessary database options set. Execute the ddl/schema_index_hotel_sqlserver2012.sql only if an import process will not be performed.
grant the necessary permissions to fwd_user, assuming the login entity was already created at instance level, as described above:
```
CREATE USER [fwd_user] FOR LOGIN [fwd_user];
ALTER USER [fwd_user] WITH DEFAULT_SCHEMA=[dbo], LOGIN=[fwd_user];
ALTER ROLE [db_datawriter] ADD MEMBER [fwd_user];
ALTER ROLE [db_datareader] ADD MEMBER [fwd_user];
ALTER ROLE [db_ddladmin] ADD MEMBER [fwd_user];
```
While the membership to db_datawriter and db_datareader are obvious, the requirement for db_ddladmin is necessary because the implementation of set-value 4GL statement is seen as a DDL statement by SQL Server.
additionally, the following will be necessary if the schema was not applied:
```
ALTER DATABASE [hotel] SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE [hotel] SET READ_COMMITTED_SNAPSHOT ON;
```
These are part of the header of ddl/schema_table_hotel_sqlserver2012.sql and will be set with schema installation.
These are mandatory for allowing concurrent access (eventually in read-only / no-lock mode) to same table from different connections (user contexts in 4GL/FWD).

Project

General

Profile

FWD

Wiki