Yann Neuhaus

In IT, it is essential to stay up to date, as technology evolves at an ever-accelerating pace.

As consultants, we constantly face new challenges, many of which extend beyond purely technical matters, while adapting to diverse contexts and audiences.

That’s what makes this profession so exciting!

At dbi services, knowledge sharing is deeply embedded in our culture.

Over the past three months, I’ve had the opportunity to share my experience working on ECM projects for years.

This post concludes the series by highlighting the key insights that have shaped my approach to these projects.

In my previous roles, I was more of a product specialist than a solutions specialist. My job was to adapt clients’ needs to the software framework.

Now, working for a company that isn’t a “pure player” enables me to select the optimal solution from the beginning rather than having to adapt, of course I love M-Files, but alfresco is another solution we like!

For me, the right approach is certainly not:
“What can this ECM do?”

Rather, it is:
“What business problems do we need to solve first, and for whom?”

ECM initiatives often fail when they attempt to tackle everything at once rather than focusing on what truly matters. In practice, users don’t adopt tools just because they exist, they embrace solutions that directly address their daily pain points. That’s why achieving early, tangible success is critical. It builds credibility and helps secure continued investment.

Rather than implementing broad, I would focus on three to five high-impact use cases, such as invoice processing, contract lifecycle management, and quality documentation.

These cases should deliver measurable results within weeks rather than quarters. Each use case should be treated as its own product, with a clear value proposition and user-centric design, rather than as just another feature within a larger system.

I used to think: “If the solution is good, people will use it.”

That’s wrong.

Adoption requires preparation. It doesn’t just happen on its own.

The hard truth is that the best ECM solution with no adoption equals failure.

What I’d do differently:

• Identify the key users early on. They will help you get the solution adopted
• Design in collaboration with actual users, not with their representatives.
• Invest time and resources in on-boarding with in-app guidance, simple training paths, and internal champions.

Remember, if users need a manual to use the solution, you’ve already lost them.

One of the biggest mistakes I’ve seen (and made) is overengineering metadata and taxonomy.

We aimed for a perfect structure. We got complexity.

The reality is that users don’t care about your taxonomy. It adds complexity, makes classification difficult, slows down their work, and ultimately reduces user adoption.

Here’s what I’m doing now:

• I’m trying to limit the number of fields to seven or fewer.
• I take full advantage of automation features, such as default values, smart classification, and recognition.
• I use an iterative approach if it results in tangible improvements.

A good structure that is used consistently is better than a perfect one that nobody follows.

Projects often treat automation as a second phase. For me, that’s a mistake.

Today, without automation, ECM is just a digital archive.

Here are some things to do from day one:

• It is essential to pinpoint tasks that are repetitive in nature and convert them into workflows. Examples of such tasks include approvals, classification, team collaborations, and more. This conversion process must be incorporated into the initial release.
• Use AI carefully and for meaningful topics. AI is trendy and can benefit us if we use it to accelerate work in areas such as classification, translation, and summarization, not just because it’s hype.

The goal is to eliminate unnecessary work and allow users to focus on what is important.

Keep in mind that success metrics are not the number of documents migrated, users trained, or system uptime.

True success is measured by the positive impact the solution brings, such as reduced processing time, increased adoption, improved compliance, and saved time.

Measure the real impact to prove added value.

This is a common mistake that I still often see during ECM implementation, and it needs to change.
An ECM is not a static project consisting of analysis and implementation, and then it’s finished.

We must adopt an agile approach of building, learning, improving, and repeating.

As soon as users start using the solution, we must maintain a backlog of improvements, perform regular releases, and continuously gather user feedback.

An ECM project is never truly finished because it must evolve with business needs. Otherwise, businesses will adapt their work to the tool and slowly abandon it.

Although governance is essential, it can also become an obstacle.

I’ve seen governance frameworks delay projects by several months because they lack flexibility.

Nothing is perfect, so while you should meet compliance requirements, keep things simple.

Clearly define responsibilities from the start:

• Who is responsible for metadata?
• Who approves changes?

Ensure that governance remains light and practical.

Strike the right balance between control and ease of use.

Although IT leads this type of project, users are often not part of the team.

Therefore, establishing strong collaboration between IT and business users is crucial.

Involve users in the decision-making process.

Hold them accountable for adoption, this is a company-wide project, and its success depends on them.

To sum up my years of experience in ECM.

While it’s initially reassuring to master a product and understand its inner workings, ultimately, clients don’t care about that.

They have various problems and want a solution and very often, the specific product doesn’t matter.

They count on us to recommend the most suitable solution because they are busy running their business and don’t have time to compare products on the market.

ECM success isn’t just about managing documents; it’s about enabling better work.

• Help users work more efficiently.
• Provide business value.
• Improve the user experience over time.

If you’re about to start a project, ask yourself:

• Are we solving real problems?
• Are users involved from day one?
• Are we delivering value early on?

If not, now is the perfect time to ask us for help!

L’article After years of ECM projects, here’s how I do things differently nowadays est apparu en premier sur dbi Blog.

In one of my previous blogs, I explained how to use a Tailscale sidecar container into an existing pod to expose it to your Tailnet. Even though this works fine, it can be tedious to set up and not very convenient for every use case and not scalable at all. Another solution is to use the Tailscale Kubernetes Operator.
Rather than going through all that manual configuration, you can simply add some annotations on your Kubernetes services and call it a day.

Yes, there’s a bit of upfront setup involved but once that’s out of the way, day-to-day management becomes significantly less painful, especially for the workloads. The operator sits inside your cluster and keeps an eye on things, picking up on any Kubernetes resource changes and making sure your Tailscale network reflects those changes accordingly.

My use case is as follows: I’ve installed ArgoCD at home and want to access it through my Tailnet. In this blog, the example will focus on an Nginx pod, but the principle is exactly the same. We’re not making any changes whatsoever to the pods or deployments; only the Services matter.

Let me guide you through the configuration of the Tailscale Operator and see how convenient it is. You’ll love it, I swear!

Before anything else, you’ll need to set up some new ACL policy rules in Tailscale, this allows any tailnet devices that the operator spins up to be identified as belonging to your Tailnet.

Login to your Tailscale admin section –> Access Controls –> Tags

You’ll need to define 2 tags: k8s-operator and k8s.
Make sure k8s-operator is set as an owner of k8s

If you open the JSON editor tab, the equivalent in JSON format should be this:

{
  "tagOwners": {
	"tag:k8s-operator": [],
	"tag:k8s": ["tag:k8s-operator"]
  }
}

Your operator needs an OAuth client to authenticate to your Tailnet and register new pods and services.

Navigate to the admin console’s Settings –> Trust credentials –> Add Credential :

Choose OAuth, give your auth key a name, click continue.

Give Read+Write permissions under the “Devices” section.

Deploy the Keys section and add Read+Write permissions to the Auth Keys parameters.

Select the tag:k8s-operator tag created earlier from the dropdown to ensure devices created by the operator will have the tag assigned.

Once you click “Generate credential”, a pop-up window displays the key. Make sure to copy it in a safe place as it won’t be shown again.

• Add the helm repository

# Add the repository
$ helm repo add tailscale https://pkgs.tailscale.com/helmcharts

# Update your client’s package list
$ helm repo update

• Run the helm install command to install the tailscale-operator

$ helm upgrade --install tailscale-operator tailscale/tailscale-operator \
  --namespace=tailscale \
  --create-namespace \
  --set-string oauth.clientId=<oauth_client_id> \
  --set-string oauth.clientSecret=<oauth_client_secret> \
  --wait

Replace the <oauth_client_id> and <oauth_client_secret> placeholders with the actual values you copied down at the end of the previous step.

• After a few seconds, check that the operator’s pod is up:

rancher:~ # kubectl get pods -n tailscale
NAME                        READY   STATUS    RESTARTS       AGE
operator-7699b5555b-v6wgw   1/1     Running   20 (16h ago)   55d

• Log into your Tailscale account and you should see the operator as a machine:

For the purpose of this article, we’ll keep things simple: we’re just going to walk through a basic example that exposes an nginx service in your tailnet.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: nginx
  labels:
    app: nginx
spec:
  replicas: 1
  selector:
    matchLabels:
      app: nginx
  template:
    metadata:
      labels:
        app: nginx
    spec:
      containers:
        - name: nginx
          image: nginx:latest
          ports:
            - containerPort: 80

---

apiVersion: v1
kind: Service
metadata:
  name: nginx
  annotations:
    tailscale.com/expose: "true"
spec:
  selector:
    app: nginx
  ports:
    - port: 80
      targetPort: 80

Take a closer look at the Service part of the YAML file. The key element here is the tailscale.com/expose annotation, sitting under the service’s metadata.annotations field.

That single annotation is all it takes to automatically expose the service in your Tailnet.
Once you apply it to your cluster, the Tailscale Operator picks it up on its own and takes care of setting up the appropriate route behind the scenes.

Set it to “true” if you want to expose the service and to “false” if not, it’s that easy!

Log back into your Tailscale account, you should see the service as a new device and your nginx pod should be exposed and reachable via its Tailscale IP address !

That’s it.
From now on, whatever you deploy in your Kubernetes cluster, and as long as it has the right annotation, will be detected by the Tailscale operator and exposed to your tailnet.

Cool, no?

L’article Expose your Kubernetes pods to your Tailnet using Tailscale operator est apparu en premier sur dbi Blog.

Since the beginning of the year, I had the opportunity to upgrade many GoldenGate setups to 26ai. On two of them, I was facing an issue regarding purge tasks that I could not solve. Since it took quite some time to finally find a solution, I figured it was worth documenting.

The client had two GoldenGate 26ai installations running on the same server.

• One extracting data from DB2, with GoldenGate for DB2/zOS.
• One replicating into an Oracle 19c database, reading from the same trail files.

Both setups were using the initial release of GoldenGate 26ai.

On both, I tried to set up trail purge tasks from the Administration Service web UI, and both GoldenGate trail purge failed with the same error.

To add a purge task, go to the Administration Service web UI, and on the left navigation panel, click on Tasks > Purge Trail.

From there, create a new task. You will be asked to provide a task name, one or multiple trail paths, retention rules, and whether to use checkpoints when purging trail files.

In my case, right after the creation, the purge task was failing with the OGG-12111 error. This happened no matter how I configured the task. The task was correctly configured, but it would fail every time it was scheduled to run

The full error displayed was the following:

JSON element '/csn' does not match any schemas
Code: OGG-12111
Cause: The JSON item does not validate against the provided JSON schema.
Action: Correct the JSON so that it conforms to the JSON schema.

I first thought that it was a configuration issue on my side, but there is no such csn field when configuring the task. I quickly realized that if something was wrong, it was internal to GoldenGate. And unfortunately, all trail files were affected in this setup, so there was no working example to investigate further. On all other setups that I worked on, I never faced the OGG-12111 error.

Digging into the Administration Service log, I found the real error underneath:

2026-04-20T14:18:41.377+0200 ERROR| ERROR   OGG-01072  Oracle GoldenGate Administration Service for Oracle:  NUMCNV_getUnsignedNumFromStr: Buffer overflow, needed: 21, allocated: 20. (Purge)

OGG-01072 is a buffer overflow in an internal GoldenGate method. It looked like a corrupted value gets embedded into the purge task JSON, and the JSON fails schema validation. And in the end, you get the OGG-12111 error in the web UI.

OGG-12111 was just the symptom, while OGG-01072 was the actual bug.

Unfortunately, I couldn’t find any MOS bug filed for this. However, by trial and error, I discovered that the fix appears to have been introduced silently in a later patch, maybe through another bug resolution (that I could not identify).

The only solution I found was patching GoldenGate to 23.26.2.0.1. I didn’t test the intermediate patch levels between 23.26.1.0.0 and 23.26.2.0.1, so I can’t say exactly which patch introduced the fix, but upgrading to 23.26.2.0.1 resolved the issue.

After patching, I went back to the Purge Trail section of the web UI, and the existing purge tasks were already working. I did not even have to recreate them.

So if you face this bug and your GoldenGate trail purge fails, don’t spend time reviewing the purge task parameters. Your tasks are most probably fine. The bug is inside the Administration Service’s numeric conversion method, and patching is the only way out.

L’article GoldenGate Trail Purge Task Fails with “OGG-12111: JSON element ‘/csn’ does not match any schemas” est apparu en premier sur dbi Blog.

In the previous post, I walked through the query side of the dashboard JSON: objectType, class, filters, date tokens. What I deliberately left out was the aggregation block, because it deserves its own post.

The aggregation is what transforms the set of matching objects into a value the widget can render. There are five aggregation types, six reducers, and one optional second dimension (seriesProperty). Together, they cover every widget shape from a single KPI number to a cross-tab pivot table.

The aggregation has two complementary components:

• The aggregation type is the shape of the result: one value (summary), one value per group (groupByProperty), one value per time bucket (groupByDateBucket), one value per admin-defined range (groupByRange), or a simple list of objects (list).
• The aggregation reducer is the operation applied within each group (or over the whole matching set for summary): count (default), sum, avg, min, max, median. Note: The reducer is ignored for list.

In other words, type decides whether you get a number, a chart, or a table; reducer decides what that number, bar, or cell actually measures.

summary reduces the entire matching set into a single value. By default it counts the matching objects. However, with a different reducer, you can make it compute a property aggregate in different ways. Here are a few example of summary aggregations:

// Total count of matching objects
"aggregation": { "type": "summary" }

// Total revenue of matching objects
// (sum all values from the "Amount" property)
"aggregation": {
  "type": "summary",
  "reducer": "sum",
  "reducerProperty": "Amount"
}

// Latest contract expiry date
// (display only the date with the highest value - drill-through still list all objects)
"aggregation": {
  "type": "summary",
  "reducer": "max",
  "reducerProperty": "Effective through"
}

The widgets that accept summary are kpiNumber, gauge, and table. If you followed this series, you probably saw a bunch of the first two, already. The last one, in this case, will render the value as a one-row table. For all other chart widgets (donut, bar, line, area), summary does not really make visual sense, as a single value cannot be plotted on an X-Y axis.

groupByProperty groups objects by the distinct values of a property (the propertyName) and applies the reducer to each group independently. Here are two examples:

// Count of matching objects per Agreement type
"aggregation": {
  "type": "groupByProperty",
  "propertyName": "Agreement type",
  "includeEmptyResults": "No"
}

// Total revenue of matching objects per Customer
// (sum all values from the "Amount" property for each Customer independently)
"aggregation": {
  "type": "groupByProperty",
  "propertyName": "Customer",
  "reducer": "sum",
  "reducerProperty": "Amount"
}

The supported fields are the following ones:

The widgets that accept groupByProperty are donut, bar, line, area and table. Basically, all charts widgets, plus the table which is kind of a Swiss knife, that works with everything.

It is very similar to the groupByProperty, but the target must be a date / timestamp. Basically, something that contains a date, whether its only the date or a full date-time is fine. Because dates would probably be a bit too wide, there is a concept of buckets, to group dates by a pre-defined range that might make sense. The default time periods are day, week, month, quarter or year. Here are two examples:

// Count of contracts expiring per month
"aggregation": {
  "type": "groupByDateBucket",
  "propertyName": "Effective through",
  "bucketSize": "month",
  "includeEmptyResults": "Yes"
}

// Sum of invoice amounts per quarter
"aggregation": {
  "type": "groupByDateBucket",
  "propertyName": "Invoice date",
  "bucketSize": "quarter",
  "reducer": "sum",
  "reducerProperty": "Amount"
}

The supported fields are pretty similar to the groupByProperty:

Note: you would usually combine groupByDateBucket with a date filter to limit the range of results. Without filters, the chart would show everything from the earliest object in the vault to the most recent, possibly spanning decades. If that’s what you want to see, then that’s absolutely fine. However, most of the time, a filter like between @startOfYear and @endOfYear for an annual trend chart might be more appropriate.

This one is also very similar to the last two groupings. With the main difference that this is the only one that allows you to define the exact range.

If you have a certain property that has a high cardinality, it might be difficult to display it with a groupByProperty. Let’s take for example the “Amount” property from before. When you want to apply math on it (sum/avg/min/max/median), then that’s fine because it only returns one value. But if you want to see the revenue themselves, without a prior grouping on something else (e.g. above we first group by “Customer”), then you would end-up with dozens/hundreds/thousands of groups? That’s where groupByRange shines, because you define the grouping (e.g. 0-1’000, 1’000-5’000, >=5’000).

You can use that grouping method with numeric / time / text / lookup properties. On the other hand, date / timestamp and boolean aren’t supported. The reason for that is simple: there is already groupByDateBucket for date / timestamp, and boolean can only have 2 values (Yes / No), so groupByProperty works just fine.

// Invoices by specific range
// 3 ranges: 0-1000, 1000-5000, 5000-10000
"aggregation": {
  "type": "groupByRange",
  "propertyName": "Amount",
  "boundaries": ["0", "1000", "5000", "10000"]
}

// Invoices by specific range, with open ranges
// 4 ranges: 0-1000, 1000-5000, 5000-10000, >=10000
"aggregation": {
  "type": "groupByRange",
  "propertyName": "Amount",
  "boundaries": ["*", "1000", "5000", "10000", "*"]
}

// Efficiency / speed of processing / duration of some actions / etc...
// 4 ranges: 0-30s, 30s-1min, 1min-2min, >=2min
"aggregation": {
  "type": "groupByRange",
  "propertyName": "Duration",
  "boundaries": ["*", "00:00:30", "00:01:00", "00:02:00", "*"]
}

// Customers by country name
// 3 ranges: A-F, F-M, >=M
// e.g. "France" in 2nd group, Switzerland in 3rd group
"aggregation": {
  "type": "groupByRange",
  "propertyName": "Country",
  "boundaries": ["A", "F", "M", "*"],
  "reducer": "count"
}

Again, the supported fields are fairly similar to the groupByProperty:

Each boundary value marks the inclusive lower bound of a range and the exclusive upper bound of the range below it, except for the last range, which is fully inclusive. For example, ["0", "1000", "5000", "10000"] creates three buckets: [0, 1000), [1000, 5000) and [5000, 10000]. This means that a value of “1000” will end-up on the 2nd bucket only. A value of “5000” or “10000” will end-up on the 3rd bucket.

In addition, as you can see in the examples above, you can use a wildcard ("*") on either end: a leading "*" creates a bucket for everything below the first boundary, and a trailing "*" creates a bucket for everything at or above the last boundary.

list are pretty similar to search results from M-Files, in the sense that it will just list something, with pre-defined columns. When using this aggregation type, the reducers have no effect, because it only lists objects but do not apply any modifications / computing on them.

// List all contracts with 3 specific columns
"aggregation": {
  "type": "list",
  "displayProperties": ["Agreement type", "Effective through", "Responsible person"]
}

For simple lists, the objects will be pre-sorted alphabetically but the user is then able to re-sort them, by each of the columns displayed. When drillThroughEnabled is set to “Yes”, then table rows become clickable and allows navigation to the object in question. There is no modal/drill-through in this case, since the table list already display the target object (no grouping).

As mentioned, all five aggregation types, except list, accept a reducer. You probably understand them already, but just as a quick table:

When min or max is applied to a date, timestamp, or time property, the reducer returns the value and it will be formatted for end-users based on their localization / regional settings: e.g. DD/MM/YYYY for dates, DD/MM/YYYY HH:mm for timestamps (minute precision), and HH:mm:ss for times.

When sum, avg or median is applied to a time property, it allows you to compute a total (or average/median) duration for a certain activity. This might be useful if you have time-constraints.

Three widgets can render date/time values: kpiNumber (a single big number), gauge (switch to date mode, covered in Post 4a) and table (simple display in rows).

The series details were already covered a bit in the posts 4b and 4c. But, this is the feature that turns a single-series widget into a multi-actor comparison. When you set a seriesProperty on a groupBy aggregation, the engine will automatically produce one series per distinct value of the series property. This allows a two-dimensional comparison.

The behavior per widget type is the following:

• line / area – one colored line per series, with an auto-generated legend.
• bar – one colored sub-bar per series (display.barLayout controls stacked vs grouped).
• donut – a multi-mini-pie grid, one donut per series, with a shared legend.
• table – a cross-tab pivot, one column per series.

As a reminder, kpiNumber and gauge ignore seriesProperty, since they only display single-values.

I repeated this in every widget post but it is worth restating: seriesProperty should be low-cardinality. A property with five distinct values produces a readable multi-series chart. A property with two hundred values produces a colored mess. In that case, you might want to use a groupByRange, to reduce the amount of groups and therefore the series.

In single-series mode, includeEmptyResults: “Yes” fills empty time buckets with zero values. In multi-series mode, it does the same across both dimensions: every series gets a zero in any bucket where it has no data. This avoids broken lines and visually confusing gaps.

The behavior also adds a (none) bucket for objects whose primary group property has no value, and a (none) series for objects whose series property has no value. These are appended at the end so it doesn’t disrupt the “main story”.

A subtle but important case: as you probably know, M-Files has a multi-select lookup property type. These allow the selection of multiple pre-defined values. Because of that, objects with multiple values will end-up in multiple buckets/groups, with the “counted once per value” rule.

Example: a customer with office locations in Geneva, Zurich and Berlin could appear three times, once for a Geneva bucket, once for a Zurich bucket and finally once for a Berlin bucket. The total of all bucket counts can therefore exceed the total number of objects when some objects have multiple values.

This is the correct and expected behavior for multi-select lookups. The alternative (counting each object only in its first value) would silently hide the multi-value relationships that often matter most.

I mentioned displayProperties in section 2.5 above, as optional column to be added for list aggregations. The same field has a second role for all other aggregation type (summary, groupByProperty, groupByDateBucket, groupByRange): it controls the columns in the drill-through modal.

"aggregation": {
  "type": "groupByProperty",
  "propertyName": "Agreement type",
  "displayProperties": ["Effective through", "Responsible person"]
}

When the user clicks a donut slice (or a bar, or a row in a count table), the drill-through modal shows one row per object in that group, with the object name plus all the optional columns defined in the displayProperties value (e.g. Effective through and Responsible person).

I already put this table at the end of Post 4c, but it is short enough, so:

Combine this with the reducer table in section 3 and you have the full answer to “can I use aggregation X with widget Y, and with reducer Z on property type T”. In any case, the Visual Designer and the validation process will prevent you to make any mistake.

The query side (Post 5) plus the aggregation side (this post) together cover everything the engine supports. If that wasn’t the case before, you should now be able to read and understand every line from any of the previous JSON example.

The combinations are richer than they look at first. A groupByDateBucket on monthly invoices with a sum reducer on Amount and a seriesProperty on Customer, rendered as a line widget with includeEmptyResults: “Yes”, gives a multi-customer revenue trend that takes about 15 lines of JSON. A groupByProperty on Agreement type with a seriesProperty on Workflow state rendered as a table gives a cross-tab pivot with drill-through on every cell.

The possibilities aren’t endless, obviously, but good luck if you would like to try them all… Last time I checked, you could create several million different widget combinations. An important part of that would trigger warnings or errors for non-supported cases, but still a considerable scale.

So far, I covered the end-user part, the JSON, the widgets and now the queries. These are the building blocks of dashboards. The remaining posts of the series will cover:

• Post 7 – The Admin tab: the actions, Visual Designer / JSON editor, the two-stage validator, import / export, access control.
• Post 8 – Building a dashboard with the Visual Designer.

Want to know more about this Business Dashboard? Contact us and we will be happy to showcase it on M-Files.

L’article M-Files BD – Aggregations, reducers and series est apparu en premier sur dbi Blog.

In the previous posts of this series, I covered the anatomy of a dashboard definition and the seven widget types (KPI & gauge, line & area, donut, bar & table) the engine supports, as of now. In this one, we will go through the Business Dashboard queries, i.e. the query section that I pretty much ignores so far. It is now time to look at it a bit more.

A query has four parts: objectType, class, filters, and aggregation. This post covers the first three. The fourth (aggregations, including its reducers) is the topic of the next post.

As mentioned before, the principle that drove the design of the Business Dashboard is to be generic. The engine never enumerates specific business values, it only translates the structured JSON query into a standard M-Files server search.

Every query starts with objectType. It is the M-Files object type the engine searches against, addressed by its display name (the singular form is fine, the engine resolves it and uses it).

The simplest form is a string:

"objectType": "Document"

Or any other object type defined in your vault:

// Looking for Customers
"objectType": "Customer"

// Looking for Projects
"objectType": "Project"

// Looking for Employees
"objectType": "Employee"

The display name is case-insensitive but must otherwise match what the vault shows. Please note that, if the vault is let’s say, in German, you might need to use “Dokument” and not “Document”. In short, you need to use what the vault defines/shows.

If the name does not match, the widget shows a clear ✗ Object type ‘xxx’ not found in this vault error.

When the same logical entity exists across more than one M-Files object type (for example, a “Proposal” can be both a Document and a Document collection), you can pass an array:

"objectType": ["Document", "Document collection"]

The engine resolves every name and builds a single search to find all matching objects. This behaves like the “is one of” filter in M-Files Advanced Search. Please note that the Drill-through will also inherit the same scope, automatically.

A small but important detail: even in this case, the engine executes a single search. This matters for performance and for consistency (a single serverScanMaxResults cap applies, not two independent caps).

The class field is optional but strongly recommended. It restricts the query to one specific class:

"objectType": "Document",
"class": "Contract or Agreement"

Querying “all Documents” in a vault that contains 50’000 documents of which only 1’000 are Contracts is pure waste. Adding “class”: “Contract or Agreement” narrows the scan immediately, which both improves performance and makes the widget result more meaningful.

Therefore, both M-Files Administrators and M-Files Users will thank you for selecting the right class to use. It avoids slowness, irrelevant results and reduces resource usage. You should always set a class, when possible.

When objectType is an array and class is specified, the engine will find any object, from that class, from any of the object types listed. So the following will match Proposal-class objects from both types:

"objectType": ["Document", "Document collection"],
"class": "Proposal"

If the class is misspelled, the widget shows a ✗ Class ‘xxx’ not found on the specified object type(s) error. As with object types, the name depends on what the vault defines/shows.

The filters array, which is optional, contains zero or more conditions. If you specify multiple filters, then all filters must match. That means that M-Files applies a AND semantic between each filter. By the way, M-Files doesn’t directly support “OR” conditions, it always joins them. The only “kind-of-an-exception”, as far as I know, is the “is one of” where you can set multiple values from the Lookup/MultiSelectLookup property values. But that’s only a single condition, not multiple conditions.

Here is an example of a filter:

"filters": [
  { "property": "Effective through", "operator": "greaterOrEqual",
    "value": "@today", "valueType": "dateToken" },
  { "property": "Agreement type", "operator": "equals",
    "value": "Subcontracting Agreement" }
]

Each filter has up to four fields:

• property: the property display name on which to apply the operator / value.
• operator: one of the operators listed in section 4 below.
• value: the value to use for the comparison with the property’s actual value. For the four “presence” operators (isEmpty, isNotEmpty, isPresentEmpty, isPresentNotEmpty), this parameter should NOT be defined. For the “begin-end” operators (between / notBetween, this parameter should be an array of two elements. For the “is one of” operators (inList / notInList), this parameter should be an array of at least two elements. Finally, in all other cases, it’s simply a string.
• valueType: either “literal” (default, it means to use the value as-is) or a “dateToken” (covered in section 5).

The engine supports a rich set of operators across two execution paths:

• Native: runs server-side before the serverScanMaxResults cap is applied. Therefore, this is the preferred option, when possible.
• Post-filter: runs in memory after the server returns up to serverScanMaxResults objects. Therefore, this is correct and useful when you want to do something that M-Files doesn’t support out-of-the-box but that still makes sense for your Business use-case.

In short, whenever possible, prefer to apply a native filter. The main reason for this is simple. Let’s assume that you have 1’000 Contracts in the vault:

• If you apply a native filter, for example Effective through >= @today, then if only 200 of them match that criteria, then M-Files will only return these 200 objects directly. There is no loss of performance here.
• If you apply a post-filter, for example Effective through contains 2026-12, then M-Files will have no other choice than returning all 1’000 Contracts first. On top of that, the post-filter compares the value of Effective through and checks whether it contains “2026-12”. This is because the “contains” condition doesn’t exist in M-Files for Date properties. Therefore, we cannot use what doesn’t exist, but we still want to provide that possibility / logic, and therefore it is done in memory after returning all results. In that example, you get 5x more results and on top of it, you also need to check which ones match the expected value.

The performance difference between native and post-filter is imperceptible for small result sets (<few hundreds), but you would probably feel it if you expect to fetch thousands of results. A great catch, if you need to apply a post-filter for a valid business reasons, is to first apply a native one, which highly reduces the result set, and then apply the post-filter one that you need. Also, don’t forget to specify a class!

Then, let’s proceed with a deep-dive on the different operators (if you only want the “summary”, look at the end of section 4 for the cheat sheet). The 22 operators support all data types, without exceptions, contrary to M-Files operators which offer much less capabilities. The only distinction is, as mentioned above, whether the operator is native or post-filter.

As you would expect, these are for exact match. Only the MultiLineText properties are processed as post-filter.

{ "property": "Agreement type", "operator": "equals",
  "value": "Subcontracting Agreement" }

{ "property": "Customer", "operator": "notEqual",
  "value": "ESTT Corporation (IT)" }

When using display names on Lookup properties (Lookup or MultiSelectLookup), as its the case in above example, the value lists are cached, so that each execution doesn’t need to re-fetch the things that it already knows of, and it can just use it directly, to ask M-Files what’s the updated count.

These “range” operators support all property types except Boolean, because it doesn’t make any sense to apply a “lessThan” to a Boolean… In addition, and similarly to above, only the MultiLineText properties are processed as post-filter, everything else is native. Obviously, you can apply these operators on Date and Numeric values, but it also works with Text, Lookup or MultiSelectLookup. When it needs to work on Text-based properties, it does a lexicographic comparison (e.g. ABC < ABD, ACE > ABB).

{ "property": "Due date", "operator": "lessThan",
  "value": "@today", "valueType": "dateToken" }

{ "property": "Amount", "operator": "greaterOrEqual", "value": 1000 }

These as also “range” operators but I put them separately because the value must be a two-element array, as previously written. between is, otherwise, exactly the same as lessThan/greaterThan/lessOrEqual/greaterOrEqual. In the array, you would provide the begin and the end of the range. to fetch.

{ "property": "Effective through", "operator": "between",
  "value": ["@startOfYear", "@endOfYear"], "valueType": "dateToken" }

{ "property": "Amount", "operator": "between", "value": [100, 1000] }

notBetween is the opposite of between, obviously, but it’s a bit more than that… As previously mentioned, M-Files does NOT handle OR logic. But if you think about it, a “notBetween” is actually an OR, because you want values below the range OR above the range (value < low OR value > high). Because of that, notBetween is always a post-filter, without exception.

{ "property": "Amount", "operator": "notBetween", "value": [100, 1000] }

These four operators test whether a property has a value, as the name suggests… However, there is a catch: M-Files only distinguish between “the property is present and empty” or “the property is present and non-empty”.

You might have faced that with Templates for example. That’s a pretty common occurrence. In M-Files, there is a parameter “Is template” which indicates whether a specific object has been defined as a template. But that parameter is only present in a few select objects, it’s usualy not present on all of them. Therefore, if you search for “Is template – is empty”, you will most probably find 0 results, because M-Files only check for documents where the property is present and where it is empty.

The distinction is simple but it matters a lot. That’s why in the Business Dashboard, the native M-Files “is empty” / “is not empty” have been renamed as isPresentEmpty and isPresentNotEmpty. These are the fully native option from M-Files, and therefore they run on the server-side.

{ "property": "Effective through", "operator": "isPresentEmpty" }

{ "property": "Responsible person", "operator": "isPresentNotEmpty" }

In addition, I also defined two other operators, isEmpty and isNotEmpty. These two are always post-filters, they scan all matching objects in memory after the server returns them:

{ "property": "Effective through", "operator": "isEmpty" }

{ "property": "Responsible person", "operator": "isNotEmpty" }

Because of that post-processing, isEmpty matches objects where the property is absent OR where it is present but empty. This allows a more “complete” result set, which might be required in some specific business use-cases.

isNotEmpty matches objects where the property is present AND non-empty. You might think it is exactly the same as isPresentNotEmpty, right? Well, in 99% of the cases, yes it is the same (but slower, since done as post-filter)… Except when there are bugs in M-Files ;). While developing the Business Dashboard, I found a few bugs, including one with lexicographic processing on MultiSelectLookup properties. The usage of my own operator was giving me a slightly different result and while investigating why, I found the reason and the bug in M-Files’s own operator.

In summary:

As the name suggests, if you are looking for multiple values, inList is what you should use. It must be an array of at least 2 elements, but you can put 50 if you want to. M-Files only support Lookup for this one, natively. Therefore, all other property types are handled as post-filters.

{ "property": "Agreement type", "operator": "inList",
  "value": ["Subcontracting Agreement", "Project Agreement"] }

{ "property": "Workflow state", "operator": "inList",
  "value": ["In review"] }

notInList is simply the opposite, so it just excludes all elements provided. It supports exactly the same thing as inList and works in the same way too.

{ "property": "Workflow state", "operator": "notInList",
  "value": ["Approved"] }

A subtle catch with “not” operators (notInList and similar): an object whose Lookup property is present but has no item selected satisfies “not in list” by default, because there is no value to match or compare with. If you don’t want to see these objects, then you can simply add a second filter on that same property with isPresentNotEmpty!

contains is a substring match (similar to matchesWildcardPattern (c.f. below) with implicit wildcards on both sides), it finds objects where the property value includes the given string anywhere inside.

{ "property": "Title", "operator": "contains", "value": "NDA" }

{ "property": "Title", "operator": "doesNotContain",
  "value": "draft" }

For Text, MultiLineText and Lookup properties, both operators are native. The rest is supported as post-filters, comparing against the string representation.

startsWith and doesNotStartWith match objects based on the beginning of a property’s value. These are native only for Text and Lookup properties.

{ "property": "Name or title", "operator": "startsWith",
  "value": "PO-"    }

{ "property": "Name or title", "operator": "doesNotStartWith",
  "value": "DRAFT-" }

endsWith and doesNotEndWith match based on the end of a property’s value. This doesn’t exist in M-Files, there is no equivalent and therefore, these are always post-filters for all property types.

{ "property": "Name or title", "operator": "endsWith",
  "value": "(final)" }

{ "property": "Name or title", "operator": "doesNotEndWith",
  "value": "(draft)" }

These are the most expressive pattern operators. The value is a regex string where \* matches any number of characters and ? matches exactly one character. This is pretty similar to the contains/startsWith/endsWith (and their opposite), obviously, but it is a bit more powerful if you need to match an exact pattern that you know of.

{ "property": "Title", "operator": "matchesWildcardPattern",
  "value": "PO-????-2026" }

{ "property": "Title", "operator": "doesNotMatchWildcardPattern",
  "value": "ID-*" }

You reached this point, so it’s time to have a cheat sheet of all operators. In the below table:

• Text and MultiLineText are NOT interchangeable
• “Lookup” includes both Lookup and MultiSelectLookup (these ARE interchangeable – one exception for between on MultiSelectLookup because of a bug in M-Files – the one I mentioned above)
• “Date” includes Date, Time and Timestamp (these ARE interchangeable – one exception for notEqual on Timestamp because of a bug in M-Files – not the same as the one mentioned above)
• “Numeric” includes Integer, Integer64 and Floating (these ARE interchangeable) (“integer”/”real”)

All pattern operators (contains, startsWith, endsWith, matchesWildcardPattern and their negations) work on date properties via the post-filter path, comparing against the ISO yyyy-MM-dd string representation (or yyyy-MM-dd HH:mm for timestamp or HH:mm:ss for time properties). This opens up some convenient patterns, even with “valueType”: “literal”:

{ "property": "Effective through", "operator": "startsWith",
  "value": "2026" }

{ "property": "Effective through", "operator": "startsWith",
  "value": "2026-04" }

{ "property": "Effective through", "operator": "endsWith",
  "value": "-31" }

{ "property": "Effective through", "operator": "matchesWildcardPattern",
  "value": "2026-??-??" }

And with “valueType”: “dateToken”, the token resolves to a full ISO date used as the exact string pattern:

{ "property": "Effective through", "operator": "contains",
  "value": "@today", "valueType": "dateToken" }

{ "property": "Effective through", "operator": "startsWith",
  "value": "@startOfMonth+5d", "valueType": "dateToken" }

A resolved token produces a full ISO date for the operators, c.f. next section for a deeper dive into dateToken details.

Hardcoding a date like “2026-01-01” in a filter works, but it ages badly. The dashboard built today shows different data on January 2nd than it did on December 31st, because the filter is now relative to a different “today”.

Date tokens solve this. Any string value used with “valueType”: “dateToken” is resolved to an absolute date at query execution time, so the dashboard stays meaningful as time passes.

The following anchors are recognized:

So @startOfYear, at the time of writing this blog (i.e. in 2026) is 2026-01-01 00:00:00. Starting from January 1st 2027, the same token will automatically resolve to 2027-01-01 00:00:00 instead. Therefore, the dashboard rolls forward automatically.

If there is a need to add more date tokens, it’s always possible.

Any anchor presented above can be followed by one or more offsets to add or subtract time:

Each offset uses d for days, h for hours, m for minutes, and s for seconds. As you can see above, offsets can be chained together in any order, so @today+2d+10h+30m is valid, as is @now-8h+30m.

The day offset is calendar days, not business days. There is no built-in concept of holidays in the engine as of now.

A literal ISO date works as both literal as well as a date token. However, if you want to apply an offset, then only dateToken can be used. As previously mentioned, “literal” really means a literal strings, so there is no computation done on it.

{ "property": "Effective through", "operator": "greaterOrEqual",
  "value": "2026-01-01+90d", "valueType": "dateToken" }

This resolves to 2026-04-01 00:00:00, at query time. This can be useful when you want a stable absolute anchor but with an offset relative to it.

The key difference between @now and @today is that @now captures the current time including hours, minutes, and seconds, while @today is truncated to midnight.

Use @today for day-level filters (most common): “contracts expiring within 30 days”, “documents created this month”. Use @now when you need intra-day precision: “events logged in the last 2 hours”, “tasks to process in next 4 hours”. Examples:

// All activity since this morning at 7am (a fixed time each day)
{ "property": "Modified", "operator": "greaterOrEqual",
  "value": "@today+7h", "valueType": "dateToken" }

// Flagged tasks created in the last 8 hours
{ "property": "Created", "operator": "greaterOrEqual",
  "value": "@now-8h", "valueType": "dateToken" }

For boolean-typed properties (M-Files “Boolean (yes/no)”), the engine accepts both “Yes” / “No” and “True” / “False” (case-insensitive):

{ "property": "Accepted", "operator": "equals",
  "value": "Yes" }

{ "property": "Accepted", "operator": "equals",
  "value": "True" }

“Yes” / “No” is the preferred form because that is what M-Files displays in the UI and what users see in their property cards. Worth noting: the M-Files Admin label “Boolean (yes/no)” is always English even on a localized vault. So you do not need “Ja” / “Nein” mappings on a German vault (at least as of today).

The query model covers the questions that come up in practice. objectType and class define the scope, filters narrow it with a rich set of operators spanning native server conditions and post-filter cases. Date tokens make filters relative without effort.

What is not in this post: the aggregation block. That is the topic of Post 6, which covers the aggregation types, the reducers, and the multi-series seriesProperty feature. Once those are explained, every JSON block in the widget posts will make complete sense.

Want to know more about this Business Dashboard? Contact us and we will be happy to showcase it on M-Files.

L’article M-Files BD – Queries: objectType, class, filters, date tokens est apparu en premier sur dbi Blog.

To administer an Oracle Database Appliance (ODA), you probably use the odacli commandline tool.

The principle of this tool is to run jobs in background. Fire-and-forget… or Fire-and-poll

odacli update-repository -f "/tmp/odacli-dcs-19.30.0.0.0-260210-GI-19.30.0.0.zip"

This command returns a jobId (e.g. f1338963-87a2-4cb9-8a3e-06e104270203) and the job is executed in background. To see if this job is (sucessfully) completed, you have to poll for the above jobId and wait for Status: Success.

odacli describe-job -i f1338963-87a2-4cb9-8a3e-06e104270203
watch -n 2 odacli describe-job -i f1338963-87a2-4cb9-8a3e-06e104270203

For automation, e.g. with Ansible, that is not optimal. We have to implement a polling mechanism for the odacli commands.

For example. we will add new software to the ODA-repository. (version=19.30, software_zip=/tmp/odacli-dcs-19.30.0.0.0-260210-GI-19.30.0.0.zip)

First of all, we run odacli to create the job to add the software to the repository and extract the jobId from the output.

  - name: import software in repository
    ansible.builtin.shell: |
      /opt/oracle/dcs/bin/odacli describe-dbsystem-image -j \
        | jq -e '.[].dbSystemImageComponents[]|select(.componentName=="DB")|.availableVersions[]|select(startswith("{{version2}}")) ' \
        |grep  {{version2}} >&2 && echo 'ALREADY_INSTALLED' && exit 0
      /opt/oracle/dcs/bin/odacli update-repository -f "{{software_zip}}"
    register: repo
    changed_when: "'ALREADY_INSTALLED' not in repo.stdout"

  - name: set job-id
    set_fact:
      jobid: "{{ (repo.stdout | from_json).jobId }}
    when: "'ALREADY_INSTALLED' not in repo.stdout"

Hint: the 1st command is to check if this software is already imported

Now, we can poll the job until it is completed. Ansible is optimized to work with json. So we will enforce odacli to return the output in json format (-j):

/opt/oracle/dcs/bin/odacli describe-job -i f1338963-87a2-4cb9-8a3e-06e104270203 -j
{
  "jobId" : "f1338963-87a2-4cb9-8a3e-06e104270203",
  "status" : "Created",
  "message" : "/tmp/odacli-dcs-19.30.0.0.0-260210-GI-19.30.0.0.zip",
  "reports" : [ ],
  "createTimestamp" : "February 24, 2026 13:58:30 PM CET",
  "resourceList" : [ ],
  "description" : "Repository Update",
  "updatedTime" : "February 24, 2026 13:58:30 PM CET",
  "jobType" : null,
  "cpsMetadata" : null
}

For polling, we can use the ansible loop control, see the Ansible documentatioon

  - name: check until job completed
    ansible.builtin.shell: /opt/oracle/dcs/bin/odacli describe-job -j -i {{jobid}}
    register: check_status
    until: "(check_status.stdout|from_json).status == 'Success'"
    retries: 10
    delay: 8
    changed_when: false
    when: "'ALREADY_INSTALLED' not in repo.stdout"

That means, Ansible will run the command every 8 seconds until status Success is returned (Success) or after 10 attemts (Failed)

TASK [check until job completed] ************************************************************************
FAILED - RETRYING: check until job completed (10 retries left).
FAILED - RETRYING: check until job completed (9 retries left).
FAILED - RETRYING: check until job completed (8 retries left).
FAILED - RETRYING: check until job completed (7 retries left).
FAILED - RETRYING: check until job completed (6 retries left).
FAILED - RETRYING: check until job completed (5 retries left).
FAILED - RETRYING: check until job completed (4 retries left).
ok: [server01]

For re-usablility, I recommend to move the job-polling to a role, so that it can be used for all asynchronous jobs

# roles/odacli_job/tasks/main.yml
  - name: set job-id
    set_fact:
      jobid: "{{ (job_stdout | from_json).jobId |default('') }}"
    when: job_stdout|default('') != ''
  - debug: var=jobid

  - name: check until repo job completed
    ansible.builtin.shell: /opt/oracle/dcs/bin/odacli describe-job -j -i {{jobid}}
    register: check_status
    failed_when: false
    until: "(check_status.stdout|from_json).status == 'Success' or (check_status.stdout|from_json).status == 'Failure'"
    retries: "{{retries}}"
    delay:   "{{delay}}"
    changed_when: false

The role can be used as follows:

  - name: include role to poll job
    include_role:
      role: odacli_job
    when: "'ALREADY_INSTALLED' not in repo.stdout"
    vars:
      job_stdout: repo.stdout 
      retries: 50
      delay: 3

The job to poll can be specified by variable “jobid”, or you can provide the json-output of launching the job via “job_stdout”, then the role extracts the jobid

After the import of the software (you will see it in /opt/oracle/oak/pkgrepos/orapkgs/clones/), you can now deploy an ORACLE_HOME with it. For that, we can use the same role to poll this asynchronous job:

  - name: create database home
    ansible.builtin.shell: |
      /opt/oracle/dcs/bin/odacli create-dbhome -j -v {{version}}
    register: home

  - name: include role to poll job
    include_role:
      role: odacli_job
    vars:
      job_stdout: home.stdout
      retries: 80
      delay: 10

What is not done in the role is the error-handling. It is up to you to define an adequate error-handling. You will get the result of the asynchronous job in the variable check_status.stdout which is of json format.

(check_status.stdout|from_json).status

If the status is

• “Success”, it is OK
• “Failure”, it is not OK
• Any other value (e.g. “Created”), you got a timeout (>retries*timeout sec.), then the result it is unknown (maybe the job completes at a later time, sucessful or not).

L’article Administer Oracle Database Appliance (ODA) with odacli and Ansible est apparu en premier sur dbi Blog.

We have been facing one issue at one of our customer where the Oracle Client connections remained opened for days blocking some avaloq JobNetz. We have been doing some tests and we could fortunately find a solution resolving the problem thanks to Oracle Database 26ai supporting now SQLNET.EXPIRE_TIME on the client side. Through this blog, I would like to share with you the problem and then the tests that have been performed helping us to conclude to a solution.

At our customer environment, client connection run from the HelperVM does not establish database connections directly to the database listener. The connection goes through the Network Load Balancer, so called NLB, and the Oracle Connection Manager, so called CMAN.

The diagram below describes the database connection establishment process.

This is how it works.

• 1 – Client seeks for connection details (ideally, get the connection details from Oracle Directory Service)
• 2 – Client connects to Network Load Balancer
• 3 – Network Load Balancer “forwards” the request to Oracle Connection MANager using Virtual IP
• 4 – Oracle Connection MANager acts as a rule-based firewall and ensure the database target service is running on the “white listed targets (next_hop)
• 5 – Oracle Database establish connectivity upon credential validate (Oracle listener acts in between). The listener hands the connection over to the Oracle CMAN gateway process, which passes data back and forth between the client and the db-server and collects statistics.

We could observed per reverse engineering  technique that the TCP connection established between the Oracle client and Oracle CMAN works upon Network Load Balancer Virtual IP.

The Network Load Balancer needs for Session persistence “statefullnes” to be enabled. This means that once the connection is established, the NLB “remembers” established connections and fails them over in case of planned downtime.

We have been facing some broken connectivity issue. Checking Linux socket connection with linux ss command (# ss -nop) we could see TCP connection hungs between client and NLB virtual IP (CNAME DNS entry). On CMAN and DB-Server side the connection were already cleaned up as per Dead Connection Detection configuration. We can see in the diagram that EXPIRE_TIME is setup with a value of 10 minutes on the CMAN side and the listener configuration from the VM Cluster database.

The connection was still opened on the client side because:

• The client was still waiting on a result which would never come
• TCP connection to the NLB Virtual IP was still existing albeit closed with the CMAN and database listener
• TCP connection to the NLB would remain alive for days

The problem is that by default Oracle client does not enable TCP Keepalive, which is an expected behavior. Oracle expects the keepalive to be set on the server side. The “Dead Connection Detection” will then be enforced for all clients.

TCP Keepalive should then be managed in our case on the client side. And we are currently running Oracle 19c Client.

Oracle 19c client does not come with SQLNET.EXPIRE_TIME aka “Oracle dead connection detection”, unless hacked over connection string hidden (unsupported) parameter ENABLE_BROKEN. 

See following blog from a one of my former colleagues:

sqlnet-expire_time and enablebroken

And what about Oracle 26ai Client? Let’s do some test…

On the lab, I will use the VM called bastion to act as the client. The bastion has already an Oracle 19c Client installed. I’m going to installed new Oracle 26ai Client on it.

First we need to download the client version, which can be done from the following website:

https://www.oracle.com/database/technologies/oracle26ai-linux-downloads.html

I will install Oracle 26ai Client version in /opt/oracle.

[root@bastion oracle]# pwd
/opt/oracle

[root@bastion oracle]# ls -ld client*
drwxr-xr-x. 52 oracle oinstall 4096 Jun  6  2024 client19c
drwxr-xr-x. 47 oracle oinstall 4096 Nov 11  2025 client_21c
[root@bastion oracle]#

I will first unzip the downloaded oracle zip file.

[oracle@bastion oracle]$ pwd
/opt/oracle

[oracle@bastion oracle]$ unzip -q LINUX.X64_2326100_client.zip

I will then rename the client installation directory:

[oracle@bastion oracle]$ ls -ld client*
drwxr-xr-x.  5 oracle oinstall   90 Jan 17 13:59 client
drwxr-xr-x. 52 oracle oinstall 4096 Jun  6  2024 client19c
drwxr-xr-x. 47 oracle oinstall 4096 Nov 11  2025 client_21c

[oracle@bastion oracle]$ mv client client26ai
[oracle@bastion oracle]$

[oracle@bastion oracle]$ ls -ld client*
drwxr-xr-x. 52 oracle oinstall 4096 Jun  6  2024 client19c
drwxr-xr-x. 47 oracle oinstall 4096 Nov 11  2025 client_21c
drwxr-xr-x.  5 oracle oinstall   90 Jan 17 13:59 client26ai
[oracle@bastion oracle]$

I will prepare the response file for the command line installation.

[oracle@bastion client26ai]$ cp -p response/client_install.rsp response/client_install_custom.rsp

[oracle@bastion client26ai]$ vi response/client_install_custom.rsp

[oracle@bastion client26ai]$ diff response/client_install.rsp response/client_install_custom.rsp
22c22
 UNIX_GROUP_NAME=oinstall
26c26
 INVENTORY_LOCATION=/opt/oracle/oraInventory
30c30
 ORACLE_HOME=/opt/oracle/client26ai
34c34
 ORACLE_BASE=/opt/oracle
48c48
 oracle.install.client.installType=Administrator
[oracle@bastion client26ai]$

And I will run the Oracle 26ai Client installation.

[oracle@bastion client26ai]$ pwd
/opt/oracle/client26ai

[oracle@bastion client26ai]$ ls -ltrh
total 24K
-rwxrwx---.  1 oracle oinstall  500 Feb  6  2013 welcome.html
-rwxr-xr-x.  1 oracle oinstall 8.7K Jan 17 12:38 runInstaller
drwxr-xr-x.  4 oracle oinstall 4.0K Jan 17 12:38 install
drwxr-xr-x. 15 oracle oinstall 4.0K Jan 17 13:37 stage
drwxr-xr-x.  2 oracle oinstall   82 May 21 16:13 response

[oracle@bastion client26ai]$ ./runInstaller -silent -responseFile /opt/oracle/client26ai/response/client_install_custom.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 415 MB.   Actual 5025 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4095 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2026-05-21_04-16-17PM. Please wait ... [WARNING] [INS-32016] The selected Oracle home contains directories or files.
   ACTION: To start with an empty Oracle home, either remove its contents or specify a different location.
*********************************************
Package: compat-openssl10-1.0.2 (x86_64): This is a prerequisite condition to test whether the package "compat-openssl10-1.0.2 (x86_64)" is available on the system.
Severity: IGNORABLE
Overall status: VERIFICATION_FAILED
Error message: PRVF-7532 : Package "compat-openssl10(x86_64)-1.0.2" is missing on node "bastion"
Cause:  A required package is either not installed or, if the package is a kernel module, is not loaded on the specified node.
Action:  Ensure that the required package is installed and available.
-----------------------------------------------
[WARNING] [INS-13014] Target environment does not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. /opt/oraInventory/logs/installActions2026-05-21_04-16-17PM.log.
   ACTION: Identify the list of failed prerequisite checks from the log: /opt/oraInventory/logs/installActions2026-05-21_04-16-17PM.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:
 /opt/oracle/client26ai/install/response/client_2026-05-21_04-16-17PM.rsp

You can find the log of this install session at:
 /opt/oraInventory/logs/installActions2026-05-21_04-16-17PM.log

The installation of Oracle Client 26ai was successful.
Please check '/opt/oraInventory/logs/silentInstall2026-05-21_04-16-17PM.log' for more details.
Successfully Setup Software with warning(s).
[INS-10115] All configuration tools were previously ran successfully, no further configuration is required.

[oracle@bastion client26ai]$

And the Oracle 26ai Client is now installed.

I will create a user on the target lab PDB, named TESTZ_TMR_003I, in order to establish sqlplus connection and test the EXPIRE_TIME configuration.

I will create a user test01 and grant the connect permissions.

[oracle@svl-oat ~]$ echo $ORACLE_SID
CDB001I

[oracle@svl-oat ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 21 14:30:39 2026
Version 19.23.0.0.0

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


Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL> show pdbs

    CON_ID CON_NAME           OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED           READ ONLY  NO
     3 TESTZ_APP_006I         READ WRITE NO
     4 RCLON_TMR_003I         MOUNTED
     5 RCLON_TMR_002I         MOUNTED
     6 RCLON_TMR_001I         MOUNTED
     7 CLONZ_TMR_002I         MOUNTED
     8 TESTZ_TMR_003I         READ WRITE NO
    10 TESTZ_APP_004I         READ WRITE NO
    11 CLONZ_APP_001I         READ WRITE NO
    12 RCLON_APP_003I         READ WRITE NO

SQL> alter session set container=TESTZ_TMR_003I;

Session altered.

SQL> create user test01 identified by "test_expire";

User created.

SQL> grant connect to test01;

Grant succeeded.

SQL>

I will first set the ORACLE_HOME variable on the appropriate client directory.

[oracle@bastion client26ai]$ echo $ORACLE_HOME
/opt/oracle/client19c

[oracle@bastion client26ai]$ export ORACLE_HOME=/opt/oracle/client26ai

[oracle@bastion client26ai]$ echo $ORACLE_HOME
/opt/oracle/client26ai
[oracle@bastion client26ai]$

I will update the PATH variable to get tnsping and sqlplus binary from the appropriate Oracle 26ai Client.

[oracle@bastion client26ai]$ echo $PATH
/usr/share/Modules/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/oracle/client19c/bin:/opt/oracle/sqlcl-24.1.0.087.0929//bin

[oracle@bastion client26ai]$ export PATH=/opt/oracle/client26ai/bin

[oracle@bastion client26ai]$ echo $PATH
/opt/oracle/client26ai/bin
[oracle@bastion client26ai]$

I will check that the appropriate tnsping and sqlplus is taken.

[oracle@bastion client26ai]$ which sqlplus
/opt/oracle/client26ai/bin/sqlplus

[oracle@bastion client26ai]$ which tnsping
/opt/oracle/client26ai/bin/tnsping

I checked that the connection to target PDB is working.

[oracle@bastion ~]$ tnsping svl-oat:1521/testz_tmr_003i.db.jewlab.oraclevcn.com

TNS Ping Utility for Linux: Version 23.26.1.0.0 - Production on 21-MAY-2026 16:28:56

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

Used parameter files:
/opt/oracle/client19c/network/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=testz_tmr_003i.db.jewlab.oraclevcn.com))(ADDRESS=(PROTOCOL=tcp)(HOST=X.X.1.135)(PORT=1521)))
OK (0 msec)
[oracle@bastion ~]$

The TNS_ADMIN used is from the 19c Oracle client directory, which is absolutely not a problem.

[oracle@bastion ~]$ echo $TNS_ADMIN
/opt/oracle/client19c/network/admin
[oracle@bastion ~]$

As I can see on my client side, I do not have any sqlplus connection running right now.

[opc@bastion ~]$ ss -nop | grep 1521
[opc@bastion ~]$

I will generate a sqlplus connection.

[oracle@bastion client26ai]$ sqlplus test01/test_expire@svl-oat:1521/testz_tmr_003i.db.jewlab.oraclevcn.com

SQL*Plus: Release 23.26.1.0.0 - Production on Thu May 21 16:34:23 2026
Version 23.26.1.0.0

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


Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL>

And I can see that I have got a sqlplus connection with no timer/keepalive.

[opc@bastion ~]$ ss -nop | grep 1521
tcp   ESTAB  0      0   X.X.0.89:51404    X.X.1.135:1521
[opc@bastion ~]$

I will now configure Oracle Dead Connection Detection with SQLNET.EXPIRE_TIME parameter set in the client sqlnet.ora with a value of 1 minute.

[oracle@bastion ~]$ cd $TNS_ADMIN

[oracle@bastion admin]$ /usr/bin/grep -i expire sqlnet.ora
[oracle@bastion admin]$ 

[oracle@bastion admin]$ /usr/bin/vi sqlnet.ora

[oracle@bastion admin]$ /usr/bin/grep -i expire sqlnet.ora
SQLNET.EXPIRE_TIME=1
[oracle@bastion admin]$

I will run a new sqlplus connection.

[oracle@bastion admin]$ sqlplus test01/test_expire@svl-oat:1521/testz_tmr_003i.db.jewlab.oraclevcn.com

SQL*Plus: Release 23.26.1.0.0 - Production on Thu May 21 16:41:06 2026
Version 23.26.1.0.0

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

Last Successful login time: Thu May 21 2026 16:34:23 +02:00

Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL>

I can now see that I have got a connection configured with a timer and keep alive remaining of 38s.

[opc@bastion ~]$ ss -nop | grep 1521
tcp   ESTAB  0      0     X.X.0.89:62868    X.X.1.135:1521   timer:(keepalive,38sec,0)
[opc@bastion ~]$

Let’s configure the EXPIRE_TIME with a value of 15 minutes.

[oracle@bastion admin]$ /usr/bin/vi sqlnet.ora

[oracle@bastion admin]$ /usr/bin/grep -i expire sqlnet.ora
SQLNET.EXPIRE_TIME=15

I run a new sqlplus connection.

[oracle@bastion admin]$ sqlplus test01/test_expire@svl-oat:1521/testz_tmr_003i.db.jewlab.oraclevcn.com

SQL*Plus: Release 23.26.1.0.0 - Production on Thu May 21 16:43:25 2026
Version 23.26.1.0.0

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

Last Successful login time: Thu May 21 2026 16:42:29 +02:00

Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL>

And I now have got a connection configured with a timer and keep alive remaining of 14min.

[opc@bastion ~]$ ss -nop | grep 1521
tcp   ESTAB  0      0     X.X.0.89:60630    X.X.1.135:1521   timer:(keepalive,14min,0)
[opc@bastion ~]$

So, all good Oracle 26ai Client is supporting Dead Connection Detection with SQLNET.EXPIRE_TIME parameter.

We can easily confirm again that Oracle 19c Client does not support Dead Connection Detection on the client side.

Let’s move back to Oracle 19c Client home.

[oracle@bastion admin]$ export PATH=/opt/oracle/client19c/bin

[oracle@bastion admin]$ which sqlplus
/opt/oracle/client19c/bin/sqlplus

Run a sqlplus connection.

[oracle@bastion admin]$ sqlplus test01/test_expire@svl-oat:1521/testz_tmr_003i.db.jewlab.oraclevcn.com

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 21 16:48:09 2026
Version 19.3.0.0.0

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

Last Successful login time: Thu May 21 2026 16:46:07 +02:00

Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.23.0.0.0

SQL>

And check connection configuration.

[opc@bastion ~]$ ss -nop | grep 1521
tcp   ESTAB      0      0    X.X.0.89:64078    X.X.1.135:1521
[opc@bastion ~]$

There is no timer/keepalive handled with Oracle 19c Client.

Oracle Database 26ai Client is now supporting Dead Connection Detection on the client side. For our customer configuration this will help the client to check every X minutes (EXPIRE_TIME configured value) for Dead Connection. So if the CMAN and listener connections have already died and if for any reason the Network Load Balancer is still keeping the connection with the client, the client will close the connection after X minutes.

L’article Oracle Database 26ai Client and SQLNET.EXPIRE_TIME est apparu en premier sur dbi Blog.

Today I attended the Azure Bootcamp Switzerland event in Bern. Here is a summary of what I saw and what I learned in the sessions.

The opening keynote was about Azure Sovereign Architecture where the presenter gave us an update on the current Azure/Microsoft projects. We also had an explanation on how sovereignty works.

Then I joined a session titled “Time Bombs In Entra ID – How Well Are Your Entra ID Apps Managed?”. The speaker explained to us how Azure App registration and service principal really work. He also gave us some advice on best practices when using this kind of Azure/Entra resource.

Before the lunch break I joined a session on how some architects resolved the “multiple teams needed to deploy something” problem. They automated the deployment with CI/CD and Terragrunt. They did a demo on how they use their code and how they make infrastructure changes with it.

After the lunch, I chose to go in a more network oriented presentation. The topic was how to get rid of VPN by using an Azure service called Global Secure Access. Even though I’m not convinced that we can get rid of VPNs, this option could be something for highly Microsoft infrastructure as it uses the Microsoft backbone for all the network routing.

The last two sessions I attended sessions on Azure Policy. The topics were first using code to deploy Azure policies, as it’s a better way to have them identical in multiple environments and as it as also faster than using the Azure interface, which is slow. The second one was about using conditional access as safer alternative for securing Azure tenants with policies. This method is quite interesting but requires a paid version of Entra to be activated.

Finally, for the closing keynote, we had a presentation about an application developed by a Swiss company that helps emergency services coordinate. It’s allowing call centers to locate and contact closest to scene savers and organize their deployment.

Once again, I’m glad that could attend this event. I learned quite a bunch of things and could also refresh my memory on some other topics. The sessions are long enough to detail a topic and the speakers are always performing well.

L’article Azure Bootcamp Switzerland 2026 edition est apparu en premier sur dbi Blog.

You probably refresh your non-production Oracle databases with production data from time to time or on a regular basis. Without Multitenant, the most common procedure to do this refresh is a DUPLICATE FROM BACKUP with RMAN. The drawback is the unavailability of the database being refreshed during the DUPLICATE. You first need to remove the old version of the database, then start the DUPLICATE and wait until it’s finished. If you have Enterprise Edition and enough CPU, you can lower the time needed for the refresh by allocating a sufficient number of channels. But with a small number of CPU (which is normal for a non-production server), or eventually with Standard Edition (single channel RMAN operations only), a multi-TB database refresh can take several hours to complete. And if it fails for some reasons, you need to retry the refresh, extending even more the downtime.

Multitenant brought new possibilities for refreshing a database, and my favorite one is a CREATE PLUGGABLE DATABASE from a database link (DB link). It’s dead easy compared to a DUPLICATE FROM BACKUP on a non-CDB database. And you can lower the downtime to the very minimum. Here is how I did this for several projects.

You probably know that one of the advantage of a pluggable database is the easiness of changing its name. You just need to stop the PDB, rename it, and restart it. You can then use this technique to refresh a PDB under a temporary name and let the actual PDB available during the refresh. Once the refresh is finished, drop or rename the actual PDB, and rename the newest one to its target name. Even if your refresh takes hours, your downtime is limited to a couple of seconds/minutes.

The PDB administrator on the source database must have the CREATE PLUGGABLE DATABASE privilege:

ssh oracle@p01-srv-ora
. oraenv <<< P19PMT
sqlplus / as sysdba
Alter session set container=P19_ERP;
grant create pluggable database to SYSERP;
exit

The target server must have a TNS entry to the source PDB (production). If your source PDB and its container are protected by a Data Guard configuration, dont’t forget to add both addresses:

ssh root@t01-srv-ora
su – oracle
. oraenv <<< D19PMT
vi $ORACLE_HOME/network/admin/tnsnames.ora
…
P19_ERP =
(DESCRIPTION =
   (LOAD_BALANCE = OFF)
   (FAILOVER = ON)
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = p01-srv-ora)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = p02-srv-ora)(PORT = 1521))
   )
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = P19_ERP)
   )
)

tnsping P19_ERP
…

A DB link is required on the target container:

ssh root@t01-srv-ora
su – oracle
. oraenv <<< D19PMT
sqlplus / as sysdba
CREATE DATABASE LINK P19_ERP CONNECT TO SYSERP IDENTIFIED BY "*************"  USING 'P19_ERP';

select count(*) from dual@P19_ERP;
  COUNT(*)
----------
         1
exit

Basically, refresh will have 5 main tasks:

• create a new PDB with a temporary name _NEW on the target container from the source PDB
• start the new PDB for its correct registration in the container
• run an optional script for modifying production data (masking, disabling tasks, …)
• stop and rename the current PDB to _OLD, then start it again
• stop and rename the new PDB to its target name and start it again

Task 2 is needed because you cannot rename a PDB immediately after creation. You first need to open it, then close it for being able to change its name.

Let’s create 2 scripts on the target server, one shell script and one SQL script:

vi /home/oracle/scripts/refresh_D19_ERP.sh
#!/bin/bash
export ORACLE_SID=D19PMT
export REFRESH_LOG=/home/oracle/scripts/log/refresh_D19_ERP_`date +%d_%m_%Y-%H_%M_%S`.log
export ORACLE_HOME=`cat /etc/oratab | grep $ORACLE_SID | awk -F ':' '{print $2;}'`
date >> $REFRESH_LOG
$ORACLE_HOME/bin/sqlplus / as sysdba @/home/oracle/scripts/refresh_D19_ERP.sql >> $REFRESH_LOG
date >> $REFRESH_LOG
exit 0

vi /home/oracle/scripts/refresh_D19_ERP.sql
set timing on
show pdbs
alter pluggable database D19_ERP_OLD close immediate;
Drop pluggable database D19_ERP_OLD including datafiles;
show pdbs
create pluggable database D19_ERP_NEW from P19_ERP@P19_ERP ;
show pdbs
alter pluggable database D19_ERP_NEW open;
show pdbs
alter session set container=D19_ERP_NEW;
@/home/oracle/scripts/post_refresh_D19_ERP.sql 
alter session set container=CDB$ROOT;
alter pluggable database D19_ERP close immediate;
alter pluggable database D19_ERP rename global_name to D19_ERP_OLD;
alter pluggable database D19_ERP_OLD open;
show pdbs
alter pluggable database D19_ERP_NEW close immediate;
alter pluggable database D19_ERP_NEW rename global_name to D19_ERP;
Alter pluggable database D19_ERP open;
Alter pluggable database D19_ERP save state;
show pdbs
exit

It does the job, although these are very basic scripts: further controls could be added to trap errors, manage services, and so on.

Scheduling can be done through the crontab, for example every evening at 11.30PM:

crontab -l | grep D19_ERP | grep refresh
30 23 * * * sh /home/oracle/scripts/refresh_D19_ERP.sh

This is definitely a smart solution as soon as you have enough space on disk to have 2 copies of the PDB. It’s quite reliable and ticks all the boxes where I deployed these scripts.

L’article Reduce downtime when refreshing your non-production databases using Multitenant est apparu en premier sur dbi Blog.

Recently, after a successful migration to GoldenGate 26ai, a customer complained that he was seeing a lot of the following error in the ggserr.log file of a GoldenGate deployment (I replaced the names for the purpose of this blog).

2026-05-18T14:32:35.948+0200  ERROR   OGG-08502. Oracle GoldenGate Receiver Service for Oracle:   Path path21 not found.

More precisely, in that case, path21 is a distribution path sending trail files from deployment ogg_test_02 to ogg_test_01. And the error shown above appeared in the log file of the ogg_test_01 deployment.

While this error did not seem to indicate any operational issue in the replication, after checking on multiple environments, I confirmed that it appears everywhere. So what is happening exactly ?

If you get this error and do not know where it comes from, log in to the web UI of the affected deployment, and go to the Receiver Service Paths tab. You should see a list of the distribution paths that are connecting to your deployments. The example below shows the path21 that is mentioned in the error.

If you click on this path… Nothing happens ! And by “nothing”, I mean “nothing abnormal”. In fact, the statistics are properly displayed (see below), and there is no error shown to the user. However, if you look at your ggserr.log file you will see that the error given above appears.

At first glance, this might not seem like a huge issue, because if you don’t click on the receiver path, you will not get the error. However, in the log file of the customer, the error appeared regularly. Every minute, to be precise.

Luckily, when debugging this issue, I started by putting the target in a blackout in the Oracle Enterprise Manager. To my surprise, the error was gone during the blackout and reappeared right after.

In this case, the Enterprise Manager Plug-in for Oracle GoldenGate is monitoring the status of the deployment every minute and generates the error in the process.

When looking at the targets in the OEM, there is no error. Again, no operational impact.

GoldenGate offers multiple ways of managing deployments : REST API, adminclient, or the web UI. Unfortunately, some bugs (and some features…) mean that you should avoid managing some objects with some of these tools (read why you shouldn’t create profiles through the adminclient, for instance).

In this specific case, all distribution path creation methods lead to the same error in the log file. It doesn’t matter whether you create the distribution path with the adminclient, the REST API or the web UI. They will all lead to this error.

Let’s dig a bit to see what is happening behind the scenes. By looking at the restapi.log file (read my blog on how to analyze REST API logs efficiently), we can see the full error:

2026-05-18 09:08:58.402+0000 ERROR|RestAPI.recvsrvr | Request #9: {
     "context": {
         "httpContextKey": 140097141801744,
         "verbId": 2,
         "verb": "GET",
         "originalVerb": "GET",
         "uri": "/services/v2/targets/path21",
         "protocol": "http",
         "headers": {
             ...
         },
         "host": "vmogg",
         "securityEnabled": false,
         "authorization": {
             "authUserName": "ogg",
             "authUserRole": "Security",
             "authMode": "Cookie"
         },
         "requestId": 8,
         "uriTemplate": "/services/{version}/targets/{path}",
         "catalogUriTemplate": "/services/{version}/metadata-catalog/path"
     },
     "isScaRequest": true,
     "content": null,
     "parameters": {
         "uri": {
             "path": "path21",
             "version": "v2"
         },
         "query": {
             "WindowRef": "%2Fservices%2Fv2%2Fcontent%2F%23%2FrecvsrvrPaths%2Fpath21%2FpathNetworkStats"
         }
     }
 }
 Response: {
     "context": {
         ...
     },
     "isScaResponse": true,
     "content": {
         "$schema": "api:standardResponse",
         "links": [
             {
                 "rel": "canonical",
                 "href": "https://vmogg/services/ogg_test_01/recvsrvr/v2/targets/path21",
                 "mediaType": "application/json"
             },
             {
                 "rel": "self",
                 "href": "https://vmogg/services/ogg_test_01/recvsrvr/v2/targets/path21",
                 "mediaType": "application/json"
             }
         ],
         "messages": [
             {
                 "$schema": "ogg:message",
                 "title": "Path path21 not found",
                 "code": "OGG-08502",
                 "severity": "ERROR",
                 "issued": "2026-05-18T09:08:58Z",
                 "type": "https://www.rfc-editor.org/rfc/rfc9110.html#name-status-codes"
             }
         ]
     }
 }

The issue comes from the following endpoint : /services/v2/targets/path21. It is described in the documentation under Retrieve an existing Oracle GoldenGate Collector Path. But looking at another endpoint described in Get a list of distribution paths, we get the following response:

{
    "$schema": "api:standardResponse",
    "links": [
        {
            "rel": "canonical",
            "href": "https://vmogg/services/ogg_test_01/recvsrvr/v2/targets",
            "mediaType": "text/html"
        },
        {
            "rel": "self",
            "href": "https://vmogg/services/ogg_test_01/recvsrvr/v2/targets",
            "mediaType": "text/html"
        },
        {
            "rel": "describedby",
            "href": "https://vmogg/services/ogg_test_01/recvsrvr/v2/metadata-catalog/targets",
            "mediaType": "application/schema+json"
        }
    ],
    "messages": [],
    "response": {
        "$schema": "ogg:collection",
        "items": [
            {
                "links": [
                    {
                        "rel": "parent",
                        "href": "https://vmogg/services/ogg_test_01/recvsrvr/v2/targets",
                        "mediaType": "application/json"
                    },
                    {
                        "rel": "canonical",
                        "href": "https://vmogg/services/ogg_test_01/recvsrvr/v2/targets/path21_ogg26dist2_7811",
                        "mediaType": "application/json"
                    }
                ],
                "$schema": "ogg:collectionItem",
                "name": "path21",
                "status": "running",
                "targetInitiated": false
            }
        ]
    }
}

Here, we see that the endpoint associated with the path21 object is not recvsrvr/v2/targets/path21 but recvsrvr/v2/targets/path21_ogg26dist2_7811. And looking at this second endpoint, we do not get an error.

{
    "$schema": "api:standardResponse",
    "links": [
        {
            "rel": "canonical",
            "href": "https://vmogg/services/ogg_test_01/recvsrvr/v2/targets/path21_ogg26dist2_7811",
            "mediaType": "text/html"
        },
        {
            "rel": "self",
            "href": "https://vmogg/services/ogg_test_01/recvsrvr/v2/targets/path21_ogg26dist2_7811",
            "mediaType": "text/html"
        },
        {
            "rel": "describedby",
            "href": "https://vmogg/services/ogg_test_01/recvsrvr/v2/metadata-catalog/path",
            "mediaType": "application/schema+json"
        }
    ],
    "messages": [],
    "response": {
        "name": "path21",
        "status": "running",
        "$schema": "ogg:distPath",
        "source": {
            "uri": "trail://localhost:7811/services/v2/sources?trail=pdb2/bb"
        },
        "target": {
            "$schema": "ogg:distPathEndpoint",
            "uri": "ws://vmogg/services/v2/targets?trail=pdb2/bb"
        },
        "options": {
            "network": {
                "appOptions": {
                    "appFlushBytes": 27985,
                    "appFlushSecs": 1
                },
                "socketOptions": {
                    "tcpOptions": {
                        "ipDscp": "DEFAULT",
                        "ipTos": "DEFAULT",
                        "tcpNoDelay": false,
                        "tcpQuickAck": true,
                        "tcpCork": false,
                        "tcpSndBuf": 16384,
                        "tcpRcvBuf": 131072
                    }
                }
            }
        }
    }
}

The problem is that it was never decided for path21 to be referred to as path21_ogg26dist2_7811 internally. And it looks like GoldenGate does not know about it either… So until the bug is corrected, you will have to filter this OGG-08502 Path not found error out of the ggserr.log file if you use it for monitoring.

L’article OGG-08502 Path not found error from OGG Receiver Service est apparu en premier sur dbi Blog.

When people think about consultants, they usually focus on expertise. “They bring experience, frameworks, and best practices.”
That’s true, of course. However, that is not the most impactful aspect of the role.
The real shift happens somewhere less visible: positioning. As an outsider, you don’t just join a team.

You become something different. Over time, I’ve come to think of it as operating within a “shadow team.”

This invisible layer changes how you navigate politics, truth, and influence.
Let’s unpack that.

As an employee, you’re clearly part of the organization.
However, when you’re an external consultant, it’s a different story.
You sit inside delivery teams while remaining outside the organization’s long-term structure. This dual positioning creates what I call a shadow team.

