For encrypting an SQL Server database, please review Encrypt an Existing MS SQL Server 2019 Database in an Always On Environment Using TDE
1. Enable encryption
1.1- On the primary database, Start (enable) Encryption
Use
the following command to enable encryption for the databases on the primary
replica.
USE [StackOverflow2013];
GO
ALTER DATABASE [StackOverflow2013]
SET ENCRYPTION ON;
SQL Server begins by scanning pages on disk and encrypting them. The progress of this scan can be monitored using the following query on the primary and secondary databases.
SELECT DB_NAME([database_id]) AS 'Database Name',
[encryption_state],
[percent_complete],
[encryption_state_desc],
[encryption_scan_state],
[encryption_scan_state_desc],
[encryption_scan_modify_date],
[create_date],
[regenerate_date],
[key_algorithm],
[key_length],
[modify_date],
[set_date],
[opened_date],
[encryptor_thumbprint],
[encryptor_type]
FROM [sys].[dm_database_encryption_keys]
The
returned information from the query shows that TempDB was encrypted
automatically and the StackOverflow2013 database is in progress of encryption –
ENCRYPTION_IN_PROGRESS.
1.2- On the primary database, pause the encryption
We can pause the scan of pages using the following command on the primary SQLNODE1\StackOverflow2013 instance:
USE [StackOverflow2013];
GO
ALTER DATABASE [StackOverflow2013]
SET ENCRYPTION SUSPEND;
GO
If we run the previous SELECT statement, the column encryption_scan_state_desc confirms that the scan of pages is SUSPENDED and column percent_complete is back to 0 on all databases.
On the primary instance SQLNODE1\StackOverflow2013
This
is the load on the system after suspending the encryption.
1.3- On the primary database, resume Encryption
To resume page encryption on the primary instance SQLNODE1\StackOverflow2013, execute the following command: RESUME.
USE [StackOverflow2013];
GO
ALTER DATABASE [StackOverflow2013]
SET ENCRYPTION RESUME;
GO
The query results show that TempDB was encrypted automatically, and TempestTest database encryption is in progress (ENCRYPTION_IN_PROGRESS).
This is the load after resuming the encryption on the primary instance SQLNODE1\StackOverflow2013
On the secondary instance, SQLNODE2\StackOverflow2013, ENCRYPTION_IN_PROGRESS is also displayed.
No comments:
Post a Comment