book

SQL Server 2025 Unveiled: The AI-Ready Enterprise Database with Microsoft Fabric Integration

Name: SQL Server 2025 Unveiled: The AI-Ready Enterprise Database with Microsoft Fabric Integration
Author: Bob Ward
ISBN: 9798868818479

by Bob Ward

October 2025

Intermediate to advanced

341 pages

7h 15m

English

Apress

Read now

Unlock full access

SQL Server 2025 Unveiled
Foreword
Introduction
Acknowledgments
Table of Contents
About the Author
About the Technical Reviewer
1. The AI-Ready Enterprise Database
Project Kauai Is BornThe Move to Announce SQL Server 2025The Acceleration to Launch Public PreviewFinally, Anyone Can Download ItMay 2024 Events and LaunchA Rest but Momentum Still StrongSummer Momentum and General AvailabilityCTP 2.1 and SQLBitsRelease Candidate (RC) of SQL Server 2025The Move to General AvailabilityFoundationsIntroducing SQL Server 2025Areas of InnovationAI Built-InDevelop Modern Data ApplicationsIntegrate Your Data with FabricMeat and PotatoesPowering SQL ServerConnected with ArcAssisted by CopilotsBenchmark LeaderOptimized for Latest HardwareBuilt for All PlatformsA New Wave of ToolsThe New SSMSVisual Studio Code and the mssql ExtensionThe SQL Server 2025 Platform ArchitectureCustomer StoriesResources for YouTradition Meets Innovation
2. Ready, Set, Go
Who Should Read This Chapter?How to Install SQL Server 2025PrerequisitesWhat Is Different for SQL Server 2025?Feature Differences and Discontinued/Deprecated FeaturesOther Installation MethodsDeploying on Other PlatformsSide-by-Side and Multi-instance InstallationsHow to Upgrade to SQL Server 2025The Importance of dbcompatConfigurationEasy to Install and Upgrade
3. AI Fundamentals
The Path for AI ApplicationsThe PathYou Control AIKnowledge Is the KeySecurity, Scalability, QualityPrompts and Retrieval Augmented Generation (RAG)AI ModelsAI Model TypesChat CompletionDeep ReasoningEmbeddingsOther Model TypesAI Model SizesWhere Do I Get AI Models and Access Them?Azure AI FoundryOpenAIHugging FaceNVIDIAONNX RuntimeFoundry LocalOllamaLet’s REST with AIPrerequires for the ExamplesREST with AI Using OllamaSecure REST with HTTPSAdd Some Vector SearchVector Search and Your DataUse REST with Ollama to Do a Vector SearchAI Tools and Model Context ProtocolAI ToolsModel Context Protocol (MCP)MCP ServerMCP ClientMCP HostAI AgentsWhat About Quality?What Have We LearnedLaunch SQL Server 2025 AI Built-In

