CONFIGURATION: Microsoft SQL SERVER

This document guides IT administrators through creating and configuring a dedicated SQL Server database for the AVIX Server. The AVIX data is stored using EMF CDO.

Overview & Prerequisites

Before configuring AVIX Server, the database engine must be installed and populated with the target database. This table demonstrates configuration properties and values from the XML configuration file /repositories.xml at the root of the AVIX Server installation:

Parameter	Configuration Property	Example Value
Host Address	`serverHost`	`localhost` or `192.168.1.50`
Port	`portNumber`	`1433`
Database Name	`databaseName`	`avix_db` (name of SQL Server db to use for AVIX )
Database User	`databaseUser`	`avix_sqlserver` (recommended: a dedicated user e.g. `avix_sqlserver` as in example below)
Password	`databasePassword`	`<your-pw>` (e.g. `ChangeThisStrongPassword123!` in example below)
SSL Connection	`useSSL`	`false` or `true`

If you follow the guide below, to install the SQL Server engine and create both a dedicated database as well as a user account, you will in the final step fill in the repositories.xml of your AVIX Server to match your SQL Server setup. Restart the AVIX Server to connect.

AVIX 5.1 has been verified to work with

SQL Server 2019 (Version 15.0.XX)
SQL Server 2022 (Version 16.0.XX)
SQL Server 2025 (Version 17.0.XX)

If you already have an installed and licensed SQL Server, you ~~can~~may ~~skip~~carry out the steps ~~1 and 2~~ below ~~and move directly~~ to ~~step 3~~.

1. Engine Installation

~~This guide demonstrates installing and configuring SQL Server 2022 (and 2025) Express Edition.~~

Windows

~~Download the SQL Server Express edition of your choice.~~

~~Edition~~ ~~Download Link~~ ~~SQL Server 2025 Express~~

~~https://download.microsoft.com/download/7ab8f535-7eb8-4b16-82eb-eca0fa2d38f3/SQL2025-SSEI-Expr.exe~~

~~SQL Server 2025 Express Core~~

~~https://download.microsoft.com/download/dea8c210-c44a-4a9d-9d80-0c81578860c5/ENU/SQLEXPR_x64_ENU.exe~~

~~SQL Server 2022 Express~~

~~https://download.microsoft.com/download/5/1/4/5145fe04-4d30-4b85-b0d1-39533663a2f1/SQL2022-SSEI-Expr.exe~~

~~SQL Server 2022 Express Core~~

~~https://download.microsoft.com/download/3/8/d/38de7036-2433-4207-8eae-06e247e17b25/SQLEXPR_x64_ENU.exe~~

~~(Express Core is the SQL Server Engine only)~~

~~Winget install Microsoft SQL Server 2025 Express (v17), or 2022 Express (v16):~~

winget install Microsoft.SQLServer.2025.Express

winget install Microsoft.SQLServer.2022.Express

Caution before installing sql server on windows 11/ SERVER 2022

~~If installing on a Windows 11/Server 2022, there may be problems due to system disk sector size greater than the supported 4KB. Please read more about it on the~~ ~~Microsoft knowledgebase article~~.

~~To know if your Windows is affected, the easiest way is to run a Command prompt as Administrator, and run:~~

fsutil fsinfo sectorinfo <DriveLetter>:

~~for example:~~

~~Quoting the Microsoft KB article:~~

~~"Look for the values~~ PhysicalBytesPerSectorForAtomicity ~~and~~ PhysicalBytesPerSectorForPerformance~~, returned in bytes. If they're different, retain the~~ ~~largest~~ ~~one to ascertain the disk sector size. A value of 4096 indicates a sector storage size of 4 KB."~~

~~If the values reported for both of these values are~~ 4096~~, you should be fine to go on and install SQL Server.~~

~~If not (as in the example above): Windows is reporting a higher value than 4 KB, which SQL Server cannot handle. You are recommended to handle this before installing SQL Server.~~

~~As the KB article indicates, there are mainly two remedies to the situation:~~

~~Make sure to set the value of the registry key~~ ForcedPhysicalSectorSizeInBytes to * 4095 ~~. This forces the sector size to be emulated as 4 KB.~~ ~~Plan to have the SQL Server's DB files off the internal NVMe drive: format a virtual hard disk -~~ create a ~~fix-sized~~database ~~virtual~~ ~~disk~~avix_db ~~in Windows Disk Management, format it explicitly with 4096-byte sector size,~~database, and ~~mount as~~ a ~~local~~user ~~drive~~avix_sqlserver ~~letter (e.g. D: ).~~

2. Network configuration - TCP/IP and port

~~The~~ ~~SQL Server Configuration Manager~~that will be used ~~for this.~~

~~SQL Server Configuration Manager - SQL Server | Microsoft Learn~~

