November 21, 2008 at 11:36 am
Comments posted to this topic are about the item Undocumented Routines: Forbidden fruit.
Best wishes,
Phil Factor
November 21, 2008 at 12:39 pm
Once again a statement that makes 100% sense.
"Let the first one without sin throw the first rock."
Few rocks will be thrown:Whistling:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
November 22, 2008 at 12:41 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2008 at 3:12 am
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
November 22, 2008 at 7:17 am
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
Consultant - Straight Path Solutions
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
November 22, 2008 at 11:36 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2008 at 3:34 pm
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!November 25, 2008 at 12:50 am
Dear......
i am found of SQL study. you help me what can i do it?
Thanks
M.Faisal.Lodhi
November 25, 2008 at 8:59 am
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
November 25, 2008 at 9:09 am
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.
November 25, 2008 at 9:13 am
mfaisal (11/25/2008)
Dear......i am found of SQL study. you help me what can i do it?
Please post unrelated questions in a new thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2009 at 6:36 pm
Heh... "Don't use undocumented features"... as opposed to what? Using just the documented ones? You mean like the wonderful and heavily documented sp_MakeWebTask whose required privs changed overnight and without warning when they shifted for 2k sp3a to sp4? Or how about when the heavily documented SUM() OVER is finally updated to meet ANSI standards so we can actually use it to do a running total?
That little privs change on sp_MakeWebTask killed a lot of people's code because it now requires "SA" privs and a lot of smart DBA's won't allow users or procs to have "SA" privs. And I can't wait to see all the code that breaks when they finally fix SUM() OVER and all the poor buggers currently using it in their code have to go back and fix things.
And then, there are the undocumented features that have already been discussed. I'll also add in the wonderfull xp_DirTree (3 operand version) and a couple of others that seem to be cast in stone. They've been there for at least 3 versions of SQL Server... why Microsoft doesn't document them, one can only guess.
To me, saying "Don't use undocumented features" is like saying "Write only ANSI code". There's a lot of things that just wouldn't get done and SQL Server would truly be "just a place to store data"... or worse... it would look and work (I hate it) like Oracle. 😛
And, all you Oracle guys who are going to get all up in arms about what I just said about Oracle... lemme ask you... how do YOU return a result set from a stored procedure to a GUI. Ok... you can sit back down. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2009 at 10:32 pm
Jeff Moden (1/31/2009)
...work (I hate it)...
Heh. I wouldn't spread that around the office, Jeff. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 1, 2009 at 6:30 am
Jeff Moden (1/31/2009)
That little privs change on sp_MakeWebTask killed a lot of people's code because it now requires "SA" privs and a lot of smart DBA's won't allow users or procs to have "SA" privs. And I can't wait to see all the code that breaks when they finally fix SUM() OVER and all the poor buggers currently using it in their code have to go back and fix things.
If there are bugs in the system, I'm glad they (ms) fix it.
Maybe the fix isn't what we would like it to be, but if it closes weak parts of the implementations, they should post a remark for this behaviour, so we can prepare for the impact.
And because we all have a nice system of Test -> QA -> EnduserTest -> Prod 😀 , this kind of problems should arrise before it shuts down any production revenues.
What is important, is that you know you are using an undocumented whatever and that you have to check its availability / operational status with every sp, hotfix, new realease, ... and be prepared to pay the price if they throw it away.
So first try to figure out the proper way before going to any darker side. 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
February 1, 2009 at 8:02 am
Yeah. spMakeWebTask. I love using this for dishing out excel-based reports via email. I still use it, but with a delegation process for doing daily reports (I'm too idle to use Service Broker) It now, sadly has a sentence of deprecation on it which makes me slightly irritated.
Robyn and I describe a delegation system on http://www.simple-talk.com/sql/t-sql-programming/process-delegation-workbench/
Jeff, there was a security issue designed-into spMakeWebTask. Actually, as well as that it used to kill the server intermittently under heavy use when I first used it in v7 . (They fixed that in 2000).
There is nothing quite so useful as spMakeWebTask for the busy programmer doing lots of reporting. I don't quite buy the idea that SQL Server 2005 Reporting Services (SSRS) is as handy. One day, maybe I can persuade Robyn to do a workbench on it, along with a nifty CLR that does the same job.
Best wishes,
Phil Factor
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply