SQL Server offers Transparent Data Encryption (TDE) and Always Encrypted as its two main database encryption features.

1. Transparent Data Encryption (TDE)

Purpose: Encrypts the entire database at rest (data files, log files, backups).

Key Features:

• Protects against unauthorised access to physical files.
• Encryption is transparent to applications.
• Uses a Database Encryption Key (DEK) stored in the database, protected by a certificate in the master database.

Setup Example:

-- Create a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<your password here>';

-- Create a certificate
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'TDE Certificate';

-- Create a database encryption key
USE YourDatabase;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

-- Enable encryption
ALTER DATABASE YourDatabase SET ENCRYPTION ON;

2. Always Encrypted

Purpose: Encrypts sensitive data in columns, protecting it both at rest and in transit.

Key Features:

• Encryption/decryption happens client-side via ADO.NET.
• SQL Server never sees the unencrypted data.
• Uses Column Encryption Keys (CEK) and Column Master Keys (CMK).

Use Cases:

• Personally identifiable information (PII)
• Credit card numbers, national IDs, etc.

Setup:

• Typically configured via SQL Server Management Studio (SSMS) or PowerShell.
• Requires .NET client driver support.

Other Encryption Options

• Cell-Level Encryption: Manual encryption of individual columns using EncryptByKey, DecryptByKey, etc.
• Encrypted Connections: Use SSL/TLS to encrypt data in transit between client and server.

From my SQL Server DBA handbook.