You collaborate closely with internal stakeholders, influence decisions without owning them, and observe dynamics that others are too immersed in to see.

You’re close enough to matter, yet distant enough to stay objective.
This reshapes everything.

Every organization has internal politics, including priorities, power structures, historical tensions, and unwritten rules. The larger the organization, the more politics there are.
Employees must live within that system.
Consultants, on the other hand, can often see the system more clearly because they aren’t fully bound by it.
This doesn’t mean you’re outside of politics, though.
It means:

You can identify misalignments more quickly, notice when decisions are driven by structure, not logic and spot friction between teams that others consider “normal.”

But here’s the key difference:

• You are less constrained by long-term consequences.
• An employee may avoid challenging a decision due to its potential impact on their career.
• However, a consultant can raise the concern because their role is to add clarity, not preserve equilibrium.
• Still, this doesn’t mean ignoring politics. It means navigating them consciously without being controlled by them.

One of the most powerful—and most fragile—assets of being an external consultant lies in the neutrality that people attribute to you.

You are not:

• Competing for a promotion
• Defending a department
• Protecting past decisions

This creates a rare opportunity. You can become a trusted bridge between stakeholders

When done right, people will:

• Share concerns they wouldn’t voice internally
• Ask for your opinion as a “safe” perspective
• Use you to validate or challenge ideas

However, neutrality is not automatic, it must be earned and can easily be lost.
You lose it when:

• You align too strongly with one stakeholder
• You start defending internal logic instead of questioning it
• You behave like an insider too quickly

The best consultants maintain a delicate balance:

They are close enough to build trust and distant enough to stay credible.

This is where the role becomes truly challenging.
As a consultant, you are often expected to:

• Tell the truth
• Challenge assumptions
• Highlight risks

However, you are also expected to:

• Maintain relationships
• Respect stakeholders
• Keep the project moving forward

These two expectations often conflict with each other.
The naive approach: “Just be brutally honest.”
This approach quickly fails. Brutality destroys trust.

The safe approach: “Say what people want to hear.”
This makes you irrelevant.

The real skill is delivering truth in a way that can be heard.
That means:

• Frame issues in terms of impact, not fault.
• Ask questions instead of making accusations.
• Adapt your message to your audience.

For example, rather than saying, “This process isn’t working at all”

A more measured approach might be: “I see a few risks associated with this process. Could we go over them together?”

The observation is the same.
However, the outcome is different.

Being a consultant isn’t just about knowledge. It’s also about positioning. You have a clearer view, speak more freely, and connect across sides.

However, our profession is based on a paradox. We must be objective enough to provide sound advice, yet also be fully committed to the task at hand. Additionally, we must offer honest feedback without hurting the client’s feelings or losing their trust.

At dbi services, we’re passionate about striking that delicate balance, whether the subject is ECM or any other area of our expertise. Learn more about us here.

L’article What being an external consultant really changes est apparu en premier sur dbi Blog.

If you are licensed for the GoldenGate Management Pack, using the Enterprise Manager plug-in for GoldenGate improves monitoring and management of your deployments. And after migrating to the Microservices Architecture, you should definitely update your plug-in and rediscover all targets. Let’s see how to do all that here.

In this blog, I will use the latest version of the Enterprise Manager (24ai) and monitor GoldenGate 26ai deployments. The overall workflow is the same for other versions of the Enterprise Manager and GoldenGate, provided OGG is in the Microservices Architecture.

Here are the main steps to monitor GoldenGate targets from the Enterprise Manager:

• Update the catalog in the Enterprise Manager
• Deploy the plug-in on the management server
• Deploy the plug-in on the agent
• Configure the discovery module
• Promote the new targets

Before attempting to install the plug-in, make sure it is not already installed in your environment. To check this, go to Setup > Extensibility > Plug-ins, and expand the Middleware section. If you do not see any line named Oracle GoldenGate, it means the plug-in is not installed yet.

Since most OEM environments do not have access to the Oracle Support directly, we’ll download the plug-in in offline mode. To do so, go to the Setup > Provisioning and Patching > Offline Patching section.

Once in the Offline Patching section, make sure Offline is selected for the connection and download the catalog file as instructed from an environment with access to the Oracle support website. Transfer it to where you have access to the OEM UI, and upload it.

Once the catalog is uploaded, you should see the following information message.

Then, go to Setup > Extensibility > Self Update and click on Check Updates.

You should see the following pop-up appear, with a link from where you will be able to download the OEM Self Update catalog file. For reference, the one I had when writing this blog was the following : https://updates.oracle.com/Orion/Download/download_patch/p9348486_112000_Generic.zip

As instructed, transfer this patch to the OMS host and import it with emcli and the import_update_catalog action. You can also import it from another managed host. It should take around twenty seconds to import everything.

oracle@oem24:~/ [oem24] emcli import_update_catalog -file=/tmp/p9348486_112000_Generic.zip -omslocal
Processing catalog for Diagnostic Tools
Processing update: Diagnostic Tools - AHFFI 25.1.0.1.0 for Linux
Processing update: Diagnostic Tools - AHF 25.5.0.0.0 for HP

[...]

Processing update: Plug-in - GoldenGate Plug-in now supports monitoring of Oracle GoldenGate Microservices, in addition to the Oracle GoldenGate Classic
Processing update: Plug-in - GoldenGate Plug-in now supports monitoring of Oracle GoldenGate Microservices, in addition to the Oracle GoldenGate Classic
Processing update: Plug-in - GoldenGate Plug-in now supports monitoring of Oracle GoldenGate Microservices, in addition to the Oracle GoldenGate Classic
Processing update: Plug-in - GoldenGate Plug-in now supports monitoring of GoldenGate Microservices Architecture, in addition to the GoldenGate Classic Architecture

[...]

Successfully uploaded the Self Update catalog to Enterprise Manager. Use the Self Update Console to view and manage updates.
Time taken for import catalog is 17.289 seconds.

When this is done, go back to the Self Update page, click on the Plug-In section. You will see the different versions of GoldenGate that are available. When I’m writing this blog, the latest version of the plug-in is 13.5.2.0.0 (the latest patch released in January 2026, 13.5.2.0.6, will be a topic for another blog). Click on the latest version and then on Download.

The following pop-up gives you the link from which you should download the plug-in update file. In my case, it was https://updates.oracle.com/Orion/Services/download/p34651099_112000_Generic.zip?aru=24962501&patch_file=p34651099_112000_Generic.zip.

Once the file is downloaded, import it in the same way as before with the catalog, but this time with the emcli import_update action.

oracle@oem24:~/ [oem24] emcli import_update -omslocal -file=/tmp/p34651099_112000_Generic.zip
Processing update: Plug-in - GoldenGate Plug-in now supports monitoring of Oracle GoldenGate Microservices, in addition to the Oracle GoldenGate Classic
Successfully uploaded the update to Enterprise Manager. Use the Self Update Console to manage this update.

Once this is done, go back to the Setup > Extensibility > Plug-in tab and expand the Middleware section. You should now see Oracle GoldenGate, and 13.5.2.0.0 as the downloaded version.

Warning : Deploying the plug-in on the management server will temporarily restart OMS components and briefly interrupt monitoring operations. To deploy the plug-in, you have two options:

• Deploying the plug-in from the web UI.
• Deploying the plug-in from the CLI.

From the web UI, click on the Oracle GoldenGate plug-in, then on Deploy On, and deploy the plug-in on the Management Servers.

Make sure the correct version of the plug-in is chosen (13.5.2.0.0), and click on Next to run the prerequisite checks.

Once the checks are successfully completed, click on Next.

You should now select the repository credentials for the OEM. You should either use new credentials (if it’s a new environment) or use existing named credentials. Click on Next.

Once everything is done, click on Deploy.

As instructed, you can check the status of the deployment with the emctl status oms -details command.

oracle@oem24:~/ [oem24] emctl status oms
Oracle Enterprise Manager 24ai Release 1
Copyright (c) 1996, 2024 Oracle Corporation.  All rights reserved.
WebTier is Up
Oracle Management Server is Down

This is due to the following plug-ins being deployed on the management server or undeployed from it:
----------------------------------------
Plugin name:    : Oracle GoldenGate
Version:        : 13.5.2.0.0
ID:             : oracle.fmw.gg
----------------------------------------

Alternatively, you can deploy the plug-in with the following command, using the oracle.fmw.gg ID for the plug-in and the latest 13.5.2.0.0 version.

emcli deploy_plugin_on_server -plugin="oracle.fmw.gg:13.5.2.0.0"

Once the plug-in is deployed on the Management Server, you can check again in the web UI : the latest version should be in the On Management Server section.

For each GoldenGate host where an OEM agent is running, deploy the plug-in. To do so, from the web UI, click on the Oracle GoldenGate plug-in, then on Deploy On, and select Management Agent.

There is currently a bug with the Supported Target Versions. No matter your patch level, you will not see the latest versions of GoldenGate. Do not worry about this yet. Just make sure 13.5.2.0.0 is selected.

Then, select the agent on which you want to deploy the plug-in.

Let the prerequisite checks run…

And once everything is ready, click on Deploy.

You can check that everything is running properly with the emcli get_plugin_deployment_status command.

Once the plug-in is correctly deployed on the OMS host and on the GoldenGate host agent, you can configure the module. I will only cover the configuration for the Microservices Architecture. Go to the Setup > Add Target > Configure Auto Discovery tab.

Choose the correct agent host, and click on Discovery Modules.

Enable the Oracle GoldenGate Microservices module, click on it, and then on Edit Parameters.

If you deployed GoldenGate with a reverse proxy, set up the plug-in as such.

If you deployed GoldenGate with a port for each service, enter the service manager port (7809, by default).

Warning : if your installation is secured with certificates, make sure to follow the instructions I gave in a blog to avoid EM-90000 errors when discovering new targets.

Once this is done, just go back to the Configure Auto Discovery section, click on the correct host, and then click on Discover Now. Then, go back to the Configure Auto Discovery section. You should now see a greater number of targets in the Discovered targets section.

If the number of targets did not increase, despite a successful discovery, check the blog linked above.

Click on the number of targets to jump to the Auto Discovery Results section. Select the newly discovered Service Manager target, and click on Promote. Once the target is promoted, you should see the new GoldenGate targets being monitored by the Enterprise Manager !

L’article Install and configure OEM plug-in for GoldenGate est apparu en premier sur dbi Blog.

When working with SQL Server environments, securing client connections can become an important requirement, especially when TLS encryption must be implemented using certificates. In this context, a customer asked us to develop an Ansible playbook and role to automate the configuration of TLS for SQL Server. The certificates are generated from the customer PKI and provided as PEM files containing the server certificate, the private key, and the certificate chain.

However, some extractions and conversions are required before these certificates can be used on Windows and configured for SQL Server.

Here, the idea is to propose a solution (the architecture) that prepares the certificate, imports it on the SQL Server host, and configures SQL Server to use it.

We will also see how to separate the preparation and activation steps in order to reduce the impact on the SQL Server service.

In this blog post, we will describe the global approach and the Ansible logic used to implement certificate-based TLS encryption for SQL Server.

Before configuring TLS encryption on SQL Server, the first point was to understand the certificate format provided by the customer PKI.

In our case, the generated file is a <machine>.pem file. This file contains the server certificate used for TLS, the private key and the certificate chain with the intermediate and root certificates.

As this format cannot be directly used as-is on the Windows side for SQL Server, some extraction and conversion steps are required.

The general idea is to use the Ansible control node as a working area.

The PEM file is first copied into a temporary folder where the different parts of the certificate are extracted:

• the leaf certificate
• the intermediate certificate
• the root certificate
• the private key

These elements are then used to build a PFX file which can be imported on the Windows SQL Server host.

The PFX is installed in the LocalMachine\My certificate store while the intermediate and root certificates are imported into the appropriate Windows certificate stores.

The implementation has been designed around three different execution modes: stage, activate, and full.

The stage mode is used to prepare the certificate without any impact on the SQL Server service. It copies the PEM file, performs the extractions, builds the PFX file, copies it to the managed Windows node and imports the certificates into the Windows certificate stores. No registry change is performed, and the SQL Server service is not restarted. This mode is useful when we want to prepare the server in advance before switching SQL Server to the new certificate.

The activate mode assumes that the certificate is already present on the Windows server. Its role is to configure SQL Server to use the installed certificate and depending on the selected option, restart the SQL Server service or leave the change pending until the next planned reboot.

This can be useful when the certificate activation must be aligned with an existing maintenance window, for example during monthly OS patching.

The full mode executes the complete configuration from end to end. It performs the extraction and conversion steps, imports the certificates, grants the required permissions, configures SQL Server to use the expected certificate, and restarts the SQL Server service only if required. To avoid unnecessary impact, the role relies on the certificate thumbprint. If the expected certificate is already configured, no change is applied and the SQL Server service is not restarted. This behavior is important for idempotency.

For example, if the full mode is executed after an activate mode, nothing should be changed if the certificate is already the correct one. The same logic applies if the playbook is executed by mistake while the certificate has not been renewed.

Another point to manage is the restart of the SQL Server service. SQL Server loads the certificate configuration when the service starts. Therefore, when a new certificate is configured, the change is only effective after a restart of the SQL Server service.

For this reason the role should provide an option to control whether the restart is performed immediately or postponed to the next planned reboot.

We also have to consider DNS aliases. The standard use case is to generate a certificate containing at least the short name and the FQDN of the SQL Server host in the subjectAltName. If DNS aliases are used by client applications, they can also be added to the certificate SAN.

For example:

[alt_names]
DNS.1 = A-WS2022-2.lab.local
DNS.2 = A-WS2022-2

Finally, the customer confirmed that the private key included in the PEM file is not encrypted.

This simplifies the conversion process to PFX, but it also means that the PEM file must be handled carefully during the Ansible execution, especially in temporary folders and during file transfers. With this approach, the role provides a controlled way to prepare, activate, or fully configure TLS encryption for SQL Server while keeping the impact on the SQL Server service under control.

The complete workflow can be represented as follows:

The certificate manipulation is performed on the Ansible control node.

The Windows certificate import and SQL Server configuration are performed on the managed Windows SQL Server host.

This separation is useful because the PEM processing and PFX generation are handled with Linux tools such as OpenSSL while the certificate installation, private key permissions, registry configuration and SQL Server restart are handled through Windows modules and PowerShell. The design also supports a controlled deployment approach.

The certificate can first be staged without service impact then activated later during a maintenance window.

The full mode can be used when the complete implementation must be executed in a single run. The use of the certificate thumbprint is important for idempotency. It allows the role to detect whether SQL Server is already configured with the expected certificate and avoids unnecessary service restarts when no change is required.

For certain reasons we do not disclose the code of the created role.

Thank you. Amine Haloui

L’article Customer case study – automating SQL Server TLS Encryption with Ansible and Certificates (Architecture) est apparu en premier sur dbi Blog.

SQLDay 2026 took place this week, from May 11th to 13th, in Wroclaw. Among the featured speakers was Erik Darling, who delivered both a main session and a full-day workshop dedicated to SQL Server performance. During his presentations, he emphasized a concept that is not always widely understood, known as the Row Goal.

The purpose of this article is to recap Erik’s key observations and to introduce this topic, which can serve as a powerful lever for query optimization.

In order to understand the explanations below, one key concept must be understood: the Pierogi.

“Pierogi are filled dumplings made from unleavened dough, popular in Polish cuisine and enjoyed worldwide, with various savory and sweet fillings” [1], [2].

To be honest, this has nothing to do with our technical topic, but this dish discovered during this trip is so good that I simply had to include it in this blog.

In this article, we will use a custom-made database simulating a Polish supermarket selling pierogis. Unfortunately, there aren’t many left, and the product distribution is not uniform. In fact, pierogis account for much less than 1% of the supermarket’s total stock.
Here is the script to create the DB, along with its article reference table and inventory:

USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'PierogiMart')
    DROP DATABASE PierogiMart;
GO

CREATE DATABASE PierogiMart;
GO

USE PierogiMart;
GO

CREATE TABLE Articles (
    ArticleID INT IDENTITY(1,1) PRIMARY KEY,
    ArticleName VARCHAR(50) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE Inventory (
    ReferenceID INT IDENTITY(1,1) PRIMARY KEY,
    ArticleID INT NOT NULL,
    ValidityDate DATETIME NOT NULL,
    Quantity INT NOT NULL,
    CONSTRAINT FK_Article FOREIGN KEY (ArticleID) REFERENCES Articles(ArticleID)
);
GO

INSERT INTO Articles (ArticleName, Price)
VALUES 
('Pierogi', 12.50),
('Pasta', 8.00),
('Sandwich', 6.50),
('Quiche', 9.00);
GO

INSERT INTO Inventory (ArticleID, ValidityDate, Quantity)
SELECT TOP 100000 
    2, 
    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 365, '2025-01-01'), 
    ABS(CHECKSUM(NEWID())) % 100
FROM sys.all_columns a CROSS JOIN sys.all_columns b;

INSERT INTO Inventory (ArticleID, ValidityDate, Quantity)
SELECT TOP 10000 
    3, 
    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 365, '2025-01-01'), 
    ABS(CHECKSUM(NEWID())) % 100
FROM sys.all_columns a CROSS JOIN sys.all_columns b;

INSERT INTO Inventory (ArticleID, ValidityDate, Quantity)
SELECT TOP 50000 
    4, 
    DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 365, '2025-01-01'), 
    ABS(CHECKSUM(NEWID())) % 100
FROM sys.all_columns a CROSS JOIN sys.all_columns b;

INSERT INTO Inventory (ArticleID, ValidityDate, Quantity)
SELECT TOP 10 
    1, 
    '2026-12-31', 
    5
FROM sys.all_columns;
GO

We are also including a few indexes to simulate a real-world use case and to support our queries, ensuring we get realistic execution plans:

CREATE NONCLUSTERED INDEX IDX_INV_QUANT ON [dbo].[Inventory] ([Quantity]) include (ArticleID)

CREATE NONCLUSTERED INDEX IDX_INV_VALIDITY on [dbo].[Inventory] ([ValidityDate]) include (ArticleID)

CREATE NONCLUSTERED INDEX IDX_INV_ART on [dbo].[Inventory] (ArticleID)

Normally, the SQL Server optimizer seeks to minimize the total cost of processing all data for a query. However, if it knows that you only need a specific number of rows (for example, via a TOP, FAST(N), or EXISTS clause), it changes its strategy.

The Row Goal is this specific row target that pushes the optimizer to favor a plan capable of delivering the first few rows as quickly as possible, even if that same plan would be catastrophic for processing the entire table.

To illustrate the definition above, let’s search for the pierogis with the furthest expiration dates.
Note that the IDX_INV_VALIDITY index supports this query:

SELECT 
    A.ArticleName, 
    A.Price, 
    I.ValidityDate
