April 4, 2012 at 10:42 pm
Okay. Just 2 more cents here, if you (and no one else at the company) can not change the database, then it really should be incumbent on the third party company that maintains the database to make the necessary changes to audit the data changes you have been asked to do. Just makes sense to me.
April 4, 2012 at 10:52 pm
It should be, I agree with you. I really don't want to be rude, but we need to leave the political aspects of this out and focus on a technical solution if one exists. That is really the only option available to me. Please just take my word on that. I don't want to be arguing that forever here. I appreciate the suggestions that people have made thus far. If anyone has others, I'll appreciate those, too. I think enough's been said on this topic. I'll write back either to let you know the minute one of the suggestions made or to be made here works, if I find another solution, or if I decide to abandon the effort. Thanks everyone so much for the help. 😉
April 5, 2012 at 1:22 am
peter 82125 (4/4/2012)
It should be, I agree with you. I really don't want to be rude, but we need to leave the political aspects of this out and focus on a technical solution if one exists. That is really the only option available to me. Please just take my word on that.
Honestly, we do. The problem is we're technical solution experts and not necessarily political ones. Bear with us as we try to do our best to try to find the technical solution for you. Your frustration is not out of our perview, just out of our available information to help because we don't know the exact political frustration and thus can't offer advice on that.
If you can't adjust the database, adjust the server. You can, depending on the connection type, enforce an N-Tier into the call. You just need to find out how the front end talks to the back end. If you can do that, you can adjust the driver and enforce auditing.
Other options are intrusive to the database design because SQL Server doesn't tend to mix server and database level calls. One option, if available to you from a design standpoint, is to muck with the vendor call, but it will take a tremendous amount of work on your part. You can design a 'middle tier' database that will have the name as the old vendor database, and use passthrough views to the original database. What this will allow you to do is apply triggers to the views, giving you more control without actually adjusting the original database.
It is a lot more effort than it is worth unless you've been bound and tortured in a dungeon, but it's an option.
Btw, welcome to the forums Peter. All the folks who chimed in so far are will go out of their way to help you if they can figure out how. They've saved my patootie a time or two so far too. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 5, 2012 at 1:57 am
couple of things and sorry if you cant do this either but you only detail no changes to the current databases.
could you not create a secondary version of the DB, then read the data out of the primary db into a staging schema in the secondary db, then compare the staging area with the seondary and do what you need to do (email, log etc) and then update the secondary for the next change
if possible would have to look at what tables you can limit on the read using existing datetime/timestamp columns so your not reading the whole table every time you do a compare.
this would get around your limitation of not changing anything in the live DB as its all done in the secondary DB
or
does it need to be real time or can you aford to stack up changes for a few hours or a day?
if so could you not just restore a the previous days full backup (if you can compare on a daily basis) or use differential backups as well, and use a tool like Red-Gates SQL Data Compare to see the differences between the two DB's?
April 5, 2012 at 5:20 am
I'd probably go with using extended events to audit the changes, but I can see how that might be difficult to see what's changed when, etc.
So, let's think outside the box a little bit...
Let's assume we don't need this done in real time. What if we took a backup each night, restored it to another location and then ran a compare on the data between Yesterday and today. Then you see what's changed. If you needed it closer to real time, you could work off a restore process done off the logs every three or four hours... it'd be a royal pain the bottom, but possible.
Or... Capture all DML through a trace (yeah, I want to use extended events for everything too, but bear with me) and then use the replay mechanism to update a second copy of the database where you have put CDC or some other mechanism in place for auditing changes?
The fact is, political or not, you're being asked to do things that may not be physically possible. I've had those kinds of arguments with management. They're stupid and never any fun. Last time I had one, I pulled my phone out and handed it to a manager. He said, "What's this for." I said, "If we're going with your approach instead of mine, you're on call now, not me." It actually worked. Funny how people don't want to be woken up at 3AM because of their own bad decisions. Not sure that will help you in this case.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 5, 2012 at 5:21 am
anthony.green (4/5/2012)
couple of things and sorry if you cant do this either but you only detail no changes to the current databases.could you not create a secondary version of the DB, then read the data out of the primary db into a staging schema in the secondary db, then compare the staging area with the seondary and do what you need to do (email, log etc) and then update the secondary for the next change
if possible would have to look at what tables you can limit on the read using existing datetime/timestamp columns so your not reading the whole table every time you do a compare.
this would get around your limitation of not changing anything in the live DB as its all done in the secondary DB
or
does it need to be real time or can you aford to stack up changes for a few hours or a day?
if so could you not just restore a the previous days full backup (if you can compare on a daily basis) or use differential backups as well, and use a tool like Red-Gates SQL Data Compare to see the differences between the two DB's?
And this is what I get for not reading all the responses. I just replicated yours... Very sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 5, 2012 at 6:00 am
Ninja's_RGR'us (4/4/2012)
That handles deletions, but it can be adapted for anything else.I've never tested it so I can't garantee how good it works.
http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/
Jeff raised that last time. Yes, it's possible. However deletes are the easiest operations to read from the log. Updates and inserts do not always have a log entry per index per row in the table. Could be more, could be less. Inserts could be logged a page at a time, requiring that the contents of the log records be cracked manually to get the key columns. etc, etc, etc
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
April 5, 2012 at 6:23 am
GilaMonster (4/4/2012)
peter 82125 (4/4/2012)
Thanks for the reply. CDC changes the database by adding tables. That's not an option on this project.Why not? What about change tracking? SQLAudit?
We had this discussion with someone else a week or so ago, trying to track changes without any changes made to DB/server is like working with both hands tied.
OK. ApexSQL. If anyone knows others, I'm still interested in hearing about them. Thanks.
The other one is Luminata or something like that, if they're still in business.
It'll require that the DB is in full recovery and that the log be read before any backup, and it may hinder performance.
Lumigent is the other 3P tool you're trying to think of. The company is apparently called "Beyond Trust" now, and the product name has changed as well.
Details here: http://www.beyondtrust.com/Products/PowerBroker-Databases/
The thing to keep in mind for both of these, or any other passive-audit solution, is that the databases to be audited MUST be in Full recovery model. If they aren't already, that also will break the "no changes to the database" rule here.
If they can't be in Full recovery, but need to be in Simple or Bulk-Logged, then there is no audit solution available, and you'll have to build something in-house. Log parsing isn't the simplest thing in the world, but a team of good devs should be able to build something for you in maybe a year or so, at a rough estimate.
Another option that doesn't require any changes to the database but does require changes at the server level is server-side tracing. It's not as useful, since you'll get commands instead of values, but "Update dbo.MyTable set MyColumn = 'X'" can be parsed for the column and value. That won't require any changes to the database, not even changes to recovery model. Pain to deal with, but it could be made to work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 5, 2012 at 6:57 am
peter 82125 (4/4/2012)
Change tracking? SQL Audit? Extended events? Don't these things need to be enabled and therefore require a change to the database? Hopefully I'm wrong, but what I've seen from looking them up is that they will require a change. The solution has to be read only.
Extended Event is not a change to the database it is a change to the server. You create an extended events session at the server level and then can add a predicate (where clause) to limit it to the specific database. Changes to the database won't affect the extended events session, unless the database is dropped and re-created, then you'd get a new database id so you'd need to change the predicate on the extended events session.
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
April 5, 2012 at 8:41 am
Peter, good luck and hope this doesn't drive you crazy.
I'd prefer the backup/restore process myself since it gives me a gross overview, and limits the chances I miss something in a trace, but that's me.
I'd be curious to know what you do. I suspect any of the ideas, outside of a third party tool, would be cumbersome to manage. The third party might be an issue in keeping logs around if your auditors want them for any length of time.
April 5, 2012 at 4:14 pm
Hello, everyone. I did promise to get back to you all when I had a resolution. This project was cancelled. Sorry about the frustration and thank you again everyone so much for the help. I did learn a lot from your suggestions.
April 5, 2012 at 4:25 pm
peter 82125 (4/5/2012)
Hello, everyone. I did promise to get back to you all when I had a resolution. This project was cancelled. Sorry about the frustration and thank you again everyone so much for the help. I did learn a lot from your suggestions.
It sounds you are probably relieved about that:cool:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2012 at 4:28 pm
...to put it mildly, yes 😉
April 5, 2012 at 10:33 pm
No apology necessary. It was an interesting discussion and I learned a few things myself.
Glad you don't have to hassle with what sounded like a fairly aggravating situation.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply