Monitoring a Table for Updates and Inserts
-
Hi all,
I'm after some advice. The application that I work on runs off meta tables, one table holds all the definitions for everyother table in the system. We used this approach to make our product easily customisable (initally done in foxpro now porting to SQL). Currently there is no procedure for releasing table structure changes to our testing team, its just a case of if the developers remember.
I am looking to come up with a solution that will resolve all the problems we are having with releases right now (other than firing all the developers). The main problem is that the developers forget to tell the testing team they have to make a structural change and then the testing team waste 2 days trying to work out whats wrong....
My idea was to run a trace against the meta table that holds all the other table defnitions, then if an update, insert or delete is made record it to another table in a separate database. Then write a sp or pakagae that pulled out all the logged changes and put them into a script. Then when applying it to the testing teams database it would make all the relevent changes.
Thats my idea, but at the minute it aint much more than a theory, I have had a little play and I think the trace will work but I'm not quite sure how to generate the script file.
Two questions really:
Is this a reasonable approach?
Is there any better approach?
Any comments would be greatfully appreciated.
Jon
-
Jon,
If I have understood you correctly, running a trace would not solve the problem. Firstly you will have to keep the trace running all the time the developers are working. This can significantly affect the server performance.
- First thing you need is to have a process in place. A deployment guide should be prepared, before a change is released to testing. Someone should have that responsibility, like the developmet team leader who knows about all the development effort in progress.
- Second problem is that you are moving from FoxPro to SQL Server. So, I think it is safe to assume that you are not using stored procedures, etc. This is one area you should look into seriously. Almost all (could be a subject of heated debate) data related logic and processing should reside on the database (performance related). When stored procedures, views, etc. are moved from development database to the test database, they will not compile if the underlying table structure does not match.
- In SQL Server all the meta data is stored in system tables and you do not have to maintain it separately. If this requires you to change the logic of your entire application, then do not look into this right now, but keep it as something to be thought about in the future.
But right now you need a quick solution even if it is dirty:
- If you are storing meta deta already then you could just compare the data in the two tables at the time of deploying the changes in the test environment, you will know what are the structure changes to be made. Else you could query the system tables directly and find the differences. For reference, start with SysObjects, SysColumns tables and Information_Schema views. If the databases are on different servers, you can make a linked server from the test server to the development server and run the comparison queries. Also, there are several third party tools available for this, I am sure some of the better DBAs would be able to give you some advice.
All in all, I would just fire all the developers
-
Have you considered putting triggers on the table that stores the table definitions to audit the changes?
Kemp
-
Jon,
DBGhost it the answer to your porblem.
With DBGhost you will not only know what changed but it will build an update script for sync'n deployment to testing.
We've used several comparsion type tools in our company from (RedGate http://www.redgate.com, Apex http://www.apexsql.com, and Innovartis http://www.dbghost.com). DBGhost has been by far the best product we've used for keeping development and qa insync.
Don't get me wrong, all three products from the above named are good. They all perform their jobs well, it is just in my humble opinion that Innovartis does it best with the least amount of issue. Plus it will help with maintaining all of your SQL code in SourceSafe, another very good idea because using a source control system allows you to know exactly what has changed and by whom, plus other benifits like rolling back to a previous version if need be.
Good luck to you...
Christopher DeMeyer -
As Kemp said a trigger could work for you once you are in SQL server. In our main application security settings are stored in a table and we want to know when someones authorization group has been changed. So we have a trigger on the auth table that whenever a change is made writes to a log table that shows the old group, new group and who made the change.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply