Alternatives to Triggers and SQL Agent Jobs
-
Currently, we are running SQL Server 2005 compatibility mode 80.
We have two databases on this server, one is off limits as far as modification since it belongs to a 3rd party vendor, and the other is our creation.
We need to gather information from the 3rd party DB and store it in ours based upon whether or not a change was made on a specific table.
We cannot add triggers to the 3rd party DB and my idea for using a SQL Agent Job to pull change data into our DB on a regular interval was rejected.
I've been instructed to think outside the box. :crazy:
I'm looking for some consensus on what other reliable alternatives there may be besides the option I presented with the constraints we are facing.
Thanks for the advice.
Keith Wiggans
-
i would do it basically the same as you were thinking;
a job running periodically that compares my local table of the last time i looked at the data.
your local table can have additional columns added, like CreationDate, UpdatedDate, MarkedAsDeletedDate, etc.
Depending on your biz process, you'd simply handle inserts one way, and updates another, deletions a third way.
without a trigger, which you can't add, there is no other ,cheap, effective way. Automating a third party log reader's log results, maybe? much more difficult to implement, not to mention the cost of the software.
Someone who is averse to SQL Agent's probably just not familiar with it; how else do you compare one thing to another on a regular basis without creating a job and scheduling it?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
OK I just completed a proof of concept. it's kind of what you want;
Check your third party license. if they allow you to use replication for disaster recovery, you are good to go.
if you use MERGE Replication, it adds a single column rowguid to each table replicated.
I did the following:
created a Publisher for merge replication for a single table from one db. the table got a rowguid column.
created a subscriber in another database(same server,but that's no biggie)
after setting it up and PULLing the data once with two rows in my source table, i added a trigger on my Destination table. just a simple audit trigger for INSERT/UPDATE/DELETE.
then i added a few more rows on the original table, then PULLEd the subscription again.
the data came over, and the trigger fired as well.,
since this adds a column to the Source Table, unless it's prevented by the third party contract, you could use this technique to fire a trigger on a copy of the data.
However, Replication uses the SQL Agent to determine when to schedule/run the push/pull of the subscription, which someone had already had a knee jerk "lets not do that" reaction.
i suppose you could use the Windows Scheduled Tasks instead of the SQL Agent, but that's introducing another chain in the link.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Great test. I'm flattered that you took the time to check this out. It's just the kind of information I need to either push the replication concept or show them that our alternatives in this case are very limited.
Let's see what comes of it. Hopefully I'll have some news to respond with in the future.
Cheers.
Keith Wiggans
-
Think outside the box - I would rather say, get a bigger box 🙂
Here's an idea - download Redgates SQL Compare and SQL Data Compare. Use SQL Compare to generate a snapshot of the database on a schedule. Once you have that, you can compare the snapshot to the current system and see all of the changes made.
To automate this you'll need the professional edition (I think that's the one).
If you need to perform data comparisons - use SQL Data Compare.
You'll find this will pay for itself within a very short amount of time.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply