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

まず、提案されている SqlPackage のデプロイ パラメーターと、issue で説明されている DACPAC model.xml の処理について確認します。これには、CLR アセンブリ、Active Directory プリンシパル、依存関係が含まれます。完了とするには、合意されたスコープ、実装のエントリポイント、ぶら下がり参照のないフィルター済みモデルの検証、および要求された動作を対象とするテストまたはレポートが必要です。

索引モデルが issue の本文から書いたものです。