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
547 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
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:ScottPletcher
ScottPletcher 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now