Option one: download and install

This is something I see a lot of people do: make a list of the software they have. Browse to the website of this toolset, download it, and install it. And repeat. This works, but it is very tedious.

Option two: automate it

As the great Rob Sewell keeps telling the community, everything you do more than once, automate it. And for this installing party, there is an option to automate by using Chocolatey.

You can think of this as a tool for reaching your Desired State Configuration (DSC). This is something enterprises also use to quickly build their digital workspace.

The only thing you need to do is find out which software you’re using and look for it in the Chocolatey repository. If it’s there, you can run the command to install the software and do something more useful (like catching up with a coworker at the coffee machine) while it installs.

Yes, you do need to complete the configuration later on, but the basics are in place for you.

Here are my current working commands. Run them in Administrator mode in PowerShell 7, and you should be good to go.

Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://community.chocolatey.org/install.ps1'))

choco install firefox -y
choco install greenshot -y
choco install git -y
choco install 1password -y
choco install vscode -y
choco install 7zip -y
choco install anaconda3 -y
choco install everything -y
choco install drawio -y
choco install FileZilla -y
choco install SoapUI -y
choco install Postman -y
choco install obs -y
choco install slack -y
choco install treesizefree -y
choco install azurestorageexplorer -y

I’m also using a number of tools that are not (yet) supported, such as OhMyPosh, Clickshare, Focusrite, GitHub Desktop, Notepad++, notepad++, whatsapp and SSMS. All of these need more manual input to download and install. You can find all supported packages via this link.

Youre a smart person, so you’ll never just run code you’ve found on the internet. So you first check out the Chocolatey website before you run it. Right? Good!

Follow me, and let’s find out!

The previous blog had this JSON as output:

{"MainElement":{"SubLevel":[{"number":1,"type":"Type 1"},{"number":5,"type":"Type 2"},{"number":10,"type":"Type 3"}]}}

Now, when we want to get all the rows, you might use logic like this:

let XML = datatable(XMLG:string) ['<?xml version="1.0" encoding="UTF-8"?><MainElement><SubLevel><number>1</number><type>Type 1</type></SubLevel><SubLevel><number>5</number><type>Type 2</type></SubLevel><SubLevel><number>10</number><type>Type 3</type></SubLevel></MainElement>'];
XML
| extend data = parse_xml(XMLG)
| extend full = parse_json(data.MainElement)
| extend sl = parse_json(full.SubLevel)
| project sl.number

This code works through the elements. First, we parse the XML to morph it into JSON. Next, we open the MainElement to what is inside, and then we’re going to the sublevels.

These look like this:

And now, let’s get the number:

Well, this is a problem. Even though we saw the data in the JSON, it won’t appear.

Let’s fix it!

When I ran into this issue, it took some time to fix it. And I know you’re here for the solution, let’s do it!

let XML = datatable(XMLG:string) ['<?xml version="1.0" encoding="UTF-8"?><MainElement><SubLevel><number>1</number><type>Type 1</type></SubLevel><SubLevel><number>5</number><type>Type 2</type></SubLevel><SubLevel><number>10</number><type>Type 3</type></SubLevel></MainElement>'];
XML
| extend data = parse_xml(XMLG)
| extend full = parse_json(data.MainElement)
| extend sl = parse_json(full.SubLevel)

Yup, this part is something you’ve already seen.

Now, I found that you can check the type of array:

| extend arrType = case(
        gettype(sl) == "array", sl,                                              // already an array
        gettype(sl) == "dictionary" and gettype(sl["items"]) == "array", sl["items"], // object that contains the array
        pack_array(sl)                                                           // single object/scalar -> wrap as 1-element array
)

In this code, I’m feeding the JSON into a function that determines the type. If it is an array, all good. If it’s not recognised as an array, it will pack the data into an array object, effectively creating an array we can work with.

| mv-expand item = arrType
| project nr = item.number,
        tp = item.type

Next, I’m expanding the array from the type checking, followed by projecting these values. The result?

If you’re interested, you can also see the different steps by skipping the project lines.

You can see that the code is expanding the data in a way that it can be processed, something I couldn’t achieve without the extra step of extending the array.

So, always through this type?

Well, not necessarily. Look at the following code:

let XML = datatable(XMLG:string) ['<?xml version="1.0" encoding="UTF-8"?><MainElement><SubLevel><number>1</number><type>Type 1</type></SubLevel></MainElement>'];
XML
| extend data = parse_xml(XMLG)
| extend full = parse_json(data.MainElement)
| extend sl = parse_json(full.SubLevel)
| project nr = sl.number,
          tp = sl.type

This is a fully single-element XML. When running this code, this is the result

This also works with the extended code:

let XML = datatable(XMLG:string) ['<?xml version="1.0" encoding="UTF-8"?><MainElement><SubLevel><number>1</number><type>Type 1</type></SubLevel></MainElement>'];
XML
| extend data = parse_xml(XMLG)
| extend full = parse_json(data.MainElement)
| extend sl = parse_json(full.SubLevel)
| extend arrType = case(
        gettype(sl) == "array", sl,                                              // already an array
        gettype(sl) == "dictionary" and gettype(sl["items"]) == "array", sl["items"], // object that contains the array
        pack_array(sl)                                                           // single object/scalar -> wrap as 1-element array
)
| mv-expand item = arrType
|project nr = item.number,
        tp = item.type

The result is identical. The main difference is that the last code fragment is robust to one or more elements, whereas the simpler code can only handle a single element.

And now you know, and maybe you can improve your code even further. Also, this may not be the best or the only solution. Please add your solution in the comments if you have something else that works!

Enjoy KQL, and thanks for reading!

One thing I ran into was that an element contained several other elements. Usually, you’ll see them in an array, but in this case, it wasn’t. Since these elements within the main element contain the information we need for the table, I started thinking about how to extract this data.

As a very simplified example, check out this XML.

<?xml version="1.0" encoding="UTF-8"?>
<MainElement>
  <SubLevel>
    <number>1</number>
    <type>Type 1</type>
  </SubLevel>
  <SubLevel>
    <number>5</number>
    <type>Type 2</type>
  </SubLevel>
  <SubLevel>
    <number>10</number>
    <type>Type 3</type>
  </SubLevel>
</MainElement>

As you can see, inside the main element, there are three identical sublevels, all with the same name but with different content.

So, all XML code?

Well, some time ago, I got the hint that when you’re parsing XML in Fabric Realtime, after the first pass, it’s no longer XML, but it’s JSON in the background. And with JSON, it can be a bit easier to work with elements. But still, if the XML is quirky, so will the JSON.

Funny discovery!

When I was trying to figure out how to get all these separate elements, I decided to take a look at what was actually on offer. And to my great surprise, my separate XML elements had been transformed into a JSON array, allowing me to unnest it and access all the good data inside.

When you run the following code, you’ll see the XML output.

let XML = datatable(XMLG:string) ['<?xml version="1.0" encoding="UTF-8"?><MainElement><SubLevel><number>1</number><type>Type 1</type></SubLevel><SubLevel><number>5</number><type>Type 2</type></SubLevel><SubLevel><number>10</number><type>Type 3</type></SubLevel></MainElement>'];
XML

Now, let’s add one row of code to parse the XML.

let XML = datatable(XMLG:string) ['<?xml version="1.0" encoding="UTF-8"?><MainElement><SubLevel><number>1</number><type>Type 1</type></SubLevel><SubLevel><number>5</number><type>Type 2</type></SubLevel><SubLevel><number>10</number><type>Type 3</type></SubLevel></MainElement>'];
XML
| extend data = parse_xml(XMLG)
| project data

Now, you’ll see the XML has been converted to JSON. The most important part is that the engine recognises that there’s an array of sublevels and returns it as an array. You’ve seen that these aren’t offered as an array in the XML! Good job! And now, I can process the values to match the business rules or whatever the use case is.

The point

Whenever you ingest data through Realtime Intelligence in Fabric, check the intermediate steps before you venture off into writing a lot of code. Before you know it, there might be some data that can be easily manipulated.

Thanks for reading!

