Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

What is the best way to Update over 50 million records in a 24x7 production table via encryption?

I have a table, tblSubscribers with 50 million records/rows. The table has one clustered index cidx_tblSubscribers_AccountID and one nonclustered index ncidx_tblSubscribers.

I am currently in the process of updating these records by encrypting the data. I have written a Cursor to update the records one row at a time but this of course will create problems since this is a production table and cursors are known to be performance hogs furthermore there are a lot of people accessing the table and inserting data to the table via our Web site.

Here is the structure of the table:

CREATE TABLE tblSubscribers  
AccountID  INT IDENTITY(1,1),
fname varchar(30) Not Null,
lname varchar(30) Not Null,
address varchar(50) Not Null,
city varchar(20) Not Null,
state CHAR(2) Not Null,
ssn varchar(10) Not Null,
CONSTRAINT PK_tblSubscribers_AccountID PRIMARY KEY(AccountID)

The table also has two indexes:

Clustered Index: PK_tblSubscribers_AccountID

Nonclustered Index: ncidx_tblSubscribers_01

Here is my cursor:

DECLARE @fname varchar(50),@lname varchar(50),@ssn varchar(10)
DECLARE UpdateCursor CURSOR FOR select fname,lname,ssn from tblSubscribers

/* Open the cursor */
OPEN UpdateCursor
/* Fetch the first row */
FETCH NEXT FROM UpdateCursor INTO @fname, @lname,@ssn

       IF (@@FETCH_STATUS <> -2)
       Update tblSubscribers set fname = dbo.fun_Encrypt(fname), lname = dbo.fun_Encrypt(lname), ssn = dbo.fun_Encrypt(ssn)
       where current of UpdateCursor
       FETCH NEXT FROM UpdateCursor INTO @fname, @lname,@ssn
CLOSE UpdateCursor

1. What is the best way to do this without using cursors? > 50 million rows!

2. How do I minimize or manage concurrency?

3. Would it be advantageous to create a "status column" which would be also be updated after a row has been adjusted?

2. Anyone has suggestions on how to implement this task on a 24x7 production database?
3 Solutions
One suggestion would be to do it in blocks within a loop :

declare @i integer
set @i = 0
while @i < 5000
begin transaction
 Update tblSubscribers set fname = dbo.fun_Encrypt(fname), lname = dbo.fun_Encrypt(lname), ssn = dbo.fun_Encrypt(ssn)
 where Acount_id between @i*10000 AND (1+@i)*10000
Disclaimer: this is not a carefully thought out suggestion, but I've only got a couple of minutes...
You could consider
1. Adding a bit column to indicate if a row has been encrypted (as you suggested yourself)
2. Modify your applications to handle both encrypted and unencrypted values
3. Start encrypting the new records
4. Modify your cursor to a) only process 10000 records at a time, b) only process records where the bit flag is 0, c) update the bit flag to 1 when it updates the other fields.
5. Rerun the cursor repeatedly during low activity periods until all data is encrypted
Scott PletcherSenior DBACommented:
I too don't have long.

Agree with jdlambert1.  That seems the best process available given the situation.

Possible additional issues:

You need to protect the encrypt/decrypt functions stored on the db, and SQL Server encyption is *not* safe since it's been broken, as is widely known.  So, remember that any 'sa' or 'db_owner' could possibly compromise your security completely be getting a copy of the encrypt/decrypt functions.  Also, you may want to pick much more obscure names for those functions, perhaps even store them in the master db (which should have fewer users/access than the app db).

Where will you do the decrypt of columns?  If you do it on the SQL Server, then the plain text is vulnerable going to the app.  If you do it in the app, then you'll have additional things to keep in sync between the app and the db, because the db will need decrypt code also.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now