Feed aggregator

 I've just published a short video on the new JOIN TO ONE clause in SELECT statements in Oracle 26ai 26.2

This clause allows you to let the database automatically determine JOIN columns based on Primary Key and Foreign Key relationships configured in the database.  JOIN TO ONE defaults to doing a LEFT OUTER JOIN so I also demonstrate how to use it for INNER JOINs

Since version 7.0.11, MongoDB natively supports OpenID Connect (OIDC) authentication. This move was part of MongoDB’s cloud strategy, since cloud environments use OIDC a lot for authentication and authorization. In version 8.0, MongoDB deprecated LDAP authentication and authorization, making it clear that OIDC is the future for MongoDB authentication. In this blog, I will present how to set up OIDC authentication for MongoDB in a self-managed environment with Okta.

OpenID Connect (OIDC) is an authentication protocol built on top of the OAuth 2.0 framework. It allows clients (like mongosh) to check the identity of the user based on the authentication performed by an authorization server (like Okta). It also provides a standardized way of obtaining user profile information, resolving the authorization part of the connection.

MongoDB supports OIDC authentication for both:

• Users : Workforce Identity Federation
• Applications : Workload Identity Federation

In this blog, I will focus on the first use case.

Before setting up OIDC authentication for MongoDB, you will need the following:

• MongoDB Enterprise Edition. OIDC authentication is only available in the Enterprise Edition of MongoDB. Alternatively, you can use Percona Server for MongoDB, which also supports OIDC authentication.
• Version 7.0.11 or later of MongoDB.
• A working Okta tenant. A 30-day trial can be obtained here.

Throughout this blog, I will use very generic names (dbiapp, dbiauth, etc.) to make sure you are not missing on configuration aspects. Some of these names will be used when configuring OIDC in MongoDB.

Start by creating an application in Okta. From the Admin Console (available at https://trial-1234567-admin.okta.com/admin/dashboard), navigate to Applications > Applications and click on Create App Integration. Then, select OIDC – OpenID Connect as the sign-in method and Native as the application type. Click on Next.

In the application configuration screen, fill in an application name (mine will be called dbiapp), and select Grant types among these three choices:

• Authorization Code: Activated by default, cannot be deactivated.
• Device Authorization: Required if you have no browser access when using mongosh. The shell will display a URL with which you will authenticate.
• Refresh Token: If enabled, the MongoDB driver caches the refresh token and renews the access token when it expires.

Then fill in the Sign-in redirect URIs with the following URL : http://localhost:27097/redirect

Finally, in the Assignments section, you can choose between multiple Controlled access options:

• Allow everyone in your organization to access
• Limit access to selected groups
• Skip group assignment for now

In this blog, I will choose Allow everyone in your organization to access. In production environments, you might choose something else. Make sure Enable immediate access with Federation Broker Mode is enabled, and click on Save.

You should now land on the newly created application page. Copy the Client ID displayed on the screen, you will need it later.

In the navigation panel, click on Security > API, and Add Authorization Server.

Choose a name for the Authorization Server (mine will be named dbiauth), and paste the Client ID retrieved earlier in the Audience field.

From the newly created authorization server, copy the Issuer Metadata URI, from https until .well-known (excluded). You should have something like https://trial-1234567.okta.com/oauth2/aus27qkm93wcRptbz412.

Staying on the authorization server summary, click on the Claims tab, and then on Add Claim.

You can choose any name for the claim. I will call it dbiclaim. The rest of the claim should be configured as follows, with the Filter set to Matches regex, using .* as filter.

WARNING: Make sure the filter is .*, not *.* or *. ! Otherwise, it could lead to MongoServerError: Authentication failed. errors.

Now, in the Access Policies tab of the authorization server, click on Add Policy.

You can choose the name of the policy that you want (mine is called dbipolicy), and you must add a Description. Set Assign to to All clients.

After creating the policy, click on Add rule.

This is the part where you should be customizing the rule based on your internal security policies. I will name my rule dbirule, and keep everything default except for the Refresh token lifetime, which is set to Unlimited.

If you already use Okta, you should have existing groups and users. But for the purpose of the blog, let’s create a group and a user. Navigate on the left to Directory > Groups, and click on Add Group.

MongoDB names the group OIDC, without stating whether it is the only name supported or not. But you can choose your own name. I will call the group dbigroup.

After creating the group, add a user in the Directory > People section, clicking on Add Person.

There are two important aspects here:

• Use an email for the Username field.
• Add the dbigroup group to the Groups.

Before continuing, make sure the user is activated following the procedure received by email

Stop your MongoDB 7.0.11+ Enterprise Edition instance, and edit the configuration file by adding the following setParameter section:

• authenticationMechanisms: set it to MONGODB-OIDC if you want to enable only OIDC authentication, or MONGODB-OIDC,SCRAM-SHA-256 if you want to keep authentication with password for previous users.
• issuer: use the Issuer Metadata URI copied after creating the authorization server (https://trial-1234567.okta.com/oauth2/aus27qkm93wcRptbz412)
• audience and clientId: for both fields, use the Client ID associated with the application created at the very beginning (0oa89cvj16d4WFKrX307, for instance)
• authNamePrefix: okta-issuer
• authorizationClaim: use the name of the claim created on the authorization server. In my case, it is dbiclaim.

# Paste this at the end of your MongoDB configuration file
setParameter:
   authenticationMechanisms: "MONGODB-OIDC"
   oidcIdentityProviders: '[ {
      "issuer": "https://trial-1234567.okta.com/oauth2/aus27qkm93wcRptbz412",
      "audience": "0oa89cvj16d4WFKrX307",
      "authNamePrefix": "okta-issuer",
      "authorizationClaim": "dbiclaim",
      "clientId": "0oa89cvj16d4WFKrX307"
   } ]'

After changing the configuration file, you can restart your MongoDB instance. If security.authorization is not enabled yet, you should set it now and make sure you have a user able to create roles.

Log in with a privileged user to your MongoDB instance, and create a new role for OIDC authentication. The role name should be based on authNamePrefix (okta-issuer) and the group name (dbigroup). In this blog, I will create the okta-issuer/dbigroup role.

use admin
db.createRole( {
   role: "okta-issuer/dbigroup",
   privileges: [ ],
   roles: [ "readWriteAnyDatabase" ]
} )

Now, any member of the dbigroup group should be able to log in with mongosh or any other connection tool, with the following parameters:

• --authenticationMechanism flag set to MONGODB-OIDC. This parameter value is the official MongoDB parameter.
• --oidcFlows flag set to device-auth. This can be used in environments where mongosh will not be able to launch a browser.

# Change the MONGO_URI accordingly
MONGO_URI="mongodb://127.0.0.1:27017"
mongosh "$MONGO_URI" --authenticationMechanism MONGODB-OIDC --oidcFlows=device-auth

After a few seconds, you will receive the URL to complete authentication:

mongodb@mongodb-lab-01:/home/mongodb/ [mdb02] mongosh "$MONGO_URI" --authenticationMechanism MONGODB-OIDC --oidcFlows=device-auth
Current Mongosh Log ID:	6a64a98717240b2e9d9df8a2
Connecting to:		mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&authMechanism=MONGODB-OIDC&appName=mongosh+2.9.2

Visit the following URL to complete authentication: https://trial-1234567.okta.com/activate
Enter the following code on that page: RQXFMWTF
Waiting...