4. AI Built-In
What Are We Trying to SolveSmarter SearchingSupport Centralized Vector SearchingProvide Building BlocksPromote Security and ScalabilityOvercome ComplexityWhat Is AI-Ready?Vector Data TypeDimensionsDriver SupportModel DefinitionsCREATE EXTERNAL MODEL Is BornSystem ViewEmbedding GenerationText ChunkingSearching with VECTOR_DISTANCEVector IndexDiskANN IndexesSQL Server DiskANN Index and VECTOR_SEARCHLimitationsLearn MoreVector Architecture1. Model Definition2. Generate Embeddings3. Create a Vector Index4. T-SQL Prompt5. Generate Prompt Embedding6. Vector Search7. Other FiltersExtending with sp_involve_external_rest_endpointWhy Enterprise?You Control All Access with SQL SecurityYou Control Which AI Models to UseAI Models Ground and/or Cloud Isolated from SQLUse RLS, TDE, and DDMTrack Everything with SQL Server AuditingLedger for Chat History and FeedbackGetting Started with VectorsPrerequisitesTry Existing Search MethodsStep 1: Create a Model DefinitionStep 2: Create a Table to Store Vectors and Generate EmbeddingsStep 3: Create a Vector IndexSteps 4–7: Use a Prompt for a Vector SearchExtending to Azure AI FoundryOther AI Model OptionsOpenAI CompatibleLocal ONNX SupportSQL Server 2025 AI FuturesSecure and Scalable AI with SQL Server 2025
5. Developers, Developers, Developers
The Best in a DecadeDeveloper EditionJSONA New, Better Wayjson Data Typejson Indexjson FunctionsExample: Using the New json Data Type and IndexPrerequisites for the ExampleExample 1: Using the New json Data TypeExample 2: Using a json IndexT-SQL LoveRegular Expressions (RegEx) with T-SQLPrerequisites for the ExampleExample Using REGEXP_LIKEOther T-SQL EnhancementsPRODUCTBASE64Fuzzy StringCURRENT_DATESUBSTRINGUNISTR|| String Concat OperatorDATEADDChange Event Streaming (CES)Comparing CES, CDC, and CTHow Does It WorkMessagesFAQ and LimitsExample: Solving Shipping Problems for ContosoPrerequisites for the ExampleCreating Azure ResourcesEnable CES in SQL ServerTesting a MessageProcessing Real MessagesCES Setting the Path ForwardREST APIsp_invoke_external_rest_endpointSecurityProcedure Syntax and ComponentsURLMethodsHeadersPayloadResponseCredentialsReturn Values and ErrorsEngine DetailsScenarios to Use RESTUsing REST for AI to Complete the RAG StoryPrerequisites for the ExampleExample to Extend RAG with Vector SearchThe Modern SQL Developer
6. Connecting to the Cloud with Azure Arc
What Is Azure Arc?SQL Server Enabled by Azure ArcWhat Is the Azure Extension for SQL Server?Enabling Hybrid Scenarios with Azure ArcInventoryLicensingExtended Support Updates (ESU)Best Practices AssessmentMicrosoft Entra ID AuthenticationAzure Policy IntegrationAzure Monitor IntegrationAzure Log Analytics IntegrationMicrosoft Defender for SQLAutomated Local BackupsAutomated PatchingMonitoringAlways On ManagementGetting Started with Azure ArcExample: Deploying Azure Arc Through SQL Server 2025 SetupSteps to Deploy and PrerequisitesExploring Azure Arc ResourcesLicense TypesSecuring SQL Server with Microsoft EntraExample: Connecting to SQL Server with Microsoft EntraPrerequisitesGo Passwordless with Microsoft Entra Managed IdentityExample: Secure Access to Azure OpenAI with a Managed IdentityConnecting SQL to the World
7. The Core Engine of SQL Server 2025
What’s New for the Engine?SecurityMicrosoft Entra and Managed IdentityBACKUP to URL with Managed IdentityManaged Identity Support for Azure Key VaultMicrosoft Entra Logins for Service PrincipalsSecurity CacheEncryption and Password EnhancementsTLS 1.3/TDS 8.0OAEP Padding Mode Support for RSA EncryptionPBKDF for Password HashesCustom Password Policy for SQL Server on LinuxPerformanceOptimized LockingHow It WorksPrerequisites for the ExampleExample Steps for Lock EscalationExample Steps for Lock After Qualification (LAQ)tempdb Resource GovernancePrerequisites for the ExampleExamples for tempdb Resource GovernanceOther tempdb EnhancementsADR in tempdbtmpfs Support for tempdb on LinuxQuery Optimization and ExecutionOptimized sp_execute_sqlCardinality Estimation (CE) Feedback for ExpressionsOptional Parameter Plan Optimization (OPPO)DOP FeedbackQuery ManagementColumnstore Index ImprovementsQuery Store on Secondary ReplicasPersisted Stats on Readable SecondariesAvailabilityAlways On Availability Groups (AGs)Failover ImprovementsTuning, Configuration, and DiagnosticsDAG EnhancementsBackup/RestoreZSTD Backup CompressionBackup on SecondariesBackup to Immutable Azure StorageHidden GemsABORT_QUERY_EXECUTIONPrerequisitesThe Bad QuerySo, bad query, “make my day”In-Memory OLTPPolyBasePolyBase Services May Not Be RequiredManaged Identity SupportDiagnosticsThe Fastest Database on the PlanetAlways Tuning the Engine
8. Integrating SQL Server 2025 with Microsoft Fabric
An Introduction to Microsoft FabricData FactoryAnalyticsDatabasesReal-Time Intelligence (RTI)Power BIAIOneLakeGovernanceServices Working TogetherUnified InterfacesCapacity ModelMirroring SQL Everywhere to FabricLet’s Start with AzureMirroring SQL EverywhereMirroring SQL Server 2025 to FabricHow It WorksConsiderations and LimitsTransactionsWhat Can Be Mirrored?ConsiderationsExample: Mirror a SQL Server 2025 Database to FabricPrerequisitesCreate the Mirrored Database in FabricUsing the SQL Analytics EndpointVerify Changes from SQL Server 2025 in FabricGoing Further with Microsoft FabricMirroring Azure SQL DatabaseDeploying SQL Database in FabricUnifying the Data with the SQL Analytics EndpointCreating a LakehouseUnified Data in the LakehouseI’m No Guy in a CubeUsing Fabric Data AgentsGood ‘Ol SSMS to the RescueSQL Is Always Part of Any Data Story
9. Microsoft SQL Ground to Cloud to Fabric
Develop Once, Deploy AnywhereSQL Server 2025AI Built-InDeveloper Modern Data ApplicationsIntegrate Your Data with FabricSecure by DefaultMission-Critical EngineFoundationsAzure SQLSQL Server in Azure Virtual MachinesAzure SQL Managed InstanceAzure SQL DatabaseSQL Database in FabricA Common BondT-SQLEngineToolsFabricAICopilotsThe Future Is Bright
Index

Content preview from SQL Server 2025 Unveiled: The AI-Ready Enterprise Database with Microsoft Fabric Integration

B. WardSQL Server 2025 Unveiledhttps://doi.org/10.1007/979-8-8688-1847-9_9

9. Microsoft SQL Ground to Cloud to Fabric

Bob Ward¹

Up until 2024, I often would use the phrase Microsoft SQL ground to cloud. I would talk about develop once, deploy anywhere from SQL Server to Azure SQL. With the introduction of SQL Database in Fabric, I knew I needed to add in the third piece to the puzzle.

This chapter is short but sums up the incredible story of Microsoft SQL and SQL Server 2025.

Develop Once, Deploy Anywhere

Figure 9-1 represents the new develop once, deploy anywhere visual for Microsoft SQL, ground to cloud to fabric.

Note

Microsoft ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

SQL Server 2019 Administration Inside Out

Publisher Resources

ISBN: 9798868818479Purchase Link Publisher Website

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQL Server 2025 Unveiled: The AI-Ready Enterprise Database with Microsoft Fabric Integration

by Bob Ward

9. Microsoft SQL Ground to Cloud to Fabric

Develop Once, Deploy Anywhere

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.