Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
570 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 672 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 664 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 664 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

705 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