~~Locate~~by the ~~manager application:~~

~~or:~~

Step-by-step configuration

~~Open~~ ~~SQL Server Configuration Manager~~ ~~Expand~~ ~~SQL Server Network Configuration~~ → ~~Protocols for~~ ~~[Instance Name]~~
~~Double-click~~ ~~TCP/IP~~ ~~(ensure it is set to~~ ~~Enabled~~). ~~Select the~~ ~~IP Addresses~~ ~~tab.~~ ~~Scroll down to the~~ ~~IPAll~~ ~~section at the bottom.~~

~~Static port is recommended, since it ensures that applications (~~AVIX ~~Server) always connect to the same endpoint, making firewall rules simple:~~

~~TCP Dynamic Ports:~~ ~~Clear this field completely (leave it~~ ~~blank~~).

~~TCP Port:~~ ~~Enter your desired port number (default is~~ 1433).

Crucial post-configuration steps

~~Restart the SQL Server Service:~~ ~~Changes made in SQL Server Configuration Manager do~~ ~~not~~ ~~take effect until you restart the~~ SQL Server (INSTANCE NAME) ~~service.~~

~~Configure Windows Firewall:~~ ~~Create an~~ ~~Inbound Rule~~ ~~on the server hosting SQL~~ Server to ~~allow~~access ~~inbound~~the ~~traffic on your specified TCP port (e.g.,~~ 1433).database.

3.1. Configure SQL Server for usage with AVIX

This guide walks through configuring the SQL Server instance for use as a relational store for AVIX Server (via EMF CDO).

The SQL Server Management Studio is the tool used to configure, manage and administer the components of a SQL Server instance. It can also be used to access the existing DB and run SQL statements.

https://learn.microsoft.com/en-us/ssms/install/install

1. Prerequisites & Initial Connection

Launch SQL Server Management Studio (SSMS).
In the Connect to Server dialog:
- Server type: Database Engine
- Server name: localhost (or localhost\SQLEXPRESS if using Express)
- Authentication: Windows Authentication
Click Connect.

2. Enable SQL Server Authentication (Mixed Mode)

By default, SQL Server allows only Windows Authentication (sometimes also referred to as "Integrated Authentication"). AVIX Server typically connects using a dedicated database user via JDBC, requiring Mixed Mode Authentication.

1.Open Server Properties:

In Object Explorer, right-click the root server instance and select Properties.

2.Change Authentication Mode:

Select the Security page in the left pane. Under Server authentication, select SQL Server and Windows Authentication mode. Click OK.

3.Restart SQL Server Service:Required for authentication changes to take effect.

Right-click the root server instance in Object Explorer and select Restart (or restart the service via services.msc).

3.

2. Create a Dedicated Application Database

Create a clean database dedicated to storing AVIX domain data managed by EMF CDO.

Method A: SSMS GUI

Right-click Databases , select New Database...
Enter Database name: avix_db (or preferred name).
Adjust file sizes (see suggested settings in image below)
Click OK

Method B: T-SQL Script

SQL

-- Create database for AVIX Server
CREATE DATABASE [avix_db]
ON PRIMARY 
(
    NAME = N'avix_db_data',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\avix_db.mdf',
    SIZE = 250MB,
    FILEGROWTH = 1024MB
)
LOG ON 
(
    NAME = N'avix_db_log',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\avix_db_log.ldf',
    SIZE = 100MB,
    FILEGROWTH = 1024MB
);
GO

4.

3. Create User account & Assign Permissions

Create a user - a SQL Server "login", and map it with appropriate permissions to the avix_db database.

Recommended Roles for EMF CDO access

CDO dynamically creates tables, while performing standard CRUD operations during application execution. These are the recommended roles, if following the principle of least privilege :

db_datareader: Read access to all tables.
db_datawriter: Write access to all tables.
db_ddladmin: Allows CDO schema updates (table/index creation and modifications).

This ensures the account can create, drop, and manage tables within its designated database but cannot access or modify other databases on the same instance. (Avoid db_owner is recommended, to prevent accidental db deletion or security changes.)

Method A: SSMS GUI

1.Create Login:

In Object Explorer, expand the root server instance.
Expand Security, right-click Logins, and select New Login...

2.Set Login Credentials: Configure login name and authentication type

On the General page:
- Enter Login name (e.g., avix_sqlserver)
- Select SQL authentication.
- Enter and confirm a strong password (we will use ChangeThisStrongPassword123! in this guide)
Clear User must change password at next login (recommended for service/application accounts to prevent unexpected connection failures). Also
Set Default database to avix_db.

3.Map User & Assign Roles: Map the user to the target database and set roles

