View Poll Results: Push or Pull data from one system to another?

Voters
4. You may not vote on this poll
  • Push

    2 50.00%
  • Pull

    2 50.00%
  • Depends on the situation. (Please explain below.)

    0 0%
Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142

    Unanswered: Are you a Puller, or a Pusher?

    We have several database types here, mainly DB2, Oracle, and MS SQL. When transmitting data between these systems, I'm of the opinion that it's the destination system's responsibility to "Pull" the data from the source, rather than for the source system to "Push" the data to another destination.

    In my experience, it's a lot easier for a system to pull data than push it to another non-native system. It's easier, I think, to bring in raw data to a generic table and then parse it out to it's final destination within your own native DBMS than trying to speak another DBMS' language. Despite that, I am frequently asked for solutions for "Pushing" the data from SQL to someplace else.

    Any thoughts on this?

    Thanks for your time.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I push.
    The receiving db should have a standard import format, be that an actual staging table or merely a flatfile or xml specification.
    If you consider a situation where the receiving db may collect data from many different sending dbs, then it becomes an administrative hassle for the receiving db (and its administrators) to accommodate all the changes, enhancements, and upgrades that may be applied to the source databases. If a source database changes, then it should be the responsibility of its administrators to adjust their end of the output logic to maintain communication. In a pull system, all the onus is on the destination db.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    David,
    I completely agree with you. I think the only time it should be a push is in the case of replication. The recieving database should be the one to pull data, due to some of the following:
    when do they want it, how often, what format do they want the data, do they need to make changes, such as, adding additional columns, removing columns, redefining where criteria.

    Blindman,
    I'm not sure how it becomes an administrative headache for the recieving DB, as it would be there decision whether they need to pick up changes/enhancements that were made to the sending DB. I would think it more of an administrative hassle with the push method as my thought is if you want the data from my DB, come and get it and I do not have to be involved after initial discussion and possibly some help with clarifying some of the data. But, then again, this is probably due to the way I have worked most of my career, supporting OLTP applications for the most part.

    Dave

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you have a db, such as a datawarehouse for instance, that consolidates data from ten other applications, across different servers/platforms/networks...you are going to make it the responsibility of the DW to know all the operational details of those ten applications? To deal with schema changes that break the process? To handle migrations to new hardware environments?
    Good luck with that...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    well, in the case of a DW support, the source system usually exposes a view which structure is supported by that system's team. They will also provide sec credentials to access the view. If there are any changes in their app, they will be responsible to retain the format that your "pull" process expects.

    Another approach I agree with is "everybody, - PUSH!!!" But to differ with blindman, everybody pushes to an agreed upon format or set of formats, so that the "pushed" data is standardized per format/structure/location. This way the daya is always available and accessible.

    The problem with the second approach is actually security and resource availability related. Security will have to be maintained not only within each system, but also on the file system level where the data is pushed to. As per resource availability, - an app developer whose data was decided to be consumed elsewhere, now is tasked with writing and maintaining an extract process, along with continuing being responsible for development of his/her app. Yikes
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The concept of pulling from a source view (or staging table) is acceptable, though slightly more complex than a direct push.
    In such a scenario, the source db is essentially "pushing" the data, but into a local datasource for pickup rather than a remote datasource.
    The advantage (especially when using staging tables in an OLTP environment) is that the "push" can be continuous and in real time, and continues regardless of the availability of the connection between the dbs. If the connection goes down, the push process continues uninterupted until the connection is brought back up again.
    The disadvantages are that the source db may get littered with multiple staging tables for whatever target dbs it supplies (I refer to them as "ambassador" tables), and the remote db must be programmed to flag and remove all the succesfully processed data once it is done accessing its ambassadors.
    I still consider that a push scenario though, because all the business logic required to create the dataset remains the responsibility of the source database.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •