SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 no logs - howto ?
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

fabianus76
Posting Yak Master

191 Posts

Posted - 11/27/2006 :  15:35:30  Show Profile  Reply with Quote
Hi TG,

It is not because YOU do not need something or do not see the need for it that it might not be necessary for some else.
Anyway, let it be - if there is not other way, then there is none.

Thanks a lot to all for your active support.

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/27/2006 :  15:36:55  Show Profile  Reply with Quote
quote:
Originally posted by fabianus76

Hi X002548,


"who are you trying to hide data chnages from?"

Anyone who could have access to db. Once the changes done I do not want them to be traceable. It's not more complicate than that.

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !



It sounds to me like you do not actually know what a SQL Server transaction log is.

Database users do no have access to the transaction logs.

Transaction logs are used internally by SQL Server to maintain the database in a consistant state. It is not an option to not have a transaction log.

Backups of transaction logs can be used by a database administrator to recover a database to a point in time, after restoring from a database backup. You are not required to make these, but they are a key security item to protect a production database.










CODO ERGO SUM
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/27/2006 :  15:41:52  Show Profile  Reply with Quote
quote:
Originally posted by fabianus76

Hi TG,

It is not because YOU do not need something or do not see the need for it that it might not be necessary for some else.



Well that's why we are asking....knowledge is a 2 way street...we would like to understand why yo need it....because this is a first time thing...I don't think anyone has ever heard of such a thing before...

Is there some reason you can't share why you need this?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/27/2006 :  15:42:39  Show Profile  Reply with Quote
What's wrong with using SIMPLE Recovery model?

Some transient changes are stored, but they will be overwritten within some short-ish period of time. Bit hard to determine what has been "overwritten" and what not, but it starts to make it very hard to piece together for the wanna-be-spy.

But as overs have implied, you loose all the benefits of being able to recover from some disaster - whether an accidental human-error "DELETE FROM TheWrongTable" or just a plain operating system failure on a RAID5 Write - e.g. Windows goes to write multiple blocks to the RAID, one drive in the RAID fails half way through, the O/S, instead of completing the Write as would be obvious to 99.999% of the population! just capitulates, and SQL Server on the next read says "This page is torn". Game over.

However, at that point there is NOTHING wrong with the log files, so [if you are using a Full Recovery Model] you can just restore the database back to itself and lose NOTHING.

Somewhat posher is to have a stand-by server where all that logging information (that you are not keen to store!) is shipped to the standby server(s) so that if the first server goes Tits-Up you can just fail-over to the stand-by server.

Kristen
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 11/27/2006 :  15:54:34  Show Profile  Reply with Quote
Fabian,

If someone has access to the transaction logs, you are royally screwed.

They have already breached your primary security defence....

DavidM

Production is just another testing cycle
Go to Top of Page

fabianus76
Posting Yak Master

191 Posts

Posted - 11/27/2006 :  15:59:17  Show Profile  Reply with Quote
Hi Kristen,

let me be sure that I understand well : "Some transient changes are stored, but they will be overwritten within some short-ish period of time."

I thought that the logs in SIMPLE recovery model would be kept till next backup. Isn't that the case?

Brett, as I told you, there might be cases where datachanges shouldn't be traceable for anybody. I agree that this is not everyday use, but my client asks me to do so.

Michael, that's how I understood logs. The problem is not about db-users.

Regards,
Fabian



my favorit hoster is ASPnix : www.aspnix.com !
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/27/2006 :  15:59:24  Show Profile  Reply with Quote
"If someone has access to the transaction logs, you are royally screwed"

I reckon if I can get that far I can probably put a sniffer on the packets, or a trace on the Profiler, and see all the transactions anyway ... log or no log.

The design is going to have to dis-allow all those sorts of wanna-be spies too I'm afraid.

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/27/2006 :  16:27:01  Show Profile  Reply with Quote
quote:
Originally posted by fabianus76
...Michael, that's how I understood logs. The problem is not about db-users...

Well, that is certainly misleading, since that is exactly what you said you were trying to protect yourself from when you were asked that specifically:

"who are you trying to hide data chnages from?"
Anyone who could have access to db.





CODO ERGO SUM
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 11/27/2006 :  16:28:08  Show Profile  Reply with Quote
Step 1) Make sure the transaction logs are stored in a secure directory.
Step 2) Do not allow your application to run as dbo, let alone sa.
Go to Top of Page

fabianus76
Posting Yak Master

191 Posts

Posted - 11/27/2006 :  16:33:07  Show Profile  Reply with Quote
yes, Michael, wasn't clear.

my favorit hoster is ASPnix : www.aspnix.com !
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/27/2006 :  16:34:48  Show Profile  Reply with Quote
"I thought that the logs in SIMPLE recovery model would be kept till next backup. Isn't that the case?"

No, in that scenario they are only stored until the next checkpoint. But a vast single transaction will not get fully overwritten until the next vast transaction.

You could Shrink the TLog file, but that is nuts. It will move all the carefully re-optimised indexes to the beginning of the database, re-fragmenting them, and cause the TLog file to immediately need extending, fragmenting it worse than before (over time). The idea is to allow files to grow to an equilibrium size so that they are as contiguous as possible.

Kristen
Go to Top of Page

fabianus76
Posting Yak Master

191 Posts

Posted - 11/27/2006 :  16:46:26  Show Profile  Reply with Quote
Hi Kristen,

thanks for your feedback. I understand the problem about shrinking the log-file.
But what is a checkpoint?

Thanks,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 11/27/2006 :  22:22:33  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
From BOL:

quote:
Checkpoints minimize the portion of the log that must be processed during a full recovery of a database. Checkpoints flush dirty data and log pages from the buffer cache of the current database, minimizing the number of modifications that have to be rolled forward during a recovery. SQL Server 2000 always generates automatic checkpoints. The interval between automatic checkpoints is based on the number of records in the log, not time.


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

fabianus76
Posting Yak Master

191 Posts

Posted - 11/28/2006 :  07:40:50  Show Profile  Reply with Quote
thanks to you harsh

my favorit hoster is ASPnix : www.aspnix.com !
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 11/28/2006 :  08:27:12  Show Profile  Visit mr_mist's Homepage  Reply with Quote
People shouldn't be able to read your transaction log files anyway. If they have access to the file system that holds the log and database files half your battle is lost.

Lock down the file system with an appropriate security model at the windows layer.

-------
Moo. :)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/28/2006 :  09:07:46  Show Profile  Reply with Quote
quote:
Originally posted by fabianus76

Brett, as I told you, there might be cases where datachanges shouldn't be traceable for anybody. I agree that this is not everyday use, but my client asks me to do so.



Bu that's not a reason.....what are those cases?

Does your Clients last name end in a vowel?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000