In the previous blog post on MCP Local, I described how to run your MCP on your laptop and connect to your local SQL Server database.

With this setup, a few new challenges arise:

• Connecting to a Fabric SQL Endpoint
• Using Entra ID
• Reading Lakehouse data

Getting the Fabric SQL Endpoint

This was the easiest part. The trick is to understand how it works. Because your connection string needs to define both the server and the database. In SQL Server, this is easy. But when you retrieve the SQL Endpoint from your Fabric Lakehouse properties, it only shows the server. The name of your Lakehouse is the actual database you’re connecting to.

And, as I found out, it’s just like firing a query against a SQL Server; it behaves the same and that’s a good job on Microsoft’s side.

Using Entra ID to authenticate

Here, the problems arose. Mostly because I had decided to use a User Assigned Managed Identity (UAMI) to connect my MCP to Fabric. Adding a UAMI to Fabric with permissions isn’t hard, but when you try to use this on your laptop that’s unknown to your Azure tenant, it doesn’t work. In my case, I had to use Azure Directory Interactive to connect. And as my dab-config.json file had too many remnants of my trials, it kept failing.

Working code

To save you (a lot of) time, here’s some working code for you (which very well may be future me ;))

mcp.json

{
  "servers": {
    "sql-mcp-server": {
      "type": "stdio",
      "command": "dab",
      "args": [
        "start",
        "--mcp-stdio",
        "role:authenticated",
        "--config",
        "${workspaceFolder}/dab-config.json"
      ]
    }
  }
}

In this mcp.json file, the key is the authenticated role; without it (at least when I was testing), it threw an authentication error.

.env

MSSQL_CONNECTION_STRING=Server=[Your Fabric Server].datawarehouse.fabric.microsoft.com;Database=[Your Lakehouse Name];Authentication=Active Directory Interactive;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;

The .env file contains your connection string. Set Authentication=Active Directory Interactive. This ensures that when you run your code, a new browser window will open to help you authenticate to Azure.

dab-config.json

{
  "$schema": "https://github.com/Azure/data-api-builder/releases/download/v1.7.90/dab.draft.schema.json",
  "data-source": {
    "database-type": "mssql",
    "connection-string": "@env('MSSQL_CONNECTION_STRING')"
  },
  "runtime": {
    "rest": {
      "enabled": true,
      "path": "/api",
      "request-body-strict": true
    },
    "graphql": {
      "enabled": true,
      "path": "/graphql",
      "allow-introspection": true
    },
    "mcp": {
      "enabled": true,
      "path": "/mcp"
    },
    "host": {
      "cors": {
        "origins": [],
        "allow-credentials": false
      },
      "authentication": null,
      "mode": "production"
    }
  },
  "entities": {
    "active_items": {
      "source": {
        "object": "dbo.active_items",
        "type": "table"
      },
      "fields": [
        {
          "name": "id",
          "primary-key": true
        }
      ],
      "graphql": {
        "enabled": true,
        "type": {
          "singular": "active_items",
          "plural": "active_items"
        }
      },
      "rest": {
        "enabled": true
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [
            {
              "action": "read"
            }
          ]
        }
      ]
    }
  }
}

In the dab-config.json file, I found that the authentication section had to be removed for it to work. In this code, it is null, which apparently works too!

Testing your config

Whilst trying to figure out why my authentication went off the rails (and yes, this was my bad), I ran into the following command:

dab validate --config dab-config.json

This command not only checks the technical validity, but also tests functions; can it connect to your endpoint, are your entities defined correctly and, as always, does every entity have a field with a primary key? For those working with Fabric, you’ll quickly recognise that there is no concept of a SQL Primary Key constraint in a Lakehouse. So you’ll need to define that in your dab-config.json. After that, it’s plain sailing.

Thanks for reading!

The error message

When I started installing SQL Server, I just went for it, not even checking the disk. During installation, I saw this warning.

This warning felt benign; more like you can run into errors. Well, as I found out, this lets you run into an SQL Server installation that just fails.

The error logs fill up with stack dumps and a fatal error that makes no sense. The installation log, however, shows you a more meaningful error.

Scenario config specific rules:
Warning    LargeSectorSizeCheck             One or more disks intended for SQL Server data have a physical sector size greater than 4 KB. SQL Server currently supports physical sector sizes up to 4 KB. Review your storage configuration or refer to the documentation for guidance.

It’s more or less the same as the warning. Only this causes the installation to break and show a red cross on the screen, or causes the entire installer to crash. I’ve seen both happen.

Solution that didn’t work

My first assumption was that the good people who supplied me with my laptop had made a mistake formatting the disk. So I went into Disk Management, shrunk a disk, and created a new drive. I made sure I formatted it to 4Kb sector sizes and tried again. And failed.

I learned that it’s not the drive sector size that counts, but the actual disk. And NVME’s have a large sector size. And you can’t change that.

Working solution

But you can ‘trick’ SQL Server into thinking the sector size is 4 Kb. The great Glenn Berry sent me a link to a Microsoft Learn page where it actually shows this problem and provides the solution.

What you need to do is add a line to your Windows registry and reboot your machine. This line lets SQL Server (and other applications that can check on sector size) think it’s a 4Kb sector size. I would strongly advise you to ensure your disk is formatted with a 4Kb sector size.

After following the steps in the docs, the installation of SQL Server went very smoothly, and when I opened SSMS 22, I could connect to the database and start having fun!

I hope this short blog helps you when figuring this out; it took me way too long, and thanks again to Glenn Alan Berry for the help!

At the end of this post, I’ll show some code to get you started with PowerShell. Other languages are available.

DAB Local

You can run your MCP service locally by using the DAB toolset. You have to create your dab-config file with an environment file that connects to your database.

The DAB start command will make your service available. From Visual Studio Code, you can start the MCP service (that will auto-shut down after 2 hours), and you can get to work. You do this by pressing Ctrl-Shift-P, finding the MCP List, and clicking the one you created. Next, select this MCP, then click Start. You’ll see a message that the connection has succeeded, and then you can move on with your quest.

When creating the config file, I found that using the AdventureWorks2019 database has some incompatible data types. When you create a full table definition (where you describe all the fields in the entity) and choose to exclude the fields with unsupported data types, the following error will still come up.

When I googled, I found this link where a poster shares that creating a view works. So, I removed the table from the definition and replaced it with a view, casting the unsupported data type to a supported one. And then, it all magically works. Creating a view is a valid workaround for these cases.
The list of unsupported data types isn’t very long, and they are somewhat exotic. At least in my line of work, types like hierarchyid aren’t the ones I run into that often.

This has been identified as a bug and will hopefully be fixed in the near future.

When I start running things in VS Code with GitHub Copilot, and the agent starts executing statements, you have to allow them. It does get a bit boring to keep allowing it to do stuff, but it works. You can allow the session’s statements, which helps with new requests.

What it’s doing

The fun part now, unlike the Azure container, is that you can see what it’s doing in your terminal.

In this screenshot, you can see one of the queries the MCP executed against the database to generate an overview of salaries by month. It returned a table that I wanted to check, because I found a few of these queries:

As you can see, there is a SELECT TOP 101 and, at the bottom, a SELECT TOP 51. I know the database, and I know that there are more records than 101. Maybe not many more, but if I’m asking for a full overview, I want the MCP to read all the records, not a subset.

The numbers, I’ve learned, are not arbitrary, but something you can configure in the runtime.pagination.default-page-size in dab-config.json. MCP enforces pagination with a default of 100. The extra record is to check if there are more records to be found. If so, it adds a next link so it can move to the next page. An agent can choose a different page size, hence the 51 (actually 50, but MCP added one to check for next pages), but I’ve also seen 1001.
If your use case demands large result sets, increase the default page size in the config, and make sure your agent has the instruction to work through all the pages using the nextLink/$after continuation tokens. Just make sure it doesn’t assume that one page has all the information it needs.

Fun use case

One of the more fun things you can do in Visual Studio Code is create a mermaid diagram. I’ve asked it to create a mermaid diagram of the organisation.

To do this, it went through a few steps.

Now, the organisation node contained the hierarchyid, a datatype not supported by DAB. But I’ve exposed it in the view by converting it to the varchar data type. Apparently, that was enough to generate the layers in the mermaid diagram.

Giving it a run for its tokens

Another fun question to ask is about the people who have bought the most articles.

When it ran, I could see different files being created:

What surprised me was that VS Code, or the GitHub Copilot Agent used, didn’t connect to the MCP directly but instead wrote PowerShell scripts. And it kept writing code, second-guessing itself, rewriting code, trying it out, fixing errors. It was almost like I was looking at myself writing code and finding solutions for issues. Just a bit faster and without the need to get a coffee.

As an example, it created this script:

# Query actual customer data using correct MCP protocol

$baseUrl = "http://localhost:5000/mcp"
$headers = @{
    "Content-Type" = "application/json"
    "Accept" = "application/json, text/event-stream"
}

# The correct method should be tools/read_records or similar
$body = @{
    jsonrpc = "2.0"
    id = "1"
    method = "tools/call"
    params = @{
        name = "mcp_sql-mcp-serve_read_records"
        arguments = @{
            entity = "SalesOrderHeader"
            first = 100
            orderby = @("TotalDue desc") 
            select = @("CustomerID", "TotalDue")
        }
    }
} | ConvertTo-Json -Depth 10

Write-Host "Querying database for top customers..." -ForegroundColor Cyan

try {
    $response = Invoke-WebRequest -Uri $baseUrl -Method Post -Body $body -Headers $headers -ContentType "application/json" -UseBasicParsing
    
    if ($response.Content) {
        # Parse Server-Sent Events format
        $lines = $response.Content -split "`n" | Where-Object { $_ -match "^data:" }
        foreach ($line in $lines) {
            $jsonData = $line -replace "^data:\s*", ""
            if ($jsonData) {
                try {
                    $parsed = $jsonData | ConvertFrom-Json
                    if ($parsed.result -and $parsed.result.value) {
                        Write-Host "Retrieved $($parsed.result.value.Count) orders" -ForegroundColor Green
                        
                        # Calculate customer totals
                        $customerTotals = @{}
                        $parsed.result.value | ForEach-Object {
                            if (-not $customerTotals.ContainsKey($_.CustomerID)) {
                                $customerTotals[$_.CustomerID] = 0
                            }
                            $customerTotals[$_.CustomerID] += [decimal]$_.TotalDue
                        }
                        
                        Write-Host ""
                        Write-Host "TOP 10 MOST ACTIVE CUSTOMERS BY SALES" -ForegroundColor Cyan
                        Write-Host ("=" * 60)
                        Write-Host ""
                        
                        $rank = 1
                        $customerTotals.GetEnumerator() | 
                            Sort-Object Value -Descending | 
                            Select-Object -First 10 | 
                            ForEach-Object {
                                $custId = $_.Name
                                $total = [math]::Round($_.Value, 2)
                                Write-Host "$rank. Customer ID: $custId"
                                Write-Host "   Total Sales: `$$total USD"
                                Write-Host ""
                                $rank++
                            }
                        
                        # Export to file
                        $top10 = $customerTotals.GetEnumerator() | 
                            Sort-Object Value -Descending | 
                            Select-Object -First 10 | 
                            ForEach-Object {
                                [PSCustomObject]@{
                                    Rank = 0
                                    CustomerID = $_.Name
                                    TotalSales = [math]::Round($_.Value, 2)
                                }
                            }
                        
                        $top10 | Export-Csv -Path 'C:\temp\MCPLocal\top_10_customers_real.csv' -NoTypeInformation
                        Write-Host "Data exported to: top_10_customers_real.csv" -ForegroundColor Yellow
                    }
                } catch {
                    # Skip non-JSON lines
                }
            }
        }
    }
} catch {
    Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red
}

As you can see, it is using the REST endpoint via Invoke-WebRequest. If you see different messages around not being able to connect, check if your local firewall allows traffic on port 5000. In my case, it was blocked, and I had to add a rule to open this port.

As always, check the output of agents, MCP services and models. Always verify before placing any trust in a model. Maybe this is an old man shouting at a cloud, but I like to be sure before I share things with customers, management or support.

When I checked the results by writing my own query, they were wrong. They were wrong because the agent (GitHub Copilot, using Claude in this case) decided that checking the first 100 rows would suffice. This has nothing to do with MCP, everything to do with the way I’m using the MCP. When I told the agent to review its work, it went off and did it (of course, telling me I’m absolutely right). And went on working for ages.

However, I did notice a message from the MCP server that it has a limitation:

As mentioned before, an Agent can tell the MCP to retrieve a large page size. The default is 100, and now we know the max size is 100.000 records for one page. Make sure your agent has this information so it can build a good call to the MCP.

Logging

When you use Visual Studio Code with the GitHub Copilot agent, your prompts and responses will be saved in the chat editor. This history is tied to the Git workspace you’re connected to. My history is cleaned up every now and then, which may very well be a company policy running somewhere. But you can export the history to save it for later analysis.

Use Ctrl-Shift-P to open the command palette and look for chat: export chat

This will generate and let you save a JSON file containing all the chat history, including self-conversations.

If you like to get some insights into how an Agent works, thinks and acts, these responses are great! Different models and versions will generate different outputs, but I just wanted to show you what can happen.

I’m running the DAB from my Windows terminal window. If you want, you can save all the output yourself or run the DAB command followed by > DAB-Output.txt. This should help save the output to a file for later review. Be warned though, the service is very chatty and your file may grow fast.

DAB lets you tame this through the runtime.telemetry.log-level section in dab-config.json, where you can set log levels per .NET namespace. For example, setting "default" to "Warning" will silence the bulk of informational noise, while you can selectively keep "Debug" or "Information" on specific namespaces like "Azure.DataApiBuilder.Core.Resolvers" if you still want to see the generated SQL queries. In development mode DAB defaults to Debug (which is why it’s so verbose), but switching to "Warning" or "Error" for namespaces you don’t care about will dramatically reduce output.

Write your own code: PowerShell

What’s more fun than writing your own code to talk to your MCP? As you may have figured out, I like PowerShell. It’s great for automating tasks and tinkering with REST services.

It took me some time, but here’s a starting point when using plain PowerShell on your MCP. There wasn’t much documentation my search could find, but with a little trial and error and a peek at the earlier generated code, I got the basics working.

Session ID

First, you need to create a session ID. You only have to do this once; when it’s created, you can reuse it in your session.

$uri = "http://localhost:5000/mcp"

# The session-create request (JSON-RPC)
$createBody = @{
    jsonrpc = "2.0"
    id      = 1
    method  = "session.create"
    params  = @{ }
} | ConvertTo-Json -Depth 10

$createResponse = Invoke-RestMethod `
    -Uri $uri `
    -Method Post `
    -Body $createBody `
    -ContentType "application/json" `
    -Headers @{
        Accept = "application/json, text/event-stream"
    }

$sessionId = $createResponse.result.sessionId

"Created MCP session: $sessionId"

Finding the possible calls

Next, a smart move is to see which calls are available.

$body = @{
    jsonrpc = "2.0"
    id      = 2
    method  = "tools/list"
    params  = @{ message = "Hello again" }
} | ConvertTo-Json -Depth 10

$response = Invoke-RestMethod `
    -Uri $uri `
    -Method Post `
    -Body $body `
    -ContentType "application/json" `
    -Headers @{
        Accept = "application/json, text/event-stream"
        "Mcp-Session-Id" = $sessionId
    }

$response

This code returns a JSON file listing all the calls you can make.

Describing the entities

Now, let’s try one out, describing all the entities. Again, the output will be in JSON format.

$uri = "http://localhost:5000/mcp"          # same endpoint you used for session.create
# assumes you already did session.create and have $sessionId

$body = @{
  jsonrpc = "2.0"
  id      = 20
  method  = "tools/call"                     # if this 404s, try "tools/run"
  params  = @{
    name      = "describe_entities"
    arguments = @{
      nameOnly = $true                       # <-- names + short summaries only
      # Do NOT include "entities" when nameOnly=true (per tool contract)
    }
  }
} | ConvertTo-Json -Depth 20

