How can I successfully decrypt column data through the whole database safely?

Question

We're moving to Azure SqlDb but as part of that, we're needing to migrate away from EncryptByPassPhrase as it's not supported.

As such, we tried the process on the weekend but we ran into the log filling up and the script stopping midway through.

What are my options here? There will be zero usage of the DB during the process, can/should I change the DB to simple from full recovery model temporarily? I don't really think we care about the individual transactions during the process, just the end result.

Also, given we're doing a compute intensive task - should I bump up the CPU resources temporarily to hopefully make it go faster? It took 9+ hours to fail, I'm wondering if the log interfered.

This is:

• SQL 2019 (15.0.2000.5)
• DB compat is 150
• We've disabled all triggers/jobs during this process

Here's part of the script:

DECLARE @Password VARCHAR(255) = '<some super cool long very high entropy password here>';

SET NOCOUNT ON;

DECLARE @TestDecryption VARCHAR(255);
SELECT TOP(1)@TestDecryption = CONVERT (VARCHAR(50), DECRYPTBYPASSPHRASE (@Password, HealthNumber_Enc))
FROM Patients.Patient
WHERE ProvinceCode = 'ON'
      AND HealthNumber_Enc IS NOT NULL
ORDER BY CreateDate DESC;
PRINT 'Test: ' + @TestDecryption;

IF @TestDecryption IS NOT NULL -- this is to ensure that our password actually works
BEGIN
    ; DISABLE TRIGGER ALL ON DATABASE;

    -- systematically decrypt data in all affected tables
    UPDATE Users.Activity
    SET [Description] = ISNULL (CONVERT (VARCHAR(1000), DECRYPTBYPASSPHRASE (@Password, Description_Enc)), [Description]);
    UPDATE Users.Activity
    SET Description_Enc = NULL;

    UPDATE Contact.[Address]
    SET Address1 = ISNULL (CONVERT (VARCHAR(1000), DECRYPTBYPASSPHRASE (@Password, Address1_Enc)), Address1), Address2 = ISNULL (CONVERT (VARCHAR(1000), DECRYPTBYPASSPHRASE (@Password, Address2_Enc)), Address2), Address3 = ISNULL (CONVERT (VARCHAR(1000), DECRYPTBYPASSPHRASE (@Password, Address3_Enc)), Address3), City = ISNULL (CONVERT (VARCHAR(255), DECRYPTBYPASSPHRASE (@Password, City_Enc)), City), PostalCode = ISNULL (CONVERT (VARCHAR(20), DECRYPTBYPASSPHRASE (@Password, PostalCode_Enc)), PostalCode), Latitude = ISNULL (CONVERT (DECIMAL(9, 6), CONVERT (VARCHAR(10), DECRYPTBYPASSPHRASE (@Password, Latitude_Enc))), Latitude), Longitude = ISNULL (CONVERT (DECIMAL(9, 6), CONVERT (VARCHAR(10), DECRYPTBYPASSPHRASE (@Password, Longitude_Enc))), Longitude);
    UPDATE Contact.[Address]
    SET Address1_Enc = NULL, Address2_Enc = NULL, Address3_Enc = NULL, City_Enc = NULL, PostalCode_Enc = NULL, Latitude_Enc = NULL, Longitude_Enc = NULL

    -- Some 50 other tables after here .. 

    ;ENABLE TRIGGER ALL ON DATABASE;
END;
ELSE BEGIN
PRINT 'Test Decryption returned NULL. Will not proceed!!';
END;

Errors looked like:

Msg 9002, Level 17, State 2, Procedure TRIG_Contact_Address_UPDATE_1, Line 26 [Batch Start Line 0]
The transaction log for database 'YourExampleDb' is full due to 'LOG_BACKUP'.

Answer 1

Do it in batches, using a TOP(n) approach.

You could monitor the transaction log size & adjust the batch size accordingly.

If you wanted to keep the database in the Full Recovery Model, then you could backup the log & shrink it as necessary.

Answer 2

Take more frequent log backups and/or increase the disk space available for the log files. Let it run for 48 hours over the weekend.

I don't know everything about your system but increasing CPU is unlikely to help.

Answer 3

I would do these especially to control transaction log growth,

1. If possible split the query to iterate each table separately instead of running updates for all 50+ tables as a single batch.
2. Batching updates, preferably on PK clustered index using careful batching approach defined here only if applicable.
3. Increase frequency of log backups.