Select the User Mapping page in the left pane.
In the top grid, check the Map checkbox next to avix_db.
In the lower pane (Database role membership for: avix_db), select the following three roles:
- db_datareader (Read access)
- db_datawriter (Write access)
- db_ddladmin (Allows CDO to create/modify tables and indexes during schema updates)

4.Save and Apply:

Click OK to create the login, database user, and role assignments simultaneously.

Method B: T-SQL Script

SQL

USE [master];
GO

-- 1. Create Login at the Server Level
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'avix_sqlserver')
BEGIN
    CREATE LOGIN [avix_sqlserver] 
    WITH PASSWORD = N'ChangeThisStrongPassword123!', 
         CHECK_EXPIRATION = OFF,
         CHECK_POLICY = ON;
END
GO

-- 2. Switch to the Application Database
USE [avix_db];
GO

-- 3. Create User in the Database for the Login
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'avix_sqlserver')
BEGIN
    CREATE USER [avix_user] FOR LOGIN [avix_user];
END
GO

-- 4. Grant Required Roles for CDO Schema & Data Management
ALTER ROLE [db_datareader] ADD MEMBER [avix_sqlserver];
ALTER ROLE [db_datawriter] ADD MEMBER [avix_sqlserver];
ALTER ROLE [db_ddladmin]   ADD MEMBER [avix_sqlserver];
GO

5.

4. Verification & Connection Test

In SSMS, click File and Connect Object Explorer...
Select SQL Server Authentication.
Enter username (avix_sqlserver) and password (ChangeThisStrongPassword123!).
Ensure you can connect and browse avix_db.

4.5. AVIX Server `repositories.xml` Integration

XML Configuration

Update repositories.xml on the AVIX Server host to reflect the database parameters:

XML

<repositoryConfig>
    <repository name="AVIX DB [SQL Server]" 
                type="se.solme.avix.server.cdo.mssql"
                serverHost="localhost"
                portNumber="1433"
                databaseName="avix_db"
                databaseUser="avix_sqlserver"
                databasePassword="ChangeThisStrongPassword123!"
                useSSL="false"/>
</repositoryConfig>

The name field (set to 'AVIX DB [SQL Server]' here) is how this repository - a connection to the underlying db - will be presented in AVIX client UI.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Miscellaneous

Detect SQL Server Generation and RTM (build version)

Right-click on the root server instance in SSMS, and show properties. Look for the "version" number (e.g. 15.0.XXX). This is the RTM or build version.
Check the SQL Server versions on: https://www.sqlserverversions.com/ and see which generation matches your RTM.

Download links to SQL Server (Express and Developer)

~~Download links of SQL Server can be somewhat difficult to find. The following resource contains working download links as per September 2026:~~

~~https://www.junian.dev/downloads/sql-server-express/~~

~~https://www.junian.dev/downloads/sql-server-developer/~~

Migrating Between SQL Server Versions

Migrating a application database across SQL Server generations (e.g., 2019 -> 2022 / 2025) is very straightforward and routine.

How It Works

Backward Compatibility: Microsoft guarantees direct upgrades across multiple versions (e.g., 2019 directly to 2022 or 2025).
Standard Upgrade Methods:
1. Backup and Restore: Take a standard .bak file on the old server and restore it on the new server.
2. Detach and Attach: Detach .mdf/.ldf files and attach them on the new instance.

Key Things to Mind

One-Way Street: Once a database is restored or attached to a newer SQL Server version, it cannot be downgraded back to the older version via file attachment or backup restore.
Database Compatibility Level: Restoring a 2019 database onto 2022 or 2025 keeps its original Compatibility Level (e.g., 150 for 2019). It must be manually bumped (to 160 for 2022 or 170 for 2025) to unlock newer query optimizer features, though standard CDO queries will work smoothly either way.

Migration Guide for AVIX

Pre-migration: stop clients of DB managed by the old server instance. E.g. AVIX Server
Backup: On old server, right-click on the avix_db in SSMS and select "Tasks" and then "Back Up". Choose "Full" backup type, set destination (local or network share) and execute.
Copy backup: transfer the .bak file to the new server
Restore: On new server, right-click "Databases" in SSMS and select "Restore Database". Select "Device", browse to the .bak file and restore. Choose "Overwrite" if the database exists.
Post-migration tasks: user accounts ("logins") may not transfer
Update the repositories.xml of the AVIX server to reflect the change of SQL Server and DB, and restart.

1. SQL SERver Installation (ENGINE)

If you don't have access to a SQL Server, you may use an Express edition to try it out. This guide demonstrates installing and configuring SQL Server 2022 (and 2025) Express Edition.

Download links of SQL Server can be somewhat difficult to find. The following resource contains working download links as per September 2026:

https://www.junian.dev/downloads/sql-server-express/

https://www.junian.dev/downloads/sql-server-developer/

For Express, you can also use the direct links in the table below.

Windows

