Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Undocumented Routines: Forbidden fruit. Expand / Collapse
Author
Message
Posted Friday, November 21, 2008 11:36 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 579, Visits: 2,520
Comments posted to this topic are about the item Undocumented Routines: Forbidden fruit.


Best wishes,

Phil Factor
Simple Talk
Post #606755
Posted Friday, November 21, 2008 12:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:47 AM
Points: 7,005, Visits: 8,451
Once again a statement that makes 100% sense.


"Let the first one without sin throw the first rock."
Few rocks will be thrown


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #606792
Posted Saturday, November 22, 2008 12:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
Phil, you say
I include DBCC Log, and its offspring fn_dblog, where there seems to be no documented way of doing perfectly legitimate actions to maintain or audit your own data.


You don't consider triggers a documented way to audit your data? The problem with reading the tran log is that it changes every version and sometimes within versions, and it's not particularly easy to read at the best of times. Look at the companies that have written log reader tools. Not cheap and not many competitive products

Also, now in 2008 there are multiple ways to audit data - SQL Auditing, Change tracking and Change data capture.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #606972
Posted Saturday, November 22, 2008 3:12 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:03 AM
Points: 579, Visits: 2,520
Yes, Some good points, Gail.

It is true that SQL Server 2008 provides some very good auditing facilities, and they're not bad in SQL Server 2005. However, the argument cuts both ways: It is obviously in Microsoft's interests to make that upgrade to 2008 as difficult to resist as possible, and in that light, the conspiracy theorists can argue that, if there was an opportunity to elucidate ways in which the users of prior versions of SQL Server can improve their auditability via a Transaction-log viewer, then Microsoft will sit on their hands. The increase in international rules for compliance are definitely a pressure point for those companies that are otherwise reluctant to upgrade. SQL Server 2008 makes compliance easier.

Whereas I'm an enthusiast of DDL triggers, I don't believe that data triggers are a good choice for auditing. They may be the best pragmatic solution for papering over the cracks in an existing application that cannot be re-engineered to be intrinsically auditable. I wouldn't even want to argue that triggers are a substitute for being able to view transaction logs, as they do nothing to solve the problems of retrospective 'forensic' audit in historical data.

It is true that the structure of the Log is likely to change, but the same is true of the behaviour of other 'implementation-specific', but supported, aspects of SQL Server. sp_MakeWebTask, for example? If the log changes within versions, as you suggest, it cannot be a significant change as the third-party log-rescue tools all work within versions.

The problem with leaving 'log rescue' to third party tools is that they don't fit easily into the commercial model of 'Try-before-buy'. You only need a tool like this in an emergency. Once the emergency is over, you don't want it. That's why SQL log Rescue is free (and up to SQL 2000 only!). It would be much better to make it part of the supported product.

One slightly scarey thought bothers me though: If the structure and processes of the transaction log were fully documented, then would it then be maliciously hacked? We'd then lose some confidence that we currently have that backups constitute 'evidence'.



Best wishes,

Phil Factor
Simple Talk
Post #606986
Posted Saturday, November 22, 2008 7:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 11,264, Visits: 13,021
Excellent points as usual, Phil. I agree that you should not use undocumented "features" in production code, but using them in ad-hoc queries and maintenance is acceptable in my opinion.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #606996
Posted Saturday, November 22, 2008 11:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
Phil Factor (11/22/2008)
One slightly scarey thought bothers me though: If the structure and processes of the transaction log were fully documented, then would it then be maliciously hacked? We'd then lose some confidence that we currently have that backups constitute 'evidence'.


I don't think so, no more than modifying data by taking a hex editor to the data file.

First the files are locked for write by SQL while the service is running, so no changes there. Also there are checksums and other protections on both data pages and log pages to detect corruption. SQL considers someone applying a hex editor to the field as corruption.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #607060
Posted Monday, November 24, 2008 3:34 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 886, Visits: 1,545
Speaking of Undcoumented and the very popular sp_MSForEach...., does anyone know if Micro$oft either has plans to or at least has commented about the implementation of some user allowed alternative to the popular undocumented routines like the 'ForEach' routine?

I'd say that it's clear by now that use of a procedure for automatically performing some operation for each Object In ObjectGroup goes beyound the halls of SQL Server development insde Redmond. I would think something like this would be a great selling/marketing point.


Thoughts?


Kindest Regards,

Just say No to Facebook!
Post #607923
Posted Tuesday, November 25, 2008 12:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 5, 2008 9:35 PM
Points: 1, Visits: 5
Dear......

i am found of SQL study. you help me what can i do it?


Thanks
M.Faisal.Lodhi
Post #608127
Posted Tuesday, November 25, 2008 8:59 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:43 PM
Points: 772, Visits: 1,185
I remember reading someone writing something (PASS-related) about next version of SQL may entertain the idea of
FOREACH

Maybe that will replace the sp_MSForEachXX functions
I almost want to bet Microsoft SQL Server staff use it as well for quick loop


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #608430
Posted Tuesday, November 25, 2008 9:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:46 AM
Points: 33,200, Visits: 15,343
The typical plan from MS is to announce deprecation in a version, say SS2K8, for a feature, continue to support it in the next version, say SS2K11, and then remove it in the following version, SS2K14.

That being said, I think some of this FOREACH functionality will be pushed to Powershell. It's a better fit for that type of language.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #608442
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse