October 14, 2010 at 11:43 am
Hello,
We are working on a middleware with backend as SQL database. We have a requirement to get data from another SQL databse on frequent basis (say 2-5 mins), load into middleware database and then forward some or more details to other 3rd party systems. The overall processing need to happen in real time.
The source database need to transfer only those records which are not yet sent and middleware dataabase need to intimate/update the source databse that the transfer has been done. We also need to have logging and intimation to source system for the errors.
To get data from other database we had thought of few approaches
- SOAP method, to invoke a web method from web service.
- Creation of flat files and post on some ftp to process.
- Sharing of databse tables. (this might be risky).
Does anyone have any such requirment worked on earlier? Please provide any help if anyone has.
Vishal
October 14, 2010 at 7:18 pm
Have you explored the option of using Replication? It's set up to do a lot of what you just outlined. I'd try that as a first choice.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 22, 2010 at 12:43 pm
Thanks for the reply. Actually we need to receive portion of data from source database. The source database will be sending only new records received and not yet transfered to middleware database. Will replication allow this? Also table in middleware database can have more columns that table in source database.
Can sharing of table/stored procedure be done?
What are security threats?
How exceptions can be transfered back?
Thank You
Vishal
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply