{ "nbformat": 4, "nbformat_minor": 5, "metadata": { "kernelspec": { "display_name": "sql", "language": "sql", "name": "sql" }, "language_info": { "name": "sql", "version": "1.0.0" }, "blog_metadata": { "topic": "Azure SQL as an AI-ready data platform: embeddings and external models in production", "slug": "azure-sql-as-an-ai-ready-data-platform-embeddings-and-extern", "generated_by": "LinkedIn Post Generator + Azure OpenAI", "generated_at": "2026-05-09T17:05:58.010Z" } }, "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Azure SQL as an AI-ready data platform: embeddings and external models in production\n", "\n", "This notebook turns the blog post into a hands-on validation flow focused on Azure SQL as a governed AI data plane. It demonstrates how to define external model endpoints, store vectors beside relational data, generate embeddings in-database, and perform semantic retrieval while keeping orchestration in the application tier.\n", "\n", "Because feature availability and syntax can vary across Azure SQL Database, Azure SQL Managed Instance, SQL Server 2025, and Fabric SQL database, treat the SQL here as production-minded starter code and verify current Microsoft documentation for your exact platform." ] }, { "cell_type": "code", "metadata": {}, "source": [ "%pip install pyodbc pandas" ], "execution_count": null, "outputs": [] }, { "cell_type": "code", "metadata": {}, "source": [ "import os\n", "import json\n", "import textwrap\n", "import pyodbc\n", "import pandas as pd" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Architecture and validation plan\n", "\n", "The pattern in this notebook is intentionally pragmatic: Azure SQL stores operational records and vectors, an `EXTERNAL MODEL` points to an approved embeddings endpoint, and applications query SQL for retrieval-ready context. SQL handles governed retrieval and embedding persistence; the app tier still owns prompt assembly, answer generation, and user experience.\n", "\n", "Reference architecture:\n", "\n", "- Application sends user query text\n", "- Azure SQL uses an approved external model definition\n", "- Embeddings are generated close to governed data\n", "- Vectors are stored beside relational rows\n", "- SQL performs vector similarity plus relational filters\n", "- Application consumes the ranked results" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Optional connection variables\n", "\n", "If you want to execute the Python connectivity example later in this notebook, set these environment variables first:\n", "\n", "- `SQLSERVER_HOST`\n", "- `SQLSERVER_DATABASE`\n", "- `SQLSERVER_DRIVER` (example: `ODBC Driver 18 for SQL Server`)\n", "- `SQLSERVER_AUTH` (example: `ActiveDirectoryInteractive`)\n", "\n", "You may also need tenant-specific authentication setup depending on your environment." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Example 1: Provision the relational foundation with Bicep\n", "\n", "This snippet provisions only the SQL logical server and database foundation for an AI-ready relational and vector workload. It intentionally omits identity, firewall, networking, credentials, and AI object configuration, which should be handled according to your platform and security standards." ] }, { "cell_type": "code", "metadata": {}, "source": [ "-- Bicep reference preserved as SQL comments for notebook continuity\n", "-- // Provision a SQL logical server and database as the foundation for AI-ready relational + vector workloads\n", "-- param location string = resourceGroup().location\n", "-- param sqlServerName string\n", "-- param adminLogin string\n", "-- @secure()\n", "-- param adminPassword string\n", "-- param databaseName string = 'appdb'\n", "--\n", "-- resource sqlServer 'Microsoft.Sql/servers@2023-08-01-preview' = {\n", "-- name: sqlServerName\n", "-- location: location\n", "-- properties: {\n", "-- administratorLogin: adminLogin\n", "-- administratorLoginPassword: adminPassword\n", "-- version: '12.0'\n", "-- }\n", "-- }\n", "--\n", "-- resource sqlDb 'Microsoft.Sql/servers/databases@2023-08-01-preview' = {\n", "-- name: '${sqlServer.name}/${databaseName}'\n", "-- location: location\n", "-- sku: { name: 'GP_S_Gen5_2' }\n", "-- }\n", "\n", "SELECT\n", " N'Provision SQL server and database outside this notebook using Bicep, ARM, Terraform, or the Azure portal.' AS Guidance,\n", " N'Validate feature support for vectors, AI_GENERATE_EMBEDDINGS, credentials, and EXTERNAL MODEL on your target platform before proceeding.' AS Prerequisite;" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Example 2: Create the external model object\n", "\n", "This step centralizes the approved embeddings endpoint as a governed database object. In production, this is a key control point because it binds endpoint location, API format, model purpose, credential reference, and timeout in one place.\n", "\n", "Validate the exact `CREATE EXTERNAL MODEL` syntax, credential binding, and endpoint URL shape for your target platform and release." ] }, { "cell_type": "code", "metadata": {}, "source": [ "-- Create an EXTERNAL MODEL in Azure SQL for an approved Azure OpenAI-compatible embeddings endpoint\n", "CREATE EXTERNAL MODEL [aoai_text_embedding_3_small]\n", "WITH\n", "(\n", " LOCATION = 'https://my-approved-openai.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2024-02-01',\n", " API_FORMAT = 'AzureOpenAI',\n", " MODEL_TYPE = EMBEDDINGS,\n", " CREDENTIAL = [https://my-approved-openai.openai.azure.com],\n", " TIMEOUT = 60\n", ");\n", "GO" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Example 3: Create a table for source data and vectors\n", "\n", "This table stores source text, a content hash for change detection, the embedding vector, and timestamps for lineage and maintenance. Keeping vectors beside relational records is useful when retrieval needs business filters, governance controls, and simpler operational ownership." ] }, { "cell_type": "code", "metadata": {}, "source": [ "-- Create a table that stores source text plus generated embeddings for retrieval scenarios\n", "CREATE TABLE dbo.ProductKnowledge\n", "(\n", " ProductId int IDENTITY(1,1) PRIMARY KEY,\n", " Title nvarchar(200) NOT NULL,\n", " Description nvarchar(max) NOT NULL,\n", " SourceHash varbinary(32) NOT NULL,\n", " Embedding vector(1536) NULL,\n", " EmbeddedAt datetime2 NULL,\n", " LastModifiedAt datetime2 NOT NULL DEFAULT sysutcdatetime()\n", ");\n", "GO" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Example 4: Generate embeddings in-database and persist them\n", "\n", "This pattern is strongest when source records already live in SQL and embedding generation is part of a governed enrichment flow. It reduces ETL hops and keeps semantic enrichment close to the data, but you should avoid putting synchronous embedding generation on hot write paths until latency and failure behavior are well understood." ] }, { "cell_type": "code", "metadata": {}, "source": [ "-- Generate embeddings with AI_GENERATE_EMBEDDINGS and persist them alongside relational records\n", "INSERT INTO dbo.ProductKnowledge (Title, Description, SourceHash, Embedding, EmbeddedAt)\n", "SELECT\n", " s.Title,\n", " s.Description,\n", " HASHBYTES('SHA2_256', CONCAT(s.Title, N'|', s.Description)),\n", " AI_GENERATE_EMBEDDINGS(\n", " CONCAT(s.Title, N'. ', s.Description)\n", " USE MODEL [aoai_text_embedding_3_small]\n", " ),\n", " sysutcdatetime()\n", "FROM (VALUES\n", " (N'Premium Support', N'24x7 enterprise support with SLA-backed response times'),\n", " (N'Cold Storage', N'Low-cost archival storage for infrequently accessed data')\n", ") AS s(Title, Description);\n", "GO" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Example 5: Query semantically relevant rows with vector distance\n", "\n", "Once vectors are stored, SQL can embed the user query and rank rows by similarity. In real workloads, you would usually combine semantic ranking with tenant filters, security predicates, business rules, and freshness constraints." ] }, { "cell_type": "code", "metadata": {}, "source": [ "-- Retrieve semantically relevant rows by embedding the query in-database and ranking by vector distance\n", "DECLARE @query nvarchar(4000) = N'enterprise support with guaranteed response times';\n", "\n", "DECLARE @query_vector vector(1536) =\n", " AI_GENERATE_EMBEDDINGS(@query USE MODEL [aoai_text_embedding_3_small]);\n", "\n", "SELECT TOP (5)\n", " ProductId,\n", " Title,\n", " Description,\n", " VECTOR_DISTANCE('cosine', Embedding, @query_vector) AS CosineDistance\n", "FROM dbo.ProductKnowledge\n", "WHERE Embedding IS NOT NULL\n", "ORDER BY VECTOR_DISTANCE('cosine', Embedding, @query_vector);\n", "GO" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Example 6: Re-embed only changed rows\n", "\n", "Initial backfill is easy; staying in sync is the harder production problem. This maintenance pattern uses a persisted content hash to detect changed rows and re-embed only what actually changed, which helps control cost, latency, and operational churn." ] }, { "cell_type": "code", "metadata": {}, "source": [ "-- Re-embed only changed rows by comparing a persisted content hash to the current source hash\n", ";WITH ChangedRows AS\n", "(\n", " SELECT\n", " pk.ProductId,\n", " NewHash = HASHBYTES('SHA2_256', CONCAT(pk.Title, N'|', pk.Description)),\n", " NewText = CONCAT(pk.Title, N'. ', pk.Description)\n", " FROM dbo.ProductKnowledge AS pk\n", " WHERE pk.SourceHash <> HASHBYTES('SHA2_256', CONCAT(pk.Title, N'|', pk.Description))\n", " OR pk.Embedding IS NULL\n", ")\n", "UPDATE pk\n", "SET\n", " SourceHash = c.NewHash,\n", " Embedding = AI_GENERATE_EMBEDDINGS(c.NewText USE MODEL [aoai_text_embedding_3_small]),\n", " EmbeddedAt = sysutcdatetime(),\n", " LastModifiedAt = sysutcdatetime()\n", "FROM dbo.ProductKnowledge AS pk\n", "JOIN ChangedRows AS c\n", " ON c.ProductId = pk.ProductId;\n", "GO" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Example 7: Validate stored rows and embedding maintenance metadata\n", "\n", "After loading and embedding data, it is useful to inspect the relational records and lineage columns. In production, consider also storing embedding model identifier, dimension, generation timestamp, and optional pipeline run metadata." ] }, { "cell_type": "code", "metadata": {}, "source": [ "SELECT\n", " ProductId,\n", " Title,\n", " Description,\n", " SourceHash,\n", " EmbeddedAt,\n", " LastModifiedAt\n", "FROM dbo.ProductKnowledge\n", "ORDER BY ProductId;\n", "GO" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Required variables for the Python application-tier example\n", "\n", "Set these values before running the next cell:\n", "\n", "- `SQLSERVER_HOST`\n", "- `SQLSERVER_DATABASE`\n", "- `SQLSERVER_DRIVER`\n", "- `SQLSERVER_AUTH`\n", "\n", "The SQL objects used below must already exist in the target database:\n", "\n", "- `dbo.ProductKnowledge`\n", "- `EXTERNAL MODEL [aoai_text_embedding_3_small]`" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Example 8: Keep orchestration in the application tier\n", "\n", "This Python example asks Azure SQL for semantically relevant rows and assembles retrieval context in the application layer. That split is usually the most effective production boundary: SQL handles embedding persistence and governed retrieval, while the app handles prompt assembly, answer generation, tool use, and user interaction." ] }, { "cell_type": "code", "metadata": {}, "source": [ "query_text = \"Which offering includes SLA-backed support?\"\n", "\n", "server = os.getenv(\"SQLSERVER_HOST\", \"myserver.database.windows.net\")\n", "database = os.getenv(\"SQLSERVER_DATABASE\", \"mydb\")\n", "driver = os.getenv(\"SQLSERVER_DRIVER\", \"ODBC Driver 18 for SQL Server\")\n", "auth = os.getenv(\"SQLSERVER_AUTH\", \"ActiveDirectoryInteractive\")\n", "\n", "conn_str = (\n", " f\"Driver={{{driver}}};\"\n", " f\"Server=tcp:{server},1433;\"\n", " f\"Database={database};\"\n", " \"Encrypt=yes;\"\n", " f\"Authentication={auth}\"\n", ")\n", "\n", "sql = \"\"\"\n", "DECLARE @q nvarchar(4000) = ?;\n", "DECLARE @v vector(1536) = AI_GENERATE_EMBEDDINGS(@q USE MODEL [aoai_text_embedding_3_small]);\n", "SELECT TOP (3) ProductId, Title, Description\n", "FROM dbo.ProductKnowledge\n", "WHERE Embedding IS NOT NULL\n", "ORDER BY VECTOR_DISTANCE('cosine', Embedding, @v);\n", "\"\"\"\n", "\n", "try:\n", " with pyodbc.connect(conn_str) as conn:\n", " with conn.cursor() as cur:\n", " cur.execute(sql, query_text)\n", " rows = cur.fetchall()\n", "\n", " context = \"\\n\".join(f\"{r.Title}: {r.Description}\" for r in rows)\n", " print(json.dumps({\"question\": query_text, \"retrieved_context\": context}, indent=2))\n", "except Exception as e:\n", " print(\"Connection or execution failed. Verify environment variables, authentication, network access, and feature support.\")\n", " print(str(e))" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Example 9: Secret rotation and external model change management\n", "\n", "Operationally, model endpoints and credentials will change over time. The safest production pattern is to rotate the database-scoped credential using the documented method for your platform and then redeploy or alter the external model definition as needed.\n", "\n", "The original blog included a PowerShell example; below is a SQL-oriented validation placeholder you can run in a notebook while keeping the actual secret rotation process in your deployment pipeline." ] }, { "cell_type": "code", "metadata": {}, "source": [ "-- Validate current external model metadata and prepare for controlled change management.\n", "-- Use platform-documented methods to rotate credentials and ALTER EXTERNAL MODEL as needed.\n", "SELECT\n", " N'Use ALTER DATABASE SCOPED CREDENTIAL for secret rotation where supported.' AS CredentialRotation,\n", " N'Use ALTER EXTERNAL MODEL for endpoint or configuration changes where supported.' AS ModelChange,\n", " N'Canary rollout, quality comparison, and rollback planning are recommended before broad cutover.' AS RolloutGuidance;\n", "GO" ], "execution_count": null, "outputs": [] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Summary\n", "\n", "This notebook validated the core production-minded pattern from the blog post:\n", "\n", "- define an approved external embeddings endpoint as a database object\n", "- store vectors beside relational records\n", "- generate embeddings close to governed data\n", "- query with vector similarity plus relational controls\n", "- maintain embeddings incrementally using content hashes\n", "- keep orchestration-heavy logic in the application tier\n", "\n", "## Next Steps\n", "\n", "1. Verify exact syntax and feature availability for your Azure SQL or SQL Server platform.\n", "2. Add tenant filters, security predicates, and freshness rules to retrieval queries.\n", "3. Store additional provenance metadata such as model identifier and embedding dimension.\n", "4. Benchmark latency, throughput, and retrieval quality before enabling synchronous paths.\n", "5. Decide whether Azure SQL alone is sufficient or whether Azure AI Search should complement retrieval for your workload." ] } ] }