You can now open the link given (https://trial-1234567.okta.com/activate), and it will ask for the activation code (RQXFMWTF).

Once the device is activated, the mongosh prompt will succeed:

mongodb@mongodb-lab-01:/home/mongodb/ [mdb02] mongosh "$MONGO_URI" --authenticationMechanism MONGODB-OIDC --oidcFlows=device-auth
Current Mongosh Log ID:	6a64a98717240b2e9d9df8a2
Connecting to:		mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&authMechanism=MONGODB-OIDC&appName=mongosh+2.9.2

Visit the following URL to complete authentication: https://trial-1234567.okta.com/activate
Enter the following code on that page: RQXFMWTF
Waiting...
Using MongoDB:		8.0.26
Using Mongosh:		2.9.2

Enterprise test>

And if you run the db.runCommand({connectionStatus:1}) command, you will see the OIDC connection information:

Enterprise test> db.runCommand({connectionStatus:1})
{
  authInfo: {
    authenticatedUsers: [ { user: 'okta-issuer/dbiblog@dbi-services.com', db: '$external' } ],
    authenticatedUserRoles: [
      { role: 'okta-issuer/Everyone', db: 'admin' },
      { role: 'okta-issuer/dbigroup', db: 'admin' },
      { role: 'readWriteAnyDatabase', db: 'admin' }
    ]
  },
  ok: 1
}

If you use the MongoDB DMK, you should either adapt the msp alias or create a new msoidc alias to connect to your instances. To do so, edit the local configuration file of DMK with the dmkl alias:

# Option 1: change the msp alias
alias::msp::novar_noforce::'ms --authenticationMechanism MONGODB-OIDC --oidcFlows=device-auth'::

# Option 2: add a new msoidc alias
alias::msoidc::novar_noforce::'ms --authenticationMechanism MONGODB-OIDC --oidcFlows=device-auth'::

L’article MongoDB OIDC Authentication with Okta est apparu en premier sur dbi Blog.

 In today's video I have demonstrated how Power BI can use DirectQuery to query an Oracle database and refresh reports without actually storing the data in the Power BI file (as would be done if "Import" was used instead of DirectQuery).

I have used SQL Tracing in the Database Instance to identify the SQL statement that Power BI executes

For the first visual in Power BI which shows total salary by Department, the Power BI module and SQL statement are identified as :

MODULE NAME:(msmdsrv.exe) 
CLIENT DRIVER:(ODPM.NET : 23.6.0.0.0)

sqlid='c8a9qd2dzks8h'

SELECT * FROM (
SELECT
*
FROM 
(

SELECT
"t1"."DEPARTMENT_NAME" "c6", SUM ( "t4"."SALARY" )
 "a0"
FROM 
((
select "$Table"."EMPLOYEE_ID" as "EMPLOYEE_ID",
    "$Table"."FIRST_NAME" as "FIRST_NAME",
    "$Table"."LAST_NAME" as "LAST_NAME",
    "$Table"."EMAIL" as "EMAIL",
    "$Table"."PHONE_NUMBER" as "PHONE_NUMBER",
    "$Table"."HIRE_DATE" as "HIRE_DATE",
    "$Table"."JOB_ID" as "JOB_ID",
    "$Table"."SALARY" as "SALARY",
    "$Table"."COMMISSION_PCT" as "COMMISSION_PCT",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "HR"."EMPLOYEES" "$Table"
) "t4"

 LEFT OUTER JOIN 

(
select "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID",
    "$Table"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."LOCATION_ID" as "LOCATION_ID"
from "HR"."DEPARTMENTS" "$Table"
) "t1" on 
(
"t4"."DEPARTMENT_ID" = "t1"."DEPARTMENT_ID"
)
)

GROUP BY "t1"."DEPARTMENT_NAME"
)
 "MainTable"
WHERE 
(

NOT(
(
"a0" IS NULL 
)
)

)

ORDER BY "a0"
DESC
,"c6"
ASC
 ) WHERE ROWNUM (lessthan) 1001

MODULE NAME:(msmdsrv.exe)
CLIENT DRIVER:(ODPM.NET : 23.6.0.0.0)

sqlid='dh7nbfqsy942q'

SELECT
"t1"."DEPARTMENT_NAME" "c6",
COUNT("t4"."EMPLOYEE_ID")
 "a0"
FROM 
((
select "$Table"."EMPLOYEE_ID" as "EMPLOYEE_ID",
    "$Table"."FIRST_NAME" as "FIRST_NAME",
    "$Table"."LAST_NAME" as "LAST_NAME",
    "$Table"."EMAIL" as "EMAIL",
    "$Table"."PHONE_NUMBER" as "PHONE_NUMBER",
    "$Table"."HIRE_DATE" as "HIRE_DATE",
    "$Table"."JOB_ID" as "JOB_ID",
    "$Table"."SALARY" as "SALARY",
    "$Table"."COMMISSION_PCT" as "COMMISSION_PCT",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "HR"."EMPLOYEES" "$Table"
) "t4"

 LEFT OUTER JOIN 

(
select "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID",
    "$Table"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."LOCATION_ID" as "LOCATION_ID"
from "HR"."DEPARTMENTS" "$Table"
) "t1" on 
(
"t4"."DEPARTMENT_ID" = "t1"."DEPARTMENT_ID"
)
)

GROUP BY "t1"."DEPARTMENT_NAME" 

When downloading Release Update 23.26.3., I could see this:

As you can see in the Product info it also has “Oracle Server – Standard Edition”. However, I haven’t found anything official yet.

I tried it and could install 23.26.3. as a Standard Edition ORACLE_HOME:

[oracle@oel10db26ai dbhome_1]$ mkdir -p /u01/app/oracle/product/26.0.0/dbhome_1
[oracle@oel10db26ai dbhome_1]$ cd /u01/app/oracle/product/26.0.0/dbhome_1
[oracle@oel10db26ai dbhome_1]$ unzip -q /tmp/p39581612_230000_Linux-x86-64.zip
[oracle@oel10db26ai dbhome_1]$ vi install/response/db_install_26ai.rsp
[oracle@oel10db26ai dbhome_1]$ cat install/response/db_install_26ai.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v23.0.0
installOption=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/26.0.0/dbhome_1
ORACLE_BASE=/u01/app/oracle
installEdition=SE2
OSDBA=oinstall
OSOPER=oinstall
OSBACKUPDBA=oinstall
OSDGDBA=oinstall
OSKMDBA=oinstall
OSRACDBA=oinstall
executeRootScript=false
dbType=GENERAL_PURPOSE
[oracle@oel10db26ai dbhome_1]$ 

[oracle@oel10db26ai dbhome_1]$ ./runInstaller -ignorePrereq -waitforcompletion -silent -responseFile install/response/db_install_26ai.rsp
Launching Oracle AI Database Setup Wizard...
...
[WARNING] [INS-13014] Target environment does not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. installActions2026-08-04_07-18-59PM.log.
   ACTION: Identify the list of failed prerequisite checks from the log: installActions2026-08-04_07-18-59PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
The response file for this session can be found at:
 /u01/app/oracle/product/26.0.0/dbhome_1/install/response/db_2026-08-04_07-18-59PM.rsp

You can find the log of this install session at:
 /tmp/InstallActions2026-08-04_07-18-59PM/installActions2026-08-04_07-18-59PM.log


As a root user, run the following script(s):
	1. /u01/app/oraInventory/orainstRoot.sh
	2. /u01/app/oracle/product/26.0.0/dbhome_1/root.sh

Run /u01/app/oraInventory/orainstRoot.sh on the following nodes: 
[oel10db26ai]
Run /u01/app/oracle/product/26.0.0/dbhome_1/root.sh on the following nodes: 
[oel10db26ai]


Successfully Setup Software with warning(s).
Moved the install session logs to:
 /u01/app/oraInventory/logs/InstallActions2026-08-04_07-18-59PM
[oracle@oel10db26ai dbhome_1]$ 

After running the root-scripts I created a Database and verified that it is really a Standard Edition 2 DB:

[root@oel10db26ai ~]# mkdir /u02
[root@oel10db26ai ~]# chown oracle:oinstall /u02
[root@oel10db26ai ~]# 

[oracle@oel10db26ai ~]$ mkdir /u02/oradata
[oracle@oel10db26ai ~]$ 
[oracle@oel10db26ai ~]$ . oraenv
ORACLE_SID = [oracle] ? dummyx
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/26.0.0/dbhome_1
The Oracle base has been set to /u01/app/oracle
[oracle@oel10db26ai ~]$ 

[oracle@oel10db26ai ~]$ export ORACLE_SID=DB26SE2
[oracle@oel10db26ai ~]$ export PDB_NAME=pdb1
[oracle@oel10db26ai ~]$ export DATA_DIR=/u02/oradata

[oracle@oel10db26ai ~]$ dbca -silent -createDatabase                                                   \
     -templateName General_Purpose.dbc                                         \
     -gdbname ${ORACLE_SID} -sid  ${ORACLE_SID} -responseFile NO_VALUE         \
     -characterSet AL32UTF8                                                    \
     -sysPassword HEllo01__01                                                 \
     -systemPassword HEllo01__01                                              \
     -createAsContainerDatabase true                                           \
     -numberOfPDBs 1                                                           \
     -pdbName ${PDB_NAME}                                                      \
     -pdbAdminPassword HEllo01__01                                            \
     -databaseType MULTIPURPOSE                                                \
     -memoryMgmtType auto_sga                                                  \
     -totalMemory 2000                                                         \
     -storageType FS                                                           \
     -datafileDestination "${DATA_DIR}"                                        \
     -redoLogFileSize 100                                                       \
     -emConfiguration NONE                                                     \
     -ignorePreReqs
...

[oracle@oel10db26ai ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.26.3.0.0 - Production on Wed Aug 5 10:42:48 2026
Version 23.26.3.0.0

Copyright (c) 1982, 2026, Oracle.  All rights reserved.


Connected to:
Oracle AI Database 26ai Standard Edition 2 Release 23.26.3.0.0 - Production
Version 23.26.3.0.0

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle AI Database 26ai Standard Edition 2 Release 23.26.3.0.0 - Production

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
SQL> show parameter control_management_pack_access

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access	     string	 NONE
SQL> 

It really seems a Standard Edition 2 DB. But let me check if it restricts me for a command not allowed in SE2:

SQL> select min(snap_id), max(snap_id) from dba_hist_snapshot;

MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
	   1	       14

SQL> var retval number;
SQL> exec :retval:=dbms_spm.load_plans_from_awr(1,14);
BEGIN :retval:=dbms_spm.load_plans_from_awr(1,14); END;

*
ERROR at line 1:
ORA-38153: Software edition is incompatible with SQL plan management.
ORA-06512: at "SYS.DBMS_SPM", line 4009
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 6479
ORA-06512: at "SYS.DBMS_SPM", line 3991
ORA-06512: at line 1
Help: https://docs.oracle.com/error-help/db/ora-38153/


SQL> ! oerr ora 38153
38153, 00000, "Software edition is incompatible with SQL plan management."
// *Cause: SQL plan management could be used only with Oracle Database Enterprise Edition.
// *Action: Ensure that Oracle is linked with the Enterprise Edition options.

Yes, it does not allow me to run a command, which is restricted for the use in Enterprise Edition DBs.

Oracle has released Release Update 23.26.3. recently for on-premises installations. According the download screen it contains the possibility to run a Standard Edition 2 DB with it. First tests showed that you really can use 23.26.3. as an ORACLE_HOME for Standard Edition 2 DBs. However, Oracle has not officially published this yet. Before using this release with a Standard Edition 2 DB I would recommend to wait for the official announcement from Oracle.

If there are news on this, I’ll update this Blog.

L’article Oracle: Standard Edition 2 available with 23.26.3.? est apparu en premier sur dbi Blog.

My last post was a rant about Oracle Support. As a result of that some folks from Oracle reached out for a meeting to discuss my experience. This was not about the specific SR, but my general experience of using Oracle Support as a customer. In this post I’ll discuss some of the information that … Continue reading "Oracle Support : An Update"

In the blog series I previously wrote, I did not answer all the customer’s questions. The last one was the following:

Can this also be applied to PostgreSQL?

In short, yes, it is possible. Let’s see how.

Here is the list of the previous blog posts:

• https://www.dbi-services.com/blog/sql-server-snapshot-backup-and-restore-with-proxmox-zfs/
• https://www.dbi-services.com/blog/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-2-3/
• https://www.dbi-services.com/blog/sql-server-snapshot-backup-and-restore-with-proxmox-zfs-rest-api-with-sql-server-2025-3-3/

We will reuse the sqlpool ZFS pool created in the first part of this series.

We identify the 300 GB disk attached to the VM. In our case, it is /dev/sdb, backed by the sqlpool/pve/vm-307-disk-0 zvol on the Proxmox side:

lsblk

We create a single partition of type Linux filesystem:

sudo sgdisk -n 1:0:0 -t 1:8300 /dev/sdb

We format the partition with XFS, which is the most commonly recommended filesystem for PostgreSQL data directories:

sudo mkfs.xfs -L pgdata /dev/sdb1 -f

We verify the result:

ahi@pgl:~$ sudo blkid /dev/sdb1
/dev/sdb1: LABEL="pgdata" UUID="028afa2f-7bb3-4a40-92aa-91c1a33f18ae9" BLOCK_SIZE="512" TYPE="xfs" PARTUUID="bd54f285-092e-4b1b-ba5e-6877f054fa7"

We create the mount point:

sudo mkdir -p /pgdata

Persistent mount via fstab:

We add the mount entry to /etc/fstab using the filesystem label rather than the device name. The device name (/dev/sdb) may change if disks are added or removed while the label remains stable:

echo 'LABEL=pgdata /pgdata xfs noatime,nodiratime 0 2' | sudo tee -a /etc/fstab
sudo systemctl daemon-reload
sudo mount /pgdata

We verify that the volume is mounted:

df -h /pgdata

We install PostgreSQL 18 from the official PGDG repository, which provides the latest PostgreSQL versions for Ubuntu:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
sudo apt install -y postgresql-18

Cluster creation on /pgdata:

The Ubuntu packages create a default cluster under /var/lib/postgresql. This is not what we want. The data files and the WAL must both reside on the ZFS-backed volume, so that a single ZFS snapshot captures a consistent state of the database. If they were on different volumes, the snapshot would not be atomic.

We drop the default cluster and recreate it on /pgdata:

ahi@pgl:~$ sudo pg_dropcluster --stop 18 main
sudo install -d -o postgres -g postgres -m 700 /pgdata/18
sudo pg_createcluster -d /pgdata/18/main 18 main
sudo systemctl enable --now postgresql@18-main
Creating new PostgreSQL cluster 18/main ...
/usr/lib/postgresql/18/bin/initdb -D /pgdata/18/main --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

fixing permissions on existing directory /pgdata/18/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
             Ver Cluster Port Status Owner     Data directory  Log file
             18  main    5432 down   postgres  /pgdata/18/main /var/log/postgresql/postgresql-18-main.log
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql@18-main.service → /usr/lib/systemd/system/postgresql@.service.

We verify that the cluster is online and located on the right volume:

ahi@pgl:~$ pg_lsclusters
Ver Cluster Port Status Owner     Data directory  Log file
18  main    5432 online <unknown> /pgdata/18/main /var/log/postgresql/postgresql-18-main.log
ahi@pgl:~$ sudo -u postgres psql -c "SHOW data_directory;"
 data_directory
-----------------
 /pgdata/18/main
(1 row)

ahi@pgl:~$ sudo -u postgres psql -c "SELECT version();"
                                                             version
-------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 18.4 (Ubuntu 18.4-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
(1 row)

We can also confirm that the WAL directory lives inside the data directory, and therefore on the zvol:

ls -ld /pgdata/18/main/pg_wal

We need a database large enough to make traditional backup and restore operations time-consuming. In the SQL Server part of this series, we used the StackOverflow database (about 207 GB). For PostgreSQL, we use pgbench, the benchmarking tool shipped with PostgreSQL.

We create the database and initialize it with a scale factor of 10000. This produces a database of approximately 146 GB, with 1 billion rows in the pgbench_accounts table:

sudo -u postgres createdb bench
sudo -u postgres pgbench -i -s 10000 --partitions=8 bench

A few minutes later:

We can monitor the data growth during the initialization:

watch -n 30 'df -h /pgdata'

A few minutes later:

On the Proxmox side:

After some time, the process completes:

vacuuming...

creating primary keys...
done in 1559.55 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 598.12 s, vacuum 675.53 s, primary keys 285.88 s).
ahi@pgl:~$

We check the database size:

We run a checkpoint before taking the snapshot. The recovery process starts replaying the WAL from the last checkpoint. By running it right before the snapshot, almost nothing needs to be replayed when the database starts after a restore:

sudo -u postgres psql -c "CHECKPOINT;"

Comparison with SQL Server:

On the SQL Server side, we had to run SUSPEND_FOR_SNAPSHOT_BACKUP and BACKUP WITH METADATA_ONLY. On the PostgreSQL side, none of that is needed.

The data files and the WAL are on the same zvol. An atomic ZFS snapshot therefore captures a state equivalent to a power loss, and PostgreSQL is designed to recover cleanly from that state through crash recovery: the WAL is replayed from the last checkpoint. This is documented and officially supported.

The snapshot is the backup. There is no .bkm file, no metadata backup.

On the Proxmox side, we create the snapshot and protect it with a hold:

SNAP="sqlpool/pve/vm-307-disk-0@pg_bench_$(date +%Y%m%dT%H%M%S)" 
zfs snapshot "$SNAP" 
zfs hold sqlsnap "$SNAP" 
echo "$SNAP"

The hold protects the snapshot from an accidental destruction, as we did in part 2 of this series. We note the exact snapshot name, it will be needed for the restore.

The database stays online during the whole operation. No I/O freeze is required.

We list the snapshots:

zfs list -t snapshot -r sqlpool/pve/vm-307-disk-0

We drop the database then we restore the snapshot:

We run the snapshot restore procedure. On the VM, we stop the cluster and unmount the volume:

sudo systemctl stop postgresql@18-main
sudo umount /pgdata

On the Proxmox side, we want to restore our snapshot. We can list the available snapshots:

zfs list -t snapshot -r sqlpool/pve/vm-307-disk-0

We roll back the snapshot:

zfs rollback -r sqlpool/pve/vm-307-disk-0@pg_bench_20260803T165409

On the VM, we mount the volume and start the service:

sudo mount /pgdata
sudo systemctl start postgresql@18-main

We check a few elements in the logs:

sudo tail -30 /var/log/postgresql/postgresql-18-main.log

The service shutdown, then the restart:

UTC [232108] LOG:  database system is shut down
UTC [233968] LOG:  starting PostgreSQL 18.4 (Ubuntu 18.4-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
UTC [233968] LOG:  listening on IPv4 address "0.0.0.0", port 5432
UTC [233968] LOG:  listening on IPv6 address "::", port 5432
UTC [233968] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

PostgreSQL detects that the database was not shut down properly and replays the WAL. This is the same crash recovery mechanism as in SQL Server. Finally, the database starts.

UTC [233974] LOG:  database system was not properly shut down; automatic recovery in progress
UTC [233974] LOG:  redo starts at 20/2CB76278
UTC [233974] LOG:  invalid record length at 20/2CB76380: expected at least 24, got 0
UTC [233974] LOG:  redo done at 20/2CB76348 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
UTC [233974] LOG:  checkpoint starting: end-of-recovery immediate wait
UTC [233972] LOG:  checkpoint complete: wrote 0 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.009 s, total=0.030 s; sync files=0, longest=0.000 s, average=0.005 s; distance=0 kB, estimate=0 kB; lsn=20/2CB76380, redo lsn=20/2CB76380
UTC [233968] LOG:  database system is ready to accept connections

We then verify that the database is available again:

sudo -u postgres psql -d bench -c "SELECT pg_size_pretty(pg_database_size('bench'));"

The previous test was done on a quiesced database: we ran a CHECKPOINT right before the snapshot, and nothing was writing. The real question is: what happens if the snapshot is taken while the database is being written to?

This is where PostgreSQL differs the most from SQL Server. There is no SUSPEND_FOR_SNAPSHOT_BACKUP. We take the snapshot in the middle of the write activity and we let the WAL replay do the work.

We start the load. The built-in pgbench script runs a TPC-B-like transaction: three UPDATE statements on the accounts, tellers and branches tables and one INSERT into the history table:

sudo -u postgres pgbench -c 8 -j 4 -T 300 bench &

While the load is running, we take a snapshot on the Proxmox side:

zfs snapshot sqlpool/pve/vm-307-disk-0@pg_bench_$(date +%Y%m%dT%H%M%S)

zfs hold sqlsnap sqlpool/pve/vm-307-disk-0@pg_bench_20260803T224551

No CHECKPOINT this time, no freeze. The database is actively writing while the snapshot is taken.

The state after some time under load:

We stop the service:

sudo systemctl stop postgresql@18-main 
sudo umount /pgdata

We restore the snapshot:

zfs rollback -r sqlpool/pve/vm-307-disk-0@pg_bench_20260803T224551 

We mount the volume, start the service and check the logs:

sudo mount /pgdata 
sudo systemctl start postgresql@18-main

This time the log shows a real recovery:

2026-08-03 20:49:03.244 UTC [234405] LOG:  database system is shut down
2026-08-03 20:50:34.333 UTC [234620] LOG:  starting PostgreSQL 18.4 (Ubuntu 18.4-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
2026-08-03 20:50:34.333 UTC [234620] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-08-03 20:50:34.333 UTC [234620] LOG:  listening on IPv6 address "::", port 5432
2026-08-03 20:50:34.335 UTC [234620] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2026-08-03 20:50:34.343 UTC [234626] LOG:  database system was interrupted; last known up at 2026-08-03 20:44:36 UTC
2026-08-03 20:50:34.381 UTC [234626] LOG:  database system was not properly shut down; automatic recovery in progress
2026-08-03 20:50:34.384 UTC [234626] LOG:  redo starts at 20/7A3618B0
2026-08-03 20:50:38.024 UTC [234626] LOG:  invalid record length at 20/8BC0A4F8: expected at least 24, got 0
2026-08-03 20:50:38.024 UTC [234626] LOG:  redo done at 20/8BC0A4D0 system usage: CPU: user: 0.71 s, system: 0.63 s, elapsed: 3.63 s
2026-08-03 20:50:38.028 UTC [234624] LOG:  checkpoint starting: end-of-recovery immediate wait
2026-08-03 20:50:53.936 UTC [234624] LOG:  checkpoint complete: wrote 105355 buffers (53.6%), wrote 5 SLRU buffers; 0 WAL file(s) added, 17 removed, 0 recycled; write=3.648 s, sync=12.232 s, total=15.911 s; sync files=189, longest=12.223 s, average=0.065 s; distance=287395 kB, estimate=287395 kB; lsn=20/8BC0A4F8, redo lsn=20/8BC0A4F8
2026-08-03 20:50:53.948 UTC [234620] LOG:  database system is ready to accept connections

Three differences compared to the first test:

• The “last known up at” timestamp (20:44:36) does not match a checkpoint we ran manually. It matches the last automatic checkpoint triggered during the load.

• The redo is not instantaneous anymore: 3.63 seconds, replaying about 280 MB of WAL (from LSN 20/7A3618B0 to 20/8BC0A4D0). All the write activity between the last checkpoint and the snapshot had to be replayed. The transactions committed before the snapshot are recovered, the ones that were in flight are rolled back.

• The end-of-recovery checkpoint then writes everything the redo rebuilt in memory: 105355 buffers, 53.6% of the buffer pool. The database is ready to accept connections about 19 seconds after the service start.

The crash recovery completed correctly and the database has been restored. We verify the TPC-B invariant. Each pgbench transaction applies the same delta to the accounts, tellers and branches tables in a single transaction. On a consistent database, the three sums must be equal:

• The snapshot covers the whole zvol. All the databases of the cluster are captured and restored together. There is no per-database restore, unlike the METADATA_ONLY approach on SQL Server which targets a single database.
• There is no backup history. SQL Server records the metadata backup in msdb. Here, the only trace is the snapshot itself on the ZFS side.
• Point-in-time recovery is not covered. The snapshot alone brings the database back to the moment it was taken. For PITR, WAL archiving would still be required on top of it.

• The snapshot backup and restore model of the SQL Server series applies to PostgreSQL (no I/O freeze, no metadata backup).
• One important rule: data files and WAL must reside on the same zvol so the snapshot is atomic.
• A 146 GB database was restored in a few seconds and in less than 20 seconds under active load, WAL replay included.

Thank you. Amine Haloui

L’article PostgreSQL Snapshot Backup and Restore with Proxmox ZFS (4/4) est apparu en premier sur dbi Blog.

If you follow me you know I have a history of moaning about Oracle Support. I have had some good experiences in the past, but overall I would have to judge the services as a complete fail. Website Since the migration of the main Oracle Support website, the user experience has been terrible. A lot … Continue reading "Oracle Support : A recent experience"

The latest AI models are remarkably capable; but they’re not perfect. So how do you use AI for coding without letting it go off the rails?

First, let’s get a few things out of the way. I do not use AI to write my articles for me. I do use AI for coding – but as a collaborator, not as a “vibe coder”. I personally review and understand every line of code deployed to production. Is it perfect? No. But it is a massive time-saver and accelerator.

I use GPT-5.6 extensively for both work and personal coding projects. When I first started (not that long ago – yes I’m a late adopter) I was told to use Plan mode for any non-trivial coding task. That helped a lot because it allowed me to review the changes the AI intended to make before it wasted a lot of tokens building something I didn’t want.

I rarely use Plan mode anymore. Instead, before writing the first line of code, I create a file.

In my repositories, I have a docs folder. Under that, I create a folder for the feature, containing one or more Markdown files. For example:

feature-name.md
design.md
implementation.md
deployment.md

For a relatively simple feature, I might use just one file containing all the details. For more complex features, I’ll use two or more Markdown files.

I start with the feature title, a summary, and whatever ideas I already have in my head. I then ask the AI to read the file and flesh out the details. It has access to the entire repository, including the schema, source code, and APEX applications; so it can design and plan the feature with direct reference to the existing codebase.

The document(s) grow over time, gaining whatever sections the AI or I think are needed, such as:

• Summary
• Requirements
• Current State
• Scope
• Design
• Project Plan
• Technical Implementation
• Open Questions
• … whatever else …

This starts a tight review-and-update loop. I read the changes made to the design doc, ask and answer questions, make adjustments, and commit the documents to the repository in stages. This makes it easy to see what changes the AI is making and revert them if it badly off track.

At any stage I can ask the AI to review the specification:

"is this spec clear, no ambiguities, gaps, or hand-waving"
"are we ready for implementation"

I can also switch to a different model or level of reasoning whenever I need to. A higher-reasoning AI is great for doing a sanity check. The document provides all the context it needs.

One advantage of this approach is that avoids the chat history problem. Long conversations involving design decisions, experiments, discarded ideas, and changes of direction can sometimes lead the AI accidentally down the wrong rabbit hole.

The document is a living specification, but it also serves as a definitive source of truth for the feature. At any point, I can start a new chat and get a fresh perspective without losing the important design decisions that have already been made.

The document also becomes a coordination tool during implementation. I ask the AI to update it with the current status of the feature, the stage we’re at, what has been deployed to development so far, and what the intended next step should be. In many cases, the implementation can proceed without interruption from start to finish. If it gets interrupted (e.g. because I’ve run out of credits) it’s easy to recover later.

In the repository root I have the AGENTS.md file containing instructions such as:

- Store feature artifacts in a folder under `docs`, named after the feature. Create the feature folder if it does not exist.
- If a feature design document does not exist, create one inside the feature folder.

After the feature is complete, I’ll typically keep its documentation. Later, if bugs or further changes arise, those documents provide valuable history and context for the AI, including the design decisions made in the past.

I’m not suggesting this approach is revolutionary or original. It’s just something that works for me, because it provides just the right level of rigour and continuity I need, without any layers of bureaucracy or unnecessary overhead.

Plan mode is wonderful. However, for serious development in collaboration with AI, design documents offer so many benefits that I can’t imagine working without them.

A simple reminder about how JSON PL/SQL methods deal with JSON values, it easy to get confused when you mix up JSON objects and their serialized counterparts, especially if some if these parts are coming from JSON SQL functions and you need to combine them with other parts generated by PL/SQL.

The code below should clarify the difference between a "real" JSON value and its textual representation, especially when you are assembling a JSON object with values containing other JSON objects or their serialized representation.

When you PUT a serialized JSON string into a new JSON, the method escapes all the special characters that otherwise would break the syntax (lines 10-16).
In order to reconstruct a valid JSON string, something that you can PARSE as JSON, you need to retrieve the value with GET_STRING or GET_CLOB if it is large (lines 18-20).

If you need to include a serialized JSON object into a new JSON object thus avoiding the automatic escaping, then you need to convert the serialized JSON string into a proper JSON object and then PUT it inside the new object (lines 26-29). 

declare
    c  varchar2(255) := '{"key": 1, "value": "X"}';
    d  varchar2(255);
    j  json_object_t;
    j1 json_object_t;
    j2 json_object_t;
begin
    
    if c is json then
        dbms_output.put_line('c is a string containing a valid JSON');
        j := json_object_t.parse(c);

        j1 := new json_object_t;
        j1.put('document', c);
        dbms_output.put_line('c is now escaped and becomes a string literal value');
        dbms_output.put_line(j1.to_string());
        dbms_output.new_line;
        d := j1.get_string('document');
        dbms_output.put_line('c is converted back into the original JSON object string');
        dbms_output.put_line(d);


        dbms_output.new_line;
        dbms_output.new_line;

        j2 := new json_object_t;
        j2.put('document', j);
        dbms_output.put_line('c is still a json object value, now embedded in a new JSON object');
        dbms_output.put_line(j2.to_string());
    else
        dbms_output.put_line('NOT JSON');
    end if;
end;
/

Watch out for NULL values because GET_STRING and GET_CLOB show two different behaviors in older releases of Oracle 19c.

Oracle released VirtualBox 7.2.14 a couple of days ago. As I mentioned in my previous post, I was expecting this release to coincide with the the normal quarterly patch cycle. The downloads and changelog are in the usual places. I’ve done installations on Windows 11 and Linux Mint and both worked fine. Vagrant I didn’t bother updating my Vagrant boxes for … Continue reading "Oracle VirtualBox 7.2.14"

Recently, I had to renew an SSL certificate on an SQL Server Reporting Services (SSRS) server. The task seemed straightforward: replacing an expired certificate with a new one containing the same configuration. However, once the change had been made, HTTPS wasn’t working anywhere — neither via the usual DNS names nor even when accessing the server directly. Only HTTP remained accessible.

Here is a step-by-step guide to how the problem was solved.

After the certificate was renewed:

• HTTP was working normally, both remotely and locally.
• HTTPS was not working.
• There were no certificate warnings or TLS errors: the issue manifested as an application error (404).

A 404 error is an application error, not an encryption error. It means that the TLS connection was established correctly (the certificate was presented and accepted), but that the server could not find any resource matching the request.

The first thing to check in any case is the certificate. Go to the server certificates and select the proprieties of the right certificate. You must ensure that it does indeed contain the correct SANs (Subject Alternative Names).

A point that is often overlooked: the CN (Common Name) of a certificate is no longer considered 100 per cent reliable. If you wish to connect via HTTPS using a specific name . That name must be included in the certificate’s SAN field, and not just in the CN.

The next step is to check the configuration of SSRS itself:

• Check that the certificate has been correctly associated with the service in Reporting Services Configuration Manager (Web Service URL and Web Portal URL).
• Check that the certificate is recognised for both IPv4 and IPv6.
• Ensure that the binding is consistent on both the SSRS service side and the server side (Windows / HTTP.sys).

This final check can be carried out via the command line using:

netsh http show sslcert

This command lists the mappings between IP addresses/ports and certificates (identified by their hash). In my case, the binding was set up using wildcards (0.0.0.0:443 and [::]:443) with a single certificate for all incoming HTTPS requests on port 443.

It was whilst looking into reserved URLs that the problem came to light. The following command lists the URLs reserved with HTTP.sys, the Windows component that manages HTTP/HTTPS listening at the system level:

netsh http show urlacl

The result revealed the source of the problem: the entries did indeed exist, but only for the server name, with an explicit host header. However, as the server name was not present in the certificate’s SAN. This is why the HTTPS connection was not working either:

The fix involves recreating the reservations assigned to each DNS so that the host header is accepted. Here, we add the URL by specifying the service account. It is important to add the SDDL (Security Descriptor Definition Language) . This is used to grant service accounts (like ReportServer) the permissions required to reserve specific URLs for web traffic. For SSRS 2017 and later, the AccountSid value is S-1-5-80-4050220999-2730734961-1537482082-519850261-379003301 and the AccountName value is NT SERVICE\SQLServerReportingServices. For Power BI Report Server, the AccountSid value is S-1-5-80-1730998386-2757299892-37364343-1607169425-3512908663 and the AccountName value is NT SERVICE\PowerBIReportServer. Here, we use the specifications for SSRS

cmd
netsh http add urlacl  url=https://"dns.name":443/Reports  
user="NT Service\ReportServerSQLServerReportingServices" sddl=D:(A;;GX;;;S-1-5-80-4050220999-2730734961-1537482082-519850261-379003301)

netsh http add urlacl  url=https://"dns.name":443/ReportServer    
user="NT Service\SQLServerReportingServices" sddl=D:(A;;GX;;;S-1-5-80-4050220999-2730734961-1537482082-519850261-379003301)

Once the previous steps had been completed without any issues being detected, the certificate contained the correct SANs, the SSRS configuration appeared to be consistent, and the DNS records were correctly pointing to the correct IP address. we had to dig deeper into the service’s configuration file itself:

C:\Program Files\Microsoft Power BI Report Server\PBIRS\ReportServer\rsreportserver.config

Contrary to what one might think, URL reservations at the HTTP.sys level (netsh http show urlacl) are not sufficient on their own: the SSRS/PBIRS service also maintains its own list of authorised names directly within this configuration file. Additions must be made after this tag : one entry for the web service (/ReportServer/) and another for the web portal (/Reports/). If a DNS entry is not explicitly declared in both of these locations, the service may refuse to recognise it as a valid name, even though HTTP.sys would be prepared to allow the request through.

The fix therefore involves manually editing the rsreportserver.config file and adding each relevant DNS to both instances of the tag, whilst strictly adhering to the syntax already in place for the existing entries.

Once changes have been made, the service must be restarted for the changes to take effect.

So here are a few key points to bear in mind:

1. A 404 error over HTTPS following a certificate change is not necessarily related to the certificate itself. If the TLS connection is established without any warnings, the problem is likely to lie in application routing (URL reservations), not in the trust chain.
2. netsh http show urlacl and netsh http show sslcert are the two key commands for distinguishing between a certificate binding issue and a URL reservation issue.
3. An explicit host header (name:443) restricts access to that name only
4. The service account is just as important as the URL itself. A technically correct reservation that is associated with the wrong account will prevent the service from creating its own endpoint, resulting in an E_ACCESSDENIED error on start-up.

Some Sources:

About reservations URL :Configure Reporting Services to use a Subject Alternative Name (SAN) – SQL Server Reporting Services (SSRS) | Microsoft Learn

About Common name on certificat : Chrome 58: Common Name in SSL Certificates Finally Dies | Dataprise

L’article Customer experience – Certificat SSL on an SQL Server Reporting Services est apparu en premier sur dbi Blog.

A production instance, mid-afternoon, nothing unusual on any dashboard. An engineer opens a transaction to patch a single row while investigating a data issue:

BEGIN TRANSACTION;
UPDATE dbo.Orders SET Status = 'Reviewed' WHERE OrderId = 482193;

No COMMIT. No ROLLBACK. The tab gets buried under three others, the investigation moves on, and the lock is still held an hour later.

Every query, every batch, every login needs a worker thread to execute on. That pool is not infinite, it is sized by max worker threads, either left on its computed default or pinned to a fixed number.

SELECT name, value_in_use FROM sys.configurations WHERE name = 'max worker threads';

name                                value_in_use
----------------------------------- -------------------------------------------------------------------------------------------------------------------------
max worker threads                  128

SQL Server schedules work cooperatively, not preemptively. Each worker is handed a quantum (4 milliseconds) to run before it is expected to voluntarily yield the scheduler to the next runnable task. This is the mechanism behind SOS_SCHEDULER_YIELD: a worker that still has work to do, but whose quantum has expired, stepping aside so someone else gets a turn.

None of this applies to the open transaction from earlier. A session that has issued no command has no task and holds no worker. Its status in sys.dm_exec_sessions is sleeping, not running, not suspended.

SELECT
    s.session_id,
    s.status AS session_status,
    ct.text
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) ct
WHERE s.session_id = 54;

session_id session_status                 text
---------- ------------------------------ --------------------------------------------------------------------------------------------------------------------
54         sleeping                       UPDATE dbo.Orders SET Status = 'Reviewed' WHERE OrderId = 482193;

It is not waiting for a quantum, because it is not competing for one. The lock it holds costs the engine nothing in scheduling terms; it is bookkeeping in the lock manager, entirely separate from the worker pool.

Let’s say that the application wants to confirm that the orders has been reviewed now it’s in the processed state.

BEGIN TRANSACTION;
UPDATE dbo.Orders SET Status = 'Processed' WHERE OrderId = 482193;

Seeing that the query didn’t complete to update the item, it will keep sending this transaction again and again, sending it 200 times let’s say.

Unlike the sleeping session above, each of these has issued a command. Each one is granted a worker to execute it, immediately hits the lock, and transitions to suspended, waiting on LCK_M_X.

wait_type                   waiting_tasks_count       wait_time_ms
THREADPOOL                      521                      2881770
SOS_SCHEDULER_YIELD             710                      41

 session_id     status        wait_type        wait_time   blocking_session_id 
    68         suspended       LCK_M_X          29873             54
   ...
   206         suspended       LCK_M_X          29478             68
   207         suspended       LCK_M_X          29478             68

scheduler_id runnable_tasks_count work_queue_count active_workers_count
       0              0               19                 43
       1              0               5                  45
       2              0               10                 45
       3              0               2                  44

active_workers    max_workers_count       
         205                    128

Note: max_workers_count only counts the user-facing pool; internal system threads, including the DAC’s own reserved worker used to capture this very output, sit outside that ceiling.

The worker is not released while the task waits. It stays attached to the suspended task for the entire duration of the block, doing nothing, simply reserved, waiting for the resource (the order line to update) to be available for updates.

The remainder cannot even be granted a worker to start waiting. They queue behind everyone else, and eventually give up entirely:

Login timeout expired
Login/Query timeout: 15/0 seconds

By this point the server has simply stopped accepting new connections.

Releasing the original lock should be the easy part: switch back to the session from the very first transaction, issue a ROLLBACK, and watch everything clear. Except that session, which has been sitting sleeping and worker-free this whole time, now has to issue a command of its own. And issuing a command means asking the pool for a worker (the same exhausted pool every other session is already queued for). The session responsible for the deadlock has no priority for fixing it. It gets in line like everyone else, behind two hundred sessions it created the conditions for.

This is where the Dedicated Admin Connection comes in the game. It runs on its own scheduler, with a worker reserved outside the regular pool, built specifically for an instance too exhausted to serve itself.

sqlcmd -A -S"." -E

SELECT blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

KILL 54;

Note: the “.” here resolves to the local default instance but unlike an ordinary local connection (which typically uses Shared Memory), the DAC always connects over its own dedicated TCP listener on the loopback adapter, regardless of protocol settings on the port 1434 or a dynamic one (full documentation here).

The KILL forces the rollback from outside the exhausted pool entirely. Workers free up in cascade, and the two hundred suspended sessions complete their updates and release their own.

In this example, we set the parameter max worker threads to 128 to easily saturate the worker threads. However, the default value for max worker threads is 0, which lets SQL Server compute the number of worker threads automatically at startup based on the number of logical CPUs and the platform architecture. Microsoft best practice can be found here and shows the following table:

And the key take-away from this experiment:

• Sleeping costs nothing, suspended costs a worker. The distinction between an idle transaction and a blocked one is the entire mechanism behind this incident: both hold a lock, only one of them holds a thread.
• The scheduler’s quantum explains CPU pressure, not threadpool exhaustion. Yielding after 4ms is about sharing a CPU among runnable workers; it has nothing to do with how many workers exist in the first place.
• The session that caused the block is not exempt from the consequences of the block. It has to compete for a worker like anything else, the moment it tries to clean up after itself.
• Never let a statement end without a COMMIT or a ROLLBACK.

L’article When an idle transaction starves the worker pool (THREADPOOL) est apparu en premier sur dbi Blog.

When organizations evaluate an Enterprise Content Management (ECM) solution, the conversation usually revolves around Artificial Intelligence, workflow automation, and integrations.

Yet the feature employees use more than any other is rarely the one showcased in demonstrations or marketing brochures: search.

The reality is simple. Most users don’t spend their days creating workflows or configuring metadata. They spend it looking for information.

Every unsuccessful search comes at a cost!

Think about your workday.

How many documents do you create?

Maybe a few.

How many do you approve?

Perhaps a few more.

Now, ask yourself a different question: How many times do you search for information?

Procedures, invoices, contracts, customer communications…

For most employees, searching is the most common interaction with an ECM system by far.

This means that even minor improvements to the search function can significantly impact productivity.

Many ECM vendors claim they can find any document in seconds.

That’s great, but it’s often not enough.

Imagine a customer calls about an invoice. You know the supplier, but not the invoice number.

A good ECM lets you find the document in seconds by searching the supplier, project, purchase order, or even the contract linked to it.

A poor search experience forces you to browse folders, ask colleagues, or search through emails.

An effective search is about relevance, not just speed.

Many organizations try to improve their search function by tweaking the search engine.

In reality, a good search starts much earlier.

It begins with:

• meaningful metadata
• consistent naming conventions
• well-designed object relationships
• document classifications
• quality-controlled information

Poor information management cannot be fixed with search alone.

Even the most advanced search engine will struggle to deliver useful results if the metadata is inconsistent or incomplete.

A good search experience should minimize the amount of thought required of users.

Instead of asking:

“What was the exact document name?”

Users should be able to search naturally:

• customer name
• supplier
• project
• Invoice number
• Contract type
• Date
• Keywords

The system should handle the complexities.

Users shouldn’t need to understand how the information is stored.

Generative AI doesn’t replace search, it changes users’ expectations of search.

Now users want to be able to ask questions like:

“Show me all contracts that expire next quarter.”

Or:

“Find the latest approved procedure for handling customer complaints.”

Behind these simple questions lies something much less glamorous: reliable metadata.

Without it, AI cannot consistently provide trustworthy answers.

In many ways, AI has made search even more important.

When discussing user adoption, project teams often focus on training.

Training certainly matters.

However, the user experience during searches is also important.

If employees consistently find what they need in seconds, their confidence in the system grows.

However, if they repeatedly fail to find information, they’ll quickly return to shared drives, email folders, or ask colleagues for help.

The quality of the search function shapes the perception of the entire enterprise content management (ECM) solution.

A good search is about more than just saving a few minutes.

• It enables better decision-making.
• It prevents duplicate work.
• It improves compliance.
• It accelerates customer service.
• It helps preserve organizational knowledge.

The value of an ECM system isn’t measured by how many documents it stores.

Rather, it’s measured by how effectively those documents can be found and used.

Search rarely appears as the headline feature in product demonstrations because every ECM platform offers some form of search.

The real difference isn’t whether a system can search, it’s how effectively users can find the right information when they need it.

A successful ECM implementation isn’t one where information is simply stored.

Rather, it’s one where information is found effortlessly, trusted confidently, and reused effectively.

After all, a document that can’t be found might as well not exist.

L’article Why search is the most underrated ECM feature est apparu en premier sur dbi Blog.

The Zabbix Agent 2 service on a Windows server was repeatedly becoming unresponsive and eventually crashing. The issue caused intermittent monitoring interruptions and required further investigation through Event Viewer messages and Zabbix Agent 2 logs to better understand why the service was no longer responding properly.

While monitoring a Windows server with Zabbix Agent 2, we encountered repeated crashes of the agent service accompanied by the following Event Viewer message:

A timeout (30000 milliseconds) was reached while waiting for a transaction response from the Zabbix Agent 2 service.

A few moments later, Windows reported:

The Zabbix Agent 2 service terminated unexpectedly.

The first assumption was that a plugin or item was taking too long to execute, causing the agent to become unresponsive. A natural idea was therefore to increase the timeout value. Here the official documentation for the PluginTimout.

Inside the Zabbix Agent 2 configuration, we identified the following parameter:

### Option:PluginTimeout
# Timeout for connections with external plugins.
## Mandatory: no
# Range: 1-30
# Default: <Global timeout>
# PluginTimeout=

However, this parameter only supports values between 1 and 30 seconds.

This raised an important question:

If a timeout already exists, why does the entire agent still become blocked and eventually crash?

Zabbix Agent 2 uses a plugin-based architecture written in Go.
Unlike isolated external processes, many plugins run inside the same agent process.

This means that if a plugin becomes blocked:

• worker threads remain occupied
• new requests start queuing
• the agent gradually stops responding
• Windows eventually considers the service frozen

The 30-second timeout seen in Event Viewer is actually the Windows Service Control Manager (SCM) timeout, not a protection mechanism for the plugin itself.

In other words:

• the plugin blocks first
• the agent becomes unresponsive
• Windows waits 30 seconds
• Windows kills the service

The Zabbix Agent 2 logs quickly pointed toward the real culprit.

[WindowsPerfMon] failed to get performance counters data:'cannot collect value No data to return.'

This indicated that the agent was struggling to collect Windows Performance Counters.

The most important recurring message was:

[WindowsPerfInstance] Cannot refresh object cache:Unable to connect to the specified computer or the computer is offline.

This error appeared continuously for several days.

The key observation here is that the agent was running locally, so the message below should never normally appear:

Unable to connect to the specified computer

This strongly suggested:

• an invalid or corrupted performance counter
• a problematic PerfMon object
• or a failing wildcard instance (Process(*), LogicalDisk(*), etc.)

Later in the logs, additional symptoms appeared:

failed to accept an incoming connection:accept tcp [::]:10050:acceptex: The I/O operation has been aborted because of either a thread exit or an application request.

At this stage, the agent was no longer able to accept incoming connections because its internal workers were saturated or blocked.

This confirmed that the issue was not a traditional crash, but rather a thread starvation / blocking situation.

An important misunderstanding was clarified during the investigation.

The default timeout value of 30 seconds does not immediately protect the agent.

Instead:

• the plugin may block for the full 30 seconds
• multiple blocked requests accumulate
• worker threads remain occupied
• the agent becomes globally unresponsive

By the time Windows notices the issue, the agent is already effectively frozen.

Instead of increasing the timeout, the correct approach was to reduce it significantly.

The following configuration was applied:

PluginTimeout=15

This acts as a protection mechanism inside the agent itself.

With this configuration:

• problematic plugin executions are aborted quickly
• blocked threads are released faster
• the agent remains responsive
• only the affected items temporarily fail

The issue was not caused by the timeout itself.

The real root cause was a problematic WindowsPerfInstance plugin execution blocking the Zabbix Agent 2 internal workers.

Reducing the plugin timeout from 30 seconds to 15 seconds prevented the entire agent from becoming unresponsive while still allowing the monitoring system to recover automatically when the plugin started responding again.

This is a good example of why increasing timeouts is not always the right solution. Sometimes, shorter timeouts are actually what keeps a monitoring agent stable.

You can find other blogs regarding Zabbix or database administration or other topics at this link: dbi blogs

L’article Zabbix Agent 2 service terminated unexpectedly on Windows server est apparu en premier sur dbi Blog.

I have been looking to study and test what Oracle GenAI Ask EM assistant, also called now Oracle AI database assistant, can offer. This generative AI assistant is directly integrated into Oracle Enterprise Manager 24ai. Before being able to look into Ask EM, I first had to install an Oracle EM platform. I had the choice between doing all the installation myself manually or installing it from Oracle Marketplace. Knowing my current need, I decided to install it from the Oracle Marketplace. I would like to share in this first Ask EM series blogs this installation.

For my current purpose of getting a lab in order to test GenAI Ask EM feature, the advantages of doing an installation from the marketplace are the following:

• Faster and less work to get a working environment ready for Ask Em testing
• Less possible errors and problems
• Oracle prope a full preconfigured image with OS and EM
• Minimalize deployement time
• More time to focus on Ask EM testing

The disadvantage would be to have:

• Less flexibility

But I really do not need any customized installation.

The manual installation complexity would mainly be to install and configure manually:

• Oracle VM with OS (Oracle Linux)
• EM repository database
• EM weblogic
• EM software installation and patching
• OMS
• …

Please note following:

• I will be installing a simple deployment sizing lab.
• Enterprise Manager Instance Shape, VM.Standard2.4, will then be suffisient.
• I will use existing VCN and subnet
• I will make a single node installation with Enterprise Manager and database installed in a private subnet. This will have the benefit not to expose the Instance on the public network. I will then need a bastion VM which will be part of the installation

But what would be the cost? The cost will come mainly from the cost of the components:

• Compute instance will be charged based on the shape, OCPU and memory. It is in general the biggest cost
• Block Volume which is charged per provisioned GB
• Networking. VCN, subnets and security lists are free of charge, and are anyhow already existing.
• Single instance repository database does not require any license
• Use EM features only covered by your existing oracle licenses. Pack such as the Diagnostics Pack, Tuning Pack, Lifecycle Management Pack, etc., require the appropriate licenses.

The EM stack from the Marketplace is with BYOL (Bring Your Own License). In any case, I will strongly recommend to check your license and evaluate the cost on your side before doing any installation, moreover if it is for a production installation. My case is just a lab testing case.

I will first sign in to OCI and go to Marketplace. From there I will search and click on Oracle Enterprise Manager, see below pictures.

Once you have selected the Oracle Enterprise Manager 24ai stack, review the information to ensure it is accurate and click on Launch State.

Following 3 pictures will show the details.

Configure Compartment, accept the terms and conditions and launch the stack, see:

Provide a name and a desciption, and click next, see:

Configure the installation and sizing details:

• Choose simple as deployment size. One node is suffisient for our lab and test
• Click on advanced deployment to reuse existing infracsture (VCN and subnets)

See following picture:

Provide existing VCN Name:

Provide networking details making sure to chose existing private subnet:

Configure Oracle Management providing Server details.

First we will provide an hostname prefix, choose Operating System version and provide Enterprise Manager password:

We will provide agent registration password and weblogic admin and node manager password, before chosing Enterprise Manager Instance shape and boot volume size. As discussed in the pre-requirement, a VM.Standard2.4 is enough for my need.

And finally also provide the public key to be able to access the VM later on with SSH.

I will also have to provide all the details concerning the repository database, that’s to say database sys and dnsnmp user password. I will keep the database in archive log mode.

As I choose to install Enterprise Manager in the private subnet, I will need a bastion. I could decide to use an existing one where the installation will make needed changes or create a new one specific for EM. I decided to create a new one, but using existing subnet, see following information that needs to be provided, before clicking next button:

Review the whole configuration:

Select run apply and click the create button.

And the we can see the job stack execution. The status will first go to Accepted status and then In progress status, see next pictures:

Once completed, we can check the job stack status, the created VM compute instance (OMS server, bastion) and EM access.

As we can see in the next picture, the job status is now Succeeded.

In Resource Manager, under Stacks menu, we can see our OEM-Lab stack that is active. All components information will be provided.

We can check in our compartment the running EM-OMS VM compute instance and EM-OMS-bastion.

Let’s first check and confirm that I can join the OMS Bastion from my MAC.

maw@DBI-LT-MAW2 ~ % ssh -i /Users/maw/Documents/Current-Dokument/Dokument/pem_ssh_key/yak_beta_workshop/srv/sshkey opc@152.67.XX.XXX
The authenticity of host '152.67.XX.XXX (152.67.XX.XXX)' can't be established.
ED25519 key fingerprint is: SHA256:CVPCXt9EwZ153fUVnZf3AGADYNVqT9fJGaU70TKbX+I
This key is not known by any other names.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '152.67.XX.XXX' (ED25519) to the list of known hosts.
** WARNING: connection is not using a post-quantum key exchange algorithm.
** This session may be vulnerable to "store now, decrypt later" attacks.
** The server may need to be upgraded. See https://openssh.com/pq.html
Last login: Mon Jul 13 08:25:33 2026 from 140.238.169.22
[opc@em-oms-bastion ~]$

AS I choose an installation on private network, I do not have access to EM through a web browser from my MAC. I first need to configure a ssh tunnel.

maw@DBI-LT-MAW2 ~ % ssh -i /Users/maw/Documents/Current-Dokument/Dokument/pem_ssh_key/yak_beta_workshop/srv/sshkey -L 7799:192.168.1.142:7799 opc@152.67.XX.XXX
** WARNING: connection is not using a post-quantum key exchange algorithm.
** This session may be vulnerable to "store now, decrypt later" attacks.
** The server may need to be upgraded. See https://openssh.com/pq.html
Last login: Mon Jul 13 11:02:53 2026 from 146.4.101.46
[opc@em-oms-bastion ~]$

Check from OMS bastion that EM console is reachable.

[opc@em-oms-bastion ~]$ curl -k https://192.168.1.142:7799/em
302 Moved Temporarily

This document you requested has moved
temporarily.
It's now at https://192.168.1.142:7799/em/login.jsp.

[opc@em-oms-bastion ~]$

Check that the connection is possible on EM from my MAC directly after I have created the SSH tunnel:

maw@DBI-LT-MAW2 ~ % curl -k https://localhost:7799/em
302 Moved Temporarily

This document you requested has moved
temporarily.
It's now at https://localhost:7799/em/login.jsp.

maw@DBI-LT-MAW2 ~ %

All is good, I can not test from my MAC using a web browser.

Let’s go in the summary page.

The easiest way for me to have got a EM lab installation to look into GenAI Ask EM was to install it from the marketplace. Now I’m ready to install some agent on some database host and test Ask EM functionality. I will be sharing this in a next blog.

L’article Oracle GenAI – Ask EM – Deploy Oracle Enterprise Manager 24ai from Marketplace est apparu en premier sur dbi Blog.

Over the past few weeks, I’ve addressed philosophical questions related to enterprise content management (ECM), such as “What should be done?” and “Why?” Now, it’s time to focus on the “how.”

When discussing user adoption of M-Files, the conversation often centers on training, change management, and automation. While these aspects are important, another factor immediately impacts the user experience: the Metadata Card.

A poorly designed Metadata Card can overwhelm users with unnecessary fields and irrelevant questions, making creating a document feel like filling out a tax form. Conversely, a well-designed Metadata Card naturally guides users through the process by displaying only the necessary information.

The goal is not to collect as much metadata as possible but rather to collect the right metadata at the right time.

Just a reminder that in M-Files, a metadata card is the panel that displays and allows users to edit the metadata properties of an object. It is an essential component of the tool.

Before creating properties or configuring rules, ask a simple question:

What information does the user actually know at this stage?

Consider an invoice.

At creation, the user probably knows:

• Supplier
• Invoice number
• Invoice date
• Amount

They probably don’t know:

• Approval status
• Payment date
• Accounting reference
• Archive classification

Those properties should appear only when they become relevant.

Metadata Card should evolve with the document lifecycle rather than exposing every possible property from the beginning.

One of the most effective improvements is dynamic property visibility.

Rather than displaying every property permanently, configure the card so that properties only appear when certain conditions are met.

For example:

• If the document class is “Contract”, display the contract expiration date.
• If the supplier is external, display vendor-specific properties.
• If the document is confidential, display the security classification section.
• If the document enters the approval workflow, display approval-related properties.

This approach reduces visual clutter and helps users focus on the task at hand.

One common mistake is making too many properties mandatory.

Although mandatory properties can be useful, they should only be used when appropriate.

For example:

The “termination date” property should not be mandatory when creating a new employee contract. This property only becomes relevant if the employee leaves the company.

Conditional mandatory properties allow for validation without frustrating users.

Rather than forcing users to enter placeholder values just to save the document, only ask for this information when it is required by the business process.

Metadata cards are easier to navigate when related properties are grouped together.

Instead of a long list of unrelated fields, organize them into logical sections.

Same properties but on the right we organized them

Users scan information much faster when it is visually organized.

Additionally, sections that are not needed at a given stage can be hidden or collapsed.

Every visible property asks the user to make a decision.

Should I fill this in?

Does this apply to my document?

What does this property even mean?

A good metadata card minimizes these decisions.

It is good practice to use:

• Automatic values
• Default values
• Value lists
• Metadata inheritance
• Calculated properties

The fewer decisions users have to make, the faster and more accurately they can classify documents.

One of the biggest design mistakes is trying to satisfy every department.

For instance, the Human Resources department requires three properties, the Legal department requests five more, the Finance department submits a request for four additional fields, and finally, the Compliance department adds another six.

After a few workshops, the metadata card ends up with thirty or forty properties.

Technically, everything is possible.

Practically, nobody enjoys using it.

Whenever a new property is requested, ask:

• Who will maintain it?
• Who actually uses it?
• What business process depends on it?
• What happens if it remains empty?

If there isn’t a clear answer, then the property probably isn’t necessary.

Most users perform the same actions repeatedly.

Optimize the metadata card for 80% of documents rather than exceptional cases.

Advanced scenarios can reveal additional properties as needed.

Simple cases should remain simple.

Administrators often focus on configuration, whereas it is the users who interact with the interface.

Therefore, it is important to keep in mind that every additional property increases cognitive load.

Similarly, every unnecessary required field creates friction.

Conversely, every hidden property reduces complexity.

A well-designed metadata card improves not only data quality but also the user experience of the entire M-Files system.

Metadata is one of M-Files’ greatest strengths, but only if users provide it.

The best metadata card isn’t the one that captures the most information.

Rather, it’s the one that asks the fewest questions while still collecting everything the business needs.

When users feel that the system understands their tasks instead of getting in their way, they will naturally adopt it.

Sometimes improving user satisfaction isn’t about adding new functionality; it’s about designing a better metadata card.

Whether you’re planning a new M-Files implementation or looking to improve an existing one, we can help you design a solution that is efficient, user-friendly, and aligned with your business needs. Feel free to contact us to discuss your project.

L’article Designing metadata cards that users like est apparu en premier sur dbi Blog.

Extended Events have a reputation for being lightweight, and most of the time they are. However, poorly configured setups can heavily degrade your SQL Server Extended Events performance, stalling application threads and keeping a disk busy for minutes after your workload has ended. The events you choose to capture matter just as much as the session settings: some events, like the showplan ones, carry a heavy cost by design regardless of how you configure the rest of the session. I wanted to see what all of this looks like from the DMV side, so I built the worst XE session I could think of, threw a StackOverflow workload at it, and compared the numbers with a healthy session.

Two DMVs are enough for this analysis:

sys.dm_xe_sessions for buffer state, data volume and dropped events
sys.dm_os_wait_stats for the wait types produced by the XE

Keep in mind that both are in-memory structures. The counters in sys.dm_xe_sessions accumulate since the session started (create_time), and sys.dm_os_wait_stats accumulates since the instance started but everything resets after a restart.

Also note that wait statistics are instance-wide: if several XE sessions run at the same time, the XE wait types aggregate all of them (that’s why we will focus on one specific XE called XE_STRESS_TEST, all others are disabled even the built-in ones).

SELECT 
    s.name AS session_name,
    s.dropped_event_count,
    s.dropped_buffer_count,
    s.buffer_policy_desc,
    CAST(ROUND(s.total_buffer_size / 1024.0 / 1024.0, 2) AS FLOAT) AS total_buffer_size_mb,
    s.total_regular_buffers,
    CAST(ROUND(s.regular_buffer_size / 1024.0 / 1024.0, 2) AS FLOAT) AS regular_buffer_size_mb,
    s.buffer_processed_count,
    CAST(ROUND(s.total_bytes_generated / 1024.0 / 1024.0, 2) AS FLOAT) AS total_bytes_generated_mb,
    DATEDIFF(MINUTE, s.create_time, GETDATE()) AS session_age_minutes,
    CAST(ROUND(s.total_bytes_generated / 1024.0 / 1024.0 
         / NULLIF(DATEDIFF(MINUTE, s.create_time, GETDATE()), 0), 2) AS FLOAT) AS bytes_generated_mb_per_minute,
    CAST(ROUND(s.dropped_event_count * 1.0 
         / NULLIF(DATEDIFF(MINUTE, s.create_time, GETDATE()), 0), 2) AS FLOAT) AS dropped_event_count_per_minute
FROM sys.dm_xe_sessions s
WHERE s.name = '<XE_NAME>';

Since the counters are cumulative, a single snapshot tells you very little. What you want to know is whether they increase between two runs of the query. In practice:

buffer_policy_desc is worth a look too: drop_event means the session is asynchronous and accepts losing events under pressure, block means NO_EVENT_LOSS was configured and application threads will wait instead of dropping anything. More on that below.

There are more XE-related wait types than this, but after testing I only kept the three that actually tell you something:

SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    CAST(wait_time_ms * 1.0 / NULLIF(waiting_tasks_count, 0) AS DECIMAL(10,2)) AS avg_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
    'XE_TIMER_EVENT',
    'XE_DISPATCHER_WAIT',
    'PREEMPTIVE_XE_DISPATCHER'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

XE_TIMER_EVENT is the dispatcher thread waiting for the next flush cycle defined by MAX_DISPATCH_LATENCY. It is always present and always harmless.

XE_DISPATCHER_WAIT is the dispatcher waiting for buffers to process. Counter-intuitively, a high average is good news: the dispatcher spends its time waiting for work. A very low average means it never gets to rest between flushes.

PREEMPTIVE_XE_DISPATCHER occurs when the dispatcher switches to preemptive mode to execute an operation outside of SQLOS control, typically writing event data to disk through an OS call. It shows up under high XE load, during OS interactions, or when the storage cannot absorb the writes. On a healthy instance this stays at zero. When it starts growing, your XE session has become a real workload for the server.

On my lab, the system_health session has been running for about 132 days:

4 GB in 132 days, nothing dropped. On the wait side, XE_DISPATCHER_WAIT averages around 57 seconds per wait, meaning the dispatcher spends almost a minute idle between two buffers, and PREEMPTIVE_XE_DISPATCHER sits at 0 ms. That is what an XE session that nobody notices looks like.

Now the opposite. This session combines everything you should not do:

CREATE EVENT SESSION [XE_STRESS_TEST] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.query_hash,
        sqlserver.query_plan_hash,
        sqlserver.plan_handle,
        sqlserver.username,
        sqlserver.database_name,
        sqlserver.client_hostname,
        package0.collect_system_time
    )
),
ADD EVENT sqlserver.query_post_execution_showplan (
    ACTION (
        sqlserver.sql_text,
        sqlserver.database_name,
        package0.collect_system_time
    )
)
ADD TARGET package0.event_file (
    SET filename = N'C:\XE\XE_STRESS_TEST.xel',
        max_file_size = 10,
        max_rollover_files = 999
)
WITH (
    MAX_MEMORY = 512 KB,
    EVENT_RETENTION_MODE = NO_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 1 SECONDS,
    MAX_EVENT_SIZE = 10240 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = ON,
    STARTUP_STATE = OFF
);

Why each configuration option is a bad idea:

• query_post_execution_showplan captures the full XML execution plan of every single query. A plan can weigh several hundred KB, sometimes a couple of MB. Microsoft documents this event as having performance overhead (link).
• MAX_MEMORY = 512 KB gives the session a tiny buffer pool that fills up after a handful of events once plans are involved, so the dispatcher flushes constantly.
• NO_EVENT_LOSS tells SQL Server that losing an event is not acceptable. The consequence is that any thread firing an event while all buffers are full has to wait. Your queries pay for the XE session.
• MAX_DISPATCH_LATENCY = 1 SECONDS forces a flush cycle every second no matter what.
• TRACK_CAUSALITY = ON adds a GUID and a sequence number to every event, which costs a bit of CPU and space each time (link to the documentation).

To create the pressure on this XE, the workload was as follows: 10 parallel SSMS sessions, each running 1 000 iterations of joins between Posts, Users and Votes on the StackOverflow database, with a variable predicate so that no plan gets reused and every execution produces a fresh showplan event (the query is not important here, the only goal is to generate workload so that the XE has something heavy to capture).

Early in the load, the session had already generated 1 GB. Nothing dropped, PREEMPTIVE_XE_DISPATCHER still at zero, but the average of XE_DISPATCHER_WAIT was down to 6 ms. The dispatcher was already working non-stop, the disk was simply keeping up so far.

A minute later the picture changed completely. 2.5 GB generated, and PREEMPTIVE_XE_DISPATCHER had jumped to over 1 168 690 ms accumulated. The dispatcher was now spending its life in preemptive mode, out of SQLOS control, waiting for the OS to complete disk writes.

Then the interesting part. The 10 load sessions finished, and the XE session kept writing. With NO_EVENT_LOSS and a 512 KB pool, a backlog of buffers had piled up in memory during the load, and the dispatcher was draining it file after file. The max_wait_time_ms of XE_DISPATCHER_WAIT climbed to 74 seconds while total_bytes_generated_mb barely moved: the disk was the bottleneck.

Eight minutes after the session started, the tally was 5.4 GB written, more than 500 rollover files of 10 MB on disk, for a workload that had ended long before.

Note the trap on dropped_event_count: both sessions show zero, for opposite reasons. The healthy session drops nothing because it has nothing to drop. The stressed session drops nothing because NO_EVENT_LOSS made the application threads wait instead. Looking at that counter alone, both sessions look fine. Only the wait types reveal the difference.

NO_EVENT_LOSS does not buy you capacity, it converts event loss into query latency. It has its place for a short targeted capture where completeness matters, never for a permanent session.

query_post_execution_showplan without a predicate will drown any buffer configuration. If you need to capture it, scope it to a database or an object using predicates.

An undersized MAX_MEMORY turns every couple of events into a flush, and every flush is an opportunity for the dispatcher to end up in PREEMPTIVE_XE_DISPATCHER.

And check both DMVs, not just one. sys.dm_xe_sessions tells you what happened to your data, sys.dm_os_wait_stats tells you what it cost the server. In my stress test, the first one looked almost reassuring. The second one did not.

L’article Measuring the real performance cost of SQL Server XE buffers est apparu en premier sur dbi Blog.

Oracle released VirtualBox 7.2.12 a couple of days ago. This comes hot on the heels of version 7.2.10, which I wrote about here. The downloads and changelog are in the usual places. I’ve done installations on Windows 11 and Linux Mint and both seem OK. As with the last version, on Windows 11 I got away with a straight upgrade, but … Continue reading "Oracle VirtualBox 7.2.12"

In this blog post, I will outline the steps required to enable and configure the M-Files Outlook Pro add-in. This includes configuring Microsoft Outlook rules to automate email handling. It took me some time to configure the rules, as they were not working as I expected. I hope this blog helps others save time.

The installation is very straight forward and well documented, it can take some time for the creation of the necessary self signed certificated for the Microsoft Azure Application. In addition, you must ensure that you have management access to the Microsoft Azure Admin Centre or an admin aside to perform the required steps

I will. not go through each step in detail, but I will highlight important steps from my point of view. The step by step documentation can be found on the M-Files web page for Integrations.

Important steps:

• Allow third-party cookies for Outlook Web.
  https://[*.]office365.com
  https://[*.]office.com
• Ensure that the lins below not blocked by the firewall.
  https://mfnewoutlookaddinprod.m-files.com
  https://login.microsoftonline.com/common
  https://login.m-files.com/
• Create a self signed certificate and convert them to be able to configure the M-Files Vault Application.
• Create and configure the Application in Azure, according the M-Files documentation.
• Installation and configure the M-Files Vault Application, just follow the M-Files documnetation.
• Install or deploy the M-Files for Outlook add-in. It is a certified Microsoft application. It can be installed individually or deployed to a specific group of users. This is the standard Microsoft process.
  

Create a self signed certificate for use in a cloud setup

Open PowerShell with administrative access and use the command below.

$certname = "{certificateName}"    ## Replace {certificateName}
$cert = New-SelfSignedCertificate -Subject "CN=$certname" -CertStoreLocation "Cert:\CurrentUser\My" -KeyExportPolicy Exportable -KeySpec Signature -KeyLength 2048 -KeyAlgorithm RSA -HashAlgorithm SHA256

Export-Certificate -Cert $cert -FilePath "C:\Users\admin\Desktop\$certname.cer"   ## Specify your preferred location

$mypwd = ConvertTo-SecureString -String "{myPassword}" -Force -AsPlainText  ## Replace {myPassword}

Export-PfxCertificate -Cert $cert -FilePath "C:\Users\admin\Desktop\$certname.pfx" -Password $mypwd   ## Specify your preferred location

In case you get an execution error from PowerShell, you can use the command below to allow the execution

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Force

If everything worked as expected you have files as in the example below.

Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----        29/06/2026     08:18            772 testcert.cer
-a----        29/06/2026     08:20           2644 testcert.pfx

To convert the certificates, you will need to use OpenSSH and Cygwin. M-Files strongly recommends using Cygwin. I encountered issues when I did not use Cygwin and it did not work.
Conversion is required in order to configure the certificate within the M-Files Vault application.

After the installation of Cygwin the command below must be executed in a Cygwin command window.

openssl pkcs12 -in MyCert.pfx -out MyCert.pem -nokeys

openssl pkcs12 -in MyCert.pfx -out MyCert.key -nocerts

openssl rsa -in MyCert.key -out MyCert_encrypted.key -aes256

You can use the Outlook and M-Files rules to automatically save received emails to the M-Files system. It is important to understand how each rule works within the M-Files Outlook add-in. This differs from creating an object in M-Files, where the workflow and required state must be explicitly defined.

Open the M-Files Outlook Add-In in Outlook and navigate to “Manage folder rules.

The screenshot below shows the configuration of the Outlook folder “Firma A – Projekt A”, which will be imported automatically and assigned to the M-Files class ‘Email Import’. Additionally, the “Email Import Validation” workflow with the state “Import prüfen” is assigned.
If the workflow is not defined in the rule, it will not be assigned as expected. As we know, when a new object is created in M-Files, the workflow of the class is used automatically.

In order to automate the process of moving incoming emails into M-Files, two steps are required. First, define the usual Outlook rule, then create a rule in the M-Files Outlook add-in, as explained above.

Don’t hesitate to get in touch with us or directly with me if you have any more questions or need support with implementation.

L’article M-Files Outlook Pro add-in configuration est apparu en premier sur dbi Blog.