Support Multi-Platform Migration

Nobody has claimed this yet.

# 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
```bash
/p:IgnoreNonSafeCLRAssemblies=true
/p:IgnoreCLRAssemblies=true # For complete CLR removal
/p:CLRPermissionSetFilter="SAFE,EXTERNAL_ACCESS" # Granular control
```

#### 2. Active Directory Object Filtering
```bash
/p:IgnoreActiveDirectoryPrincipals=true
/p:ADDomainFilter="DOMAIN1,DOMAIN2" # Specific domain exclusion
/p:PreserveLocalPrincipals=true # Keep SQL Server authentication users
```

#### 3. Dependency Resolution
```bash
/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
```xml

```

### 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

```bash
# 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.

Start by reviewing the proposed SqlPackage deployment parameters and the DACPAC model.xml processing described in the issue, including CLR assemblies, Active Directory principals, and dependency relationships. Done would require an agreed scope, implementation entry points, validation of filtered models without dangling references, and tests or reports covering the requested behaviors.

Written by the indexing model from the issue text.