Configuring QIE with Microsoft SQL Server¶

Minimum version

QIE requires Microsoft SQL Server 2016 or later.

Create a QIE Database Schema¶

Using Microsoft SQL Server Management Studio or a similar tool, connect to the MSSQL database server where the QIE database resides and create a database schema called 'qie' or whichever name you wish to use for the QIE related database.

Create a Database User with DB Owner Privileges to the QIE Database Schema¶

Create a database user to be used by QIE for connecting to the QIE database schema created above. This user must be configured with full privileges to the QIE database schema as the QIE application handles the creation and management of all database tables, indexes, etc. Ensure that the default database associated with the user is set to the QIE database schema create above. When you create the new user, uncheck the "User must change password at next login". If this was checked, login to SQL Server with the new user and change the password before using it with QIE.

Note

The database user used by QIE must be an actual SQL Server user (not an active directory user) as QIE cannot be configured to connect to SQL Server using integrated authentication.

Scripted MSSQL provisioning

For automated or repeatable deployments, run the following T-SQL from a login with sysadmin privileges instead of using the SSMS UI. Substitute a strong password for <strong-password>.

-- 1. Create the QIE database
CREATE DATABASE qie;
GO

-- 2. Set Simple recovery model to minimize transaction-log growth (QIE does not require full recovery)
ALTER DATABASE qie SET RECOVERY SIMPLE;
GO

-- 3. Create a SQL Server login for QIE
CREATE LOGIN qie WITH PASSWORD = '<strong-password>',
    DEFAULT_DATABASE = qie,
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = OFF;
GO

-- 4. Create the matching database user and grant db_owner
USE qie;
GO
CREATE USER qie FOR LOGIN qie;
ALTER ROLE db_owner ADD MEMBER qie;
GO

On Azure SQL Database, run the CREATE LOGIN in the master database and the CREATE USER / role grant against the QIE database.

Setup In-Memory Tables (High Availability Only)¶

In-Memory tables are required for High Availability in QIE. For Microsoft SQL Server, In-Memory tables were introduced in 2016. Versions of MSSQL prior to 2016 are not supported with QIE High Availability. Here is a script to enable In-Memory tables on MSSQL:

Azure SQL Database tier requirement

In-Memory OLTP on Azure SQL Database is only available on the Premium and Business Critical service tiers. Basic, Standard, and General Purpose tiers do not support In-Memory tables and cannot be used to host a QIE HA database. When choosing the tier for a new Azure SQL Database, pick Premium or Business Critical before running the script below.

High Availability support

ALTER DATABASE CURRENT SET AUTO_CLOSE OFF
GO

1. Validate that In-Memory OLTP is supported

IF SERVERPROPERTY(N'IsXTPSupported') = 0
BEGIN
   PRINT N'Error: In-Memory OLTP is not supported for this server edition or database pricing tier.';
END

IF DB_ID() < 5
BEGIN
   PRINT N'Error: In-Memory OLTP is not supported in system databases. Connect to a user database.';
END
ELSE
BEGIN
   BEGIN TRY;

2. Add MEMORY_OPTIMIZED_DATA filegroup when not on Azure SQL DB

IF SERVERPROPERTY('EngineEdition') != 5
BEGIN
   DECLARE @SQLDataFolder nvarchar(max) = cast(SERVERPROPERTY('InstanceDefaultDataPath') as nvarchar(max))
   DECLARE @MODName nvarchar(max) = DB_NAME() + N'_mod';
   DECLARE @MemoryOptimizedFilegroupFolder nvarchar(max) = @SQLDataFolder + @MODName;

   DECLARE @SQL nvarchar(max) = N'';

   -- Add a MEMORY_OPTIMIZED_DATA filegroup.
   IF NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE type = N'FX')
   BEGIN
      SET @SQL = N'
ALTER DATABASE CURRENT
ADD FILEGROUP ' + QUOTENAME(@MODName) + N' CONTAINS MEMORY_OPTIMIZED_DATA;';
      EXECUTE (@SQL);
   END;

   -- Add container in the filegroup in the default data folder.
   IF NOT EXISTS (SELECT * FROM sys.database_files WHERE data_space_id IN (SELECT data_space_id FROM sys.filegroups WHERE type = N'FX'))
   BEGIN
      SET @SQL = N'
ALTER DATABASE CURRENT
ADD FILE (name = N''' + @MODName + ''', filename = '''
         + @MemoryOptimizedFilegroupFolder + N''')
