January 3, 2008 at 10:21 am
Here's the problem for us... Our Financial DB is going to go through changes that will impact hundreds of our tables. I cannot make the changes, our vendor has to make the changes. We are changing our account structure and the account as structured today is in hundreds of tables. The vendor, has never done this before, will attempt to locate the tables and make the changes to our 2008 data so going forward we will be utilizing the new structure.
My problem is I don't have total confidance in the vendor. I KNOW what tables need to be changed but I cannot make the changes, the vendor has to do the work to maintain warranty, whatever...
My goal is to track the changes one table at a time which I think I can only do by checking to see what tables they are hitting. I will not get a roadmap from the vendor, "we will figure it out". Yikes!
So I'm thinking if I somehow track the work in the log table to the test database I will see what they are hitting and missing and potentially doing wrong with their code.
I have the list of tables from a query I developed that hits SysObjects, SysColumns, and SysTypes.
Do I need a product like Log Explorer 2.0? Something else?
Your thoughts would be welcome to help me monitor the vendor's activity.
January 3, 2008 at 10:25 am
Put the SQL Profiler on and your capture every single piece of code they run select, insert update.
January 3, 2008 at 11:20 am
Perfect, thank you Tracey!
January 5, 2008 at 11:04 am
Take a look at the SQL Compare and SQL Data Compare tools from Redgate. I hope they are making these changes on a test system first. You can use these tools to compare the schema as they make the changes and can then check the data in specific tables as they run any conversions.
Good luck.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2008 at 10:28 am
Or build in auditing triggers (DML or DDL) to keep track of what changes.
January 7, 2008 at 7:25 am
we have a consultant go into our Great Plains servers once in a while, but that is all they hit. anyone who wants to run anything anywhere else needs to give the DBAs a complete list of everything they plan to do and it must be approved by us first.
no way would we let anyone just run something that will affect everything without knowing what it is first.
January 8, 2008 at 12:59 am
Can't help but ask why you want to track the changes they are making...? Do you think you're going to have the time to find/fix errors in their script in the middle of a change window?
In a situation like this where you are liable/responsible for but not able to affect the outcome within the rules you really have to hold the line on a proper change process. The last thing you want is to have to sit there for hours on end while the vendor tries to thrash through a bad change.
Proper approach would be something along the line of:
(1) Full backup of the database.
(2) Run script(s), be sure to grab output/logs from script(s)
(3) Perform post change testing (the vendor does have a test plan?)
At that point you've got two possible branches:
Tests are Fail:
(4) Restore from full database backup.
(5) Close change request as a failed change
(6) Have Vendor try again at next approved change window once they've fixed their script.
Tests are good:
(4) High five vendor & PM.
(5) Close change request as completed.
(6) Have vendor/PM buy you a beer for staying late/after hours.
(7) Retain backup just in case everything goes to hell on Monday.
No matter what, don't fall for the "but if we do this, maybe it will fix..." scenario AKA the weekend killer - change should either pass or fail on first iteration, this is expecially true with "complex" changes where you can spend days trying to hunt down some absolutely stupid problem (been there, done that) while under the gun to get the system back up before the rest of the office comes in in the morning/on Monday.
Joe
January 8, 2008 at 8:23 am
I think I've got hold of a proper process given the circumstances.
This will ALL be done on a test database first. All testing by users will be against a "finished" test database. When I'm convinced all the changes are in order and I've looked through the tables I know need to be worked I'll allow the company to hit the live database. Then we'll test again before we let any transactions go through the system.
"Fix changes midway through a screen", hardly think even I would be that naive...
January 8, 2008 at 8:45 am
Naivete to the side, here's an old saw:
There's never time to do it right the first time, but there's always time to do it over.
January 8, 2008 at 8:50 am
Not when it's your General Ledger... There's time to do it right the first time or find a new job. You do not want to be the person who brought the GL to its knees, forced redoing days of journal entries... It'll always come back to your doorstep as the person who should have asked for more resources/time.
January 8, 2008 at 12:08 pm
Understood and agreed - from where you're sitting. Depending on how good your vendor is, they may fall somewhere between where you sit and what i quoted, and I apologize if you thought that at any time I was referring to you. The quote was intended to be in reference to the vendor's perspective - and I hope that it doesn't apply in this case at all.
January 9, 2008 at 10:08 am
Hi,
Have you ever look into using DDL triggers ?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply