Data Removal Real?

I have a situation where my client wants to ensure that data which is removed from a SQL Server database is truly removed. That is, if I delete a record, or delete data from a row, can I be sure that the data is truly gone and that it's not just the internal B-Tree pointers that were removed? Can I trust that someone with access to the .mdb file could not perform some binary pattern matching on the file and find the data that I assumed was deleted (regardless of how difficult that task would be)? I'm using SQL Server 2000. Thanks in advance.

The data is deleted.

However, depending upon your Recovery model, it may be 'hanging' around in the TRANSACTION LOG file for awhile, and anyone with access (and permissions) to the TRANSACTION LOG file, and one of several third party tools, may be able to inspect the deleted data.

(It's best, before they show up with the subpoenas, to backup and restore - and delete any saved backups and transaction logs, and the use a DOD approved sector wiping utility and burn the tapes... But of course, that is not a good thing to be caught having done...)

|||

Ok, thanks. So, I should look for a recovery model that doesn't use logging? This implies no recoverable transactions, but that would be ok.

(Side note, I'm not worried about subpoenas. Foreign governments are the perceived risk.)

|||

Simple recovery model has the shortest logging latency.

However, that means no data recovery since the last FULL BACKUP.

It seems that you may need to better secure the server ...

|||

I wasn’t sure about this one, so I asked the experts and this is what they replied back:

all the deletes are only logical … The only way to get rid of the data for sure is to DTS to a new (clean) database.

They mentioned some SQL Server 2005 SP2 feature for Common Criteria (CC) that is used to clear the information on the pages, but again, they mentioned it is only a logical deletion.

Arnie, it may be possible that you were talking about this feature, can you please confirm? Unfortunately I have limited knowledge on this particular area myself.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Data marshalling from Sql server into business objects

Hi there,
I am having real problems finding the optimum way of retrieving data
from a Sql server and marshalling that into my c# business objects. I
have a fairly complex object hierarchy which I want to populate all in
a single shot (rather than relying on lazy loading which would cause
many roundtrips when the event's subordinates are enumerated). In my
object model...
an event has 0...n comments
a comment has 0...n visibility groups
an event has 1...n links
a link has 0...n link descriptors
an event has 1...n messages
In the relational world, this necessitates seven tables within my
schema :
tb_events
tb_events_comments
tb_events_comments_groups
tb_groups (pre-fetched)
tb_events_links
tb_links
tb_events_messages
tb_messages (pre-fetched)
The query I am using to retrieve this lot uses temporary tables and
goes like this...
/* -- START -- */
/* select all the events into a temporary table
this first statement can be modified if any queries are required
against
any of the subordinate
*/
select *
into #events
from tb_events
where is_deleted = 0
/* now select all the comments into a temporary table which
are subordinates of the events identified in query #1
*/
select *
into #events_comments
from tb_events_comments
where event_uid in (select uid from #events)
/* and the same for all the links
*/
select *
into #events_links
FROM tb_events_links
WHERE event_uid in (select uid from #events)
/* and now all the output queries which actually perform the retrieval
*/
select * from #events
select * from #events_comments
select * from tb_events_comments_groups where comment_uid in (select
uid from #events_comments)
select * from #events_links where event_uid in (select uid from
#events)
select * from tb_links where uid in (select link_uid from
#events_links)
select * from tb_link_descriptors where link_uid in (select link_uid
from #events_links)
select * from tb_events_messages where event_uid in (select uid from
#events)
/* and tidy up all the temp tables
*/
drop table #events
drop table #events_comments
drop table #events_links
/* -- END -- */
My question really is : is the above a valid way of retreiving all the
data required for population of the object hierarchy or are there any
nicer / faster / more elegant solutions? I've run it through Profiler
as both an ad-hoc script and a stored procedure (the sp is naturally
faster) and it doesn't seem to impose an unreasonable burden on the db.
The only other way I can think of is using a proxy object (EventProxy)
which contains "top line" details and if the full event is required
then it can be separately retrieved.
Surely there's a nicer way of doing it than this!?
Hope you can help,
MattI think your approach is suitable not knowing anything about your
situation. Volume is the biggest concern I would have. Are you pulling
one event, 10 events, all events? How many events in the table when you
query it? What kind of indexes? etc, etc.
Personally, I would not use the temp tables and would query the tables
directly. I might consider moving some of the subqueries to Table-value
functions for clarity and reuse.
Your biggest pain is going to be in your DAC code, where you have to
rebuild the object by looping through all these rows.
However, if you really want to be clever, you can make this one massive
query complete with all the joins, return it as XML, and serialize it
right into your object. Wouldn't that be ? Not easy at all, but
very .
--
700cb Development, Inc.
http://www.700cb.net
.NET utilities, developer tools,
and enterprise solutions
"Matt" <matt.jones@.studio10.co.uk> wrote in news:1132060815.255026.14610
@.g14g2000cwa.googlegroups.com:

> Hi there,
> I am having real problems finding the optimum way of retrieving data
> from a Sql server and marshalling that into my c# business objects. I
> have a fairly complex object hierarchy which I want to populate all in
> a single shot (rather than relying on lazy loading which would cause
> many roundtrips when the event's subordinates are enumerated). In my
> object model...
> an event has 0...n comments
> a comment has 0...n visibility groups
> an event has 1...n links
> a link has 0...n link descriptors
> an event has 1...n messages
> In the relational world, this necessitates seven tables within my
> schema :
> tb_events
> tb_events_comments
> tb_events_comments_groups
> tb_groups (pre-fetched)
> tb_events_links
> tb_links
> tb_events_messages
> tb_messages (pre-fetched)
> The query I am using to retrieve this lot uses temporary tables and
> goes like this...
> /* -- START -- */
> /* select all the events into a temporary table
> this first statement can be modified if any queries are required
> against
> any of the subordinate
> */
> select *
> into #events
> from tb_events
> where is_deleted = 0
> /* now select all the comments into a temporary table which
> are subordinates of the events identified in query #1
> */
> select *
> into #events_comments
> from tb_events_comments
> where event_uid in (select uid from #events)
> /* and the same for all the links
> */
> select *
> into #events_links
> FROM tb_events_links
> WHERE event_uid in (select uid from #events)
> /* and now all the output queries which actually perform the retrieval
> */
> select * from #events
> select * from #events_comments
> select * from tb_events_comments_groups where comment_uid in (select
> uid from #events_comments)
> select * from #events_links where event_uid in (select uid from
> #events)
> select * from tb_links where uid in (select link_uid from
> #events_links)
> select * from tb_link_descriptors where link_uid in (select link_uid
> from #events_links)
> select * from tb_events_messages where event_uid in (select uid from
> #events)
> /* and tidy up all the temp tables
> */
> drop table #events
> drop table #events_comments
> drop table #events_links
> /* -- END -- */
> My question really is : is the above a valid way of retreiving all the
> data required for population of the object hierarchy or are there any
> nicer / faster / more elegant solutions? I've run it through Profiler
> as both an ad-hoc script and a stored procedure (the sp is naturally
> faster) and it doesn't seem to impose an unreasonable burden on the db.
> The only other way I can think of is using a proxy object (EventProxy)
> which contains "top line" details and if the full event is required
> then it can be separately retrieved.
> Surely there's a nicer way of doing it than this!?
> Hope you can help,
> Matt
>