Support Multi-Platform Migration

Feature Request: Cross-Platform DACPAC Deployment Filters for SQL Server on Linux

Summary

Add support for selective filtering of CLR assemblies and Active Directory objects when deploying DACPACs from Windows SQL Server environments to SQL Server on Linux containers, enabling seamless cross-platform database deployments.

Problem Statement

Current Challenge

Organizations using SQL Server 2022 Enterprise Edition on Windows often need to deploy their databases to SQL Server on Linux containers (Ubuntu) for cloud-native deployments. However, this cross-platform migration faces two critical blockers:

1. CLR Assembly Incompatibility: Non-SAFE CLR assemblies that work on Windows SQL Server are not supported on SQL Server on Linux
2. Active Directory Dependencies: Windows-specific AD users and groups cannot be resolved in Linux environments

Impact

• Deployment Failures: DACPACs fail to deploy due to unsupported CLR assemblies and AD references
• Manual Workarounds Required: Teams must manually edit DACPAC contents or maintain separate versions
• Broken CI/CD Pipelines: Automated deployments fail when moving from Windows to Linux environments
• Development Overhead: Significant time spent on platform-specific database versions

Proposed Solution

New SqlPackage Parameters

Add the following deployment parameters to enable selective filtering:

1. CLR Assembly Filtering

/p:IgnoreNonSafeCLRAssemblies=true
/p:IgnoreCLRAssemblies=true  # For complete CLR removal
/p:CLRPermissionSetFilter="SAFE,EXTERNAL_ACCESS"  # Granular control

2. Active Directory Object Filtering

/p:IgnoreActiveDirectoryPrincipals=true
/p:ADDomainFilter="DOMAIN1,DOMAIN2"  # Specific domain exclusion
/p:PreserveLocalPrincipals=true  # Keep SQL Server authentication users

3. Dependency Resolution

/p:RemoveDependentObjects=true  # Auto-remove objects that depend on filtered items
/p:OrphanedObjectHandling="Remove|Warn|Fail"  # How to handle broken references

Detailed Behavior

CLR Assembly Handling

• Identify Permission Levels: Parse CLR assemblies to determine SAFE vs EXTERNAL_ACCESS vs UNSAFE
• Cascade Removal: Automatically remove functions, procedures, and triggers that reference filtered assemblies
• Dependency Tracking: Map and remove all objects with ExternalName properties pointing to filtered assemblies
• Relationship Cleanup: Remove orphaned relationships that reference deleted CLR objects

Active Directory Principal Handling

• Pattern Recognition: Identify AD principals by domain prefix patterns (e.g., DOMAIN\user)
• Selective Filtering: Remove only AD-based logins, users, and role memberships
• Preserve SQL Authentication: Keep SQL Server authentication and contained database users
• Permission Cleanup: Remove permissions and role assignments for filtered AD principals

Dependency Resolution Engine

• Multi-pass Analysis: Iteratively identify and remove dependent objects
• Impact Assessment: Generate reports showing what will be removed
• Safe Removal: Ensure no dangling references remain that could cause deployment failures

Use Cases

Enterprise Scenario

Company: Large financial institution
Environment: 50+ databases with CLR assemblies for business logic
Challenge: Migrating from Windows SQL Server to Azure SQL Database/Linux containers
Solution: Use new parameters to automatically filter incompatible components

DevOps Pipeline

Scenario: CI/CD pipeline deploying to multiple environments
Windows Environment: Full feature set including CLR assemblies
Linux Environment: Filtered deployment without CLR dependencies
Benefit: Single DACPAC source, platform-specific deployments

Hybrid Cloud Migration

Current State: On-premises Windows SQL Server with AD integration
Target State: Cloud-native SQL Server on Linux
Migration Path: Gradual transition with filtered deployments

Technical Implementation

DACPAC Structure Analysis

<!-- Example: CLR Assembly Detection -->
<Element Type="SqlAssembly" Name="[MyAssembly]">
  <Property Name="PermissionSet" Value="UNSAFE" />
