How do I encrypt old rows after ALTER TABLE ENCRYPTED?

[Breck_Carter]

Testing with Hexplorer shows that ALTER TABLE ENCRYPTED does NOT encrypt existing data.

In other words, rows inserted before ALTER TABLE ENCRYPTED are not encrypted, but rows inserted after the ALTER are encrypted.

The Help does not seem say this explicitly but I dropped out of law school before learning how to read stuff like that 🙂

Question: How do I force those old rows to become encrypted?

(Version 17, table encryption, dbinit -ea AES256 -ek "abc" -et^)

[VolkerBarth]

Well, as the according page claims:

When table encryption is enabled, table pages for the encrypted table, associated index pages, temporary file pages, and transaction log pages containing transactions on encrypted tables are encrypted.

and

After encrypting a table, any data for that table that was in temporary files or the transaction log before encryption still exists in unencrypted form. To address this, restart the database to remove the temporary files.

I would assume that encrypting a table SHOULD encrypt existing data, too - otherwise the second quote about data in temporary files or logs would be moot. But I have not tested this, I'm usually using database encryption...

[Breck_Carter]

encrypting a table SHOULD encrypt existing data

That's what I thought too, but it is NOT what it does, and I can't wait for a fix change in behavior.

The question remains, How do I force those old rows to become encrypted?

(should I post the reproducible?)

[jack_schueler]

I think what you are seeing are old table fragments in free pages. If you can, try the REORGANIZE TABLE statement on the table in question, shut down the database, and then see if any unencrypted data is still present.

If REORGANIZE table doesn't work, create a new table, fill it with lots of random data rows, delete it, and then check to see if the table pages containing unencrypted data were reclaimed in the process. I know this isn't any kind of solution but it should show that the unencrypted data that you are seeing resides in freed pages.

[Breck_Carter]

Excellent answer... alas, inserts only, no updates or deletes, so freed pages are ( cough 🙂 unlikely.

[graeme_perrow]

I think Jack is right. When you run ALTER TABLE ENCRYPTED, we go through and encrypt each page associated with that table. My guess is that the previous (unencrypted) version of the page gets freed and the new (encrypted) version replaces it. Eventually the freed page will be reused.

[VolkerBarth]

So you do not encrypt "in-situ"?

[Breck_Carter]

Your guess makes sense, even though...

"The placement of each leading row segment on a page is immutable." ...at least according to my book 🙂

However, I'm guessing that doesn't affect most of the data in big fAt blob columns like in this case...

INSERT t2 VALUES ( 1, 1, 
   REPEAT ( 'This text should be INVISIBLE. ', 10000 ) );

It should be fairly easy to confirm your guess with some carefully crafted strings.

HOWEVER... "eventually the freed page will be reused" is not good enough for a client who wants to encrypt a VERY BIG table in-place; i.e., with minimal outage, so no unload reload.

So, back to the original question, restated...

How do I remove the clear data after ALTER TABLE ENCRYPTED?

Will the REORGANIZE suggestion work?