FROM Articles A
INNER JOIN Inventory I ON A.ArticleID = I.ArticleID
WHERE A.ArticleName = 'Pierogi'
order by I.ValidityDate desc;

SELECT top 10
    A.ArticleName, 
    A.Price, 
    I.ValidityDate
FROM Articles A
INNER JOIN Inventory I ON A.ArticleID = I.ArticleID
WHERE A.ArticleName = 'Pierogi'
order by I.ValidityDate desc

The difference between these two queries is that one requests only the first 10 rows, while the other requests all matching rows. However, this simple distinction is not merely applied when displaying the results; this condition is pushed deeper into the execution plan to influence the choice of operators (Nested Loop, Hash Join, Merge Join) further down the tree.

For the first query, here is the resulting plan:

As we can see, the optimizer chose a Hash Join given the volume of data to be joined. A Hash Match implies that all the data must be read in order to produce the desired result.

For the second query, here is the execution plan:

We can see that this time, the optimizer chose a Nested Loop, which takes each row from the reference table (Inventory) and joins them with the Articles table. This operation can be very time-consuming if a large number of rows must be processed. However, this is where EstimateRowsWithoutRowGoal comes into play. The value of this property is 40’002.5; this means that in a case where a subset of rows was not specifically required, the optimizer would have estimated the number of rows returned by this operator at that value. We can see, however, that the estimation actually used is 10 rows for one execution, a value clearly derived from the TOP(10).

In summary, adding the TOP(10) allowed the optimizer to use a less expensive join for a small amount of data, even though the TOP operator is located at the very end of the execution plan (since a plan is read from right to left).

As explained previously, the EXISTS clause has a cardinality of 1 because the very first row meeting the internal condition is enough to validate the case. This triggers a Row Goal, as the optimizer must estimate how many rows it will need to read to satisfy (or not) this condition.

Note: In cases where the condition is never met, the optimizer’s plan can become highly inefficient; for full details, see Erik Darling’s blog [here].

We will now observe this behavior with the following query, varying the internal condition of the EXISTS clause by testing one highly selective (discriminant) case and another much less so.

SELECT 
    A.ArticleName, 
    A.Price
FROM Articles A
WHERE not EXISTS (
    SELECT 1/0
    FROM Inventory I 
    WHERE I.ArticleID = A.ArticleID 
    AND I.Quantity > 10 -- vs 98
);

As you may have noticed, I am looking here for products that maintain a certain quantity for every possible consumption date. My goal, of course, is to avoid depleting the stocks of these excellent Polish pierogis so that everyone can enjoy them!

The case where we want to ensure that all existing quantities for an item are greater than 10 is very difficult to satisfy; based on the statistics available to the optimizer, all items have 10 or more units in stock, except for the pierogis!
Since this condition is so widespread, the optimizer knows it will have to scan a large number of rows to find a single case where the condition is not met. This is why it opts for a Scan. This behavior is evidenced by the estimated number of rows to be read (160’010, which represents the entire table).

On the other hand, for a very restrictive condition (quantity > 98), the optimizer recognizes that this condition is highly selective. This is why it favors a Nested Loop, estimating that only 1’608 rows will be necessary to prove the non-existence of the condition.

In summary, EXISTS forces the optimizer to estimate the number of rows required to find a single occurrence that proves whether a condition is met or not, thereby triggering a local optimization of the execution plan.

The OPTION(FAST N) hint allows you to manually introduce the Row Goal concept into a query. This hint does not limit the total number of results returned; instead, it optimizes the execution plan to retrieve the first N rows as quickly as possible (potentially at the expense of performance for the remaining rows).

In our example below, we have two identical queries retrieving items with a quantity greater than 10. However, the second one uses an execution plan optimized to return the first row as fast as possible (just to make sure no one steals the last available pierogi from the top of the pile!).

select * from Inventory i
where i.Quantity > 10 
order by i.ArticleID

select * from Inventory i
where i.Quantity > 10 
order by i.ArticleID option(fast 1)

Once again, the plans diverge. To retrieve a single row, the IDX_INV_ART index (which already contains sorted ArticleIDs) is used. It performs a Seek on the smallest ArticleID to check if it satisfies the condition of having a quantity greater than 10.

However, by enabling SET STATISTICS TIME ON, we can see that the second execution plan is slower than the first when returning all requested rows (250ms vs. 204ms). While the gap is not massive due to the small table size, the difference is nonetheless observable.

To conclude, the Row Goal is a double-edged sword; brilliant when you only need a quick glimpse of your data, but it can become a real performance trap if the optimizer’s “bet” fails.

Fortunately, if you find that SQL Server is making bad decisions by being too optimistic, you can take back control. By using the hint OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL')), you force the optimizer to stop daydreaming and focus on the actual cost of the query. It’s the ultimate tool to ensure your execution plan doesn’t end up as messy as a dropped plate of pierogis!

L’article How Row Goal shapes your SQL Server query strategy by hunting for pierogis est apparu en premier sur dbi Blog.

The proposed architecture consists in adding a small internal REST API on the Proxmox server in order to expose a controlled ZFS snapshot operation. SQL Server 2025 can then call this API through sp_invoke_external_rest_endpoint, instead of running SSH commands directly or relying on an external tool.

The role of the API is deliberately limited: it receives a snapshot request, checks that the requested zvol is authorized, and then runs the zfs snapshot command on the Proxmox side. An allowlist is used to restrict the ZFS volumes that can be accessed. This prevents a REST call from being able to manipulate any dataset on the server.

With this approach, we can reproduce a behavior close to what an enterprise storage array provides, but using Proxmox and ZFS. It is important to note that Proxmox does not natively provide the same level of integration as Pure Storage for SQL Server snapshots. Pure Storage provides dedicated mechanisms and integrations. In our case, we need to build a specific orchestration layer. The REST API therefore acts as an adapter between SQL Server, which drives the snapshot backup workflow, and ZFS, which actually performs the storage-level snapshot.

Here is a global overview of the architecture:

• SQL Server freezes the database I/Os
• SQL Server 2025 calls the internal REST API
• The REST API validates the request and checks the zvol allowlist
• The API triggers the ZFS snapshot on Proxmox
• The API returns the snapshot information to SQL Server
• SQL Server creates the metadata-only backup
• The database I/Os are released

Under Proxmox, we install the required packages:

apt update
apt install -y python3-venv sudo openssl

We create a dedicated user:

useradd --system \
  --home /opt/sql-zfs-api \
  --shell /usr/sbin/nologin \
  sqlsnap

We create the following folders:

mkdir -p /opt/sql-zfs-api
mkdir -p /etc/sql-zfs-api

We declare the authorized zvol :

cat >/etc/sql-zfs-api/allowed-zvols <<'EOF'
sqlpool/pve/vm-302-disk-0
EOF

We create a root-only allowlist:

chown root:root /etc/sql-zfs-api/allowed-zvols
chmod 600 /etc/sql-zfs-api/allowed-zvols

Then we create the secured ZFS helper. This script is executed as root through sudo, but it rejects any dataset that is not defined in the allowlist.

cat >/usr/local/sbin/sql-zfs-helper <<'EOF'
#!/usr/bin/env bash
set -euo pipefail

ALLOW_FILE="/etc/sql-zfs-api/allowed-zvols"
LOCK_FILE="/run/sql-zfs-helper.lock"

die() {
  echo "$*" >&2
  exit 1
}

exec 9>"$LOCK_FILE"
flock -n 9 || die "another snapshot operation is already running"

[[ -r "$ALLOW_FILE" ]] || die "allowlist not readable: $ALLOW_FILE"

mapfile -t ALLOWED_DATASETS < <(grep -Ev '^\s*(#|$)' "$ALLOW_FILE")

is_allowed() {
  local ds="$1"
  local allowed
  for allowed in "${ALLOWED_DATASETS[@]}"; do
    [[ "$ds" == "$allowed" ]] && return 0
  done
  return 1
}

valid_snapname() {
  [[ "$1" =~ ^[A-Za-z0-9_.:-]{1,120}$ ]]
}

ACTION="${1:-}"
shift || true

case "$ACTION" in
  snapshot)
    SNAPNAME="${1:-}"
    shift || true

    valid_snapname "$SNAPNAME" || die "invalid snapshot name: $SNAPNAME"
    [[ "$#" -ge 1 ]] || die "no zvol specified"
    [[ "$#" -le 8 ]] || die "too many zvols"

    SNAPSHOTS=()

    for DS in "$@"; do
      is_allowed "$DS" || die "dataset not allowed: $DS"
      /sbin/zfs list -H -t volume -o name "$DS" >/dev/null 2>&1 || die "zvol not found: $DS"

      FULLSNAP="${DS}@${SNAPNAME}"

      if /sbin/zfs list -H -t snapshot -o name "$FULLSNAP" >/dev/null 2>&1; then
        die "snapshot already exists: $FULLSNAP"
      fi

      SNAPSHOTS+=("$FULLSNAP")
    done

    /sbin/zfs snapshot "${SNAPSHOTS[@]}"
    /sbin/zfs hold sqlsnap "${SNAPSHOTS[@]}"

    printf '{"status":"ok","snapshots":['
    SEP=""
    for S in "${SNAPSHOTS[@]}"; do
      printf '%s"%s"' "$SEP" "$S"
      SEP=","
    done
    printf ']}\n'
    ;;

  list)
    /sbin/zfs list -H -t snapshot -o name -r sqlpool | grep '@sql_' || true
    ;;

  *)
    die "usage: sql-zfs-helper snapshot SNAPNAME ZVOL [ZVOL...]"
    ;;
esac
EOF

chown root:root /usr/local/sbin/sql-zfs-helper
chmod 750 /usr/local/sbin/sql-zfs-helper

We only allow the helper through sudo:

cat >/etc/sudoers.d/sql-zfs-helper <<'EOF'
sqlsnap ALL=(root) NOPASSWD: /usr/local/sbin/sql-zfs-helper *
EOF

chmod 440 /etc/sudoers.d/sql-zfs-helper
visudo -cf /etc/sudoers.d/sql-zfs-helper

We install the FastAPI API:

python3 -m venv /opt/sql-zfs-api/venv
/opt/sql-zfs-api/venv/bin/pip install fastapi "uvicorn[standard]"

We create the application file:

cat >/opt/sql-zfs-api/app.py <<'EOF'
import os
import re
import json
import socket
import secrets
import subprocess
from datetime import datetime, timezone
from fastapi import FastAPI, Header, HTTPException
from pydantic import BaseModel, Field

API_KEY = os.environ.get("SQL_ZFS_API_KEY", "")
ALLOW_FILE = "/etc/sql-zfs-api/allowed-zvols"
SNAP_RE = re.compile(r"^[A-Za-z0-9_.:-]{1,120}$")

app = FastAPI(title="SQL ZFS Snapshot API", version="1.0.0")


class SnapshotRequest(BaseModel):
    database: str = Field(..., min_length=1, max_length=128)
    vmid: int = 302
    snapname: str = Field(..., min_length=1, max_length=120)
    zvols: list[str] = Field(..., min_length=1, max_length=8)


def load_allowed_zvols() -> set[str]:
    with open(ALLOW_FILE, "r", encoding="utf-8") as f:
        return {
            line.strip()
            for line in f
            if line.strip() and not line.strip().startswith("#")
        }


def check_api_key(x_sqlsnap_key: str | None) -> None:
    if not API_KEY:
        raise HTTPException(status_code=500, detail="API key not configured")

    if not x_sqlsnap_key:
        raise HTTPException(status_code=401, detail="missing API key")

    if not secrets.compare_digest(x_sqlsnap_key, API_KEY):
        raise HTTPException(status_code=403, detail="invalid API key")


@app.get("/health")
def health():
    return {
        "status": "ok",
        "host": socket.gethostname(),
        "utc": datetime.now(timezone.utc).isoformat(),
    }


@app.post("/v1/sql-zfs/snapshot")
def create_snapshot(
    req: SnapshotRequest,
    x_sqlsnap_key: str | None = Header(default=None, alias="x-sqlsnap-key"),
):
    check_api_key(x_sqlsnap_key)

    if not SNAP_RE.fullmatch(req.snapname):
        raise HTTPException(status_code=400, detail="invalid snapname")

    allowed = load_allowed_zvols()

    for zvol in req.zvols:
        if zvol not in allowed:
            raise HTTPException(status_code=403, detail=f"zvol not allowed: {zvol}")

    cmd = [
        "sudo",
        "/usr/local/sbin/sql-zfs-helper",
        "snapshot",
        req.snapname,
        *req.zvols,
    ]

    try:
        completed = subprocess.run(
            cmd,
            text=True,
            stdout=subprocess.PIPE,
            stderr=subprocess.PIPE,
            timeout=30,
            check=False,
        )
    except subprocess.TimeoutExpired:
        raise HTTPException(status_code=504, detail="zfs snapshot timeout")

    if completed.returncode != 0:
        raise HTTPException(
            status_code=500,
            detail={
                "error": completed.stderr.strip(),
                "stdout": completed.stdout.strip(),
            },
        )

    snapshots = [f"{zvol}@{req.snapname}" for zvol in req.zvols]

    return {
        "status": "ok",
        "database": req.database,
        "vmid": req.vmid,
        "snapname": req.snapname,
        "snapshots": snapshots,
        "media_description": "zfs|" + socket.gethostname() + "|" + ";".join(snapshots),
    }
EOF

chown -R root:root /opt/sql-zfs-api
chmod 755 /opt/sql-zfs-api
chmod 644 /opt/sql-zfs-api/app.py

We configure and generate the key:

APIKEY="$(openssl rand -hex 32)"
echo "$APIKEY"

We create the environment file:

cat >/etc/sql-zfs-api/sql-zfs-api.env <<EOF
SQL_ZFS_API_KEY=$APIKEY
EOF

chown root:root /etc/sql-zfs-api/sql-zfs-api.env
chmod 600 /etc/sql-zfs-api/sql-zfs-api.env

We need to save the generated key.

Next, we enable HTTPS. SQL Server sp_invoke_external_rest_endpoint calls HTTPS endpoints, and the documentation specifies that only HTTPS endpoints with TLS are supported.

openssl req -x509 -newkey rsa:4096 -sha256 -days 360 -nodes \
  -keyout /etc/sql-zfs-api/tls.key \
  -out /etc/sql-zfs-api/tls.crt \
  -subj "/CN=promox1" \
  -addext "subjectAltName=DNS:promox1,IP:192.168.1.110"

chown root:sqlsnap /etc/sql-zfs-api/tls.key /etc/sql-zfs-api/tls.crt
chmod 640 /etc/sql-zfs-api/tls.key
chmod 644 /etc/sql-zfs-api/tls.crt

The /etc/sql-zfs-api/tls.crt certificate must be imported into the Windows trusted root certification authorities on the SQL Server side. Otherwise, the HTTPS call may fail.

We create the systemd service:

cat >/etc/systemd/system/sql-zfs-api.service <<'EOF'
[Unit]
Description=SQL Server to ZFS Snapshot API
After=network-online.target
Wants=network-online.target

[Service]
User=sqlsnap
Group=sqlsnap
WorkingDirectory=/opt/sql-zfs-api
EnvironmentFile=/etc/sql-zfs-api/sql-zfs-api.env
ExecStart=/opt/sql-zfs-api/venv/bin/uvicorn app:app --host 0.0.0.0 --port 8443 --ssl-keyfile /etc/sql-zfs-api/tls.key --ssl-certfile /etc/sql-zfs-api/tls.crt
Restart=on-failure
RestartSec=3

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable --now sql-zfs-api
systemctl status sql-zfs-api

We check the status of our API:

It is possible to call the API in PowerShell using Invoke-RestMethod with PowerShell 7:

$headers = @{
"Content-Type"  = "application/json"
"x-sqlsnap-key" = "MyKey"
}

$body = @{
database = "StackOverflow"
vmid     = 302
snapname = "StackOverflow_test010"
zvols    = @("sqlpool/pve/vm-302-disk-0")
} | ConvertTo-Json -Depth 5

Invoke-RestMethod `
-Uri "https://192.168.1.110:8443/v1/sql-zfs/snapshot" `
-Method Post `
-Headers $headers `
-Body $body `
-ContentType "application/json" `
-SkipCertificateCheck

This gives:

A certificate was generated on Proxmox and it needs to be imported on the SQL Server host. In my case, it was located here:

I then imported it on Windows Server:

For testing purposes, I created something simple. On the SQL Server side, we can create a database that will be used to store our future stored procedure. This procedure will allow us to interact with the API. In my case, I created a database called dbi_tools:

This database will contain a credential. In our case, the DATABASE SCOPED CREDENTIAL is used to securely store the authentication information required to call the REST API from SQL Server. This allows us, for example, to protect the API key:

USE [dbi_tools]
GO

IF NOT EXISTS (
    SELECT 1
    FROM sys.symmetric_keys
    WHERE name = '##MS_DatabaseMasterKey##'
)
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyStrongPassword_%99';
END
GO