</Element>

<!-- Example: AD Principal Detection -->
<Element Type="SqlLogin" Name="[DOMAIN\User]">
  <Property Name="LoginType" Value="WindowsLogin" />
</Element>

Filtering Algorithm

1. Parse DACPAC: Extract and analyze model.xml
2. Classify Objects: Identify CLR assemblies by permission set, AD principals by pattern
3. Build Dependency Graph: Map relationships between objects
4. Apply Filters: Remove filtered objects and their dependencies
5. Validate Model: Ensure no broken references remain
6. Update Checksums: Recalculate model checksums for integrity

Backward Compatibility

• All new parameters optional (default: false)
• Existing deployments unchanged
• Clear warnings when objects are filtered
• Detailed logs of removed components

Expected Benefits

For Development Teams

• Simplified Workflows: Single DACPAC for multiple platforms
• Reduced Maintenance: No need for platform-specific database versions
• Faster Deployments: Automated filtering vs manual editing

For DevOps

• Reliable Pipelines: Predictable cross-platform deployments
• Environment Parity: Consistent deployment process across platforms
• Automated Migration: Seamless Windows-to-Linux transitions

For Organizations

• Cloud Migration: Easier adoption of containerized SQL Server
• Platform Flexibility: Deploy to Windows or Linux as needed
• Cost Optimization: Leverage Linux containers for cost savings

Alternative Approaches Considered

1. Pre-processing Tools

Approach: External tools to modify DACPACs before deployment
Issues: Additional toolchain complexity, version compatibility concerns

2. Manual DACPAC Editing

Approach: Script-based modification of DACPAC contents
Issues: Error-prone, maintenance overhead, no dependency resolution

3. Schema Comparison Tools

Approach: Use SqlPackage Compare with filtered schemas
Issues: Doesn't address DACPAC deployment scenarios, limited automation

Implementation Priority

Phase 1: Core Filtering

• Basic CLR assembly filtering by permission set
• Simple AD principal removal by pattern matching
• Dependency cascade removal

Phase 2: Advanced Features

• Granular permission set control
• Domain-specific filtering
• Orphaned object handling options

Phase 3: Reporting & Validation

• Detailed removal reports
• Impact analysis tools
• Deployment validation hooks

Success Metrics

• Deployment Success Rate: Increase from ~60% to 95%+ for cross-platform scenarios
• Manual Intervention: Reduce from hours to minutes per deployment
• Developer Productivity: 50% reduction in platform-specific maintenance overhead
• Pipeline Reliability: Near-zero deployment failures due to platform incompatibilities

Related Issues & References

• Cross-platform SQL Server deployment challenges
• CLR assembly limitations on SQL Server on Linux
• Active Directory integration in containerized environments
• DACPAC portability across different SQL Server editions

---

Example Usage

# Deploy Windows DACPAC to Linux SQL Server
SqlPackage.exe /Action:Publish \
  /SourceFile:MyDatabase.dacpac \
  /TargetConnectionString:"Server=linux-sql;Database=MyDB;User Id=sa;Password=MyPass;" \
  /p:IgnoreNonSafeCLRAssemblies=true \
  /p:IgnoreActiveDirectoryPrincipals=true \
  /p:RemoveDependentObjects=true \
  /p:OrphanedObjectHandling=Remove

# Result: Clean deployment without CLR/AD dependencies

This feature would significantly improve the developer experience for cross-platform SQL Server deployments and enable more organizations to adopt modern, containerized database architectures.

Iniziare esaminando i parametri di distribuzione proposti di SqlPackage e l’elaborazione di DACPAC model.xml descritta nell’issue, inclusi gli assembly CLR, i principal di Active Directory e le relazioni di dipendenza. Per considerarlo completato sarebbero necessari un ambito concordato, punti di ingresso per l’implementazione, la convalida di modelli filtrati senza riferimenti pendenti e test o report che coprano i comportamenti richiesti.