<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Lucius Fox on Medium]]></title>
        <description><![CDATA[Stories by Lucius Fox on Medium]]></description>
        <link>https://medium.com/@LuciusFox22?source=rss-615c2b210af1------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*tvUjTbJ2P6AeS0kUDO9THw.jpeg</url>
            <title>Stories by Lucius Fox on Medium</title>
            <link>https://medium.com/@LuciusFox22?source=rss-615c2b210af1------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 22:37:24 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@LuciusFox22/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Navigating the Layers: A Practical Guide to Monitoring SQL Server on AKS]]></title>
            <link>https://medium.com/@LuciusFox22/navigating-the-layers-a-practical-guide-to-monitoring-sql-server-on-aks-e913ddde3258?source=rss-615c2b210af1------2</link>
            <guid isPermaLink="false">https://medium.com/p/e913ddde3258</guid>
            <dc:creator><![CDATA[Lucius Fox]]></dc:creator>
            <pubDate>Mon, 18 May 2026 13:33:54 GMT</pubDate>
            <atom:updated>2026-05-18T13:33:54.990Z</atom:updated>
            <content:encoded><![CDATA[<p>As organizations push to modernize their data platforms, moving Microsoft SQL Server onto Kubernetes has gone from a niche experiment to a standard production architecture. Running SQL Server on Azure Kubernetes Service (AKS) offers incredible flexibility, but it also changes the rules of the game for database administrators and DevOps engineers alike.</p><p>Traditional SQL Server monitoring was built for the comfortable, static world of bare-metal servers and virtual machines. In a containerized world, a simple “is the server up?” check won’t cut it. When a query slows down, the culprit could be anything from bad execution plans to Kubernetes node throttling or Azure storage latency.</p><p>To get a true picture of your database health, you need a layered monitoring strategy. In this post, we’ll break down how to build a comprehensive observability framework for SQL Server on AKS using a mix of Azure-native and Kubernetes-native tools.</p><h3>The Big Picture: Our Monitoring Architecture</h3><p>Before diving into the configurations, let’s look at how the data flows. A robust monitoring strategy requires visibility at every layer of the stack, from the underlying Azure hardware up to the specific T-SQL queries executing in the engine.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6qSZDJrOxAG07gaTZuIu0A.png" /></figure><pre>AKS Infrastructure<br>│<br>├── Azure Monitor / Container Insights (Nodes, Pods, Logs)<br>│<br>├── SQL Server Engine (Query Store, Error Logs, Extended Events)<br>│<br>└── Kubernetes-Native Telemetry (sql_exporter → Prometheus → Grafana)</pre><p>By combining these three pillars, you ensure that no blind spots are left in your environment. Let’s look at how to set each one up.</p><h3>Layer 1 : Infrastructure Observability with Azure Monitor</h3><p>Before you blame the database engine for poor performance, you need to make sure the underlying Kubernetes infrastructure isn’t gasping for air. Azure Monitor for AKS (via Container Insights) acts as your foundational layer.</p><p>If your SQL Server pod is hitting its memory limits or experiencing high disk I/O latency on its Azure Disks, Azure Monitor is where you’ll see it first.</p><h4>Getting It Enabled</h4><p>You can flip the switch on Azure Monitor directly through the Azure Portal by navigating to your <strong>AKS Cluster → Insights → Enable</strong>. Alternatively, you can spin it up via the Azure CLI with a single command:</p><pre>az aks enable-addons \<br>  --resource-group &lt;your-resource-group&gt; \<br>  --name &lt;your-aks-cluster-name&gt; \<br>  --addons monitoring \<br>  --workspace-resource-id &lt;your-log-analytics-workspace-id&gt;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JX5zlue_HSc6vg_I5hzVsA.png" /></figure><h4>Navigating Container Insights</h4><p>Once data starts flowing, the <strong>Insights</strong> tab in the Azure Portal becomes your command center. It groups your metrics into clear, actionable areas:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/426/1*dYiRkcE_z7C_Cj5sh0e8SQ.gif" /></figure><p>Because all this telemetry lands in a Log Analytics Workspace, you can also run custom Kusto queries to spot trends or issues before they trigger alerts.</p><h3>Layer 2 : SQL Server Engine-Level Monitoring</h3><p>Infrastructure metrics tell you how hard your Kubernetes cluster is working, but not <em>what</em> your database is doing. To get the full picture, you need to look inside the SQL Server engine using three native tools: <strong>Query Store</strong>, <strong>Error Logs</strong>, and <strong>Extended Events (XEvents)</strong>.</p><p><strong>Note:</strong> Just like traditional SQL Server deployments on Windows machines, these powerful native diagnostic options are fully available and function exactly the same way when running containerized on Linux within AKS.</p><blockquote><strong>1. Query Store: The Flight Data Recorder</strong></blockquote><p>Query Store acts as an automatic flight data recorder, continuously capturing query history, execution plans, and runtime statistics directly within the database. It allows you to instantly spot plan regressions, trace resource-heavy queries, and track down performance trends over time. Because the data lives inside the database itself, your performance history survives even if the underlying hosting environment restarts.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/308/0*FjSTvhnyW7aO2QVk" /><figcaption><a href="https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver17">https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver17</a></figcaption></figure><p><a href="https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver17">Monitor Performance by Using the Query Store - SQL Server</a></p><blockquote><strong>2. SQL Server Error Logs: The Operational Diary</strong></blockquote><p>The Error Log is the engine’s daily journal, tracking system-level events like startup sequences, login failures, deadlocks, and memory pressure warnings. It is your go-to tool for root-cause analysis, making it easy to determine whether a sudden application interruption was caused by an internal database crash or a host-level system event.</p><blockquote><strong>3. Extended Events (XEvents): Real-Time Tracing</strong></blockquote><p>Extended Events is a lightweight, granular tracing framework designed to capture deep database activity in real time with minimal performance overhead. It is ideal for pinning down transient, intermittent bugs during sudden workload spikes, allowing you to track down exact deadlock graphs, blocking chains, and microsecond-level wait statistics the moment they happen.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/674/1*m2-CmvWQaDwP0PZ1jfAnCA.png" /></figure><h3>Layer 3 : Kubernetes-Native Telemetry (Prometheus &amp; Grafana)</h3><p>While native tools handle internal database tracking, you still need a cloud-native way to feed classic engine metrics like active connections, buffer cache hit ratios, and deadlock rates into your centralized engineering dashboards. This is where the open-source trifecta of <strong>sql_exporter</strong>, <strong>Prometheus</strong>, and <strong>Grafana</strong> comes into play.</p><p>By deploying an exporter alongside your database, you can translate raw SQL Server performance counters into Prometheus-compatible metrics, creating a seamless visualization pipeline across your entire Kubernetes ecosystem.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BHahUfZHDe2BLy6jzlEWgw.png" /></figure><p>For a complete, step-by-step walkthrough on setup, configuring custom collectors, and building Grafana dashboards for your environment, check out the below documentation link</p><p><a href="https://github.com/FoxInCode22/Monitoring_SQLServer_AKS">GitHub - FoxInCode22/Monitoring_SQLServer_AKS</a></p><h3>Conclusion</h3><p>Monitoring SQL Server on AKS requires visibility across both Kubernetes infrastructure and SQL Server engine internals. Azure Monitor and Container Insights provide foundational observability for cluster health, resource utilization, and container operations, while Query Store enables deep query performance analysis directly within SQL Server. SQL Server logs and Kubernetes events further assist with operational troubleshooting and root cause analysis.</p><p>To complement infrastructure monitoring, Prometheus, Grafana, and SQL Exporter introduce Kubernetes-native SQL telemetry by exposing engine-level metrics such as connections, memory utilization, database size, IO activity, and workload statistics. Together, these monitoring layers provide a comprehensive observability framework for SQL Server workloads running in containerized environments on AKS.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e913ddde3258" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Part 2: Enabling Vector Search on SQL Server for Linux Using Local Ollama Models]]></title>
            <link>https://medium.com/@LuciusFox22/part-2-enabling-vector-search-on-sql-server-for-linux-using-local-ollama-models-40654c0bddc5?source=rss-615c2b210af1------2</link>
            <guid isPermaLink="false">https://medium.com/p/40654c0bddc5</guid>
            <category><![CDATA[vector-database]]></category>
            <category><![CDATA[sql-server-on-linux]]></category>
            <category><![CDATA[sqlserver2025]]></category>
            <category><![CDATA[vector-embeddings]]></category>
            <category><![CDATA[ollama]]></category>
            <dc:creator><![CDATA[Lucius Fox]]></dc:creator>
            <pubDate>Fri, 02 Jan 2026 13:23:12 GMT</pubDate>
            <atom:updated>2026-01-02T13:23:12.669Z</atom:updated>
            <content:encoded><![CDATA[<p>Imagine being able to perform semantic search directly inside SQL Server without calling Azure OpenAI, sending data over the network, or paying per token?<br>This is now completely achievable on Linux thanks to lightweight local inference runtimes like Ollama and native vector support in SQL Server 2025.</p><p>In Part 1, we discussed embeddings, DiskANN indexes, and cloud‑powered RAG patterns. In this article, we explore how to:</p><ul><li>Run embedding models locally with Ollama</li><li>Generate vectors directly from SQL Server on Linux</li><li>Execute fast semantic similarity searches using native VECTOR_DISTANCE operations.</li></ul><p><strong>Configuring a Local AI Model on Linux for SQL Server 2025</strong></p><p>SQL Server 2025 supports integrating external AI models for tasks such as <strong>embedding generation</strong>.</p><p>However, there is an important requirement: <strong>The external model endpoints must be exposed over HTTPS</strong>.</p><p>Since most local model runtimes (like Ollama) expose <strong>HTTP-only endpoints by default</strong>, we need an HTTPS reverse proxy in front of them. This guide walks through the complete setup on Linux.</p><p><strong>Step 1: Install and Configure Ollama</strong></p><pre>curl -fsSL https://ollama.com/install.sh | sh # Installs ollama</pre><p>This installs the Ollama service and CLI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/982/1*Vc_ZfBEJFtjODoN8SIKkUQ.png" /></figure><pre>ollama pull nomic-embed-text # Pulls an embedding model</pre><p><strong>Pull an embedding model : </strong>This model produces <strong>768-dimension embeddings</strong>, which aligns well with SQL Server’s VECTOR type.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U3jeAAmsJTB0pi_u8kjbKg.png" /></figure><p><strong>Verify Ollama is running</strong></p><pre>curl -k http://localhost:11434/api/tags  # Checks the api endpoint</pre><p><strong>Why an HTTPS Proxy Is Required?</strong></p><p>SQL Server 2025 enforces HTTPS for all external REST integrations, including:<br>- External models<br>- REST-based AI inference</p><p>Because Ollama exposes only HTTP, we introduce <strong>Caddy</strong> as a local HTTPS reverse proxy.</p><p><strong>Step 2: Install Caddy (HTTPS Reverse Proxy)</strong></p><pre>sudo apt install -y caddy<br><br>#backup the default config<br><br>sudo cp /etc/caddy/Caddyfile /etc/caddy/Caddyfile.bak</pre><p><strong>Step 4: Configure Caddy to Proxy Ollama over HTTPS</strong></p><p>Replace the Caddy configuration with the following:</p><pre>sudo tee /etc/caddy/Caddyfile &gt;/dev/null &lt;&lt;&#39;EOF&#39;<br>https://localhost:11435 {<br>  tls internal<br>  reverse_proxy 127.0.0.1:11434<br>}<br>EOF<br><br>#reload Caddy<br><br>sudo systemctl reload caddy</pre><ul><li>tls internal tells Caddy to generate its own local CA and certificates</li><li>Traffic on <a href="https://localhost:11435/">https://localhost:11435</a> is securely forwarded to Ollama on port 11434</li></ul><p><strong>Step 5: Trust Caddy’s Certificate (Critical Step)</strong></p><p>The <strong>local root CA </strong>generated by Caddy must be trusted by:</p><ol><li>SQL Server’s SQLPAL layer</li><li>The Linux OS</li></ol><pre>#Trust the certificate at SQL Server(SQLPAL)<br>#Create the certificate directory<br><br>sudo mkdir -p /var/opt/mssql/security/ca-certificates<br><br>#Copy Caddy’s root certificate<br><br>sudo cp /var/lib/caddy/.local/share/caddy/pki/authorities/local/root.crt \<br>    /var/opt/mssql/security/ca-certificates/caddy-root.crt<br><br>#Fix permissions<br><br>sudo chown -R mssql:mssql /var/opt/mssql/security<br>sudo chmod 744 /var/opt/mssql/security/ca-certificates<br>sudo chmod 644 /var/opt/mssql/security/ca-certificates/caddy-root.crt<br><br>#Trust the certificate at OS level<br><br>sudo cp /var/lib/caddy/.local/share/caddy/pki/authorities/local/root.crt \<br>    /usr/local/share/ca-certificates/caddy-local-ca.crt<br>sudo update-ca-certificates<br><br>#Restart SQL Server<br>sudo systemctl restart mssql-server</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nuRkOKrirG1P5Rf0cTaw0A.png" /><figcaption>Configuring a trusted CA certificate for SQL Server on Linux by placing it in the PAL security directory.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uXan2SaEuaMcFl9WZipfnA.png" /><figcaption>Adding the Caddy-generated root certificate to the Linux CA trust store</figcaption></figure><p><strong>Step 6 : Test embedding generation over HTTPS</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2JX2G6Wm6XBtTudBGp8tbA.png" /></figure><pre>#Architecture<br>SQL Server 2025<br>   |<br>   |  HTTPS (trusted)<br>   v<br>Caddy (11435, TLS internal)<br>   |<br>   |  HTTP<br>   v<br>Ollama (11434)</pre><p>After configuring the Ollama embedding model on the local server, the next step is the SQL Server workflow: ingesting unstructured text, generating embeddings, storing them in native vector columns, and performing vector similarity search.</p><p>This repository contains a complete, step-by-step set of SQL scripts to implement the entire workflow:</p><p><a href="https://github.com/FoxInCode22/sql-server-vector-search-ollama-linux/tree/main">GitHub - FoxInCode22/sql-server-vector-search-ollama-linux</a></p><h4>Summary</h4><p>The repository demonstrates how to integrate a locally hosted AI embedding model with SQL Server 2025 on Linux while meeting the platform’s strict HTTPS requirements for external model access. The solution uses Ollama as the local model runtime and Caddy as an HTTPS reverse proxy to securely expose the model endpoint.</p><p>The walkthrough covers end-to-end setup, including installing and configuring Ollama, securing the model endpoint with Caddy’s internal TLS, and correctly establishing certificate trust at both the Linux OS level and within SQL Server’s SQLPAL certificate store. This ensures SQL Server can securely invoke the external model without bypassing platform security constraints.</p><p>The repository also includes a complete set of SQL scripts that implement a full vector search workflow in SQL Server 2025. This workflow ingests unstructured text data, generates embeddings using the external model, stores them in native vector columns, optionally creates a vector index, and executes vector similarity queries. A real-world dataset is used to validate the approach and demonstrate search results.</p><p>Overall, the repository provides a practical, security-compliant reference architecture for running local AI models alongside SQL Server 2025 on Linux and performing native vector search without relying on managed cloud AI services.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=40654c0bddc5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[RAG on SQL Server for Linux: Vectors, DiskANN, and OpenAI]]></title>
            <link>https://medium.com/@LuciusFox22/rag-on-sql-server-for-linux-vectors-diskann-and-openai-d919c5ea9823?source=rss-615c2b210af1------2</link>
            <guid isPermaLink="false">https://medium.com/p/d919c5ea9823</guid>
            <category><![CDATA[vector-database]]></category>
            <category><![CDATA[sqlserver2025]]></category>
            <dc:creator><![CDATA[Lucius Fox]]></dc:creator>
            <pubDate>Sat, 20 Dec 2025 13:08:05 GMT</pubDate>
            <atom:updated>2025-12-23T11:36:34.913Z</atom:updated>
            <content:encoded><![CDATA[<p>SQL Server 2025 introduces a fundamental shift in how developers build AI‑powered applications by enabling <strong>native vector storage</strong>, <strong>in‑database embedding operations</strong>, and <strong>high‑performance vector search </strong>all running directly inside the SQL Server engine, including on Linux distributions such as Ubuntu, RHEL, and SUSE.</p><p>These features allow SQL Server to function as a <strong>complete vector database</strong>, eliminating the need for external vector stores and deeply simplifying Retrieval‑Augmented Generation (RAG) architectures.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2ifQkCiqc2AGjJxhJY1yHw.png" /><figcaption>Vector Architecture in SQL Server 2025</figcaption></figure><h4>1.<strong>Embeddings: The Semantic Backbone of AI in SQL Server 2025</strong></h4><p><em>Embeddings are numerical vectors that capture the semantic meaning of data such as text, images, or structured information</em><strong><em>. </em></strong>SQL Server 2025 introduces the VECTOR data type, a native and optimized format designed to efficiently store and process high-dimensional embeddings. These embeddings are model-generated, fixed-length numeric vectors that represent data in a continuous vector space, enabling similarity search through distance metrics such as cosine similarity or dot product.<br>For example, the words <em>“doctor”</em> and <em>“physician”</em> generate vectors that are close to each other geometrically because they are semantically similar.</p><p><strong>Usage and Syntax : </strong>To use vector capabilities, you must first enable preview features for the database:</p><pre>ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;</pre><p><strong>Why Store Embeddings in SQL Server?</strong></p><p>Traditional AI architectures often rely on a separate vector database for semantic search and retrieval. With SQL Server 2025, Microsoft brings vector storage and search capabilities directly into the relational engine, enabling a more unified architecture:</p><ul><li>Store relational data and embeddings side by side in the same database.</li><li>Eliminate or significantly reduce ETL latency between operational and semantic layers.</li><li>Secure embeddings as first-class data using native SQL Server capabilities such as Row-Level Security (RLS), Transparent Data Encryption (TDE), and auditing.</li></ul><h4>How to store embeddings?</h4><p>SQL Server represents embeddings using the new VECTOR(n) data type.</p><pre>CREATE TABLE Documents (<br>    Id INT PRIMARY KEY,<br>    Content NVARCHAR(MAX),<br>    Embedding VECTOR(1536)</pre><p>To generate embeddings, SQL Server can call external model providers using the built‑in <em>sp_invoke_external_rest_endpoint</em> stored procedure.</p><h3><strong>2. Vector Search</strong></h3><p>Vector Search refers to finding embeddings that are <em>similar</em> to a query embedding.</p><p>SQL Server provides two distinct methods for finding similar data points: <strong>Exact Search</strong> for precision and <strong>Approximate Search</strong> for speed at scale.</p><p><strong>Exact Search: VECTOR_DISTANCE</strong></p><p>For smaller datasets, you can calculate the exact mathematical distance between vectors. This operation scans every row, providing 100% recall (accuracy) but lower performance as data scales.</p><p><strong>Approximate Search: VECTOR_SEARCH</strong></p><p>For large-scale applications, SQL Server 2025 introduces the VECTOR_SEARCH function, which uses a specialized vector index to perform Approximate Nearest Neighbor (ANN) searches. This index enables efficient retrieval of vectors close to a query, dramatically improving performance compared to exhaustive scans on large datasets.</p><h3><strong>3. DiskANN Indexes: High‑Performance ANN in SQL Server</strong></h3><p>SQL Server 2025 leverages DiskANN, a graph-based ANN indexing algorithm to accelerate vector similarity search. DiskANN is designed for SSD-backed indexes, enabling large-scale vector workloads without excessive memory requirements. This makes it possible to manage and query billions of embeddings directly within SQL Server while maintaining predictable resource usage.</p><p>To enable ANN search, you create a VECTOR INDEX specifying the DISKANN type.</p><pre>CREATE VECTOR INDEX IX_DocumentsRag_Embedding<br>ON dbo.DocumentsRag (Embedding)<br>WITH (<br>    METRIC = &#39;cosine&#39;,<br>    TYPE = &#39;DiskANN&#39;<br>);<br>GO</pre><p><strong>How DiskANN Works?</strong></p><ul><li>SQL Server builds a <strong>graph</strong> connecting all vectors.</li><li>Query vectors traverse the graph quickly by exploring promising neighbors first.</li><li>DiskANN relies on a graph structure to index vectors and is designed for SSD-backed storage with minimal memory overhead.</li><li>This enables extremely large datasets without memory explosion.</li></ul><p><strong>Putting It All Together: RAG on SQL Server</strong></p><p>A typical RAG flow inside SQL Server:</p><ol><li>Store source documents in SQL.</li><li>Generate embeddings using Azure OpenAI.</li><li>Store embeddings in VECTOR(n) columns.</li><li>Build DiskANN vector indexes.</li><li>Run semantic search using VECTOR_SEARCH.</li><li>Pass retrieved documents into an LLM for grounded generation.</li></ol><p>All inside the SQL engine, no separate stores, no ETL.</p><p>For the complete code and detailed implementation, check out the GitHub repository at</p><p><a href="https://github.com/FoxInCode22/sqlserver-2025-rag-linux/tree/main">GitHub - FoxInCode22/sqlserver-2025-rag-linux: Retrieval-Augmented Generation (RAG) on SQL Server 2025 in Linux</a></p><h4>Summary</h4><p>SQL Server 2025 elevates SQL Server on Linux from a traditional RDBMS to a <strong>hybrid relational + vector database</strong>, capable of powering modern AI applications:</p><ul><li><strong>Embeddings</strong>: Store semantic representations of data</li><li><strong>Vector Search</strong>: Query for similarity using exact or approximate search</li><li><strong>DiskANN Indexes</strong>: High-scale, high-speed ANN search inside SQL</li></ul><p>In part 2 of this blog, we will look into enabling vector search using local Ollama models with SQL on Linux</p><p>References :</p><p><a href="https://learn.microsoft.com/en-us/sql/sql-server/ai/vectors?view=sql-server-ver17">Vector Search &amp; Vector Index - SQL Server</a></p><p><a href="https://github.com/microsoft/bobsql/tree/master/demos/sqlserver2025/AI">bobsql/demos/sqlserver2025/AI at master · microsoft/bobsql · GitHub</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d919c5ea9823" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Forecasting the Future: Building an End-to-End Time-Series Pipeline with LSTM & Prophet on Azure…]]></title>
            <link>https://medium.com/@LuciusFox22/forecasting-the-future-building-an-end-to-end-time-series-pipeline-with-lstm-prophet-on-azure-1d1c75bc09a0?source=rss-615c2b210af1------2</link>
            <guid isPermaLink="false">https://medium.com/p/1d1c75bc09a0</guid>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[timeseries-forecasting]]></category>
            <category><![CDATA[azure-postgresql]]></category>
            <category><![CDATA[microsoft-azure]]></category>
            <dc:creator><![CDATA[Lucius Fox]]></dc:creator>
            <pubDate>Sun, 09 Nov 2025 02:51:54 GMT</pubDate>
            <atom:updated>2025-11-09T02:51:54.513Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Forecasting the Future: Building an End-to-End Time-Series Pipeline with LSTM &amp; Prophet on Azure PostgreSQL</strong></h3><p>In today’s data-driven world, <strong>time-series forecasting</strong> powers countless applications, from predicting temperatures and electricity load to anticipating stock prices and traffic flows. Yet did you know that you can build a <strong>complete forecasting pipeline</strong>: from a cloud database, through deep learning and statistical modelling, all the way to a deployable model in one notebook?</p><p>In this post, I’ll walk you through how I built such a pipeline. The underlying dataset is the daily climate data for Delhi.</p><p>Tech Stack :</p><ul><li><strong>Azure Database for PostgreSQL</strong> for the data source</li><li><strong>Python (Pandas, TensorFlow/Keras, Prophet)</strong> for modelling</li><li><strong>Azure ML (optional)</strong> for dataset registration and model versioning</li></ul><p>By the end of this guide you’ll understand how to go from raw CSV to trained model, and compare two very different forecasting approaches: <strong>neural sequence modelling (LSTM)</strong> and <strong>additive statistical forecasting (Prophet)</strong>.</p><h3><strong>Why this approach?</strong></h3><p>Because many real-world forecasting problems share the same structure:</p><ul><li>Data stored in a scalable cloud database.</li><li>Need to build a reproducible pipeline (extract → clean → model → save).</li><li>Want to compare multiple modelling strategies.</li><li>Need artifacts (model + scaler + forecast) ready for deployment or further analysis.</li></ul><p>With this notebook I demonstrate how you can <em>practically</em> build this in one place, and how you can apply it to other domains; not just weather but also IoT, demand forecasting and more.</p><h3><strong>Dataset &amp; Data Engineering</strong></h3><p>The dataset I use comes from the <strong>Daily Delhi Climate Time Series</strong>: daily measurements of mean temperature, humidity, wind speed and pressure in Delhi.</p><p>The key steps:</p><ol><li><strong>Read the CSV</strong> (or extract from PostgreSQL via psycopg2).</li><li>Convert date column to datetime and set as index.</li><li><strong>Handle missing values if any, and resample to uniform frequency (daily).</strong></li><li><strong>Visualise trends and seasonality.</strong></li><li><strong>Focus on one target variable — meantemp — for modelling.</strong></li></ol><p>This gives you a clean series: ds = date, y = temperature.</p><h3><strong>Modelling Approach</strong></h3><p><strong>Model A: Prophet (Interpretability First)</strong></p><p>I restructure the data into the format required by Prophet: rename date → ds and meantemp → y.</p><p>Key steps:</p><ul><li>Fit the Prophet model with historical data.</li><li>Generate a <em>future dataframe</em> for a forecast horizon (e.g., next 48 hours / days).</li><li>Obtain predictions (yhat) along with yhat_lower, yhat_upper (confidence intervals).</li><li>Visualise forecast + components (trend, seasonality).</li></ul><p><strong>Why use Prophet?</strong></p><p>It is great when you care about <strong>interpretable trends</strong> and <strong>seasonality</strong> (weekly/annual cycles). It also gives confidence intervals, which many neural methods don’t by default.</p><pre>df_prophet = pd.read_csv(csv_out, parse_dates=[&#39;date&#39;])<br>df_prophet = df_prophet.rename(columns={&#39;date&#39;: &#39;ds&#39;, &#39;meantemp&#39;: &#39;y&#39;})[[&#39;ds&#39;,&#39;y&#39;]].dropna()<br><br>m = Prophet()<br>m.fit(df_prophet)<br><br>PERIODS, FREQ = 48, &#39;H&#39;  # predict next 48 hours<br>future = m.make_future_dataframe(periods=PERIODS, freq=FREQ)<br>forecast = m.predict(future)<br><br>forecast_out = os.path.join(repo_root, &#39;outputs&#39;, &#39;prophet_forecast.csv&#39;)<br>forecast.to_csv(forecast_out, index=False)<br>print(&#39;Forecast →&#39;, forecast_out)<br><br>fig1 = m.plot(forecast); plt.title(&#39;Prophet forecast&#39;); plt.show()<br>fig2 = m.plot_components(forecast); plt.show()</pre><p><strong>Model B: LSTM (Neural Sequence Modelling)</strong></p><p>Here I build a deep learning model using TensorFlow/Keras:</p><ul><li>Scale the target series using StandardScaler.</li><li>Create sliding windows of size <strong>WINDOW = 24</strong> (e.g., past 24 days/hours) to predict the next value.</li><li>Reshape into (samples, window, 1) for LSTM input.</li><li>Split data into training (80 %) and validation (20 %).</li><li>Define an architecture:<br> LSTM(64, return_sequences=True) → Dropout(0.2) → LSTM(32) → Dense(1)</li><li>Train with MSE loss, Adam optimizer, and early stopping.</li><li>Save: the trained model (.keras file) and the scaler (.pkl).</li></ul><p><strong>Why use LSTM?</strong></p><p>When you expect <strong>short-term dependencies</strong> and <strong>non-linear relationships</strong> (for example, the temperature today influenced by past 24 hours/hours), LSTM can capture patterns that a simpler additive model might miss.</p><pre>os.makedirs(os.path.join(repo_root, &#39;outputs&#39;), exist_ok=True)<br><br>df_prep = load_and_prepare(csv_out, &#39;date&#39;, &#39;meantemp&#39;)<br>series = df_prep[&#39;meantemp&#39;].values.reshape(-1, 1)<br><br>scaler = StandardScaler()<br>series_scaled = scaler.fit_transform(series).flatten()<br><br>WINDOW = 24<br>X, y = create_lstm_windows(series_scaled, window_size=WINDOW)<br>X = X.reshape((X.shape[0], X.shape[1], 1))<br><br>split = int(0.8 * len(X))<br>X_train, X_val = X[:split], X[split:]<br>y_train, y_val = y[:split], y[split:]<br><br>model = keras.models.Sequential([<br>    keras.layers.Input(shape=(WINDOW, 1)),<br>    keras.layers.LSTM(64, return_sequences=True),<br>    keras.layers.Dropout(0.2),<br>    keras.layers.LSTM(32),<br>    keras.layers.Dense(1)<br>])<br>model.compile(optimizer=&#39;adam&#39;, loss=&#39;mse&#39;, metrics=[&#39;mae&#39;])<br><br>history = model.fit(<br>    X_train, y_train,<br>    validation_data=(X_val, y_val),<br>    epochs=20,<br>    batch_size=32,<br>    callbacks=[keras.callbacks.EarlyStopping(patience=5, restore_best_weights=True)],<br>    verbose=2<br>)<br><br># Plot loss<br>plt.figure(); plt.plot(history.history[&#39;loss&#39;], label=&#39;train&#39;); plt.plot(history.history[&#39;val_loss&#39;], label=&#39;val&#39;);<br>plt.legend(); plt.title(&#39;LSTM loss&#39;); plt.show()</pre><h3><strong>Results &amp; Interpretation</strong></h3><p><strong>Prophet Output</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/798/1*Cax0wZuwpZX66t1vAJINmw.png" /></figure><ul><li>A CSV of future predictions (prophet_forecast.csv) with columns like ds, yhat, yhat_lower, yhat_upper.</li><li>A visual plot: historical data points + forecast line + uncertainty band.</li><li>Component plots showing how much of the variation is due to trend, weekly/annual seasonality, and residuals.</li></ul><p><strong>Interpretation</strong></p><p>Prophet nicely captures the overarching trend and seasonal cycles. If you want to explain the forecast to stakeholders (“This is because summer comes in these months, trend is upward”), it’s very useful. However, it may lag behind sudden shifts or short-term spikes.</p><p><strong>LSTM Output</strong></p><ul><li>The training/validation loss curves show how the model learned (and whether overfitting occurs).</li><li>The saved model and scaler allow you to generate future predictions later.</li><li>You can also plot predicted vs actual to visualise error.</li></ul><p><strong>Interpretation</strong></p><p>The LSTM model is less immediately explainable, but in many cases it can <strong>reduce error</strong> for short-horizon forecasts by learning pattern dependencies. It is best when you have sequences and want responsiveness, but harder to interpret.</p><h3><strong>Comparative Insights</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/674/1*g4NNksI80lv8e_pMmTVBOQ.jpeg" /></figure><p>These two models are complementary. Use Prophet to <em>understand</em> the baseline trend and seasonality; use LSTM to <em>exploit</em> sequence-based patterns and refine predictions.</p><p>For the complete code and detailed implementation, check out the GitHub repository at</p><p><a href="https://github.com/FoxInCode22/forecasting-pipeline-azure-postgresql-ml/blob/main/README.md">forecasting-pipeline-azure-postgresql-ml/README.md at main · FoxInCode22/forecasting-pipeline-azure-postgresql-ml</a></p><h3><strong>Final Thoughts</strong></h3><p>Time-series forecasting is both an art and a science. It requires understanding the data, choosing the right modelling tools, and aligning output with business or operational needs. By combining <strong>Prophet</strong> (for interpretability) and <strong>LSTM</strong> (for sequence learning), you get the best of both worlds: insights and performance.</p><p>Everything from data ingestion (Azure PostgreSQL) to model output (saved artifacts) is encapsulated in this single workflow. You can copy, adapt, and scale it for your own use.</p><p>Happy forecasting! And may your next model predict what matters before it happens!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1d1c75bc09a0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Azure Automation + SQL Server: Tackling the Kerberos Delegation Challenge]]></title>
            <link>https://medium.com/@LuciusFox22/azure-automation-sql-server-tackling-the-kerberos-delegation-challenge-0a64b5da7bc3?source=rss-615c2b210af1------2</link>
            <guid isPermaLink="false">https://medium.com/p/0a64b5da7bc3</guid>
            <category><![CDATA[azure-runbook]]></category>
            <category><![CDATA[kerberos]]></category>
            <category><![CDATA[kerberos-delegation]]></category>
            <category><![CDATA[credssp]]></category>
            <category><![CDATA[sql-server]]></category>
            <dc:creator><![CDATA[Lucius Fox]]></dc:creator>
            <pubDate>Sun, 19 Oct 2025 04:31:53 GMT</pubDate>
            <atom:updated>2025-10-19T04:31:53.171Z</atom:updated>
            <content:encoded><![CDATA[<h4>Introduction</h4><p><strong>Picture this:</strong> Your Azure Automation runbook runs flawlessly on the Hybrid Worker — until it needs to query SQL Server. Suddenly, authentication fails, and you’re staring at this dreaded error:</p><p>Login failed for user &#39;NT AUTHORITY\ANONYMOUS LOGON&#39;</p><p>What happened? You’ve just hit the classic <strong>Kerberos double-hop problem,</strong> a common challenge when scripts jump across multiple machines. In our case, the flow looked like this:</p><p><strong>Azure Automation → Hybrid Runbook Worker → SQL Server VM</strong></p><p>The credentials didn’t make it past the second hop, leaving SQL Server with no identity to authenticate. Recently, we helped a customer troubleshoot this exact scenario. Here’s why it happens and how to fix it.</p><h4>Understanding the Problem</h4><p>When PowerShell scripts run on <strong>Hybrid Runbook Workers (HRW)</strong>, they typically execute under a <strong>local system account</strong> or a <strong>Run As account</strong>. By default, these sessions <strong>do not carry a delegated Kerberos ticket</strong>.</p><p>This becomes a problem when your script needs to make a second hop — like connecting from the Hybrid Worker to SQL Server. Kerberos cannot delegate the user’s credentials, so SQL Server sees <strong>NT AUTHORITY\ANONYMOUS LOGON</strong> instead of the actual user.</p><p><strong>Flow of failure:</strong></p><pre>Azure Automation → Hybrid Runbook Worker (1st hop) → SQL Server VM (2nd hop)</pre><p>This limitation is well-documented:</p><p><a href="https://learn.microsoft.com/en-us/powershell/scripting/security/remoting/ps-remoting-second-hop?view=powershell-7.5">Making the second hop in PowerShell Remoting - PowerShell</a></p><h3>Why Constrained Delegation Won’t Help</h3><p>While <strong>Constrained Kerberos Delegation</strong> is often used in AD environments, it <strong>does not support WinRM’s second hop</strong> for PowerShell remoting. So, this approach won’t solve the issue in Azure Automation scenarios.</p><h3>The Recommended Fix: CredSSP</h3><p>The best solution is to enable <strong>Credential Security Support Provider (CredSSP)</strong>, which allows credential delegation and resolves the double-hop issue.</p><h3>Steps to Enable CredSSP</h3><h4>On the Client (Hybrid Runbook Worker):</h4><pre>Enable-WSManCredSSP -Role Client -DelegateComputer sqlnewdb.contoso.com</pre><h4>On the Server (SQL Machine):</h4><pre>Enable-WSManCredSSP -Role Server</pre><h3>Sample Script Using CredSSP</h3><p>Here’s a working example that verifies CredSSP and SQL connectivity:</p><pre># Get credentials from Azure Automation Credential Asset<br><br>$cred = Get-AutomationPSCredential -Name &quot;sqldem&quot;<br>$targetServerName = &quot;sqlnewdb&quot;<br>$remoteComputer = &quot;hybridwrk&quot;<br><br>Write-Output &quot;Username: $($cred.UserName)&quot;<br><br>$result = Invoke-Command -ComputerName $remoteComputer -Credential $cred -Authentication Credssp -ScriptBlock  {<br>    Write-Output &quot;SELECT SYSTEM_USER, HOST_NAME(), @@SERVERNAME&quot;<br>    sqlcmd -S sqlnewdb -E -Q &quot;SELECT SYSTEM_USER, HOST_NAME(), @@SERVERNAME&quot;<br>} -ArgumentList $targetServerName<br><br>Write-Output $result</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*go_kQMg2kQsDB4gu0rabNw.png" /></figure><p>We are able to test it with the simple script, this query verifies :</p><ul><li>SYSTEM_USER → Which account SQL Server sees.</li></ul><p>Here CONTOSO\Sqlservice → Indicates the SQL query executed under the intended domain account, not NT AUTHORITY\ANONYMOUS LOGON.</p><ul><li>HOST_NAME() → The machine initiating the SQL connection.(Hybrid worker)</li><li>@@SERVERNAME → The SQL Server instance name.</li></ul><pre>[Azure Automation] → [Hybrid Runbook Worker] → [SQL Server VM]<br>       (CredSSP enabled)          (CredSSP enabled)</pre><h4>Key Takeaways</h4><ul><li>The error occurs due to <strong>Kerberos double-hop limitations</strong> in PowerShell remoting.</li><li><strong>CredSSP</strong> is the recommended approach for enabling credential delegation in this scenario.</li><li>Always enable CredSSP on <strong>both the Hybrid Worker and the SQL Server VM</strong>.</li></ul><h3>Wrapping Up</h3><p>I hope this walkthrough provides clarity and helps you resolve similar scenarios with confidence. Implementing <strong>CredSSP</strong> is a simple yet effective way to overcome the Kerberos double-hop challenge in Azure Automation Hybrid Runbooks.</p><p><strong>Keep exploring, keep learning!</strong></p><p>References :</p><ul><li><a href="https://devblogs.microsoft.com/scripting/enable-powershell-second-hop-functionality-with-credssp/">Enable PowerShell &quot;Second-Hop&quot; Functionality with CredSSP - Scripting Blog [archived]</a></li><li><a href="https://learn.microsoft.com/en-us/powershell/module/microsoft.wsman.management/enable-wsmancredssp?view=powershell-7.5">Enable-WSManCredSSP (Microsoft.WSMan.Management) - PowerShell</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0a64b5da7bc3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a ChatGPT- like SQL Assistant: Empowering Natural Language Queries with Semantic Kernel]]></title>
            <link>https://medium.com/@LuciusFox22/building-a-chatgpt-like-sql-assistant-empowering-natural-language-queries-with-semantic-kernel-a1d7b02e80fe?source=rss-615c2b210af1------2</link>
            <guid isPermaLink="false">https://medium.com/p/a1d7b02e80fe</guid>
            <category><![CDATA[chatgpt]]></category>
            <category><![CDATA[microsoft-semantic-kernel]]></category>
            <category><![CDATA[llm-applications]]></category>
            <category><![CDATA[azure-postgresql]]></category>
            <category><![CDATA[azureopenai]]></category>
            <dc:creator><![CDATA[Lucius Fox]]></dc:creator>
            <pubDate>Sat, 11 Oct 2025 12:02:10 GMT</pubDate>
            <atom:updated>2025-10-12T05:20:59.976Z</atom:updated>
            <content:encoded><![CDATA[<p>In the era of data-driven decision-making, databases hold the keys to insights, but querying them often requires SQL expertise which is a barrier for many users. Letting end users or analysts query relational databases using <em>natural language</em> (NL) is becoming a compelling UX paradigm. Instead of writing SQL, users could ask questions like <em>“Show me the top 10 customers by revenue last quarter”</em>, and the system translates it into a valid SQL query to fetch results.</p><p>In this post, I walk through the architecture, design considerations, trade-offs, and challenges behind <strong>semantic-sql-assistant </strong>and discuss lessons, limitations, and possible extensions. By the end, you’ll have a working example querying the classic Iris dataset — measuring flower attributes to classify species — while grounding LLM outputs in real database facts to minimize hallucinations.</p><h4>A Quick Intro to Semantic Kernel</h4><p>Semantic Kernel is an open-source SDK from Microsoft designed to simplify the development of AI-orchestrated applications. It acts as a lightweight framework for integrating large language models (LLMs) like those from Azure OpenAI with your code, enabling “skills” — reusable, composable functions that chain prompts, tools, and logic into intelligent agents. Think of it as a conductor for AI workflows: it handles prompt engineering, function calling, and memory management, making it easier to build systems like chatbots, RAG pipelines, or our NLQ demo here. With support for Python, C#, and Java, it’s versatile for cross-platform prototypes, emphasizing modularity to reduce boilerplate while grounding outputs in real data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aHXX5CG0aQA0BGZqzHfVLw.png" /></figure><h4>Project Overview: From Words to SQL and Back</h4><p>The Semantic SQL Assistant is a lightweight, open-source demo that orchestrates an end-to-end NLQ pipeline:</p><p>1. <strong>Input</strong>: A natural language question from the user.</p><p>2. <strong>SQL Generation</strong>: An LLM (via Azure OpenAI) translates the question into executable SQL, guided by schema-aware prompts.</p><p>3. <strong>Query Execution</strong>: Run the SQL against an Azure PostgreSQL instance (or mock it for testing).</p><p>4. <strong>Response Synthesis</strong>: The LLM formats results into readable English.</p><h4><strong>Step-by-Step Setup: From Kernel to Skills</strong></h4><p>Step 1 : Environment and Kernel Initialization. We start by loading configs and bootstrapping SK</p><pre>import os<br>from dotenv import load_dotenv<br>import psycopg2<br>import semantic_kernel as sk<br>from semantic_kernel.connectors.ai.open_ai import AzureChatCompletion<br>import json<br>import asyncio<br><br># Load environment variables<br>load_dotenv(override=True)<br><br>kernel = sk.Kernel()<br>deployment_name = os.getenv(&quot;AZURE_OPENAI_DEPLOYMENT&quot;)<br>endpoint = os.getenv(&quot;AZURE_OPENAI_ENDPOINT&quot;)<br>api_key = os.getenv(&quot;AZURE_OPENAI_KEY&quot;)<br>try:<br>    kernel.add_service(<br>        AzureChatCompletion(<br>            service_id=&quot;azure-openai&quot;,<br>            deployment_name=deployment_name,<br>            endpoint=endpoint,<br>            api_key=api_key,<br>        )<br>    )<br>except Exception as e:<br>    print(f&quot;Failed to initialize AzureChatCompletion: {e}&quot;)<br>    raise</pre><p>Step 2 : Database Connection</p><pre>try:<br>    conn = psycopg2.connect(<br>        host=os.getenv(&quot;PGHOST&quot;),<br>        dbname=os.getenv(&quot;PGDATABASE&quot;),<br>        user=os.getenv(&quot;PGUSER&quot;),<br>        password=os.getenv(&quot;PGPASSWORD&quot;),<br>        port=os.getenv(&quot;PGPORT&quot;),<br>    )<br>    cursor = conn.cursor()<br>except psycopg2.Error as e:<br>    print(f&quot;Failed to connect to database: {e}&quot;)<br>    raise</pre><p>Step 3 : Defining Skills: The AI Magic</p><ul><li>Here, we define:</li></ul><ol><li>GenerateSql → Converts natural language into SQL queries.</li><li>AnswerQuestion → Formats raw query results into human-readable answers.</li></ol><pre># Define SQL and Answer functions<br>sql_skill = kernel.add_function(<br>    plugin_name=&quot;SqlSkill&quot;,<br>    function_name=&quot;GenerateSql&quot;,<br>    prompt=&quot;&quot;&quot;<br>    You are a SQL generator for a PostgreSQL database.<br>    Convert the user natural language question into a SQL query for the &#39;iris&#39; table with columns: sepal_length, sepal_width, petal_length, petal_width, species.<br>    Return only the SQL query without explanation.<br>    For average calculations, use ROUND(AVG(column)::numeric, 1) to handle double precision.<br>    Examples:<br>    - For &quot;average sepal length by species&quot;: &quot;SELECT species, ROUND(AVG(sepal_length)::numeric, 1) AS avg_sepal_length FROM iris GROUP BY species;&quot;<br>    - For &quot;list virginica flowers with petal width greater than 2.0&quot;: &quot;SELECT sepal_length, sepal_width, petal_length, petal_width FROM iris WHERE species = &#39;virginica&#39; AND petal_width &gt; 2.0;&quot;<br>    User Question: {{$input}}<br>    &quot;&quot;&quot;<br>)<br><br>answer_skill = kernel.add_function(<br>    plugin_name=&quot;AnswerSkill&quot;,<br>    function_name=&quot;AnswerQuestion&quot;,<br>    prompt=&quot;&quot;&quot;<br>    You are a helpful assistant.<br>    Answer the user question based only on the provided database results in JSON format.<br>    If the results are empty, say &quot;No data available to answer the question.&quot;<br>    For averages, use the format: &quot;The average sepal length is X for setosa, Y for versicolor, and Z for virginica.&quot;<br>    For lists, use: &quot;There are N &lt;species&gt; flowers with &lt;condition&gt;: (x1, x2, x3, x4), ...&quot;<br>    Numerical values should be rounded to one decimal place.<br>    User Question: {{$input}}<br>    SQL Results: {{$results}}<br>    &quot;&quot;&quot;<br>)</pre><p>The function demonstrates the use of <strong>Few-Shot Learning</strong> — a prompt engineering technique that enables a large language model to generalize to new tasks by providing only a few representative examples, without any retraining or fine-tuning.</p><p>In this case, the prompt explicitly defines:</p><ul><li>The <strong>task context</strong> (translate natural language to SQL for the Iris dataset)</li><li>The <strong>schema structure</strong> (table columns and types)</li><li>A few <strong>example pairs</strong> of questions and corresponding SQL queries</li></ul><p>Step 4 : Orchestration: The ask_db Pipeline.</p><p>Tie it together in an async function:</p><pre># --- Orchestration Function ---<br># The ask_db function:<br>#   1. Takes a natural language question<br>#   2. Uses the SQL skill to generate a SQL query<br>#   3. Executes the SQL query against Azure PostgreSQL<br>#   4. Uses the Answer skill to turn results into a friendly response<br>#   5. Returns the final answer to the user<br><br>async def ask_db(question):<br>    print(f&quot;Processing question: {question}&quot;)<br>    <br>    # Generate SQL query<br>    sql_skill = kernel.get_function(&quot;SqlSkill&quot;, &quot;GenerateSql&quot;)<br>    try:<br>        sql_query_result = await sql_skill.invoke(kernel, input=question)<br>        sql_query = str(sql_query_result)<br>        print(f&quot;Generated SQL: {sql_query}&quot;)<br>    except Exception as e:<br>        return f&quot;Error generating SQL: {e}&quot;<br><br>    # Execute SQL query<br>    try:<br>        cursor.execute(sql_query)<br>        results = cursor.fetchall()<br>        columns = [desc[0] for desc in cursor.description]<br>        sql_results = [dict(zip(columns, row)) for row in results]<br>        print(f&quot;SQL Results: {sql_results}&quot;)<br>        conn.commit()  <br>    except psycopg2.Error as e:<br>        conn.rollback() <br>        return f&quot;Database error: {e}&quot;<br><br>    # Answer the question<br>    answer_skill = kernel.get_function(&quot;AnswerSkill&quot;, &quot;AnswerQuestion&quot;)<br>    try:<br>        answer = await answer_skill.invoke(<br>            kernel,<br>            input=question,<br>            results=json.dumps(sql_results)<br>        )<br>        return str(answer)<br>    except Exception as e:<br>        return f&quot;Error generating answer: {e}&quot;</pre><h3>Hands-On Demo: Querying the Iris Dataset</h3><p>Fire up the notebook and test! Sample runs:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/816/1*rC8-J8HE9U-7VR2pauJKJw.png" /></figure><p>For the complete code and detailed implementation, check out the GitHub repository at</p><p><a href="https://github.com/FoxInCode22/semantic-sql-assistant/blob/main/README.md">semantic-sql-assistant/README.md at main · FoxInCode22/semantic-sql-assistant</a></p><h3>Limitations and Future Extensions</h3><p>This demo shines for simple SELECTs but has edges:</p><ul><li><strong>Prompt Brittleness</strong>: Complex joins/aggregates need richer examples.</li><li><strong>Security</strong>: Validate generated SQL (e.g., via SQLAlchemy AST) to block injections.</li><li><strong>Scalability</strong>: Async handles concurrency, but add caching for repeated queries.</li></ul><p>Extend by:</p><ul><li>Fine-tuning on your schema.</li><li>Integrating vector search (e.g., PGVector) for semantic joins.</li><li>Deploying as a Streamlit app for user-friendly UI.</li></ul><h3>Wrapping Up: Your Gateway to AI-Powered Databases</h3><p>The Semantic SQL Assistant demo proves how Semantic Kernel democratizes data access — turning databases into conversational tools. Fork the repo, tweak the prompts, and query away. It’s a blueprint for NLQ, blending AI orchestration with robust DB ops.</p><p>Ready to build? Clone, run, and share your variants. Questions? Drop a comment below.</p><p>References :</p><p><a href="https://devblogs.microsoft.com/all-things-azure/how-to-build-chatgpt-like-enterprise-search-on-your-own-data/">How to build ChatGPT-like enterprise search on your own data | All things Azure</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a1d7b02e80fe" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Part 2 : Real-Time Inference with Azure ML + SQL MI]]></title>
            <link>https://medium.com/@LuciusFox22/part-2-real-time-inference-with-azure-ml-sql-mi-432137031479?source=rss-615c2b210af1------2</link>
            <guid isPermaLink="false">https://medium.com/p/432137031479</guid>
            <category><![CDATA[sql-managed-instance]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <category><![CDATA[azure-machine-learning]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[real-time-inference]]></category>
            <dc:creator><![CDATA[Lucius Fox]]></dc:creator>
            <pubDate>Sun, 28 Sep 2025 01:31:46 GMT</pubDate>
            <atom:updated>2025-09-28T01:31:46.653Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Part 2 : Real-Time Inference with Azure ML + SQL MI</strong></h3><p>In Part 1 of this series, we explored how to train an ML model in Azure ML using data from Azure SQL Managed Instance (SQL MI), focusing on the Iris dataset to build a Random Forest Classifier for species classification. That foundation set the stage for iterative data-driven ML development. Now, in Part 2, we shift to the deployment and inference phase, demonstrating how to enable real-time predictions using the trained model. Real-time inference allows applications to make immediate decisions based on new data, such as classifying a flower species on-the-fly from user input.</p><p>Most organizations already use <strong>Azure SQL MI </strong>(or PostgreSQL) as the backbone of their business operations. However, SQL by itself can only answer “what has happened.” To make decisions in <strong>real-time</strong>, organizations need Machine Learning (ML) models that answer “what will happen.”</p><p><strong>Real-time inference</strong> means that as soon as new data is entered into a system (e.g., a loan application, a financial transaction, or a customer order), an ML model can instantly generate predictions or recommendations.</p><p>This combination allows:</p><ul><li>Banks → to instantly approve or decline loans.</li><li>Retailers → to personalize offers at checkout.</li><li>Healthcare providers → to flag high-risk patients as soon as new data is recorded.</li></ul><p>Azure ML’s online endpoints provide a scalable way to deploy models for real-time scoring, while Azure SQL MI serves as a reliable data store for both input features and output predictions. This integration ensures low-latency inference, secure data handling, and seamless incorporation into production workflows. The below example illustrates this process, from registering the model to deploying an online endpoint and testing it for inference.</p><p>Step-by-Step Guide: The Inference Process</p><p>Step 1 : Import the Libraries</p><pre>import joblib, os,json, numpy as np, pandas as pd<br>from dotenv import load_dotenv<br><br>load_dotenv()</pre><p>Step 2: Initialize ML Client</p><pre># Initialize MLClient to interact with Azure Machine Learning workspace<br><br>from azure.ai.ml import MLClient<br>from azure.ai.ml.entities import Model, ManagedOnlineEndpoint, ManagedOnlineDeployment, Environment, CodeConfiguration<br>from azure.ai.ml.constants import AssetTypes<br>from azure.identity import AzureCliCredential<br>import os<br>import json<br>import subprocess<br><br># Initialize MLClient<br>credential = AzureCliCredential()<br>ml_client = MLClient(<br>    credential=credential,<br>    subscription_id=os.getenv(&quot;AZURE_SUBSCRIPTION_ID&quot;),<br>    resource_group_name=os.getenv(&quot;AZURE_RESOURCE_GRP&quot;),<br>    workspace_name=os.getenv(&quot;AZURE_WORKSPACE_NM&quot;)<br>)</pre><p>Step 3 :Register the Model</p><pre># Register the Iris classification model in Azure ML<br># Model file (iris_model.pkl) must exist at the specified path<br><br>model_path = &quot;/home/azureuser/cloudfiles/code/outputs/iris_model.pkl&quot;<br>if not os.path.exists(model_path):<br>    raise FileNotFoundError(f&quot;Model file not found at {model_path}&quot;)<br><br>model = Model(<br>    path=model_path,<br>    name=&quot;iris-model&quot;,<br>    description=&quot;Iris classification model&quot;,<br>    type=AssetTypes.CUSTOM_MODEL<br>)<br>try:<br>    registered_model = ml_client.models.create_or_update(model)<br>    print(&quot;Model registered successfully&quot;)<br>except Exception as e:<br>    print(f&quot;Model registration failed: {e}&quot;)<br>    raise</pre><p>Post this, we configure a Conda environment for Iris model inference by creating an env.yml file with dependencies like Python 3.9, scikit-learn, pandas, numpy, and azureml-inference-server-http if it doesn’t exist. It then uses the Azure ML SDK to register this environment with a Docker image, ensuring a consistent setup for real-time predictions, with error handling for reliability.</p><p>Step 4 : Create or Update the Online Endpoint</p><pre># Create or update a managed online endpoint for real-time inference<br><br>endpoint_name = &quot;iris-realtime-endpoint&quot;<br>endpoint = ManagedOnlineEndpoint(<br>    name=endpoint_name,<br>    description=&quot;Real-time endpoint for Iris model inference&quot;,<br>    auth_mode=&quot;key&quot;,<br>    location=&quot;southeastasia&quot;<br>)<br>try:<br>    ml_client.online_endpoints.begin_create_or_update(endpoint).result()<br>    print(&quot;Endpoint created successfully&quot;)<br>except Exception as e:<br>    print(f&quot;Endpoint creation failed: {e}&quot;)<br>    raise</pre><p>Step 5: Verify or Create the Scoring Script</p><pre># Create or verify the scoring script (score.py)<br># Handles model loading and prediction logic for the endpoint<br><br><br>scoring_script_path = &quot;/home/azureuser/cloudfiles/code/score.py&quot;<br>if not os.path.exists(scoring_script_path):<br>    score_py_content = &quot;&quot;&quot;<br>import os<br>import json<br>import joblib<br>import numpy as np<br><br>def init():<br>    global model<br>    model_path = os.path.join(os.getenv(&#39;AZUREML_MODEL_DIR&#39;), &#39;iris_model.pkl&#39;)<br>    try:<br>        model = joblib.load(model_path)<br>        print(f&quot;Model loaded from {model_path}&quot;)<br>    except Exception as e:<br>        print(f&quot;Model loading failed: {e}&quot;)<br>        raise<br><br>def run(raw_data):<br>    try:<br>        if isinstance(raw_data, str):<br>            payload = json.loads(raw_data)<br>        else:<br>            payload = raw_data<br>        data = payload.get(&quot;data&quot;) or payload.get(&quot;input_data&quot;, {}).get(&quot;data&quot;)<br>        if not data:<br>            raise ValueError(&quot;Input data is missing or incorrectly formatted&quot;)<br>        X = np.array(data)<br>        if X.ndim != 2 or X.shape[1] != 4:<br>            raise ValueError(&quot;Input data must be a 2D array with 4 features&quot;)<br>        y_pred = model.predict(X).tolist()<br>        return json.dumps({&quot;predictions&quot;: y_pred})<br>    except Exception as e:<br>        return json.dumps({&quot;error&quot;: str(e)})<br>    &quot;&quot;&quot;<br>    with open(scoring_script_path, &quot;w&quot;) as f:<br>        f.write(score_py_content)<br>    print(f&quot;Created score.py at {scoring_script_path}&quot;)</pre><p>Step 6 : Deploying the endpoint for real-time inference</p><pre># Deploy the model to the online endpoint<br># Uses Standard_B1s instance with a single instance for cost efficiency<br><br>blue_deployment = ManagedOnlineDeployment(<br>    name=&quot;blue&quot;,<br>    endpoint_name=endpoint_name,<br>    model=registered_model,<br>    environment=f&quot;azureml:{env_name}:{env_version}&quot;,<br>    instance_type=&quot;Standard_B1s&quot;,<br>    instance_count=1,<br>    code_configuration=CodeConfiguration(code=&quot;/home/azureuser/cloudfiles/code/&quot;, scoring_script=&quot;score.py&quot;)<br>)<br>try:<br>    ml_client.online_deployments.begin_create_or_update(blue_deployment).result()<br>    print(&quot;Deployment created successfully&quot;)<br>except Exception as e:<br>    print(f&quot;Deployment failed: {e}&quot;)<br>    # Fetch and print deployment logs for debugging<br>    try:<br>        logs = ml_client.online_deployments.get_logs(name=&quot;blue&quot;, endpoint_name=endpoint_name, lines=100)<br>        print(&quot;Deployment logs:\n&quot;, logs)<br>    except Exception as log_e:<br>        print(f&quot;Failed to retrieve logs: {log_e}&quot;)<br>    raise</pre><p>For the complete code and detailed implementation, check out the GitHub repository at <a href="https://github.com/FoxInCode22/azure-ml-sql-mi-iris">https://github.com/FoxInCode22/azure-ml-sql-mi-iris</a></p><p>The above setup automates the ML lifecycle, from data to deployment, minimizing latency and cost. For example, in the demo, the Iris dataset in SQL MI was used to train a Random Forest Classifier in AML, with inference results stored in a separate table.</p><h3>Conclusion</h3><p>AML and SQL MI can absolutely be used in real-world scenarios, offering a powerful combination for data-centric ML. From fraud detection to predictive maintenance, this setup drives efficiency and innovation. For implementation, start with the demo notebooks, and explore Azure’s resources for advanced configurations. Whether you’re in finance, healthcare, or retail, this integration can transform your data into actionable intelligence.</p><p>References :</p><p><a href="https://learn.microsoft.com/en-us/azure/machine-learning/how-to-deploy-online-endpoints?view=azureml-api-2&amp;tabs=cli">Deploy Machine Learning Models to Online Endpoints — Azure Machine Learning | Microsoft Learn</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=432137031479" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Part 1 : Train an ML Model in Azure ML using Data from Azure SQL MI]]></title>
            <link>https://medium.com/@LuciusFox22/part-1-train-an-ml-model-in-azure-ml-using-data-from-azure-sql-mi-6f32c08d7b0a?source=rss-615c2b210af1------2</link>
            <guid isPermaLink="false">https://medium.com/p/6f32c08d7b0a</guid>
            <category><![CDATA[sql-managed-instance]]></category>
            <category><![CDATA[azure-machine-learning]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Lucius Fox]]></dc:creator>
            <pubDate>Fri, 26 Sep 2025 01:33:54 GMT</pubDate>
            <atom:updated>2025-10-11T11:42:45.754Z</atom:updated>
            <content:encoded><![CDATA[<h3>Part 1 : Train an ML Model in Azure ML using Data from Azure SQL MI</h3><h3>Introduction</h3><p>In the rapidly evolving world of data science and artificial intelligence, the ability to seamlessly integrate data storage, processing, and machine learning workflows is crucial for building efficient, scalable solutions. Microsoft Azure offers a robust ecosystem for this purpose, with Azure Machine Learning (Azure ML) serving as a comprehensive platform for developing, training, and deploying ML models, and Azure SQL Managed Instance (Azure SQL MI) providing a fully managed, secure, and scalable database service compatible with SQL Server.</p><p>In this blog, we’ll explore how to train an ML model in Azure ML using data from Azure SQL Managed Instance (SQL MI). We’ll walk through a practical example using the Iris dataset, demonstrating the integration’s power. This setup allows organizations to leverage structured data stored in SQL MI for ML tasks in Azure ML, enabling scenarios like predictive analytics, anomaly detection, and personalized recommendations.</p><p>What Does This Setup Do?</p><p>This integration enables you to fetch live data directly from Azure SQL Managed Instance (SQL MI), process it in Azure ML, train a model, and save the trained model for deployment. Essentially, it bridges a managed database (Azure SQL MI) with a machine learning platform (Azure ML), allowing for seamless data flow without manual exports or imports. By working with real-time data from the database, this approach eliminates the need for data migration, reducing efforts in data handling, minimizing storage costs, and optimizing overall operational efficiency.</p><p>Step-by-Step Guide: Training the Model</p><p>Step 1: Import Libraries</p><pre>#Import Libraries<br>import pyodbc<br>import pandas as pd<br>from sklearn.ensemble import RandomForestClassifier<br>from sklearn.model_selection import train_test_split<br>from sklearn.metrics import accuracy_score<br>import joblib, os</pre><p>Step 2 : Establish SQL Connection</p><pre># Setting up the connection to Azure SQL Managed Instance<br>conn_str = (<br>    f&quot;DRIVER={driver};&quot;<br>    f&quot;SERVER={server};DATABASE={database};UID={username};PWD={password};&quot;<br>    &quot;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;&quot;<br>)</pre><p>Step 3 : Load Data from SQL MI</p><pre># Queries the IrisData table and loads rows for model training<br><br>print(&quot;Connecting to SQL Server...&quot;)<br>conn = pyodbc.connect(conn_str)<br>query = &quot;SELECT SepalLength, SepalWidth, PetalLength, PetalWidth, Species FROM IrisData&quot;<br>df = pd.read_sql(query, conn)<br>print(f&quot;Loaded {len(df)} rows from IrisData&quot;)</pre><p>Step 4 : Prepare Data for Training</p><pre># Preparing features (X) and target variable (y) for model training<br># X: Numerical features; y: Species labels<br><br>X=df[[&#39;SepalLength&#39;, &#39;SepalWidth&#39;, &#39;PetalLength&#39;, &#39;PetalWidth&#39;]]<br>y=df[&#39;Species&#39;]<br><br>#Data is split for training<br>X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)</pre><p>Step 5: Train the Model</p><pre># Training the Random Forest Classifier and evaluating its accuracy<br><br>spclf = RandomForestClassifier(n_estimators=100, random_state=42)<br>spclf.fit(X_train, y_train)<br>y_pred = spclf.predict(X_test)<br>accuracy = accuracy_score(y_test, y_pred)<br>print(f&quot;Model accuracy: {accuracy*100:.2f}%&quot;)</pre><p>Step 6: Save the Model</p><pre># Saving the trained model to a file for deployment<br><br>os.makedirs(&#39;outputs&#39;, exist_ok=True)<br>model_path = &#39;outputs/iris_model.pkl&#39;<br>joblib.dump(spclf, model_path)<br>print(f&quot;Model saved to {model_path}&quot;)</pre><p>For the complete code and detailed implementation, check out the GitHub repository at</p><p><a href="https://github.com/FoxInCode22/azure-ml-sql-mi-iris">GitHub - FoxInCode22/azure-ml-sql-mi-iris: Explore &quot;azure-ml-sql-mi-iris&quot;: Building a Hybrid ML Workflow: Azure ML + Azure SQL MI Real-time Inference with the Iris Dataset</a></p><p>Integrating <strong>Azure SQL Managed Instance (SQL MI)</strong> with <strong>Azure Machine Learning (Azure ML)</strong> opens the door to a wide range of real-world scenarios:</p><ol><li><strong>Predictive Analytics</strong> — Forecast demand, predict equipment failures, or estimate customer churn using structured transactional data stored in SQL MI.</li><li><strong>Personalized Recommendations</strong> — Retailers and e-commerce platforms can train models on customer purchase history in SQL MI to recommend products in real time.</li><li><strong>Fraud Detection</strong> — Financial institutions can leverage transaction data in SQL MI to build ML models that detect anomalies and fraudulent behavior.</li><li><strong>Healthcare Insights</strong> — Hospitals can use patient data securely stored in SQL MI to predict health risks, personalize treatment, or optimize resources.</li><li><strong>Operational Optimization</strong> — Manufacturers can analyze IoT and production data stored in SQL MI to improve efficiency, reduce downtime, and lower costs.</li></ol><p>By running training directly in Azure ML while pulling data from SQL MI, businesses avoid manual data handling and keep data flows secure, scalable, and efficient.</p><h3>Conclusion</h3><p>In this blog, we demonstrated how to <strong>train an ML model in Azure ML using data from Azure SQL Managed Instance</strong>. By connecting these two powerful Azure services, organizations can create an end-to-end ML pipeline that starts with data stored in SQL MI and ends with a deployable ML model ready for production.</p><p>This integration provides:</p><ul><li><strong>Seamless data access</strong> without manual exports.</li><li><strong>Scalability and security</strong> for enterprise workloads.</li><li><strong>Faster experimentation</strong> for data scientists.</li></ul><p>This was <strong>Part 1</strong> of our two-part series. In <strong>Part 2: Real-Time Inference with Azure ML + SQL MI</strong>, we’ll explore how to deploy the trained model as an endpoint and perform <strong>real-time predictions</strong> directly from SQL MI or other applications.</p><p>Together, these steps show how you can <strong>operationalize AI directly on your enterprise data.</strong></p><p>References :</p><p><a href="https://learn.microsoft.com/en-us/azure/machine-learning/how-to-train-model?view=azureml-api-2&amp;tabs=python">Train ML models — Azure Machine Learning | Microsoft Learn</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6f32c08d7b0a" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>