Database Server Setup for Microsoft SQL Server 2022 on Windows¶

Database Server Setup for Microsoft SQL Server 2022 on Windows

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.

Prerequisite¶

The installation of SQL Server 2022 requires a modern OS. We tested Windows 11 Pro and Windows Server 2025 Standard. Also make sure you have at least 2GB free space for installer and 20 GB for each created instance. Additionally, make sure you have enough space for database (data, indexes, logs, etc). Note that, during the SQL instance setup you can choose a dedicated disk for data. See second bullet of step 11 in Download of Binaries and Installation Process below.

Download of Binaries and Installation Process¶

The SQL Server 2022 Developer can be downloaded from https://www.microsoft.com/en-us/sql-server/sql-server-downloads. The same page will offer links for download for other versions of SQL Server, but we not interested in them at this time.

Click on SQL Server 2022 Developer box and the browser will download a small installer named SQL2022-SSEI-Dev.exe;
Execute it to start the download and setup wizard;
1. Choose Custom button. It will ask for a location (Media Location) where the downloaded package will be saved and then unpacked. Note that the files from this folder should be kept in case in case you want to add new features of modify an existing SQL instance. To make changes to SQL server installation you can use the setup.exe application which will be placed here by the installer;
2. After choosing the right location, click Install. It will download and unpack the setup files. When finished, it will automatically launch the setup application which will open the SQL Server Installation Center;
3. From the left menu, click Installation, then New SQL Server standalone installation or add features to an existing installation;
4. A new window will open prompting for edition. The Developer should be preselected. Click Next >;
5. Read and accept the license terms by checking the respective checkbox. Click Next >;
6. The setup will scan the system and show a summary. Ideally, all should be green. In my case there was a yellow warning for Windows Firewall. We will handle this later. Click Next >;
7. Setup offers to install/configure Azure Extension. Uncheck the checkbox at the top of the window and click Next >;
8. A list of features is shown. I enabled only the Full-Text and Semantic Extraction Search, just in case we will use it for a specific implementation of contains function/4GL Word Indexes. Click Next >;
9. Instance Configuration. We go with a named instance called fwdcluster. Click Next >;
10. Server configuration. The default Collation will generally suit your project due to compatibility with 1252 CP text encoding. Click Next >;
11. Database Engine Configuration
  - In Server Configuration tab:
    - Select Mixed Mode (SQL Server authentication and Windows authentication) radio button.
    - Enter a STRONG password for sa;
    - Click Add Current User to set it as system administrator;
  - Optionally, in Data Directories the location where the database is saved can be chosen. Note: you may want pick a location on your fastest device. Make sure it have at least 100GB free space;
12. You guessed it, click Next >;
13. A summary will be presented on Ready to install page. Click Install to start the installation process.
14. Hopefully, all features will be successfully installed (marked green check). Click Close;

Although optional, The SQL Server Management Studio should be installed since it is a better solution for executing SQL statements in isolation and testing than the CLI (sqlcmd).

go to https://learn.microsoft.com/en-us/ssms/install/install in your browser. The latest version is SSMS21, downloadable from https://aka.ms/ssms/21/release/vs_SSMS.exe;
after the download finished, launch vs_SSMS.exe:
1. Click Continue;
2. Click Install to start the process of downloading and installing of components;
3. Click OK when process ends and then you can close the Visual Studio Installer application.
From Start menu, start the new SQL Server Management Studio 21;
1. Click Skip and add accounts later link
2. From File menu, click Connect Object Explorer
3. Click Browse (at the top) then Local. The newly created server instance should appear. Select it;
4. Click Trust Server Certificate checkbox. TODO: discuss security issues;
5. Click Connect. The Object Explorer in the left side will show a tree structure of existing databases and their structure.

Configuring SQL Instance¶

From Start menu, open Sql Server Configuration Manager;
Select Protocols for FWDCLUSTER under SQL Server Network Configuration;
- TCP/IP is most likely disabled. Double click it to open its properties;
- In Protocol tab, click Enabled and choose Yes;
- In IP Addresses, scroll to bottom and in IPAll / TCP Port enter 1433, the default static port for SQL Server;
- Click OK to close the properties dialog. A warning will be shown requesting to restart the instance;
Go to SQL Server Services and select SQL Server (FWDCLUSTER). It should be in Running state;
- Click Restart Service button from toolbar (has a blue rounded arrow icon).

Configure Windows Firewall (Windows Server 2025)¶

From Start menu, open Windows Defender Firewall with Advanced Firewall;
From the left panel, right-click Inbound Rules to open contextual menu;
Select New Rule... to open New Inbound Rule Wizard;
Select Port Rule Type then Next >;
Select TCP and enter 1433 (default, or other if you went for a different value in IP Addresses above) in Specific local ports editbox. Click Next >;
Keep Allow the connection selected and click Next >;
Make sure Public network is checked and click Next >;
Type a name for the new rule (Ex: Allow inbound on default SQL Server port) and click Finish.

Test TCP Access with CLI¶

The next paragraphs use 192.168.0.130 as the IP of the machine where SQL Server runs. To obtain the IP of the machine use ipcpnfig command from Windows prompt.

open a console (CMD or PowerShell)
execute
```
sqlcmd -S 192.168.0.130,1433 -C
```

If 1> prompt is displayed, then everything is well configured and queries can be tested. You can type

1> select 1+1, db_name()
2> go

The expected response is

---- ---------
   2 master

Each statement is executed only on go.

If an error is printed, then go back and check:

if the TCP/IP protocol is Enabled for FWDCLUSTER instance;
if the port is correctly set to 1433 (or other you have chosen);
if you restarted the instance after setting the above settings;
the IP is correct;
the port is correctly configured in firewall.

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;

Install MSSQL Client on Ubuntu¶

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/

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. The IP and port number are from my configuration. Please see yours from Enable TCP/IP connections above.

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

Important note:
When accessing the SQL Server instance from guest Ubuntu OS in a WSL2, the localhost / 192.168.0.1 and localname cannot be used. The guest OS is a different machine with different network adapters and different sets of ports. Using these will cause FWD (and sqlcmd and any other application running in Ubuntu) to try to connect to local/guest port, not the port on the host machine where SQL Server instance runs. So, it will fail.

To properly configure the import/runtime use the external IP of the host OS, or its external name if it is correctly resolved by the DNS of the guest OS. Basic ping and telnet commands can be used to troubleshoot and choose the right host.

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://192.168.0.130\\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
```
The list of all available collations can be seen at the step 10 of instance's Installation Process above.
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