Solved

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

Posted on 2004-08-19
5
562 Views
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

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



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?
0
Comment
Question by:DeMyu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 17

Accepted Solution

by:
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
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
commit
end
0
 
LVL 15

Assisted Solution

by:jdlambert1
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
0
 
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.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

738 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