CREATE DATABASE SCOPED CREDENTIAL [https://192.168.1.110:8443/v1/sql-zfs/snapshot]
WITH
    IDENTITY = 'HTTPEndpointHeaders',
    SECRET = '{"x-sqlsnap-key":"MyAPIKey"}';
GO

We then create a stored procedure to encapsulate the code used to call the API:

USE dbi_tools;
GO

CREATE OR ALTER PROCEDURE dbo.usp_BackupDatabase_WithZfsSnapshot
    @DatabaseName sysname,
    @BackupDirectory nvarchar(4000) = N'D:\Backups\'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Url nvarchar(4000) =
        N'https://192.168.1.110:8443/v1/sql-zfs/snapshot';

    DECLARE @Vmid int = 302;

    DECLARE @ZvolsJson nvarchar(max) =
        N'["sqlpool/pve/vm-302-disk-0"]';

    DECLARE @Stamp varchar(20) =
        REPLACE(REPLACE(CONVERT(varchar(19), SYSUTCDATETIME(), 126), '-', ''), ':', '') + 'Z';

    DECLARE @SafeDbName nvarchar(128) =
        REPLACE(REPLACE(REPLACE(@DatabaseName, N' ', N'_'), N'[', N''), N']', N'');

    DECLARE @SnapName nvarchar(128) =
        CONCAT(N'sql_', @SafeDbName, N'_', @Stamp);

    DECLARE @BackupFile nvarchar(4000) =
        CONCAT(@BackupDirectory, N'\', @SafeDbName, N'_', @Stamp, N'.bkm');

    DECLARE @Payload nvarchar(max) =
    (
        SELECT
            @DatabaseName AS [database],
            @Vmid AS [vmid],
            @SnapName AS [snapname],
            JSON_QUERY(@ZvolsJson) AS [zvols]
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );

    DECLARE @ReturnCode int;
    DECLARE @Response nvarchar(max);
    DECLARE @SnapshotList nvarchar(max);

    SELECT @SnapshotList =
        STRING_AGG(CONCAT([value], N'@', @SnapName), N';')
    FROM OPENJSON(@ZvolsJson);

    DECLARE @MediaDescription nvarchar(max) =
        CONCAT(N'zfs|promox1|', @SnapshotList);

    DECLARE @Sql nvarchar(max);

    BEGIN TRY
        SET @Sql =
            N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) +
            N' SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;';

        EXEC sys.sp_executesql @Sql;

        EXEC @ReturnCode = sys.sp_invoke_external_rest_endpoint
            @url = @Url,
            @method = N'POST',
            @headers = N'{"Content-Type":"application/json","Accept":"application/json"}',
            @payload = @Payload,
            @credential = [https://192.168.1.110:8443/v1/sql-zfs/snapshot],
            @timeout = 30,
            @response = @Response OUTPUT;

        IF @ReturnCode <> 0
        BEGIN
            DECLARE @Err nvarchar(max) =
                CONCAT(N'ZFS snapshot API failed. ReturnCode=', @ReturnCode, N' Response=', @Response);
            THROW 51001, @Err, 1;
        END;

        SET @Sql =
            N'BACKUP DATABASE ' + QUOTENAME(@DatabaseName) + N'
              TO DISK = @BackupFile
              WITH METADATA_ONLY,
                   FORMAT,
                   MEDIANAME = @MediaName,
                   MEDIADESCRIPTION = @MediaDescription,
                   NAME = @BackupName;';

        EXEC sys.sp_executesql
            @Sql,
            N'@BackupFile nvarchar(4000),
              @MediaName nvarchar(128),
              @MediaDescription nvarchar(max),
              @BackupName nvarchar(128)',
            @BackupFile = @BackupFile,
            @MediaName = @SnapName,
            @MediaDescription = @MediaDescription,
            @BackupName = @SnapName;

        SELECT
            @DatabaseName AS database_name,
            @SnapName AS zfs_snapshot_name,
            @SnapshotList AS zfs_snapshots,
            @BackupFile AS metadata_backup_file,
            @MediaDescription AS media_description,
            @Response AS api_response;
    END TRY
    BEGIN CATCH
        IF DATABASEPROPERTYEX(@DatabaseName, 'IsDatabaseSuspendedForSnapshotBackup') = 1
        BEGIN
            SET @Sql =
                N'ALTER DATABASE ' + QUOTENAME(@DatabaseName) +
                N' SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF;';

            EXEC sys.sp_executesql @Sql;
        END;

        THROW;
    END CATCH
END;
GO

We then call the stored procedure:

EXEC dbi_tools.dbo.usp_BackupDatabase_WithZfsSnapshot
    @DatabaseName = N'StackOverflow',
    @BackupDirectory = N'D:\Backups\';

The backup was generated :

sp_invoke_external_rest_endpoint

Thank you. Amine Haloui

L’article SQL Server Snapshot Backup and Restore with Proxmox ZFS – REST API with SQL Server 2025 (3/3) est apparu en premier sur dbi Blog.

In the previous section, we discussed the drawbacks of running the commands manually. Indeed, the manual process was taking too much time and could directly impact the database state while the freeze was occurring.

To address this issue, it is possible to automate the solution with PowerShell. The idea is to automate the different operations involved in the snapshot backup and restore process.

We will use two scripts:

• One script to perform the backups and create the snapshots.
• One script to perform the restores.

Here is how the backup process works:

• We connect to the corresponding SQL Server instance.
• We change the state of the database using ALTER DATABASE … SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON. At this point, the I/Os are frozen.
• We connect to the hypervisor through SSH.
• We create the snapshot.
• We back up the database using BACKUP DATABASE … WITH METADATA_ONLY.
• We change the state of the database using ALTER DATABASE … SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF. At this point, the I/Os are unfrozen.

Here is the code used to perform the backup:

param(
    [string]$SqlInstance = "VM-WS25-SQL2",
    [string]$Database    = "StackOverflow",
    [string]$BackupDir   = "D:\Backups",
    [string]$PveHost     = "192.168.1.110",
    [string]$PveUser     = "MyUser",
    [string[]]$Zvols     = @("sqlpool/pve/vm-302-disk-0")
)

$Timestamp = Get-Date -Format "yyyyMMddTHHmmss"
$SnapName  = "sql_${Database}_${Timestamp}"

$DbSafe = $Database.Replace("]", "]]")
$BackupFile = Join-Path $BackupDir "${Database}_${Timestamp}.bkm"

$ZfsSnapshots = $Zvols | ForEach-Object { "$_@$SnapName" }
$ZfsSnapshotArgs = $ZfsSnapshots -join " "

$MediaDescription = "zfs|$PveHost|$ZfsSnapshotArgs"

$BackupFileSql = $BackupFile.Replace("'", "''")
$MediaSql = $MediaDescription.Replace("'", "''")

$connString = "Server=$SqlInstance;Database=master;Integrated Security=True;TrustServerCertificate=True;Application Name=ZFS-TSQL-Snapshot;"
$conn = New-Object System.Data.SqlClient.SqlConnection $connString

function Invoke-SqlNonQuery {
    param([string]$Sql)

    $cmd = $conn.CreateCommand()
    $cmd.CommandTimeout = 0
    $cmd.CommandText = $Sql
    [void]$cmd.ExecuteNonQuery()
}

try {
    $conn.Open()

    Write-Host "Freezing SQL database writes..."
    Invoke-SqlNonQuery "ALTER DATABASE [$DbSafe] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;"

    Write-Host "Taking ZFS snapshot on Proxmox..."
    ssh "$PveUser@$PveHost" "zfs snapshot $ZfsSnapshotArgs && zfs hold sqlsnap $ZfsSnapshotArgs"

    if ($LASTEXITCODE -ne 0) {
        throw "ZFS snapshot failed on $PveHost"
    }

    Write-Host "Writing SQL metadata backup..."

    Invoke-SqlNonQuery @"
BACKUP DATABASE [$DbSafe]
TO DISK = N'$BackupFileSql'
WITH METADATA_ONLY,
     MEDIADESCRIPTION = N'$MediaSql',
     NAME = N'$SnapName';
"@

    Write-Host "Snapshot backup completed:"
    Write-Host "  Snapshot: $ZfsSnapshotArgs"
    Write-Host "  Metadata: $BackupFile"
}
catch {
    Write-Warning $_

    try {
        Write-Warning "Attempting to unfreeze SQL database..."
        Invoke-SqlNonQuery "ALTER DATABASE [$DbSafe] SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF;"
    }
    catch {
        Write-Warning "Could not unfreeze cleanly. Check SQL Server error log."
    }

    throw
}
finally {
    $conn.Close()
}

Here is how the restore process works:

• We connect to the corresponding SQL Server instance.
• We take the database offline.
• The volume dedicated to the StackOverflow database is taken offline.
• We connect to the hypervisor through SSH.
• We roll back the corresponding snapshot.
• We restore the database using the corresponding backup, which was created at the same time as the snapshot.

Here is the code used to perform the restore:

param(
    [string]$SqlInstance = "VM-WS25-SQL2",
    [string]$Database    = "StackOverflow",
    [string]$BackupFile  = "D:\Backups\StackOverflow_20260514T122642.bkm",
    [string]$SnapName    = "sql_StackOverflow_20260514T122642",
    [string]$PveHost     = "192.168.1.110",
    [string]$PveUser     = "MyUser",
    [string[]]$Zvols     = @("sqlpool/pve/vm-302-disk-0"),
    [string[]]$DatabaseDriveLetters = @("T"),
    [switch]$NoRecovery
)

$ErrorActionPreference = "Stop"

function Assert-SafeName {
    param(
        [string]$Value,
        [string]$Name,
        [string]$Pattern
    )

    if ($Value -notmatch $Pattern) {
        throw "$Name contained not allowed characters : $Value"
    }
}

function Normalize-DriveLetter {
    param([string]$DriveLetter)

    $letter = $DriveLetter.Trim().TrimEnd(":").ToUpperInvariant()

    if ($letter -notmatch '^[A-Z]$') {
        throw "Drive letter invalid : $DriveLetter"
    }

    return $letter
}

function Get-DiskForDriveLetter {
    param([string]$DriveLetter)

    $letter = Normalize-DriveLetter $DriveLetter

    $partition = Get-Partition -DriveLetter $letter -ErrorAction Stop
    $disk = $partition | Get-Disk -ErrorAction Stop

    return [pscustomobject]@{
        DriveLetter = $letter
        DiskNumber  = [int]$disk.Number
        IsOffline   = [bool]$disk.IsOffline
        FriendlyName = $disk.FriendlyName
        Size        = $disk.Size
    }
}

function Invoke-SshChecked {
    param([string]$Command)

    Write-Host "SSH $PveUser@$PveHost :: $Command"

    & ssh "$PveUser@$PveHost" "$Command"

    if ($LASTEXITCODE -ne 0) {
        throw "SSH command failed with code $LASTEXITCODE : $Command"
    }
}

function New-SqlConnection {
    $connString = "Server=$SqlInstance;Database=master;Integrated Security=True;TrustServerCertificate=True;Application Name=ZFS-TSQL-Restore-NoVmRestart;"
    return New-Object System.Data.SqlClient.SqlConnection $connString
}

function Invoke-SqlNonQuery {
    param([string]$Sql)

    $conn = New-SqlConnection

    try {
        $conn.Open()
        $cmd = $conn.CreateCommand()
        $cmd.CommandTimeout = 0
        $cmd.CommandText = $Sql
        [void]$cmd.ExecuteNonQuery()
    }
    finally {
        $conn.Close()
    }
}

function Invoke-SqlScalar {
    param([string]$Sql)

    $conn = New-SqlConnection

    try {
        $conn.Open()
        $cmd = $conn.CreateCommand()
        $cmd.CommandTimeout = 0
        $cmd.CommandText = $Sql
        return $cmd.ExecuteScalar()
    }
    finally {
        $conn.Close()
    }
}

function Set-DatabaseDisksOffline {
    param([object[]]$DiskInfos)

    $offlinedByScript = @()

    foreach ($diskInfo in ($DiskInfos | Sort-Object DiskNumber -Unique)) {
        if ($diskInfo.IsOffline) {
            Write-Host "Disque $($diskInfo.DiskNumber) déjà offline. Lecteur $($diskInfo.DriveLetter):"
            continue
        }

        Write-Host "Taking the Windows disk offline $($diskInfo.DiskNumber), drive $($diskInfo.DriveLetter):"
        Set-Disk -Number $diskInfo.DiskNumber -IsOffline $true

        $offlinedByScript += $diskInfo
    }

    return $offlinedByScript
}

function Set-DatabaseDisksOnline {
    param([object[]]$DiskInfos)

    foreach ($diskInfo in ($DiskInfos | Sort-Object DiskNumber -Unique)) {
        Write-Host "Bringing the Windows disk back online. $($diskInfo.DiskNumber), drive $($diskInfo.DriveLetter):"
        Set-Disk -Number $diskInfo.DiskNumber -IsOffline $false
    }

    Write-Host "Update-HostStorageCache..."
    Update-HostStorageCache
}

Assert-SafeName -Value $SnapName -Name "SnapName" -Pattern '^[A-Za-z0-9_.:-]{1,160}$'

foreach ($zvol in $Zvols) {
    Assert-SafeName -Value $zvol -Name "Zvol" -Pattern '^[A-Za-z0-9_.:/-]{1,240}$'
}

$DbQuoted = "[" + $Database.Replace("]", "]]") + "]"
$DbLiteral = $Database.Replace("'", "''")
$BackupFileSql = $BackupFile.Replace("'", "''")

$ZfsSnapshots = $Zvols | ForEach-Object { "$_@$SnapName" }
$ZfsSnapshotArgs = ($ZfsSnapshots | ForEach-Object { "'$_'" }) -join " "

$RecoveryOption = if ($NoRecovery) { "NORECOVERY" } else { "RECOVERY" }

$DatabaseDiskInfos = @()
$DisksOfflinedByScript = @()

Write-Host ""
Write-Host "Restore SQL Server from a ZFS snapshot, without restarting the VM"
Write-Host "SQL Instance : $SqlInstance"
Write-Host "Database     : $Database"
Write-Host "BackupFile   : $BackupFile"
Write-Host "DB volumes   : $($DatabaseDriveLetters -join ', ')"
Write-Host "Snapshots    :"
$ZfsSnapshots | ForEach-Object { Write-Host "  $_" }
Write-Host ""

try {
    Write-Host "Checking ZFS snapshots..."
    Invoke-SshChecked "zfs list -H -t snapshot -o name $ZfsSnapshotArgs >/dev/null"

    Write-Host "Identifying Windows disks containing SQL Server files..."
    foreach ($driveLetter in $DatabaseDriveLetters) {
        $diskInfo = Get-DiskForDriveLetter $driveLetter
        $DatabaseDiskInfos += $diskInfo

        Write-Host "Drive $($diskInfo.DriveLetter): -> Windows disk $($diskInfo.DiskNumber) [$($diskInfo.FriendlyName)]"
    }

    $backupDrive = $null
    if ($BackupFile -match '^([A-Za-z]):\\') {
        $backupDrive = Normalize-DriveLetter $Matches[1]

        try {
            $backupDiskInfo = Get-DiskForDriveLetter $backupDrive
            $targetDiskNumbers = @($DatabaseDiskInfos | ForEach-Object { $_.DiskNumber } | Select-Object -Unique)

            if ($targetDiskNumbers -contains $backupDiskInfo.DiskNumber) {
                throw @"
The backup file $BackupFile is located on drive $backupDrive, which is on the same Windows disk as the SQL Server data volume.
Taking the data disk offline would make the .bkm file inaccessible, and a rollback could also make the .bkm file disappear.
Move the .bkm file to C:, a network share, or another disk that is not rolled back.
"@
            }
        }
        catch {
            throw
        }
    }

    Write-Host "Checking whether the SQL Server database exists..."
    $DbExists = Invoke-SqlScalar "SELECT CASE WHEN DB_ID(N'$DbLiteral') IS NULL THEN 0 ELSE 1 END;"

    if ($DbExists -eq 1) {
        Write-Host "Taking database $Database OFFLINE..."
        Invoke-SqlNonQuery @"
ALTER DATABASE $DbQuoted SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE $DbQuoted SET OFFLINE WITH ROLLBACK IMMEDIATE;
"@
    }
    else {
        Write-Host "Database $Database does not exist in SQL Server. Continuing with disk offline and ZFS rollback."
    }

    Write-Host "Taking Windows disks containing MDF/LDF files offline..."
    $DisksOfflinedByScript = Set-DatabaseDisksOffline -DiskInfos $DatabaseDiskInfos

    Write-Host "Rolling back ZFS snapshot..."
    $RollbackCommands = ($ZfsSnapshots | ForEach-Object { "zfs rollback -r '$_'" }) -join "; "
    Invoke-SshChecked "set -e; $RollbackCommands"

    Write-Host "Bringing Windows disks back online..."
    Set-DatabaseDisksOnline -DiskInfos $DisksOfflinedByScript
    $DisksOfflinedByScript = @()

    Write-Host "Short pause to let Windows and SQL Server detect the restored disk state..."
    Start-Sleep -Seconds 5

    Write-Host "Restoring SQL Server metadata-only backup..."

    $RestoreSql = @"
RESTORE DATABASE $DbQuoted
FROM DISK = N'$BackupFileSql'
WITH METADATA_ONLY,
     REPLACE,
     $RecoveryOption;
"@

    Invoke-SqlNonQuery $RestoreSql

    if (-not $NoRecovery) {
        Write-Host "Setting database back to MULTI_USER..."
        Invoke-SqlNonQuery @"
ALTER DATABASE $DbQuoted SET MULTI_USER;
"@
    }

    Write-Host ""
    Write-Host "Restore completed."
    Write-Host "Database : $Database"
    Write-Host "Snapshot : $SnapName"
    Write-Host "Backup   : $BackupFile"
}
catch {
    Write-Warning "Restore failed: $_"

    if ($DisksOfflinedByScript.Count -gt 0) {
        try {
            Write-Warning "Attempting to bring disks offlined by the script back online..."
            Set-DatabaseDisksOnline -DiskInfos $DisksOfflinedByScript
            $DisksOfflinedByScript = @()
        }
        catch {
            Write-Warning "Unable to automatically bring the disks back online. Check with Get-Disk."
        }
    }

    try {
        $DbExistsAfterError = Invoke-SqlScalar "SELECT CASE WHEN DB_ID(N'$DbLiteral') IS NULL THEN 0 ELSE 1 END;"

        if ($DbExistsAfterError -eq 1 -and -not $NoRecovery) {
            Write-Warning "Attempting to set the database back ONLINE/MULTI_USER..."
            Invoke-SqlNonQuery @"
ALTER DATABASE $DbQuoted SET ONLINE;
ALTER DATABASE $DbQuoted SET MULTI_USER;
"@
        }
    }
    catch {
        Write-Warning "Unable to automatically set the database back ONLINE/MULTI_USER."
    }

    throw
}

We start the backup process:

We verify that the snapshot is present:

We verify that the backup is present:

We drop the StackOverflow database:

We start the restore process:

The database is available again. The restore took only a few seconds for a database of approximately 200 GB.

In my case, the solution is executed from the SQL Server itself. Ideally, it should rather be hosted on another server or client machine. We could also imagine running these scripts from a scheduler such as RedDeck, for example.

During the database restore, the database is switched to SINGLE_USER mode. This could be an issue if the applications using the database reconnect very frequently. A better approach would probably be to explicitly terminate the active sessions using the KILL command.

We have also not yet covered the use of a REST API.

Thank you. Amine Haloui

L’article SQL Server Snapshot Backup and Restore with Proxmox ZFS – Powershell implementation (2/3) est apparu en premier sur dbi Blog.

We are currently working with clients on migrations to SQL Server 2022 and SQL Server 2025. During a discussion with one client, we reviewed some of the benefits introduced in the latest SQL Server 2022 and 2025 releases.

Among the available features, starting with SQL Server 2022, we have:

• T-SQL snapshot backup : https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-transact-sql-snapshot-backup?view=sql-server-ver17

Starting with SQL Server 2025:

• REST API Call through sp_invoke_external_rest_endpoint : https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql?view=sql-server-ver17&tabs=request-headers

The customer’s environment consists of a very large number of instances, some of which host very large SQL Server databases. In this customer’s case, we are referring to a database of approximately 6–7 TB, configured for high availability using Always On Availability Groups. For this database, backups take around two hours, and restores take slightly longer.

In addition, the customer has a Pure Storage array.

We explained to the customer that it is possible to use certain SQL Server 2025 features together with their Pure Storage array to perform snapshots and restores very quickly.

In summary, the process consists of performing the following operations:

• Change the database state to suspend writes.
• Create the snapshot using the storage system.
• Perform a backup using the BACKUP DATABASE MyDB WITH METADATA_ONLY command to indicate that a snapshot has been taken.

Reference: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-transact-sql-snapshot-backup?view=sql-server-ver17

However, the customer raised several interesting questions, which, reading between the lines, can be summarized as follows:

• Can this also be applied to PostgreSQL?
• Are we dependent on Pure Storage to achieve this?

Several articles have been published about the implementation of this process between SQL Server and Pure Storage including the following one:

• https://www.nocentino.com/posts/2025-05-19-t-sql-rest-api-integration-in-sql-server-2025-streamlining-t-sql-snapshot-backups

In my opinion, it is possible to reproduce this operating model with other systems. In my case, we will use Proxmox and ZFS.

ZFS pool provides fast, storage-level, copy-on-write snapshots with minimal space overhead. This makes it well suited for SQL Server snapshot backups, where the database writes are briefly suspended while the underlying virtual disk is captured. ZFS also allows precise rollback or cloning of a snapshot, which is useful for both restore testing and recovery scenarios.

On Proxmox, it integrates naturally with VM disks, making it a practical alternative to enterprise storage snapshot platforms.

The environment consists of a server and two disks: one disk used to store the VMs, and a 1 TB Samsung T7 disk that will be used to create our ZFS pool.

We identity the path of the related volume (Samsung T7) :

for d in /dev/disk/by-id/*; do
  [ "$(readlink -f "$d")" = "/dev/sda" ] && echo "$d"
done

We create the pool. Everything stored in the disk will be erased :

DISK="/dev/disk/by-id/usb-Samsung_PSSD_T7_S6TWNJ0T300328F-0:0"

wipefs -a "$DISK"
sgdisk --zap-all "$DISK"
zpool create \
  -o ashift=12 \
  -o autotrim=on \
  -O compression=lz4 \
  -O atime=off \
  -O xattr=sa \
  -O acltype=posixacl \
  -m /mnt/sqlpool \
  sqlpool "$DISK"

Then we create a Proxmox dataset for the VM disks:

zfs create sqlpool/pve

We add it to proxmox:

pvesm add zfspool sql-zfs \
  --pool sqlpool/pve \
  --content images,rootdir \
  --sparse 1

We check the pool:

zpool status sqlpool

zfs list

pvesm status
pool: sqlpool
state: ONLINE

config:
       NAME                                       STATE     READ WRITE CKSUM
       sqlpool                                    ONLINE       0     0     0
       usb-Samsung_PSSD_T7_S6TWNJ0T300328F-0:0    ONLINE       0     0     0

errors: No known data errors

NAME          USED  AVAIL  REFER  MOUNTPOINT
sqlpool       636K   899G    96K  /mnt/sqlpool
sqlpool/pve    96K   899G    96K  /mnt/sqlpool/pve

Name             Type     Status     Total (KiB)      Used (KiB) Available (KiB)        %
local             dir     active        98497780        42429080        51019152   43.08%
local-lvm     lvmthin     active      3746553856       285112748      3461441107    7.61%
sql-zfs       zfspool     active       942931428              96       942931332    0.00%

My VM ID is 302 and we have to add the virtual disk into the ZFS pool:

VMID=302
qm set "$VMID" --agent enabled=1
qm set "$VMID" --scsihw virtio-scsi-single
qm set "$VMID" --scsi1 sql-zfs:700,cache=none,discard=on,iothread=1,ssd=1

Be carefull to the scsi ID. You may overwrite a used volume.

Once the pool created we have something like this :

On the virtual machine side, I have 3 disks :

• 1 for my virtual machine (for Windows Server)
• 1 for SQL Server
• 1 linked to the ZFS pool to store the user database (the StackOverflow database)

The virtual machine used for the tests runs with:

• Windows Server 2025 Standard Edition
• SQL Server 2025 Enterprise Developer Edition

The mounted zvol is represented by the Databases (T:) volume. Most of the files related to the SQL Server installation are stored on the SQL (D:) volume while the StackOverflow database is located on the Databases (T:) volume.

Here is how we will proceed to create a snapshot and then restore the database:

• ALTER DATABASE [StackOverflow] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
• Create the snapshot using the zfs snapshot command.
• Run BACKUP DATABASE [StackOverflow] … WITH METADATA_ONLY.

To avoid confusion and to be able to link the snapshot to the backup, we will include the snapshot name in the MEDIADESCRIPTION clause.

Here are the corresponding commands to create the snapshot:

ALTER DATABASE [StackOverflow] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

We perform the snapshot:

zfs snapshot sqlpool/pve/vm-302-disk-0@StackOverflow_11052026_235500

In the same session as the ALTER DATABASE command, we perform a backup:

BACKUP DATABASE [StackOverflow]
TO DISK = N'D:\Backups\StackOverflow_11052026_235500.bkm'
WITH METADATA_ONLY, MEDIADESCRIPTION = N'zfs|proxmox1|sqlpool/pve/vm-302-disk-0@StackOverflow_11052026_235500';

The error log shows the following:

We verify that the snapshot has been successfully created:

And the SQL backup :

We now need to be able to restore the database. Before doing so, we can delete a few tables to verify that the database has been restored as expected. We deleted most of the tables, leaving only three:

To perform the restore, we will follow these steps:

• Take the database offline.
• Rollback the snapshot using the zfs rollback command.
• Restore the database using the SQL backup created earlier.

This is done using the following commands:

ALTER DATABASE [StackOverflow] SET OFFLINE WITH ROLLBACK IMMEDIATE;

Snapshot restore:

zfs rollback -r sqlpool/pve/vm-302-disk-0@StackOverflow_13052026_230000

Database restore:

RESTORE DATABASE [StackOverflow]
FROM DISK = N'D:\Backups\StackOverflow_13052026_230000.bkm'
WITH METADATA_ONLY, REPLACE, NORECOVERY;

RESTORE DATABASE [StackOverflow] WITH RECOVERY;

We were able to restore our database in less than one second, even though it is approximately 207 GB in size.

The process is manual, and we need to switch between running commands in SQL Server and performing the snapshot/restore operations in Proxmox. This freezes the database for a certain amount of time. During that period, connected applications could generate errors or timeouts.

The solution to this problem would be to automate the process using PowerShell, for example.

While writing this blog post, I omitted two points:

• When the database is deleted, it is necessary to take the volume dedicated to the StackOverflow database, Databases (D:), offline. Indeed, When you run a DROP DATABASE, SQL Server deletes the files from disk, and the database no longer exists. Then, if you perform a zfs rollback while Windows still sees the disk as online, you are effectively changing the disk “under Windows feet” Windows may keep the previous NTFS state cached: an empty directory, MFT information, file handles, volume metadata, and so on. As a result, the ZFS rollback may have completed successfully, but Windows does not properly refresh its view of the disk.
• We did not make any calls to a REST API. Indeed, this functionality does not exist in my case, but it is possible to implement it.

Thank you. Amine Haloui

L’article SQL Server Snapshot Backup and Restore with Proxmox ZFS (1/3) est apparu en premier sur dbi Blog.

One of our clients encountered blocking during their daily data load. The process loads several million rows and then performs an ALTER TABLE … SWITCH operation into a partitioned table. This operation usually takes some time, but in this case it became blocked.

Initially, I did not have access to much information. The only element I received from the client was a extract of the output from the sp_WhoIsActive procedure.

Based on this extract, we were able to perform a first-level analysis:

A Python session executed a query against MyTable without applying a date filter. On a table containing approximately 244 million rows, this prevented proper partition elimination and forced SQL Server to read a much broader data set than necessary. Queries against partitioned tables only benefit from partition elimination when the predicate references the partitioning column without such a predicate, SQL Server may have to search or scan all partitions.

The Python session eventually became sleeping but remained with open_tran_count = 1. This is a typical sign of an unclosed transaction on the client side: autocommit disabled, cursor not closed, result set not fully consumed, connection returned to the pool without a rollback…

Session 146 then attempted to perform the partition TRUNCATE/SWITCH operation. However, TRUNCATE TABLE requires a schema modification lock, Sch-M, and ALTER TABLE … SWITCH also requires a Sch-M lock on both the source and target tables.

This Sch-M lock could not be acquired while session 167 was still referencing the object. SQL Server documents Sch-M as the lock required to modify the schema and to ensure that no other session is referencing the object. Once the Sch-M request from session 146 was queued, new read queries were also blocked behind it. Even NOLOCK would not avoid this issue: queries still acquire Sch-S locks during compilation and execution, and Sch-S and Sch-M locks block each other.

After some time, we were able to access the client’s environment. Query Store was enabled on the affected database, and an Extended Events session was configured on the SQL Server instance to track blocking.

Querying the Extended Events session provided detailed information about the blocking events that occurred, and we were able to identify the specific blocking issue reported by the client.

By looking more closely at this blocking issue, we found the following:

EXEC [STAGING_DB].[ETL].[sp_ETL_Exec]
    @ETL_StepIKs_List = '["Exec-[TARGET_DB].dbo.[SP_Load_TargetTable]"]',
    @StartAsJob = 0

Which is blocked by:

WITH position AS
(
    SELECT ...
    FROM [SOURCE_DB].[SCHEMA_NAME].[LARGE_PARTITIONED_TABLE]
    ...
)

<blocking-process>
    spid="167"
    status="sleeping"
    trancount="1"
    clientapp="python[version]"
    hostname="client-host-..."
    loginname="user_account"
    inputbuf="... WITH position AS ..."
</blocking-process>

However, the blocking report highlights an important point: session 167 was no longer actively executing the query at the time the report was captured:

• status = sleeping
• trancount = 1

However, by correlating this information with Query Store data, we were able to obtain additional details. By retrieving the corresponding query, we could better understand what was happening.

The blocking report also showed that session 146 was requesting a Sch-M lock, meaning a Schema Modification Lock. This is a strong lock required for operations such as TRUNCATE, ALTER TABLE, and partition SWITCH.

According to the data, session 146 waited for more than two hours, approximately 7,770,160 ms.

However, by correlating this information with Query Store data, we were able to obtain additional details. Specifically, by retrieving the query:

It was executed 30 times during the following time interval: 05-05-2026 from 2:00 PM to 3:00 PM. The average execution time was 49.1 seconds, with a maximum execution time of approximately 57 seconds. This represents a total of around 24 minutes of cumulative execution time over a one-hour period.

Based on this data, the issue was therefore not caused by the performance of the query itself, but rather by the state of session 167. Indeed, the session left a transaction open, with an open_tran_count of 1, thereby locking the corresponding objects and preventing other sessions from accessing them.

The observed blocking can likely be explained by a misconfiguration or misuse of the Python driver used to access SQL Server. The root session was a Python connection in a sleeping state, but with trancount = 1, which indicates that a transaction was still open even though the query was no longer actively running.

In this situation, SQL Server may continue to hold transaction-related locks even if the application appears to have completed its work.

If the Python driver was running with autocommit = 0, each SELECT statement could implicitly start a transaction that then had to be explicitly closed with a commit or rollback. If the cursor was not closed properly, the result set was not fully consumed, or a rollback was not issued before returning the connection to the pool, the session could remain open on the SQL Server side. This residual transaction likely prevented the related ETL process from acquiring the Sch-M lock required for the TRUNCATE or partition SWITCH operation.

As a result the ETL session was not the initial root cause. It was waiting for a lock held by an idle Python connection.

Next queries then accumulated behind the pending Sch-M lock request, creating the impression of a global outage.

Switching to autocommit = 1 significantly reduces this risk, because read operations are no longer tied to an open transaction by default. Finally, preventing parallel pipeline execution helps avoid amplifying the issue when a job is delayed.

Thank you. Amine Haloui

L’article When a Python driver configuration issue may cause blocking in SQL Server est apparu en premier sur dbi Blog.

Our client was experiencing issues after publishing a report that used Direct Query mode. Specifically, when the report was queried, the following error occurred:

Error :  We couldn’t connect to the Analysis Services server. Make sure you’ve entered the connection string correctly.

However, this issue did not occur in Power BI Desktop.

In Power BI, several data loading modes are available. Import mode loads data into the Power BI model, which usually provides faster performance and richer modeling capabilities. DirectQuery mode does not store the data in the model instead, each interaction sends queries to the source system in real time. Import is generally better for speed and flexibility, while DirectQuery is useful when data must stay in the source or remain near real-time. The trade-off is that DirectQuery depends more heavily on source performance, network latency, and source-system limitations.

At first glance, one might think that the corresponding report is trying to connect to an SSAS service and that there is a connectivity issue between Power BI Report Server and a SQL Server Analysis Services instance.

However, after reviewing the data source, there was no connection to SSAS:

We did not have this type of configuration:

The questions that arise

Why are we getting an error message even though the report is not trying to connect to a SQL Server Analysis Services instance?

Why is our client seeing this error message and unable to query the report?

By reviewing the Power BI Report Server logs, it was possible to see this type of message:

Failed to get CSDL. —> MsolapWrapper.MsolapWrapperException: Failure encountered while getting schema.

CannotRetrieveModelException: An error occurred while loading the model… Verify that the connection information is correct and that you have permissions to access the data source.

It is also possible to retrieve some information from the ExecutionLog3 table:

Indeed,  whenever a Power BI report is rendered or a scheduled refresh is executed, new entries are written to the ExecutionLog3 table. These entries can be queried through the ExecutionLog3 view in the Report Server catalog database. The ConceptualSchema event corresponds to a user viewing the report.

When querying the Event Viewer, it returned these errors at the time we tried to query the report:

We have two error messages that seem to point in two different directions. In reality, the first error messages are not very useful and appear because although the error message refers to Analysis Services, the report was not connecting to an external SSAS instance. Power BI Report Server uses an internal Analysis Services engine to load and query Power BI report models. Therefore, the error was raised by the internal PBIRS Analysis Services engine, not by a standalone SQL Server Analysis Services instance.

Power BI Report Server may report an Analysis Services-related error even when the report does not connect to an external SSAS instance. This is because PBIRS uses an internal Analysis Services engine to host and execute the Power BI semantic model behind the report. In DirectQuery mode, the data remains in SQL Server, but the report model, metadata, relationships, measures, and DAX queries are still processed through this internal engine.

When a user opens the report, PBIRS asks this local Analysis Services process to load the model and generate the queries sent to SQL Server.

Therefore, if the internal engine fails while loading the model, validating metadata, or connecting to the SQL Server data source, the error may mention Analysis Services. This does not mean that the report is connected to a standalone SSAS instance.

This was the second error that pointed us in the right direction to actually resolve the issue. After looking at it more closely, we started considering connection encryption and certificates. This problem is documented, and several solutions are available.

Indeed, the SQL Server instance queried to retrieve the data did not have a certificate issued by a trusted certificate authority. It was using a self-generated certificate.

This can lead to errors such as the ones mentioned above, or errors like the following:

Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the login process. Provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.

We had at least three options to resolve this issue:

• Change the connection mode to Import
• Install a certificate issued by a trusted certificate authority however this would represent a major change
• Create a new environment variable on the Power BI Report Server

The client chose the easiest solution to implement: creating the corresponding environment variable.

We then restarted the corresponding Power BI Report Server service and this resolved the issue.

https://learn.microsoft.com/en-us/power-bi/report-server/scheduled-refresh-troubleshoot

https://learn.microsoft.com/en-us/power-query/connectors/sql-server#sql-server-certificate-isnt-trusted-on-the-client-power-bi-desktop-or-on-premises-data-gateway

Thank you. Amine Haloui

L’article A Misleading SSAS Error in Power BI Report Server When Using DirectQuery Mode est apparu en premier sur dbi Blog.

SQLDay 2026 offers a full-day workshop program on 11 May 2026, before the main conference scheduled for 12–13 May 2026 in Wrocław, with onsite and online participation options depending on the session. The workshops cover several areas of the modern data platform: advanced BI, AI and MLOps, SQL performance tuning, PostgreSQL adoption, and Microsoft Fabric automation.

This workshop is designed for Power BI users who already know the basics of DAX but now need to solve more complex business problems. The focus is on moving from simple reports to reusable, efficient and business-oriented DAX patterns.

Participants will work on practical scenarios such as advanced slicer logic, hierarchical calculations, year-to-date reporting, visual calculations, cumulative totals, ranking, and relative-period analysis. The main objective is to extend the participant’s DAX toolbox and help them write expressions that are both more powerful and better performing

This Polish onsite workshop covers the complete lifecycle of machine learning models in Databricks. The goal is to show how to move from data preparation to training, automation, deployment and monitoring in a production-oriented environment.

The workshop focuses on the practical implementation of MLOps using Databricks and MLflow. Topics include AI/ML architecture, data pipelines, feature engineering, model training, deep learning, CI/CD, orchestration, model versioning and monitoring. It is mainly targeted at engineers, data scientists and architects who are already working with machine learning or planning to start.

This Polish onsite workshop focuses on building conversational and autonomous agents with Microsoft Copilot Studio. The format is highly practical, with most of the time dedicated to hands-on exercises.

Participants will build agents that automate business processes, use multimodal data, generate data-driven answers and connect to enterprise data sources. The workshop also covers Dataverse grounding, flows, plugins, actions, autonomous triggers, Responsible AI, moderation and access control. It is a good fit for participants who want to understand how Copilot Studio can be used beyond simple chatbot scenarios.

This workshop is focused on real-world SQL Server troubleshooting and refactoring. The starting point is familiar to many DBAs and developers: complex stored procedures, poor query patterns, blocking data modifications, bad use of CTEs, problematic window functions, indexed views, dynamic SQL, user-defined functions and execution plans that are difficult to understand.

The objective is not only to identify what is slow, but also to understand why it is slow and how to rewrite it properly. This session is especially relevant for people who regularly inherit problematic T-SQL code and need a structured way to fix it without guessing.

This workshop targets SQL Server professionals who need to add PostgreSQL to their technical scope. The context is clear: many organizations are adding PostgreSQL without immediately replacing SQL Server, which creates a need for people who understand both platforms.

The workshop compares the two database engines, explains the areas of overlap, and highlights the differences that can make PostgreSQL challenging for SQL Server users. It also covers tooling, documentation, cloud options and practical resources to support the learning path.

This onsite hands-on lab focuses on automation in Microsoft Fabric. The goal is to help participants automate the full lifecycle of a Fabric data platform, from design and setup to deployment and documentation.

The workshop covers platform setup using code and configuration scripts, metadata-driven ingestion, semantic model foundations, CI/CD with GitHub and Azure DevOps, Fabric CLI, REST APIs and the fabric-cicd Python library. The expected outcome is a more robust, scalable and repeatable approach to building Fabric solutions, with less manual work and lower operational risk.

The SQLDay 2026 workshop program is clearly oriented toward practical implementation. Each session addresses a common challenge faced by data teams: improving analytical models, industrializing AI, fixing complex SQL code, extending SQL Server skills to PostgreSQL, or automating a modern Microsoft Fabric platform.

The common thread is operational efficiency. These workshops are not only about learning features; they are about applying them in real environments, with constraints such as performance, maintainability, automation, governance and production readiness.

Thank you. Amine Haloui.

References :

L’article SQLDay 2026 Workshops Overview est apparu en premier sur dbi Blog.