Anmelden
 
HomeLibraryDownloadsSupportWebcasts & EventsCommunityForenBlogs
Ressourcen für IT-Professionals >
> 
> 
Best data transfer technique

Answered Best data transfer technique

  • Montag, 23. April 2012 13:02
     
     
    Anmelden
    0

    I have 2 different servers (One is Oracle and another is SQL Server) from which I need to retrieve the data over the internet and collect to a centralised database in SQL Server. This should be a job like process and which should run in every nights. Which is the best method to achieve this by considering SQL Server as a solution provider.

    Thanks in advance,

    Maju

    MJVP

     

Alle Antworten

  • Montag, 23. April 2012 16:55
     
     
    Anmelden
    0

    Hello Maju,

    for me it worked best by using:

    • SSIS with Attunity Oracle connectors
    • Oracle Client 

    It works smoothly. I can push 5 million data rows on a corparate network in about 30 minutes.

    best regards Christian

     
  • Dienstag, 24. April 2012 06:51
    Moderator
     
     Beantwortet
    Anmelden
    0

    Hi Maju V Poulose,

    Regarding to your description, you can try to download and install the latest MyODBC 3.51 drivers. (Available at http://dev.mysql.com/downloads/connector/odbc/3.51.html)

    And then you can follow the steps as below:
    1. Open the ODBC Data Source Administrator
    2. Start -> Programs -> Administrative Tools -> Data Sources (ODBC)
    3. Click the System DNS tab and press the Add button
    In the new window, choose MySQL ODBC 3.51 Driver and press the Finish button
    2. Test the connection by pressing the Test button. If everything tests correctly, press the Ok button.

    For more information please refer to How to: Retrieve Data from an Oracle Data Source
    http://msdn.microsoft.com/en-us/library/cc627448(v=sql.100).aspx

    Regards, Amber zhang

     
  • Dienstag, 24. April 2012 07:40
     
     
    Anmelden
    0

    Hi All

    Actually I am looking for a best and secure method to transfer data over the internet between 2 different servers.

    Thanks

    Maju

    MJVP

     
  • Dienstag, 24. April 2012 16:57
     
     Vorgeschlagene Antwort
    Anmelden
    0

    Maju,

    Please take a look at BizTalk.  I have not personally used it but it provides secure communications between business servers on the internet utilizing different OSs or DB platforms.  Think of it as BI on steroids.

    Here is a link for you - http://www.microsoft.com/biztalk/en/us/default.aspx.

    Noral

    • Als Antwort vorgeschlagen Noral Kuhlmann Dienstag, 24. April 2012 16:57
    •  
     
  • Mittwoch, 25. April 2012 07:47
     
     
    Anmelden
    0

    Hi Noral, I need to implement the transfer process only using SQL Server. So Biztalk is out of scope in my current requirement even if it is one of a good solution.

    Please see the possible methods below and please tell me which one is more secured and better.

    1. CLR
    2. Service Broker
    3. Linked Server
    4. BCP Command
    5. SSIS-direct connection with source server by exposing
    6. SSIS-FTP task
    7. .Net-File transfer

    Thanks

    Maju

    MJVP

     
  • Mittwoch, 25. April 2012 20:49
     
     
    Anmelden
    0

    SSIS direct connection could be the best in your choice list. But, create multiple SSIS packages like

    Package 1. Pulling the data from oracle to Staging tables

    Package 2. Pulling the data from SQL to Staging tables

    Package 3. Transform the Staging table to Actual tables

    So, that you improve performance, reduce the maintenance  and remove dependency.

    Regards

    Yoganandh N

     
  • Donnerstag, 26. April 2012 07:31
     
     
    Anmelden
    0

    Can anybody tell whether it is possible to work with CLR without exposing the remote server (if server is not in a local network). Please note that I need to fetch data from a remote server and save to my local server.

    Regards

    Maju

    MJVP

     
  • Freitag, 4. Mai 2012 19:17
     
     
    Anmelden
    0

    hi please refer the below link for your problem.

    http://www.oracle.com/technetwork/database/enterprise-edition/index-093639.html

     
  • Freitag, 11. Mai 2012 20:38
    Beantworter
     
     
    Anmelden
    0

    I vote for SSIS as well. SSIS Import / Export Wizard:

    http://www.sqlusa.com/bestpractices/ssis-wizard/

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012