SQL Server 2025 and SqlPackage 170.2.70 (October 2025) - Vector databases, AI integration, and latest features
/plugin marketplace add JosiahSiegel/claude-plugin-marketplace/plugin install ssdt-master@claude-plugin-marketplaceThis skill inherits all available tools. When active, it can use any tool Claude has access to.
MANDATORY: Always Use Backslashes on Windows for File Paths
When using Edit or Write tools on Windows, you MUST use backslashes (\) in file paths, NOT forward slashes (/).
Examples:
D:/repos/project/file.tsxD:\repos\project\file.tsxThis applies to:
NEVER create new documentation files unless explicitly requested by the user.
SQL Server 2025 is the enterprise AI-ready database with native vector database capabilities, built-in AI model integration, and semantic search from ground to cloud.
SqlPackage 170.2.70 (October 14, 2025) - Latest production release with full SQL Server 2025 support, data virtualization, and parquet file enhancements.
Three major 2025 releases:
Data Virtualization (170.1.61+):
New Data Types:
New Permissions (170.0+):
ALTER ANY INFORMATION PROTECTION - SQL Server 2025 & Azure SQLALTER ANY EXTERNAL MIRROR - Azure SQL & SQL database in FabricCREATE/ALTER ANY EXTERNAL MODEL - AI/ML model managementDeployment Options:
/p:IgnorePreDeployScript=True/False - Skip pre-deployment scripts/p:IgnorePostDeployScript=True/False - Skip post-deployment scripts# Publish to SQL Server 2025
sqlpackage /Action:Publish \
/SourceFile:Database.dacpac \
/TargetServerName:server2025.database.windows.net \
/TargetDatabaseName:MyDatabase \
/TargetDatabaseEdition:Premium \
/p:TargetPlatform=SqlServer2025 # New target platform
# Extract from SQL Server 2025
sqlpackage /Action:Extract \
/SourceServerName:server2025.database.windows.net \
/SourceDatabaseName:MyDatabase \
/TargetFile:Database.dacpac \
/p:ExtractAllTableData=False \
/p:VerifyExtraction=True
# Export with SQL Server 2025 features
sqlpackage /Action:Export \
/SourceServerName:server2025.database.windows.net \
/SourceDatabaseName:MyDatabase \
/TargetFile:Database.bacpac
New ScriptDom version for SQL Server 2025 syntax parsing:
// Package: Microsoft.SqlServer.TransactSql.ScriptDom 170.0.64
using Microsoft.SqlServer.TransactSql.ScriptDom;
// Parse SQL Server 2025 syntax
var parser = new TSql170Parser(true);
IList<ParseError> errors;
var fragment = parser.Parse(new StringReader(sql), out errors);
// Supports SQL Server 2025 new T-SQL features
MAJOR MILESTONE: Microsoft.Build.Sql SDK entered General Availability in 2025!
Breaking Change from Preview:
Current Status: SQL Server 2025 target platform support coming in future Microsoft.Build.Sql release (post-2.0.0).
Workaround for SDK-Style Projects:
<!-- Database.sqlproj (SDK-style with SQL Server 2025 compatibility) -->
<Project Sdk="Microsoft.Build.Sql/2.0.0">
<PropertyGroup>
<Name>MyDatabase</Name>
<!-- Use SQL Server 2022 (160) provider until 2025 provider available -->
<DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
<TargetFramework>net8.0</TargetFramework>
<SqlServerVersion>Sql160</SqlServerVersion>
<!-- SQL Server 2025 features will still work in runtime database -->
<!-- Only build-time validation uses Sql160 provider -->
</PropertyGroup>
<ItemGroup>
<Folder Include="Tables\" />
<Folder Include="Views\" />
<Folder Include="StoredProcedures\" />
</ItemGroup>
</Project>
Requirement: Visual Studio 2022 version 17.12 or later for SDK-style SQL projects.
Note: Side-by-side installation with original SQL projects (legacy SSDT) is NOT supported.
Current Status: SQL Server 2025 (17.x) is in Release Candidate (RC1) stage as of October 2025. Public preview began May 2025.
Predicted GA Date: November 12, 2025 (based on historical release patterns - SQL Server 2019: Nov 4, SQL Server 2022: Nov 16). Expected announcement at Microsoft Ignite conference (November 18-21, 2025).
Not Yet Production: SQL Server 2025 is not yet generally available. All features described are available in RC builds for testing purposes only.
Native Enterprise Vector Store with built-in security, compliance, and DiskANN indexing technology.
Key Capabilities:
Vector Embedding & Text Chunking:
-- Create table with vector column
CREATE TABLE Documents (
Id INT PRIMARY KEY IDENTITY,
Title NVARCHAR(200),
Content NVARCHAR(MAX),
-- Half-precision vectors support up to 3,996 dimensions
ContentVector VECTOR(1536) -- OpenAI ada-002: 1,536 dims
-- ContentVector VECTOR(3072) -- OpenAI text-embedding-3-large: 3,072 dims
-- ContentVector VECTOR(3996) -- Maximum: 3,996 dims
);
-- Insert vectors (T-SQL built-in embedding generation)
INSERT INTO Documents (Title, Content, ContentVector)
VALUES (
'AI Documentation',
'Azure AI services...',
CAST('[0.1, 0.2, 0.3, ...]' AS VECTOR(1536))
);
-- Semantic similarity search with DiskANN
DECLARE @QueryVector VECTOR(1536) = CAST('[0.15, 0.25, ...]' AS VECTOR(1536));
SELECT TOP 10
Id,
Title,
Content,
VECTOR_DISTANCE('cosine', ContentVector, @QueryVector) AS Similarity
FROM Documents
ORDER BY Similarity;
-- Create DiskANN vector index for performance
CREATE INDEX IX_Documents_Vector
ON Documents(ContentVector)
USING VECTOR_INDEX
WITH (
DISTANCE_METRIC = 'cosine', -- or 'euclidean', 'dot_product'
VECTOR_SIZE = 1536
);
-- Hybrid search: Combine vector similarity with traditional filtering
SELECT TOP 10
Id,
Title,
VECTOR_DISTANCE('cosine', ContentVector, @QueryVector) AS Similarity
FROM Documents
WHERE Title LIKE '%Azure%' -- Traditional keyword filter
ORDER BY Similarity;
Built into T-SQL - Seamlessly integrate AI services with model definitions directly in the database.
Supported AI Services:
Developer Frameworks:
External Models (ONNX):
-- Create external model from ONNX file
CREATE EXTERNAL MODEL AIModel
FROM 'https://storage.account.blob.core.windows.net/models/model.onnx'
WITH (
TYPE = 'ONNX',
INPUT_DATA_FORMAT = 'JSON',
OUTPUT_DATA_FORMAT = 'JSON'
);
-- Use model for predictions
DECLARE @Input NVARCHAR(MAX) = '{"text": "Hello world"}';
SELECT PREDICT(MODEL = AIModel, DATA = @Input) AS Prediction;
-- Grant model permissions (new SQL Server 2025 permission)
GRANT CREATE ANY EXTERNAL MODEL TO [ModelAdmin];
GRANT ALTER ANY EXTERNAL MODEL TO [ModelAdmin];
GRANT EXECUTE ON EXTERNAL MODEL::AIModel TO [AppUser];
AI Service Integration:
-- Example: Azure OpenAI integration
-- Model definitions built directly into T-SQL
-- Access through REST APIs with built-in authentication
Key Innovation: Dramatically reduces lock memory consumption and minimizes blocking for concurrent transactions.
Two Primary Components:
Transaction ID (TID) Locking:
Lock After Qualification (LAQ):
Benefits:
Enabling Optimized Locking:
-- Enable RCSI (required for LAQ)
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON;
-- Optimized locking is automatically enabled at database level
-- No additional configuration needed for SQL Server 2025
-- Verify optimized locking status
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'MyDatabase';
-- Monitor optimized locking performance
SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
Integration: Near real-time replication of SQL Server databases to Microsoft Fabric OneLake for analytics.
Key Capabilities:
Supported Scenarios:
How It Works:
-- SQL Server 2025 uses change feed (automatic)
-- Azure Arc agent handles replication to Fabric OneLake
-- Traditional SQL Server 2016-2022 approach (CDC):
-- EXEC sys.sp_cdc_enable_db;
-- EXEC sys.sp_cdc_enable_table ...
-- SQL Server 2025: Change feed is built-in, no CDC setup needed
Benefits:
Configuration:
Monitoring:
-- Monitor replication lag
SELECT
database_name,
table_name,
last_sync_time,
rows_replicated,
replication_lag_seconds
FROM sys.dm_fabric_replication_status;
New JSON Data Type: Native JSON data type for Azure SQL Database (coming to SQL Server 2025).
-- New JSON data type
CREATE TABLE Products (
Id INT PRIMARY KEY,
Name NVARCHAR(100),
Metadata JSON -- Native JSON type
);
-- JSON functions enhanced
INSERT INTO Products (Id, Name, Metadata)
VALUES (1, 'Laptop', JSON('{"brand": "Dell", "ram": 16, "ssd": 512}'));
-- Query JSON with improved performance
SELECT
Id,
Name,
JSON_VALUE(Metadata, '$.brand') AS Brand,
JSON_VALUE(Metadata, '$.ram') AS RAM
FROM Products;
T-SQL RegEx Functions: Validate, search, and manipulate strings with regular expressions.
-- RegEx matching
SELECT REGEXP_LIKE('test@example.com', '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') AS IsValidEmail;
-- RegEx replace
SELECT REGEXP_REPLACE('Phone: 555-1234', '\d+', 'XXX') AS MaskedPhone;
-- RegEx extract
SELECT REGEXP_SUBSTR('Order #12345', '\d+') AS OrderNumber;
Built-in REST Capabilities: Call external REST APIs directly from T-SQL.
-- Call REST API from T-SQL
DECLARE @Response NVARCHAR(MAX);
EXEC sp_invoke_external_rest_endpoint
@url = 'https://api.example.com/data',
@method = 'GET',
@headers = '{"Authorization": "Bearer token123"}',
@response = @Response OUTPUT;
SELECT @Response AS APIResponse;
-- Enrich database data with external APIs
UPDATE Customers
SET EnrichedData = (
SELECT JSON_VALUE(response, '$.data')
FROM OPENROWSET(REST, 'https://api.example.com/customer/' + CustomerId)
)
WHERE CustomerId = 12345;
Performance Enhancement: SQL Server 2025 introduces OPPO to enable optimal execution plan selection based on customer-provided runtime parameter values.
Key Benefits:
Enabling OPPO:
-- Enable at database level
ALTER DATABASE MyDatabase
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;
-- Check status
SELECT name, is_parameter_sensitive_plan_optimization_on
FROM sys.databases
WHERE name = 'MyDatabase';
-- Monitor OPPO usage
SELECT
query_plan_hash,
parameter_values,
execution_count,
avg_duration_ms
FROM sys.dm_exec_query_stats
WHERE is_parameter_sensitive = 1;
Security Enhancement: SQL Server 2025 adds support for Microsoft Entra managed identities for improved credential management.
Key Benefits:
Configuration:
-- Create login with managed identity
CREATE LOGIN [managed-identity-name] FROM EXTERNAL PROVIDER;
-- Grant permissions
CREATE USER [managed-identity-name] FOR LOGIN [managed-identity-name];
GRANT CONTROL ON DATABASE::MyDatabase TO [managed-identity-name];
-- Use in connection strings
-- Connection string: Server=myserver;Database=mydb;Authentication=Active Directory Managed Identity;
Sensitivity classification and encryption:
-- Classify sensitive columns
ADD SENSITIVITY CLASSIFICATION TO
Customers.Email,
Customers.CreditCard
WITH (
LABEL = 'Confidential',
INFORMATION_TYPE = 'Financial'
);
-- Query classification
SELECT
schema_name(o.schema_id) AS SchemaName,
o.name AS TableName,
c.name AS ColumnName,
s.label AS SensitivityLabel,
s.information_type AS InformationType
FROM sys.sensitivity_classifications s
INNER JOIN sys.objects o ON s.major_id = o.object_id
INNER JOIN sys.columns c ON s.major_id = c.object_id AND s.minor_id = c.column_id;
# Publish with 2025 features
sqlpackage /Action:Publish \
/SourceFile:Database.dacpac \
/TargetConnectionString:"Server=tcp:server2025.database.windows.net;Database=MyDb;Authentication=ActiveDirectoryManagedIdentity;" \
/p:BlockOnPossibleDataLoss=True \
/p:IncludeCompositeObjects=True \
/p:DropObjectsNotInSource=False \
/p:DoNotDropObjectTypes=Users;RoleMembership \
/p:GenerateSmartDefaults=True \
/DiagnosticsFile:deploy.log
<!-- Database.publish.xml -->
<Project>
<PropertyGroup>
<TargetConnectionString>Server=tcp:server2025.database.windows.net;Database=MyDb;Authentication=ActiveDirectoryManagedIdentity;</TargetConnectionString>
<BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
<TargetDatabaseName>MyDatabase</TargetDatabaseName>
<ProfileVersionNumber>1</ProfileVersionNumber>
</PropertyGroup>
<ItemGroup>
<SqlCmdVariable Include="Environment">
<Value>Production</Value>
</SqlCmdVariable>
</ItemGroup>
</Project>
# Deploy using MSBuild
msbuild Database.sqlproj \
/t:Publish \
/p:PublishProfile=Database.publish.xml \
/p:TargetPlatform=SqlServer2025
State-Based Deployment (Recommended):
Testing & Quality:
Security:
Version Control:
name: Deploy to SQL Server 2025
on:
push:
branches: [main]
jobs:
build-and-test:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- name: Setup .NET 8
uses: actions/setup-dotnet@v4
with:
dotnet-version: '8.0.x'
- name: Install SqlPackage 170.2.70
run: dotnet tool install -g Microsoft.SqlPackage --version 170.2.70
- name: Build DACPAC
run: dotnet build Database.sqlproj -c Release
- name: Run tSQLt Unit Tests
run: |
# Run unit tests and capture results
# Abort if tests fail
echo "Running tSQLt unit tests..."
# Add your tSQLt test execution here
- name: Generate Deployment Report
run: |
sqlpackage /Action:DeployReport \
/SourceFile:bin/Release/Database.dacpac \
/TargetConnectionString:"${{ secrets.SQL_CONNECTION_STRING }}" \
/OutputPath:deploy-report.xml \
/p:BlockOnPossibleDataLoss=True
- name: Publish to SQL Server 2025
run: |
sqlpackage /Action:Publish \
/SourceFile:bin/Release/Database.dacpac \
/TargetConnectionString:"${{ secrets.SQL_CONNECTION_STRING }}" \
/p:TargetPlatform=SqlServer2025 \
/p:BlockOnPossibleDataLoss=True \
/DiagnosticsFile:publish.log \
/DiagnosticsLevel:Verbose
- name: Upload Artifacts
if: always()
uses: actions/upload-artifact@v4
with:
name: deployment-logs
path: |
publish.log
deploy-report.xml
trigger:
- main
pool:
vmImage: 'windows-2022'
steps:
- task: MSBuild@1
displayName: 'Build Database Project'
inputs:
solution: 'Database.sqlproj'
configuration: 'Release'
- task: SqlAzureDacpacDeployment@1
displayName: 'Deploy to SQL Server 2025'
inputs:
azureSubscription: 'Azure Subscription'
authenticationType: 'servicePrincipal'
serverName: 'server2025.database.windows.net'
databaseName: 'MyDatabase'
deployType: 'DacpacTask'
deploymentAction: 'Publish'
dacpacFile: '$(Build.SourcesDirectory)/bin/Release/Database.dacpac'
additionalArguments: '/p:TargetPlatform=SqlServer2025'
# Enable detailed diagnostics
sqlpackage /Action:Publish \
/SourceFile:Database.dacpac \
/TargetServerName:server2025.database.windows.net \
/TargetDatabaseName:MyDatabase \
/DiagnosticsLevel:Verbose \
/DiagnosticPackageFile:diagnostics.zip
# Creates diagnostics.zip containing:
# - Deployment logs
# - Performance metrics
# - Error details
# - Schema comparison results
New in SqlPackage 162.5+: Full support for SQL database in Microsoft Fabric.
Fabric Deployment:
# Deploy to Fabric Warehouse
sqlpackage /Action:Publish \
/SourceFile:Warehouse.dacpac \
/TargetConnectionString:"Server=tcp:myworkspace.datawarehouse.fabric.microsoft.com;Database=mywarehouse;Authentication=ActiveDirectoryInteractive;" \
/p:DatabaseEdition=Fabric \
/p:DatabaseServiceObjective=SqlDbFabricDatabaseSchemaProvider
# Extract from Fabric
sqlpackage /Action:Extract \
/SourceConnectionString:"Server=tcp:myworkspace.datawarehouse.fabric.microsoft.com;Database=mywarehouse;Authentication=ActiveDirectoryInteractive;" \
/TargetFile:Fabric.dacpac
# New permission: ALTER ANY EXTERNAL MIRROR (Fabric-specific)
GRANT ALTER ANY EXTERNAL MIRROR TO [FabricAdmin];
<PropertyGroup>
<TargetPlatform>SqlServer2025</TargetPlatform>
</PropertyGroup>
-- Verify vector support
SELECT SERVERPROPERTY('IsVectorSupported') AS VectorSupport;
-- Track model execution
SELECT
model_name,
AVG(execution_time_ms) AS AvgExecutionTime,
COUNT(*) AS ExecutionCount
FROM sys.dm_exec_external_model_stats
GROUP BY model_name;
-- Classify all PII columns
ADD SENSITIVITY CLASSIFICATION TO dbo.Customers.Email
WITH (LABEL = 'Confidential - GDPR', INFORMATION_TYPE = 'Email');
This skill should be used when the user asks to "create a slash command", "add a command", "write a custom command", "define command arguments", "use command frontmatter", "organize commands", "create command with file references", "interactive command", "use AskUserQuestion in command", or needs guidance on slash command structure, YAML frontmatter fields, dynamic arguments, bash execution in commands, user interaction patterns, or command development best practices for Claude Code.
This skill should be used when the user asks to "create an agent", "add an agent", "write a subagent", "agent frontmatter", "when to use description", "agent examples", "agent tools", "agent colors", "autonomous agent", or needs guidance on agent structure, system prompts, triggering conditions, or agent development best practices for Claude Code plugins.
This skill should be used when the user asks to "create a hook", "add a PreToolUse/PostToolUse/Stop hook", "validate tool use", "implement prompt-based hooks", "use ${CLAUDE_PLUGIN_ROOT}", "set up event-driven automation", "block dangerous commands", or mentions hook events (PreToolUse, PostToolUse, Stop, SubagentStop, SessionStart, SessionEnd, UserPromptSubmit, PreCompact, Notification). Provides comprehensive guidance for creating and implementing Claude Code plugin hooks with focus on advanced prompt-based hooks API.