This page describes how to encrypt the data-files and the communication of the Microsoft SQL Server and describes the performance impact to be expected by it.

...

The following example shows encryption of the yuuvisRAD database using a certificate named MyServerCert that's installed on the server.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
GO
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
GO
USE yuuvisRAD;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE yuuvisRAD SET ENCRYPTION ON;
GO

To enable TDE on a database, SQL Server must do an encryption scan. The scan reads each page from the data files into the buffer pool and then writes the encrypted pages back out to disk.
The progress of the encryption scan can be monitored by the following statement:

SELECTDB_NAME(database_id)ASDatabaseName,encryption_state,
encryption_state_desc=
CASEencryption_state
         WHEN'0'  THEN  'No database encryption key present, no encryption'
         WHEN'1'  THEN  'Unencrypted'
         WHEN'2'  THEN  'Encryption in progress'
         WHEN'3'  THEN  'Encrypted'
         WHEN'4'  THEN  'Key change in progress'
         WHEN'5'  THEN  'Decryption in progress'
         WHEN'6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
         ELSE'No Status'
         END,
percent_complete,encryptor_thumbprint,encryptor_type  
FROMsys.dm_database_encryption_keys
source: https://www.sqlshack.com/how-to-monitor-and-manage-transparent-data-encryption-tde-in-sql-server/

Starting with SQL Server 2019 the encryption scan can be paused with ALTERDATABASE <db_name> SET ENCRYPTION SUSPEND; and resumed with ALTER DATABASE <db_name> SET ENCRYPTION RESUME;

To backup the server certificate and its private key execute the following Statements - after adjusting the paths and passwords to your individual configuration (see https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/back-up-the-service-master-key?view=sql-server-ver15):

USE master;
GO
BACKUP SERVICE MASTERKEY
    TOFILE = '\\SECURE_SERVER\keys\service_master_key'
    ENCRYPTION BYPASSWORD = '<UseStrongPasswordHere>'
GO
BACKUP MASTERKEY
    TOFILE = '\\SECURE_SERVER\keys\master_key'
    ENCRYPTION BYPASSWORD = '<UseStrongPasswordHere>'
GO
BACKUP CERTIFICATE MyServerCert
    TOFILE = '\\SECURE_SERVER\cert\MyServerCert.cer'
    WITHPRIVATEKEY (
                                     FILE = '\\SECURE_SERVER\cert\MyServerCert.key',
                                     ENCRYPTION BYPASSWORD = '<UseStrongPasswordHere>'
                                   )
GO

Communication encryption

As described in https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15 the Micrososft SQL Server supports communication encryption using SSL (deprecated), TLS, and IPSec. Here we show how to setup TLS encryption. 

...

The encryption and decryption of the data and the communications communication add another step in the processing pipeline and thus come at some performance costs. Since the encrypted data has the same size as the plain data, I/O and memory costs will not be affected but the CPU load will definitely increase. To get an impression of the impact, we tested the following scenarios by creating 4500 objects in a row:

• unencrypted
• communications communication encryption only (TLS)
• TDE + communications communication encryption

As expected, I/O and memory did not show any change, but the CPU load did increase. For the communications communication encryption only scenario this was an increase of about 3% and for the TDE + communications communication encryption scenario about 8%.

Microsoft itself states that TDE comes at about 3-5% of performance costs (see https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/security/transparent-data-encryption#:~:text=TDE%20has%20an%20estimated%20performance,this%20topic%20for%20more%20details). If we add our measured 3% for communications communication to that it fits the 8% impact of TDE + communicationscommunication.

It also fits with the results of the more in-depth tests done by https://www.mssqltips.com/sqlservertip/2641/sql-server-transparent-data-encryption-tde-performance-comparison/. They state that in the worst case TDE can come at 10-20% performance costs but in real life scenarios 3-10% are to experienced.