October 6, 2009 at 5:29 pm
Hi,
I am hoping that this forum would lead me to a correct direction. We are currently designing a process in where we have two databases, one for maintenance (Database A) and one for production (Database B). Per databases resides a table named Item, the item table has flag column which indicates if a data is ready to be inserted to the production database (Database B) or it can be by scheduled insert to database B.
Flag column can have values "Ready" and "Scheduled", if it is a scheduled insert then we will refer to its scheduled date which is also one of the Item table column.
I need to create some sort of a listener or something that will check if there are any changes with the Item table and will do the following task I have describe above.
Any suggestions on how to efficiently handle this kind of process is always welcome.
Thanks for your time.
October 6, 2009 at 11:35 pm
I don't understand the underlying methodology that you are proposing. Why stage it in the A db just to move it into B. Couldn't you use some kind of effective dating scheme to prep it all in the prod db and then it becomes effective on the specified date, not additional movement necessary..
CEWII
October 7, 2009 at 2:24 am
Do you really need to move the records "in real time"? Otherwise, either follow Elliott's advice, or schedule a stored proc / SSIS package to periodically move a batch of records.
For a "real time" record move, maybe you'll consider a service broker based solution.
October 7, 2009 at 3:03 am
Not sure what you are trying to achieve here, you have good suggestions in previous posts. if you want data movement to be based on a data change to a column, you can also use a trigger on that table to effect the change. but i would hard about the process you are trying to implement and think about all options.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 7, 2009 at 6:51 pm
Hi All:
Thanks for your prompt response. The current scheme is an existing one before I came into this company and the reason that they this kind of design is they need to clean up some data from A and if it is ready, we transfer it to B. My task is to lookup on a certain table column from A to see if data is ready to be shipped out to B. If trigger is that effective, can you please give me a concrete example to be able to use it on my scenario.
Thank you for your time.
October 7, 2009 at 8:04 pm
What do you want to do about UPDATEs?
Also, I recommend that provide the CREATE TABLE statement along with any existing keys and indexes. Some test data would be helpful, as well. Please see the first link in my signature line below for how to provide both.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2009 at 7:09 pm
Alright I will try create table script with sample data in it. I just want to know about the possible strategies in doing such scenario and I think the script may help.
- There is an existing process that will update that certain column on Item table on Database A. My only concern is on what strategy should I use in order for me to look-up that column on Item table inside Database A and see if there is a "Ready" flag that would let me copy those data into Database B Item table.
October 9, 2009 at 2:31 pm
Two solutions...
1) Application in database A knows when a row is ready to be "moved" to database B, isn't it?... so, let application in database A connect to database B and "move" the row when needed.
2) Replicate affected table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 20, 2009 at 5:32 pm
PaulB-TheOneAndOnly (10/9/2009)
Two solutions...1) Application in database A knows when a row is ready to be "moved" to database B, isn't it?... so, let application in database A connect to database B and "move" the row when needed.
2) Replicate affected table.
Sorry to have replied on this thread late, been busy lately.
Yes you're correct about item number 1, but what about the scheduled data? it is okay for the application in database A connect to database B when the data has an immediate flag, but we have also data with an scheduled flag. What can you suggest in doing some scheduled task for the data to be copied from database A to B?
October 27, 2009 at 12:34 pm
Put an index on the flag column, schedule a job to run every other minute then let scheduled job look for the rows that are ready to move... and move them.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply