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

Posted on 2004-08-19
Last Modified: 2008-01-09
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?
Question by:DeMyu
LVL 17

Accepted Solution

BillAn1 earned 168 total points
ID: 11846086
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
LVL 15

Assisted Solution

jdlambert1 earned 166 total points
ID: 11846116
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
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 166 total points
ID: 11846778
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question