Encryption

-- Summary. Full article: "How to configure Transparent Data Encryption (TDE) in SQL Server", SQLShack – https://www.sqlshack.com/how-to-configure-transparent-data-encryption-tde-in-sql-server/

-- TDE encrypts the data and log files (and backups) at rest.
-- IMPORTANT: back up the certificate and its private key right away. Without them
-- the database and its backups cannot be restored on any other server.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';
CREATE CERTIFICATE TDE_Cert WITH SUBJECT = 'TDE certificate';
BACKUP CERTIFICATE TDE_Cert TO FILE = 'D:\Keys\TDE_Cert.cer'
  WITH PRIVATE KEY (FILE = 'D:\Keys\TDE_Cert.pvk',
    ENCRYPTION BY PASSWORD = '<strong password>');

USE MyDatabase;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
ALTER DATABASE MyDatabase SET ENCRYPTION ON;

-- Progress (encryption_state 3 = encrypted)
SELECT DB_NAME(database_id) AS db, encryption_state, percent_complete
  FROM sys.dm_database_encryption_keys;

-- On the target server, before restoring: create a master key, then
USE master;
CREATE CERTIFICATE TDE_Cert FROM FILE = 'D:\Keys\TDE_Cert.cer'
  WITH PRIVATE KEY (FILE = 'D:\Keys\TDE_Cert.pvk',
    DECRYPTION BY PASSWORD = '<strong password>');