适用于SQL Always Encryption加密列的更佳复制方案咨询

Great question—this is a common pain point when combining encryption requirements with replication in SQL Server. Let’s break down your options based on your needs for synchronization latency, complexity, and GDPR compliance:

1. Switch to SQL Server Always On Availability Groups (AGs) #

Always On AGs are natively compatible with Always Encryption, making them a strong replacement for transactional replication in many cases. Here’s why:

• AGs use log-based synchronization, which preserves the encrypted state of columns during replication—no decryption/re-encryption happens mid-stream.
• You get the added benefit of high availability alongside synchronization, which is a plus for production systems.

Key considerations:

• AGs require the entire database to be synchronized (you can’t replicate just specific tables/columns like with transactional replication). If you only need to sync a subset of data, this might not be ideal.
• Ensure your target server(s) have access to the same Column Master Key (CMK) and Column Encryption Key (CEK) as the source. For example:
  • If using Azure Key Vault for CMK, grant the target server’s managed identity/service principal access to the vault.
  • If using a local certificate, back up the CMK certificate from the source and restore it on the target server.
• Your databases must be in the full recovery mode (a requirement for AGs anyway).

2. Use Snapshot Replication (For Non-Real-Time Needs) #

Snapshot replication works seamlessly with Always Encryption because it copies raw encrypted column data directly from source to target—no decryption is involved in the replication process.

Best for:

• Scenarios where you don’t need real-time sync (e.g., nightly or hourly updates to a reporting database).
• Simple sync workflows where you can tolerate periodic full snapshots of the data.

Setup tips:

• Configure the snapshot agent with an account that has read access to the source database (it doesn’t need decryption permissions, since it’s copying encrypted bytes).
• Pre-create the encrypted column schema and matching CMK/CEK on the target database before running the first snapshot.

3. Build a Custom Sync Solution (SSIS or Custom Code) #

If you need granular control over which tables/columns to sync, a custom solution gives you maximum flexibility. Options include:

• SSIS Packages: Create a package that reads encrypted columns from the source (using the Always Encryption-enabled driver) and writes them to the target. Ensure the SSIS runtime account has access to the CMK if you need to perform any in-package operations on the data (otherwise, you can copy the encrypted bytes directly).
• Custom Applications: Use SQL Server’s Always Encryption SDK (available for .NET, Java, etc.) to build a service that polls for changes or reads from change logs, then syncs encrypted data to the target.

Pros & Cons:

• ✅ Full control over sync logic (filter rows, handle conflicts, etc.)
• ❌ Requires ongoing development and maintenance (you’ll need to handle error retries, monitoring, and schema changes manually).

4. Change Data Capture (CDC) + Custom Sync #

For near-real-time sync without transactional replication, combine CDC with a custom sync service:

1. Enable CDC on the source database to capture changes to your encrypted columns.
2. Build a service that reads the CDC logs (which store encrypted data as raw bytes) and applies those changes to the target database.

Key notes:

• CDC captures encrypted column data exactly as it’s stored, so you don’t need to decrypt anything during the sync process.
• Like custom solutions, you’ll need to maintain the sync service, but CDC reduces the work of detecting changes compared to polling the database directly.

Critical Compliance & Key Management Tips #

Whichever solution you choose, make sure to:

• Secure your keys: GDPR requires strict control over encryption keys. Ensure CMKs are stored in a secure vault (Azure Key Vault, HSM, etc.) and only authorized servers/services have access.
• Encrypt data in transit: Use SSL/TLS for all database connections between source, sync services, and target to meet GDPR’s data-in-transit requirements.
• Validate target encryption: Confirm that the target database’s encrypted columns use the same encryption settings as the source, and that only authorized users can decrypt data.

内容的提问来源于stack exchange，提问作者Nagaraj M