Download the SQL Server Express edition of your choice.

Edition Download Link SQL Server 2025 Express

https://download.microsoft.com/download/7ab8f535-7eb8-4b16-82eb-eca0fa2d38f3/SQL2025-SSEI-Expr.exe

SQL Server 2025 Express Core

https://download.microsoft.com/download/dea8c210-c44a-4a9d-9d80-0c81578860c5/ENU/SQLEXPR_x64_ENU.exe

SQL Server 2022 Express

https://download.microsoft.com/download/5/1/4/5145fe04-4d30-4b85-b0d1-39533663a2f1/SQL2022-SSEI-Expr.exe

SQL Server 2022 Express Core

https://download.microsoft.com/download/3/8/d/38de7036-2433-4207-8eae-06e247e17b25/SQLEXPR_x64_ENU.exe

(Express Core is the SQL Server Engine only)

Winget install Microsoft SQL Server 2025 Express (v17), or 2022 Express (v16):

winget install Microsoft.SQLServer.2025.Express

winget install Microsoft.SQLServer.2022.Express

Caution before installing sql server on windows 11/ SERVER 2022

If installing on a Windows 11/Server 2022, there may be problems due to system disk sector size greater than the supported 4KB. Please read more about it on the Microsoft knowledgebase article.

To know if your Windows is affected, the easiest way is to run a Command prompt as Administrator, and run:

fsutil fsinfo sectorinfo <DriveLetter>:

for example:

Quoting the Microsoft KB article:

"Look for the values PhysicalBytesPerSectorForAtomicity and PhysicalBytesPerSectorForPerformance, returned in bytes. If they're different, retain the largest one to ascertain the disk sector size. A value of 4096 indicates a sector storage size of 4 KB."

If the values reported for both of these values are 4096, you should be fine to go on and install SQL Server.

If not (as in the example above): Windows is reporting a higher value than 4 KB, which SQL Server cannot handle. You are recommended to handle this before installing SQL Server.

As the KB article indicates, there are mainly two remedies to the situation:

Make sure to set the value of the registry key ForcedPhysicalSectorSizeInBytes to * 4095 . This forces the sector size to be emulated as 4 KB. Plan to have the SQL Server's DB files off the internal NVMe drive: format a virtual hard disk - create a fix-sized virtual disk in Windows Disk Management, format it explicitly with 4096-byte sector size, and mount as a local drive letter (e.g. D: ).

2. SQL SERver Installation (Network config.- TCP/IP)

The SQL Server Configuration Manager will be used for this.

SQL Server Configuration Manager - SQL Server | Microsoft Learn

Locate the manager application:

or:

Step-by-step configuration

Open SQL Server Configuration Manager Expand SQL Server Network Configuration → Protocols for [Instance Name]
Double-click TCP/IP (ensure it is set to Enabled). Select the IP Addresses tab. Scroll down to the IPAll section at the bottom.

Static port is recommended, since it ensures that applications (AVIX Server) always connect to the same endpoint, making firewall rules simple:

TCP Dynamic Ports: Clear this field completely (leave it blank).

TCP Port: Enter your desired port number (default is 1433).

Crucial post-configuration steps

Restart the SQL Server Service: Changes made in SQL Server Configuration Manager do not take effect until you restart the SQL Server (INSTANCE NAME) service.

Configure Windows Firewall: Create an Inbound Rule on the server hosting SQL Server to allow inbound traffic on your specified TCP port (e.g., 1433).

CONFIGURATION: Microsoft SQL SERVER

Overview & Prerequisites

AVIX 5.1 has been verified to work with

1. Engine Installation

Windows

Caution before installing sql server on windows 11/ SERVER 2022

2. Network configuration - TCP/IP and port

Step-by-step configuration

Crucial post-configuration steps

3.1. Configure SQL Server for usage with AVIX

1. Prerequisites & Initial Connection

2. Enable SQL Server Authentication (Mixed Mode)

3.

2. Create a Dedicated Application Database

Method A: SSMS GUI

Method B: T-SQL Script

4.

3. Create User account & Assign Permissions

Recommended Roles for EMF CDO access

Method A: SSMS GUI

Method B: T-SQL Script

5.

4. Verification & Connection Test

4.5. AVIX Server repositories.xml Integration

XML Configuration

Miscellaneous

Detect SQL Server Generation and RTM (build version)

Download links to SQL Server (Express and Developer)

Migrating Between SQL Server Versions

How It Works

Key Things to Mind

Migration Guide for AVIX

1. SQL SERver Installation (ENGINE)

Windows

Caution before installing sql server on windows 11/ SERVER 2022

2. SQL SERver Installation (Network config.- TCP/IP)

Step-by-step configuration

Crucial post-configuration steps

4.5. AVIX Server `repositories.xml` Integration