$response = Invoke-RestMethod -Uri $uri -Method Post -Body $body -ContentType "application/json" -Headers @{
  Accept = "application/json, text/event-stream"
  "Mcp-Session-Id" = $sessionId
}

$response | ConvertTo-Json -Depth 20

And now, on your screen, you’ll see a JSON file with all the entities.

Based on these results, you can move on to doing what you want. The calls are very, very fast. The trick will be to craft the best calls for your use case. And that, dear reader, is all up to you.

Thanks for reading!

For the demo, I’m using the Stack Overflow 2013 database on an Azure SQL General Purpose Serverless 8-core database. It’s not a very large dataset (making it less expensive to host on Azure), but it has enough data and online examples to have some fun with.

We’re going through several steps to create the correct Azure resources, connect them, and ensure the MCP is online.

Create the Azure Container Registry (ACR)

The following code creates a resource in Azure to host your ACR. This is a resource you need to host your MCP server. The code shown here will log you in to your tenant, set your subscription to the correct one and deploy the ACR in the correct resource group.

$TENANT_ID = ""
$SUBSCRIPTION_ID = ""
$RESOURCE_GROUP = ""


az login --tenant $TENANT_ID 

az account set --subscription $SUBSCRIPTION_ID

$ACR_NAME = "acrsqlmcp$(Get-Random -Minimum 1000 -Maximum 9999)"

az acr create --resource-group $RESOURCE_GROUP --name $ACR_NAME --sku Basic --admin-enabled true

Create a config file

Next, initialise your config file. This file holds all the information about your database. I found this part to be quite a lot of work, describing the tables and columns. For those looking at the full listing, you’ll observe that I may have missed tables and columns. Reason being, it’s a demo, and I got a bit fed up with adding all this metadata. Call me human.

dab init --database-type mssql --connection-string "@env('MSSQL_CONNECTION_STRING')" --host-mode Production --config dab-config.json

The first step, of course, is creating the file itself. You can find it in the C:\Users\YourName directory. Easy to find and easy to open. Spoiler alert: this is not the location you want this file to be in for later steps.

Tables

Next, you need to add information specific to the tables. This is a bit of work. You can download the full code file below. An example is this, where it declares the location of the Posts table and provides a short description. For a production system, I’d add more info to help the agent.

dab add Posts `
  --source dbo.Posts `
  --permissions "anonymous:read" `
  --description "all non-deleted posts"

Columns

Next comes a lot of work: describing each column, but remember it isn’t mandatory. You can leave them out, although you have to remember that tables that have no defined primary key need a fields identifier in your JSON file to make it work. You can enter the column names, a description, and whether each column is a primary key. In the descriptions, I’ve been playing around with different options to see what it will and won’t pick up on.

dab update Posts `
  --fields.name Id `
  --fields.description "Unique Post identifier" `
  --fields.primary-key true

dab update Posts `
  --fields.name PostTypeId `
  --fields.description "Type of Post, links to PostTypes table"

