Developing AI-Enabled Database Solutions

Build the database foundation before adding AI. Practice on SQL Server 2025, Azure SQL, or SQL database in Fabric while checking each feature's applies-to and preview notes.

• Select correctly sized data types, nullability, defaults, and keys
• Implement PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT constraints
• Compare rowstore, columnstore, filtered, covering, and partition-aligned indexes
• Know when SEQUENCE differs from IDENTITY and why gaps can occur
• Design temporal, ledger, graph, external, and in-memory tables from requirements
• Keep stable attributes relational and govern semi-structured JSON columns
• Validate JSON and make frequent JSON predicates indexable
• Create views, scalar functions, inline table-valued functions, procedures, and triggers
• Use CTEs, window functions, correlated queries, JSON construction, and shredding
• Practice regular expressions, fuzzy matching, graph MATCH, and TRY/CATCH transactions

Layer identity, authorization, data protection, audit, and performance. Avoid treating masking as encryption or lower isolation as a universal tuning switch.

• Distinguish TDE, Always Encrypted, column-level encryption, and key ownership
• Use Dynamic Data Masking as exposure reduction, not a security boundary
• Implement RLS filter and block predicates with trusted SESSION_CONTEXT
• Create contained Microsoft Entra principals and passwordless managed-identity access
• Grant object and model permissions through least-privilege roles
• Configure SQL auditing, retention, review, and alerts for sensitive operations
• Compare lock-based and row-versioning isolation from consistency requirements
• Read estimated and actual execution plans, DMVs, and wait evidence
• Use Query Store and Query Performance Insight to find regressions
• Diagnose blocking and deadlocks; shorten transactions, improve indexes, order access, and retry within bounds

Turn the schema into a controlled product surface and a repeatable artifact. Authentication, API authorization, database authorization, tests, and deployment approvals are separate controls.

• Create DAB configuration with environment-based connection settings
• Expose only approved tables, views, and stored procedures
• Configure REST and GraphQL names, actions, fields, key fields, and relationships
• Practice pagination, filtering, sorting, searching, aggregation, and bounded caching
• Configure Microsoft Entra/JWT authentication and explicit DAB roles and policies
• Layer DAB permissions with database grants and RLS
• Deploy DAB statelessly and export OpenTelemetry to Application Insights
• Choose CES, CDC, Change Tracking, SQL trigger bindings, or Logic Apps from change semantics
• Create an SDK-style SQL Database Project and version reference data
• Build a dacpac, run tests and code analysis, detect drift, review deployment plans, and promote one artifact
• Use branch protection, code owners, workload identity, approvals, rollback, and post-deployment checks

Choose and govern models as dependencies. Preserve source, chunk, model, dimension, content hash, and status metadata so embeddings can be rebuilt and compared safely.

• Evaluate model modality, language, dimensions, context, quality, latency, throughput, region, price, and structured output
• Create a database-scoped credential with managed identity where supported
• Create and grant a least-privilege external model of type EMBEDDINGS
• Choose source columns and exclude secrets, volatile noise, and unauthorized data
• Design structure-aware chunks, overlap, token limits, metadata, and citation granularity
• Generate embeddings with AI_GENERATE_EMBEDDINGS or a governed worker
• Align model output dimension with the VECTOR column and distance metric
• Select Change Tracking, CDC, CES, triggers, Functions, Logic Apps, or Foundry for maintenance
• Implement idempotent jobs, durable checkpoints, bounded retries, poison handling, quota backpressure, and batch limits
• Monitor coverage, stale vectors, backlog age, failures, model latency, tokens, and migration state

Evaluate retrieval before generation. Exact identifiers, semantic intent, authorization, freshness, latency, cost, and citations all need independent tests.

• Implement and tune full-text search for terms, codes, names, and phrases
• Use VECTOR_NORMALIZE, VECTORPROPERTY, and VECTOR_DISTANCE where appropriate
• Build ENN ground truth and understand when exhaustive search is affordable
• Create and evaluate current DiskANN vector indexes and VECTOR_SEARCH syntax where supported
• Measure ANN recall against ENN plus p95, QPS, CPU, I/O, index size, build time, updates, and filters
• Run lexical and vector retrieval under identical trusted metadata filters
• Implement reciprocal rank fusion, candidate depth, deduplication, tie-breaking, and relevance evaluation
• Build bounded JSON context and call a model with sp_invoke_external_rest_endpoint
• Validate structured output, citations, finish status, size, and safe abstention
• Secure REST, GraphQL, MCP, credentials, models, prompts, logs, and telemetry
• Gate CI/CD on retrieval quality, tenant isolation, freshness, p95, schema drift, data loss, and cost
• Run failure drills, publish sanitized evidence, remove access, and delete lab resources