SQLSERVER: BCP import and encrypt columns using Database Key

Question

I have a large file which needs to be imported to SQL Server. File contains columns of personal information (like first_name, phone_number). Currently I'm importing the large file onto SQL Server using BCP tool. And as a next step, I'm encrypting the columns using Database Key as shown below.

CREATE TABLE users (
    first_name VARCHAR(4000)
)

CREATE CERTIFICATE db_cert1
WITH SUBJECT = 'Encrypt PII data'; 
GO 

CREATE SYMMETRIC KEY db_symkey1
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE db_cert1;
GO

BEGIN TRY
    UPDATE users
    SET first_name = CAST(EncryptByKey(KEY_GUID('db_symkey1'),[first_name]) AS VARCHAR(MAX))
END TRY
BEGIN CATCH
    DELETE FROM users;
END CATCH

There are 100s of columns in my table and 10s of such sensitive columns which needs encryption and millions of rows. Currently it is slow (due to number of rows and VARCHAR(MAX/4000))

Is there a better way to achieve this? Does BCP offer any out of the box solution?

Answer 1

Below are the steps followed to improve performance.

1. Created two columns for each sensitive data
   • first_name_plaintext VARCHAR(256)
   • first_name VARBINARY(308)
   • Thanks @gotqn for this
2. Added an auto incrementing id column, added a clustered index(this makes sure its already sorted) on the table and did updates in batches (like WHERE [id] BETWEEN 1 AND 100000).
3. Commit after each iteration (to reduce the transaction logs usage)
4. Changed the DB Recovery model to Simple (IMPORTANT)
5. Increased the DB file size
6. If there are no restrictions, you can use AES_128 encryption for key creation instead of AES_256, but our security advisor didn't allow this.

This improved the time from 3 minutes to 1:17 minutes for 1 million records.