When you choose to open the JSON config file, you’ll see something like this:

  "entities": {
    "Posts": {
      "description": "all non-deleted posts",
      "source": {
        "object": "dbo.Posts",
        "type": "table"
      },
      "fields": [
        {
          "name": "Id",
          "description": "Unique Post identifier",
          "primary-key": true
        },
        {
          "name": "PostTypeId",
          "description": "Type of Post",
          "primary-key": false
        },

It’s essentially the code you entered in PowerShell, just in a different format. For those of you big on automation, I can see options to create the JSON directly without the DB update command.

Basic automation

One thing you can do is create the commands by running T-SQL. I used this to make sure my tables and columns were registered correctly in the JSON. Also, note that every table needs a primary key! I tried to run a container with a table that lacked a primary key, but it refused to start, throwing an error about the missing key.

SELECT concat('dab add ', t.name, ' --source ', concat(s.name, '.', t.name), ' --permissions "anonymous:read" --description "', t.name ,'"')
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
where s.name = 'dbo'
order by t.name

select concat('dab update ', t.name, ' --fields.name ', c.name ,' --fields.description ', c.name, ' --fields.primary-key ', CASE WHEN ixc.index_column_id = 1 Then 'True' else 'False' end)
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.columns c on t.object_id = c.object_id
inner join sys.indexes ix on t.object_id = ix.object_id 
left outer join sys.index_columns ixc on t.object_id = ixc.object_id and ix.index_id = ixc.index_id and c.column_id = ixc.column_id
where s.name = 'dbo'
order by t.name

Advanced automation

If you’re already more into automation, take a good look at this blog post from the wonderful Jess Pomfret. She’s shared a very nice set of steps to create your DAB config file.

Create a Docker file

The next step was somewhat confusing for me; I had to create a Docker file. Microsoft provides some text you should use for this, and my first reaction was to run the code, expecting the Dockerfile to be created. What you need to do is create a new file named Dockerfile. No extension. Then paste the content from the quickstart Azure Container Apps page.

At the time of writing (February 2026), you need to change the image version from the shown 83 to 81. 83 kept throwing errors and issues, while 81 just worked. I will try to keep up with releases, but the numbers shown here may be outdated.

Build and push the image

The next PowerShell command will create the image and upload it to the Container registry. You can see the $ACR_NAME variable, which references the Azure Container Registry you created earlier.

az acr build --registry $ACR_NAME --image sql-mcp-server:1 .

When you start building the image to push, you might run into an error like this:

It took me way too long to realise that this was both my company laptop playing up, and also running the code in the wrong folder. The easiest fix for me was to move both files into a new folder: C:\Temp\MCP. The ACR build takes a few seconds, and you’re done.

Create the container app environment

Well, nothing much here, just the script to create the container app.

$ACR_NAME = "acrsqlmcp$(Get-Random -Minimum 1000 -Maximum 9999)"

az acr create --resource-group $RESOURCE_GROUP --name $ACR_NAME --sku Basic --admin-enabled true

$CONTAINERAPP_ENV = "sql-mcp-env"
$CONTAINERAPP_NAME = "sql-mcp-server"

az containerapp env create `
  --name $CONTAINERAPP_ENV `
  --resource-group $RESOURCE_GROUP `
  --location $LOCATION

Create the container app

Again, just a bunch of code to run. Make sure you’re still in the same PowerShell session so you can reuse the variable values. The SQL Connection string uses a manual username and password. As mentioned in the initial blog, other forms of authentication are available.

$ACR_LOGIN_SERVER = az acr show --name $ACR_NAME --query loginServer --output tsv

$ACR_USERNAME = az acr credential show --name $ACR_NAME --query username --output tsv

$ACR_PASSWORD = az acr credential show --name $ACR_NAME --query "passwords[0].value" --output tsv

$CONNECTION_STRING = "Server=tcp:sqlservername.database.windows.net,1433;Database=StackOverflow2013;User ID=[UserName];Password=P@$$w0rd;Encrypt=true;TrustServerCertificate=false;Connection Timeout=30;"

az containerapp create --name $CONTAINERAPP_NAME --resource-group $RESOURCE_GROUP --environment $CONTAINERAPP_ENV --image "$ACR_LOGIN_SERVER/sql-mcp-server:1" --registry-server $ACR_LOGIN_SERVER --registry-username $ACR_USERNAME --registry-password $ACR_PASSWORD --target-port 5000 --ingress external --min-replicas 1 --max-replicas 3 --secrets "mssql-connection-string=$CONNECTION_STRING" --env-vars "MSSQL_CONNECTION_STRING=secretref:mssql-connection-string" --cpu 0.5 --memory 1.0Gi

Since this resource now has direct access to your database (with the permissions associated with the account), you will want to ensure it is safe. Even though the output tells me the connection string is saved as a secret, better be sure.

To check how it’s saved, open the container app and select Secrets. As I’m the owner, I can see everything, including the hardcoded secret with the connection string.

When you open a secret, you can convert it to a Key Vault secret, making it much more secure. For this demo, it’s all good, but for your workload, where you’ll expose real data, go to Key Vault.

AI Foundry

Just when you think it’s all over, it isn’t. The Azure Container App you created can’t do anything; it needs something to connect to it and tell it what to do. The container exposes the MCP toolset over HTTP as a server. Your next step is to connect a compatible client or agent. You can find an example list of clients here, with the direct notion that this is not an endorsement of that list.
The Agent you’re going to create will connect to the MCP service, retrieve knowledge from it, and work from there.

The steps are quite clearly documented on the Microsoft Learn pages, so I’m not going to repeat that.
For my agent, I started with the GPT 4.1 model. There are others available, and I’d recommend that you try to find the one that works best for you.

Agent Instructions

One of the things that really changes the results is the instructions you give to your agent.

In this screenshot, you can see the instructions I gave it. Since the MCP service has the relevant database information, I chose to exclude it from the instructions. After an initial test, I told it to execute statements on the database unless I told it not to. I did this because I found that the results got stuck on the MCP data; it seemed to ignore the fact that it could actually connect to the database.

The last line in the suggestion is a personal preference; the agent kept generating and executing subqueries, and I just prefer Common Table Expressions (CTEs). And it actually generates them the way I’m expecting it to.

Example prompt

I thought I’d give the agent an interesting prompt: Can you find a specific location where users are most probable to be lurkers?

The step-by-step approach gives some insight into what it’s going to write.

WITH UserStats AS (
    SELECT
        u.Location,
        COUNT(*) AS total_users,
        SUM(
            CASE
                WHEN (COALESCE(p.post_count,0) = 0 AND COALESCE(c.comment_count,0) = 0) THEN 1
                ELSE 0
            END
        ) AS lurker_count
    FROM Users u
    LEFT JOIN (
        SELECT OwnerUserId, COUNT(*) AS post_count
        FROM Posts
        GROUP BY OwnerUserId
    ) p ON u.Id = p.OwnerUserId
    LEFT JOIN (
        SELECT UserId, COUNT(*) AS comment_count
        FROM Comments
        GROUP BY UserId
    ) c ON u.Id = c.UserId
    WHERE u.Location IS NOT NULL AND u.Location != ''
    GROUP BY u.Location
)
SELECT TOP 1 Location, total_users, lurker_count, 
       CAST(lurker_count AS FLOAT)/total_users AS lurker_ratio
FROM UserStats
WHERE total_users >= 10
ORDER BY lurker_ratio DESC, total_users DESC

This seems like a very valid result!

After the query is executed, you can see a clear representation of the results. Also, you can see the time it took to create it, the amount of tokens used (2165 in this case) and the AI Quality. Now, take this last one with a small pinch of salt; it’s AI judging itself, so it won’t be too harsh on itself.

I was misled!

The above screenshots looked really amazing, but they weren’t from my MCP. It was hallucinating like crazy. Because I was running build 83, essentially, the container never started. Something that you can see when you take a good look at the container app. What you want to see is a message that the container app is running.

When you click Show Logs, you can see what happened, either historically or in real time.

Historical log:

If you want to run your own query, that’s fine, as the data is saved in a Log Analytics Workspace. So you can add your own monitoring to these logs if you want. In my case, only the errors were logged.

Next, you can check it in Foundry.

When you want to make sure you’re using the MCP, ask it this:

This will show you what it can do.

When it works

You have to be very careful about what you ask the MCP; it seems a bit limited in what it can handle. I found it very easy to run into the following error message:

Initially, I thought the reason it’s throwing this error is that the MCP service apparently can’t handle this amount of work. For us, the query would be very easy; a select with a filter, a having clause and an order by to get the correct top 10. The MCP service must go through several steps. Getting the correct users, then counting the number of posts, putting them in order and finally getting 10.
I received some very valuable feedback that my reasoning of limitations in the MCP is not completely correct. I learned that it’s far more likely that this has to do with the model and its limitations. When I tested something similar in my local environment, I found that complex requests were handled quite good. I then discovered that the Azure models are stuck at the S0 SKU without an option to upgrade them. This can limit what the agent can handle.

When you change the prompt and explicitly tell it to paginate, you will get a result. So, prompting is even more important than chatting with an agent that has a low SKU.

For example, some simple questions on the users.

If you’ve read the news around Open Claw and what it did beyond its permissions, you’ll be happy to see this.

Because I’ve set the table definitions in the dab-config to read only, it won’t do anything else. These do not only apply to my SQL part, but also to REST, GraphQL and the MCP overview. I learned that the DML tools for Create, Update and Delete will not be available for the role that has the permission “anonymous:read”.

Which is good.

Also, the suggestions start by creating SELECT query’s to see what would be changed, before suggesting update query’s. This is something I teach in my SQL classes as well; when you want to update data, first write it as a SELECT to see if it touches all the records you want it to touch and, more importantly, skips all the records you do not want to be updated.

Models to use

When you deploy your solution in Azure, you can select a large number of models to do the work. I found that the Foundry Model Router returns the best results. The model router doesn’t do anything itself; it checks your prompt and tries to find the best model to return a result. Much like the auto-setting in Visual Studio Code, GitHub Copilot. When you use the auto setting there, the Copilot will try to find the best model for your prompt and move from there. As I can’t keep up with all the models and options, it’s the best way to go.

Digging deeper

I like to see what a model is doing. On the one hand, you can use Extended Events on your SQL database to see the queries that are executed. This is all good. But it can be interesting to dig somewhere else. One thing you can do is click on the debug button at the bottom of your chat screen.

From prompt to reply

When you click on this button, it shows you the steps it took to get the answers to your prompt.

As you can see, the MCP translated my request to return the first 10 users in the database into the fields it needed to query, and the result is JSON-structured. This can then be shown in your foundry as a list of names with locations. If you want, you can try finding the live view of your container to see a lot of things happening, including the commands coming through. Or, as described in my next blog on MCP for SQL Server, you can watch it in your terminal.

Quality of the response

You’ll also see a number with AI Quality. This is clickable as well, revealing the following (depending on your settings).

It feels a bit like the agent is judging itself. As much as I like AI models and agents, I still think they should be more critical of their own work.

Token usage

You can also click on the tokens list; you’ll see the number of input and output tokens for your specific prompt and answer.

But wait, there’s more! Because when you’re using AI Foundry, you’re using a model that has a token limit. And that’s something you need to track, because using tokens also costs money.

To see how your model is being used, go to the Models tab in Foundry, select the model you’re using, and click Monitor.

In my case, this came back.

I know that I haven’t written 75 prompts, but my prompt has created 75 requests. The number of tokens is related to my prompt, the requests it generates, and the length of the output.

You can also see that, as I’ve chosen the router model, the difference in usage between the models. So far, I’ve spent 40 cents trying to get my prompts set up correctly and executed in the database.

Effective prompting will save both tokens and money. Just like writing a good query will save you CPU cycles and memory usage. I still like to think of both as skills you can hone.

Next steps?

Well, I’d advise you to give it a try. Spin up an Azure SQL database and restore the default database that you can select in the steps; probably some version of AdventureWorks. Or restore your own testing database that doesn’t have any sensitive data. Next, see what it can do and whether it matches what you’re looking for. No matter the outcome, let Microsoft know. Let them know what works and what doesn’t. At the time of writing, the MCP part of the DAB is in preview.

Thanks for reading, and see you soon in part three, where we’ll be going on-premises!

This first blog will focus on the technology behind it; part 2 will focus on Azure SQL options; and the last part will dig a bit deeper into running this on your local database. There will be a little delay between publishing these posts, more like days than weeks though.

This blog will take you through my first steps, including getting to know the Data API Builder, which now supports the MCP (Model Context Protocol). It has been in preview for some time, so it’s high time to start playing with it. The Data API Builder has been around for some time now; the MCP part is new.

To start, I followed the Microsoft Learn tutorial. Make sure you have PowerShell, DotNet 9 and the Az commandlets installed on your machine. There is quite a lot of code involved in this process. In this blog, I’ll show you most of it. If you want the full listing, go to my GitHub and get the Ps1 file. The only reason I’m not showing all the code is that it doesn’t really help you read. At the bottom of this blog, I’ll also include the links to the Microsoft Learn pages that will really help you to get started.

This also leads to a first observation: setting up MCP (and using it for that matter) is not something I’ll see C-level people do very soon. It’s not a dig at them; I’d just expect data professionals to set this up, maybe create a nice shell around it, and offer it to the organisation.

Alright, enough of the opinions, let’s get into the tech!

What is MCP (Model Context Protocol)?

In essence, it’s your USB-C hub for your data. It works with any agent and can connect to most data types. I could try to write more about it, but you can also follow this link to read more.

It’s more interesting to look at use cases. Because why get a tool when you don’t have a use case? For that, I feel it’s logical to look at the Data Api Builder use case. And there’s not just one, but multiple. As the name suggests, Data API Builder lets you create an API for your database. Websites like to connect to APIs, and data extraction tools do as well. It also has cross-platform capabilities, making it easier to offer a single API while using different databases in the background.

When creating an API (regardless of what it is or does), security is paramount nowadays. You really do not want your company name on the front page of the newspaper (or, for you modern people, news websites). So, think about authentication! The DAB offers four options.

Let’s make things easy, really quick, and say that there are only three ways shown here. In my opinion, Anonymous isn’t authenticating; it’s opening your front door to everyone. In general, a bad idea, unless you want to share something with the world.

As most of my work revolves around Azure, I’d love to stick with Entra ID (or, when you run your database locally, and you’re not using Azure Arc-enabled magic, Active Directory). It’s easy to manage, proven, well-documented and just works. If you want to use EasyAuth or something you’ve created or leveraging from a different supplier, you do you. Zero judgement, as long as you’ve verified it’s secure.

And now, the Data API Builder includes the MCP as well. But why would you do that?

Why the MCP and not an agent?

There are a few differences. For one, when you use an agent (like Copilot or ChatGPT), they have a so-called cut-off point on their knowledge. The models (GPT for instance) are trained on data up to a certain date. From that point on, they don’t ‘know’ what has happened but have to infer. You can provide context manually in the shape of documentation, web-links etc. But that won’t work when you want it to return data on your database. For instance, when was the last time customer X logged in and placed an order. For this, it needs access to your database. And a model can’t do that, it needs an agent. But most agents have no support for database access. There is a reason Microsoft created the Copilot for SSMS functionality, where the agent runs inside SSMS and leverages that route to access your database. The thing is, not everyone will have access to your database. Or even have SSMS installed.

But, by connecting MCP to your agent, it suddenly has the context of the database, it can have the MCP execute queries on the database and return the result in understandable language. Or, you can leverage it as an API, having it return data to a website or even insert or update data.

A main difference between, for instance, Copilot for SSMS and the MCP is that the MCP service runs under its own account. As a user of this service, you do not need any database credentials. You will access it using the MCP account and be able to do what the MCP account is allowed to do. You do not need access to the database. To clarify, you can create a connection string for instance with a user and password. This account is being used by the MCP to access the database.

This is something to be really aware of. If the MCP can read all the data, so can the user who has access to the MCP.

Read this again.

Make sure the MCP has the permissions you want the general user to have. Or the website that uses this. Or whatever external toolset will connect to your MCP. You can use it not only to read data, but you can also leverage all Create, Read, Update and Delete (CRUD) operations. Of course, make sure you have the permissions set correctly, as you do not want a bad actor to ‘clean your database’.

Conversely, when you use an agent like Copilot in SSMS, it can only do what your account has been permitted to do on the database. You have to log in; your permissions are shared with that agent, and from that point on, the agent can do what you can do.

So, please make a good note of these things; it is your data you’re going to expose. And when you’re not sure, or you want to try it (like me), take a harmless database, restore it outside your company’s network and see how you have to configure it to meet your needs, without the risk of data leaks.

Now, enough of scaring you, let’s get started!

Get Data API Builder

The first step is to get the Data API Builder on your machine. It’s a simple command, but for some reason Microsoft Learn only shows it in Bash; here’s the PowerShell command.

dotnet tool install --global Microsoft.DataApiBuilder --prerelease

For this specific exercise, you’ll need the pre-release version, as the MCP part is still in preview. It’s up to you to decide what data you want to feed into this resource.

This command looks quite simple, but it has some requirements. For instance, I needed not only Dotnet 9, but also some Dotnet 8 SDK’s. I’ve found that it works better with PowerShell 7. When you go with the Azure container apps route, you’ll need the Azure command-line toolset as well.

When you work on your company’s laptop, you might run into errors around folder access, or even the ability to install the mentioned SDK’s.

When you’re going to deploy the Data Api Builder in Azure, you have to make sure you have the correct permissions to do that.

The steps you’ll be going through (and that will be explained in more detail in the next blog post) are:

• Create a config file
• Add the tables and views you want to expose
• Add the columns available in the tables (the documentation shows you it’s optional, but in my experience, it doesn’t work without column descriptions) if your table lacks a primary key. Add this as a field in the config file
• Create a local Docker file (just a PowerShell command, no Docker necessary)
• Push the image to your Azure tenant/subscription/workspace
• Create the container app environment
• Create the container app
• Connect a model to your MCP in Microsoft Foundry

This may seem like a lot of work. And when you walk through them for the first time, they are. I wasn’t really surprised it took that much time, because I had to find things out, misread steps, and ran into some issues with my work laptop. And I was trying to see what was happening under the covers too. Which was quite hard as my knowledge of container apps is minimal.

Time investment

When I’m taking all the steps together, it took me around 3 hours to get it all going. Apart from the local machine issues, most of the time was spent creating the table and column definitions. This is something you need to factor in when creating a solution like this. If you have a document describing your database or its metadata, I can see ways to automate this process. At least to save you all the time lost by typing all the things.

And yes, more on this in the detailed posts on Azure SQL and SQL Server.

Cost

There are several cost drivers in this solution.

1. The Azure SQL Database. It’s already there, but, depending on the number of queries, may need an upgrade. From what I’ve seen, the MCP seems quite efficient at preparing queries before they’re sent to the database. Remember that to get data, this MCP reverts to paging quickly, meaning if you have to query a large number of rows, there will be multiple queries.
2. The hours you’re investing. It’s up to you how to calculate that.
3. AI Foundry: manages your model(s) and endpoints. It’s the portal where all the moving parts come together. To get a good grip on how this works, I’d suggest you dig into the documentation added below.
4. Azure OpenAI: hosts your model for a few cents per set of tokens. When this process proves to be successful in your organisation, the costs may add up.
5. Azure Container Apps: also next to free, depending on your usage

Running this locally, there are hardly any costs involved. Running the Data API Builder on your machine is free, as is Visual Studio Code. Your database is already running, and I’m assuming the license has been paid for. The one thing you need is Github Copilot. This may cost some money, but when you have a 4 euro a month subscription, you get a free Github Copilot. It is limited in how much tokens you can use, but may be good enough for you to get going. As things progress, you may need more and find the best fit for your use case.

Remember, the SQL MCP service is in preview; costs may apply as you use it more intensively. But still, it feels like a very cheap and interesting solution!

Concluding

The main points:

• Use DAB MCP if you do not want users, websites or data extractors to have direct access to your database
• Think hard about permissions, implement them, and review regularly
• Keep an eye on costs and usage in the Foundry portal
• Train users in prompting
• Upgrade your knowledge on container apps (when running in Azure)

But also, give it a go to see if it fits your organisation.

Links

Dotnet SDK 8
Install Azure CLI
Install Data API Builder
DAB Container Apps
DAB Microsoft Foundry
DAB in Visual Studio Code
Microsoft Foundy

For those of you who have been following this blog for a long time, you may have read the posts on Fabric where I’m comparing the F64 Trial with the F2, and other shenanigans. Because Fabric keeps evolving, and new releases keep coming that improve or change the behaviour, I felt it was only fair to give Fabric a new run for its capacities.

The idea is not to create a solution that works as quickly as possible. It’s not the goal to tune Fabric, nor to get the most excitement for your Euro. The main goal of this blog (and the session that I’m presenting on this topic), is to show you the differences, the error messages and where to look when you get lost. Because, for all its intents and purposes, error handling is still tricky, and it seems to be very hard to get rid of “Something went wrong” messages.

Setup

As in previous runs, I’ve been using about 500 GB of TPC-H data. CSV files with different sizes that will, at some point, either throw errors or just take ages to get processed.

For my testing, I’m using the F64 Trial, which will kick off a pipeline with many stages. Using pipelines, notebooks, Eventstreams and Dataflows Gen2 (old and new ones) to ingest data into Fabric Lakehouses, Warehouses, Eventhouses and Fabric SQL Database.

No matter how you look at it, ingesting these data volumes will hurt. The funny thing is, Fabric will show you the pain in different ways and sometimes takes quite some time to show it.

Storage

For all these experiments, I’m using Lakehouse, Warehouse, Eventhouse and Fabric SQL Database as ‘endpoints’. They all have differences that I’d like to quickly touch on, just to make sure we’re on the same level. Feel free to skip ahead if you’re confident in your knowledge.

Lakehouse

The Lakehouse uses Spark-based storage, leveraging Parquet files with Delta enabled. It will leverage the setup of OneLake easily, as it seems to be built around this. It also feels like this is as close as Microsoft can come to Databricks.

Warehouse

The Warehouse also stores its files in Parquet, but it has a SQL ‘shell’ around it. This means you can use most of the T-SQL you’re used to and apply it to your data solution. You can write stored procedures and regular SQL queries, making the transition from a current SQL-based solution easy. Until you run into the Warehouse’s limitations. Limitations mainly caused by the limitations of parquet files and the Spark engine.

Eventhouse

The Eventhouse is a whole different beast. Data is stored in a Kusto database, which is essentially a time-series database. This makes it an excellent storage for time-based data, such as IoT device data. The performance is really good, and when you learn Kusto Query Language (KQL), you’ll find that it’s not that different to SQL, and that it’s very logical. Some people even prefer it over T-SQL because you control, on each line, how much data is being processed. And if you really want to, you can also use T-SQL to query the data.

Fabric SQL Database

Finally, Fabric SQL Database, an Azure Serverless SQL Database made available inside of Fabric. Next to 0 configuration (apart from the name), and it’s there. In all honesty, coming from a DBA background, this still makes me nervous. Even though deployment is very fast, I have so little control over the database. Things like collations, backups, integrity checks, and degree of parallelism, to name just a few. Even the number of cores is unknown.

Ingestion methods

To get data from its CSV state into something Fabric can digest, I have to use the tools available to me inside of Fabric. Yes, I could store the CSV as such in OneLake and process from there, but where’s the fun in that?

Pipelines

Pipelines and the copy data activity are one of the easiest ways to do this. Tell it where the source is, where to write it, and done. Well, almost, because you have to tell it what the schema of the file is. And, because my files are built differently, the separator isn’t a comma but a pipe sign. But with that, you’re good to go. The copy data will read the CSV and mash the results into one of the available storage options.

Notebooks

Using a notebook is a little bit trickier as it’s not unusual to see that clients and third parties restrict access to their data by either using IP filtering or requiring some sort of VPN. Both of which were not supported by Notebooks, though Microsoft just released something in preview that is heading into a new direction for Notebooks and connections.
As I did not wish to open up my storage account to the world, I had to put my files in OneLake, using a different capacity to power the Lakehouse, and process from there. As an added complexity, Notebooks can use environments where you can do much of the configuration yourself. To prevent an endless number of variations, I limited myself to the default, the Native Execution Engine and the preview Fabric Runtime 2.0.

Dataflow Gen2

When I started testing in Fabric, I quickly came to dislike the Dataflow Gen2. Not only slow, but it’s also weird to configure (I’m most certainly not coming from a Power BI background!) and extremely costly. Some time after testing, Microsoft released the Fast Copy option that alleviated some of the pain. At FabCon Vienna in 2025, there was a major announcement on the Dataflow Gen2 CI/CD. Long story short, this new version works much more efficiently, uses less capacity, but is still, for a code person like me, weird to work with. Putting that last argument aside, I think it’s a very viable option if you need to go low-code.

Eventstream

The last option used is the event stream. In a project, we’re processing an incoming stream of XML data into structured data. I used this knowledge to build a setup that allows me to read from an Azure storage account and write the data to an Eventhouse. What you need to be aware of is that my CSV is malformed, so only the RAW option works. And there is another limitation I ran into, more on that later

Different tests

One more paragraph before moving to the results. And that is the difference between running everything serial (which means the region file/table first, then nation, then parts, etc.), opposed to running everything in parallel (meaning all the ingestion processes for the specific ingestion method and target are started at once). My main questions were, does it matter in time used, does it matter in capacity used, and are there differences in errors?

Time for action!

Pipeline, serial ingestion

Welcome to the party, pal! The action starts with loading the data using pipelines and slamming it into the Lakehouse, Warehouse and SQL database.

As expected, the Lakehouse just works. It’s not very lightweight, but the data is transferred and transformed from CSV to parquet without many issues. To my pleasant surprise, the Warehouse wasn’t really angry with me either. Yes, it was a bit slower and took some more capacity units, but all in all, it worked. And performance-wise, it’s not that far from the Lakehouse. Something unexpected was the delay with the Fabric SQL database. To me, it seems it was somewhat surprised by the incoming data load and had to do quite a bit of extra work to store it.

My (somewhat educated) guess is that, since it’s running in full recovery mode, every transaction is fully logged. Lake and Warehouses do not suffer from this. Also, data is automatically replicated from the SQL Database files (MDF, NDF, and LDF) to OneLake. This won’t help performance either. Finally, I’m wondering how many CPUs the database was using; the monitor shows exactly 0, even though there were quite a few processes running.

Pipeline, parallel ingestion

Part two of the tests involves removing the lines between the tables. In other words, I’m feeding the pipeline 500 GB of data and just letting it rip through it.

The Lakehouse target, again, gets the job done. Both Warehouse and SQL Database fail, but with a weird error. I’m hitting a limit of 400 somethings.

I think it has to do with data transaction units, but the math doesn’t hold up, especially when you look at the documentation, mentioned in the link.

This is the calculation I’ve made:

As you can see, the last two tables, the ones that went into the error, cross the boundary of 400.

Looking at the documentation, you can see a different number. The funny thing is, when you try it dig a bit deeper, you’ll find a different link, click here to follow. There, you see wildly different numbers compared to the previous link.

An error message sending you to a wrong link, besides being not very clear about what’s actually going wrong. So always check a level deeper before accepting the error message. In a way, these are just like an AI agent; you should always check their work too.

Dataflows Gen2 to lakehouse

The next tests are using a dataflow Gen2 to load data into the Lakehouse. There are a few tricky parts here. First, going serial is a huge challenge, as this item can’t be configured to load just one table, then move on to the next; I have to create a separate flow for each table. With that, it will pick up just one file.

When I created the dataflows to plough through all of them, without caring about order or anything, think of it as a very parallel process, both the dataflow Gen2 and the newest one with CI/CD enabled and all the cool new features turned on, everything just failed. And my main issue with error messages came back again.

So yeah, something went wrong, but we’re not going to tell you what it was. Coming from a code-first background (T-SQL!!), I still have a bit of an issue with low-code solutions. I like to be in control and see squiggly lines when I make a mistake. I really don’t mind being wrong or doing something stupid, but just tell me. When an item fails, sure, no problem. But tell me what I did wrong, and preferably a way I might solve it.

Notebooks to Lakehouse

This is my favourite part. Not only because every architecture I design for customers relies heavily on this concept, but also because I’m still convinced it is the best way to load and process data in Fabric.

I wrote a notebook that processes the data serially, table by table. And one set of notebooks that will do the same thing, but all in parallel. The best thing is, the only failures I ran into were because of mistakes I made. Like not using the correct Lakehouse or ABFSS path. When the config was correct, the processing just worked flawlessly.

Different environments

When you’re using notebooks, you can start with the default environment or create your own. When you go into the direction of creating your own, there are almost an infinite number of possibilities you can use. As much as I love you, my dear reader, I can’t test them all. So I went for the three main options: Default, native execution engine, and the 2.0 preview.

Before I actually ran the tests, I had the feeling that the native execution engine would be the fastest, in any case, outperforming the default. Maybe the preview 2.0 version would be quick as well, but I wasn’t sure.

So here are the results, with the fastest times in bold.

Apart from the differences, I found it most surprising that they were all over the place. Fabric 2.0 has 4, the Native execution engine has 3, and the default is the quickest on two occasions. This is an average over three runs, by the way, so some outliers will influence the results. However, I think you can still see that the defaults are the least good option to work with, even though they are not bad.

Eventhouse

Having worked with clients in Realtime, I’ve quickly come to really like the Realtime experience in Fabric. Even though there are some pitfalls to be aware of (like the Always-On option that can eat capacity like mad), it has solid use cases and can handle incredible amounts of data without issue. If you’re working with XML that’s fairly stable and the files keep coming in, it is a very efficient way to handle them. That is, up to a point, because I ran into an issue that isn’t, as far as I know at the time of writing, clearly documented.

Apart from the fact that someone decided to show errors in Bytes (640 Kb should be enough for everyone, right ;)), it is a clear message that I’m doing something wrong. If you don’t feel like doing the math, I’ve done it for you. The message says my 11,5 GB file is larger than the allowed ingestion limit: 768 MB. Apart from the bytes part, this is an error message I can work with. In theory, I could have split all the large files into smaller ones, but that would defeat the purpose of these tests, which is to see when and where Fabric breaks.

Costs

The main point of this exercise is to find out what it all costs. Not only in capacity units, but also in money. Because in the end, we all want the most excitement for our euro. But to find out the cost, you first have to calculate this.

Using this number, you can calculate the actual cost of items and decide whether they’re worth it for your business needs.

Costs in terms of storage:

Storage-wise, it’s clear that Fabric SQL DB is the most expensive, even though it’s not crazy expensive.

When I presented this session, an attendee asked me about the comparison between CU’s per-GB processing. Especially because the Eventhouse was very quick, but also faulted fast because of the file sizes. As shown in the table below, the Eventhouse didn’t store a large amount of data. But this is only part of the story, as Eventhouse is exceptionally good at compressing data. The original data size is almost double that of what is stored.

Looking at the processing costs, there’s a lot to learn.

This tells a very different story. As you can see, using pipelines is very expensive. Dataflows Gen2 are costly as well, even more so given that they failed. The new ones are much less expensive, making the failure a tolerable experience. Spending about 2.5 euro’s to find out you made a mistake isn’t that bad. Notebooks still look like they are the most cost-effective option.

Tying it all together

So, after all these tests, anything to conclude?

First of all, these tests are a little silly in their setup. Slamming a huge amount of files into Fabric to see what happens isn’t exactly a production scenario. Yet this was already disclaimed at the beginning of this post. This was more about finding differences and seeing when, how, and why it breaks. But it’s also meant to be an inspiration to start testing your own workloads.

I can go on and tell you what the best solution is, but this is only part of the equation. There are other factors to consider as well. One of these is your company’s workforce. If you’re working with people who are amazing in SQL, but limited in PySpark, it makes a lot of sense to build the solution in a Warehouse. If you are on your own, coming from a Power BI background, using Dataflows Gen2 could be the way to go.
Apart from knowledge, budget to learn and expand expertise is something to factor in. This includes time. The cost of learning a new language and toolset may not outweigh the time and class costs. This is something you have to calculate for yourself.

Another thing to keep in mind is when you hire a consultancy company. I know a few, and they all have their preferences. But always make sure to ask if they can build the solution in a toolset that fits your company, expertise and workforce. It’s up to you to decide how to work with their answers.

Ok, but you want to know my preference? Well, when I started with Fabric, I took an immediate liking to Lakehouses and Notebooks. Even though I find writing notebooks frustrating (I mean, a space has a meaning??), they offer the best performance and flexibility for the lowest price. The downside is that you can’t force them to use an On-Premises Data Gateway to leverage VPN or IP filtering. In those situations, I like to revert to a Copy Job, a Copy Data activity, or a dataflow Gen2.

Finally, if you’re running workloads in Fabric, you probably want serious monitoring to see what happens. The Fabric Capacity Metrics App can help you with a lot of basics, but check out Fabric Unified Admin Monitoring (FUAM) for deeper monitoring and analysis.

Before getting into the details, at the time of writing, I’m not fully OK. It’s not a full burnout, but dangerously close. I wrote about it on LinkedIn; not going to repeat it here, but it colours my experience.

Travelling

Usually, travel is quite easy. And for the most part, it was this time. The only thing is that I’m not really good friends with the public transport app. Whenever I try to find a route to or from the airport, I have to account for at least 2 hours. Where it should only take about 20 minutes. So I was very lucky to have rides with other speakers to and from the event. In these cases, Uber or taxis are my best friends.

Speaker dinner

Usually the highlight of the event, as this is the best place to catch up with other speakers. We share stories, have laughs and generally enjoy the evening. I still did and had great conversations, but it took quite a bit of energy.
The dinner was held at a special restaurant, Das Wirtshaus, specialising in knoedeln or dumplings. And not the Asian ones, but made from potatoes and different fillings. I had never heard of this before, and it was very much worth trying out. The portions were huge, so quite a few people skipped dessert.

The organisers ensured we had bus tickets for the route to and from this location, making travel very easy. Get on the bus, don’t fall asleep and get out close to the hotel.

Main event

The ease the organisers demonstrated during the speaker dinner carried over to the conference day; rooms were easy to find, and, most importantly, the tech just worked. It’s not something you can take for granted, but this was nothing but connect and go. The hotel lobby served as the main expo for the sponsors and as the place for two of the organisers’ opening and closing talks.

New session

I premiered my new session, a follow-up on load testing Microsoft Fabric. In essence, I’m doing stupid things inside of Fabric to see when and how it breaks. This is not a session to mock Fabric in any way, but to provide insights into the differences in cost, behaviour and error messages. If you know how things break and when, you can account for this. And yes, I’m having a bit of fun along the way.

I had around 30 attendees in the room, who interacted nicely, asked good questions en seemed genuinely interested. In the end, the main goal of presenting a session is to help out at least one person in the audience.

After my session, I usually choose to hang out with other speakers or join sessions. This time, I choose to retire to my room and decompress. It felt a bit weird, but Traci Sewell was close to kicking me to my room. And that was a very good strategy; it helped recharge my battery.

The session reviews were good, as it was a brand new one; feedback is essential to iron out the creases. I received positive, actionable feedback that I can take with me to improve the session. Thank you for filling in the feedback! Also, one person wrote the following:

After a good lunch, I went into volunteering mode, helping out at two sessions. Damir Matesic on JSON in SQL Server and Traci Sewell on Burnout. Both good sessions that sparked inspiration.

After event drinks, dinner, and more drinks

After the event closed, most speakers and helpers hung out at the bar, chatting and having fun. This continued through dinner and drinks afterwards.

It was also time to find new speakers (some of whom are on their way to New Stars of Data) and to have impromptu mentoring sessions. One of the things I firmly believe in as a not-new speaker. Help the new people out, give them advice or just listen to them. This also led to offers for speaking slots at my Meetup. When I started out, people in the data community were kind enough to offer me spots to practice. Yes, speaking is a skill that needs to be honed. And practice makes perfect. Now that I’m hosting a user group, it only feels logical and fair to return the favour.

So, a little advertising here: when you look at the Meetup I’m organising with Ynte Jan Kuindersma, you’ll see two extra, online, sessions. If you can, join these sessions and make sure you provide feedback!

Overall

I think this is another great event you should consider going to as an attendee, submitting to as a speaker or volunteering. The organisers (Ben, Marcus, Thomas and Wolfgang) are great people who will do their utmost to help you out. They’re also wonderful people, great to have a chat with.