TO FILEGROUP ' + QUOTENAME(@MODName);
      EXECUTE (@SQL);
   END
END

3. Set compat level to 130 if it is lower

IF (SELECT compatibility_level FROM sys.databases WHERE database_id=DB_ID()) < 130
   ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130

4. Enable MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT for the database

   ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
   END TRY
   BEGIN CATCH
      PRINT N'Error enabling In-Memory OLTP';
      IF XACT_STATE() != 0
         ROLLBACK;
      THROW;
   END CATCH;
END;
GO

Configure the QIE Service Manager to connect to Microsoft SQL Server

Add the following Arguments on the Startup tab of the QIE Service Manager:

-Dconnection.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
-Dconnection.url=jdbc:sqlserver://{server}:{port, default=1433};databaseName={qie}
-Dhibernate.dialect=com.qvera.qie.persistence.SQLServer2022UnicodeDialect
-Dconnection.username=???
-Dconnection.password=???

Note

Use the following list to select your correct SQL Server dialect:

Microsoft SQL Server 2022, use SQLServer2022UnicodeDialect

Microsoft SQL Server 2019, use SQLServer2019UnicodeDialect

Microsoft SQL Server 2017, use SQLServer2017UnicodeDialect

Microsoft SQL Server 2016, use SQLServer2016UnicodeDialect

Note

Refer to the Database Related Java Options section of this guide for a complete description of each of the above options.

Connecting to SQL Server over TLS

The Microsoft SQL Server JDBC driver defaults to encrypt=true, which requires the server's certificate to chain to a CA the QIE host's JVM trusts. To preserve the older unencrypted default so channels do not fail at startup, QIE automatically appends ;encrypt=false to -Dconnection.url when the URL does not already specify an encrypt value; a warning is logged prompting you to set the value explicitly.

Configure encrypt on the connection URL to match your environment:

Setting	Behavior
`encrypt=false`	Plain-text connection. Silences the auto-append warning.
`encrypt=true`	TLS with server-certificate validation. Requires the server's CA to be trusted by the QIE host's JVM.
`encrypt=true;trustServerCertificate=true`	TLS without server-certificate validation. Use only when the JVM cannot be given the correct trust anchors and the risk of skipping validation is acceptable.

-Dconnection.url=jdbc:sqlserver://<server>:1433;databaseName=<qie-db>;encrypt=true

If the server certificate is signed by an internal CA, import it into the JVM truststore, or point QIE at a separate truststore with -Djavax.net.ssl.trustStore=/path/to/truststore.jks and -Djavax.net.ssl.trustStorePassword=<password> in the Service Manager Java Options.

SQL Server instances with a NOCOUNT default

If the SQL Server the QIE database lives on has SET NOCOUNT ON enabled at the server level (a shared-instance default set by the DBA), JDBC updates return no row count. Hibernate then sees every update as affecting zero rows and QIE aborts startup with StaleStateException / "a result set was generated for update".

Two ways to resolve:

Server-side (preferred): disable the SET NOCOUNT default on the QIE database, or run QIE against a database where the default is off.
QIE-side: add a Hibernate StatementInspector Java Option that prepends SET NOCOUNT OFF; to every statement QIE sends:
```
-Dhibernate.session_factory.statement_inspector=com.qvera.qie.persistence.SQLServerCountSafeStatementInspector
```
This overrides the server default at the session level so Hibernate sees the row counts it expects.

Azure SQL with Managed Identity

QIE can store its own configuration in an Azure SQL database using a managed identity, with no password needed. In place of -Dconnection.password, add -Dconnection.ignorePassword=true, and put Authentication=ActiveDirectoryManagedIdentity in the connection URL:

-Dconnection.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
-Dconnection.url=jdbc:sqlserver://<server>.database.windows.net:1433;databaseName=<qie-db>;Authentication=ActiveDirectoryManagedIdentity
-Dhibernate.dialect=com.qvera.qie.persistence.SQLServer2022UnicodeDialect
-Dconnection.username=<msi-client-id-or-blank>
-Dconnection.ignorePassword=true

With connection.ignorePassword=true, QIE skips sending a password to the JDBC driver, and the driver authenticates through the managed identity assigned to the host. For a user-assigned managed identity set -Dconnection.username to the identity's client ID; for a system-assigned identity leave the value blank (or omit the option entirely).