Post reply

How can I improve this flow of data

  • July 31, 2008 at 5:11 am

    #99205

    I’m trying to seek advice about how best to improve this dataflow as currently it seems a bit convoluted.

    Box 1: Oracle 10G (Corporate System)

    Box 2: SQL Server 2000 on Active Directory (Utility Server)

    Box 3: SQL Server 2000 in DMZ, not on Active Directory (Used for Displaying Reporting Services reports over the internet.)

    Each evening this happens.

    1.A fairly simple DTS fires on Box 2, which pulls data from the Oracle Box into tables on Box 2. (Approx 5 Mil rows, so it takes a while!)

    2.Then a Replication Snapshot is taken on Box 2 (of the data we’ve just lifted from Box 1).

    3.That subscription is the pushed up to Box 3, into a (Staging Database).

    4.Then the Live Report DB (also on Box 3) is cleared and then loaded with the data from the Staging DB

    The reason we have the Box 2 is because Box 3 (the live box) can’t directly see Box 1 (the oracle box) due Box 3’s location in the DMZ.

    Secondly, we have the 2 DB’s on live to try to minimise the downtime of the live instance being unavailable.

    I can’t help but think there must be a better way to do this. I would really appreciate any advice on how we could improve this movement of data, to both speed it up, and keep the downtime on the Live DB to a minimum.

    Thanks,

    David

  • July 31, 2008 at 6:24 am

    #851198

    As an alternative, extract directly into files and then load the files into Box 3. These would eliminate the steps of loading into tables on Box 2 and then extracting from Box 2.

    Box 1: Oracle 10G (Corporate System)

    Box 2: SQL Server 2000 on Active Directory (Utility Server)

    Box 3: SQL Server 2000 in DMZ, not on Active Directory (Used for Displaying Reporting Services reports over the internet.)

    Each evening this would happens.

    1. A fairly simple DTS fires on Box 2, which pulls data from the Oracle Box into files on Box 2.

    2. Move the files from Box 2 to Box 3 (FTP ?)

    3. Use a DTS package to load the files into Box 3's Staging Database.

    4. Then the Live Report DB (also on Box 3) is cleared and then loaded with the data from the Staging DB

    Can any of the following be done?

    a) A DTS fires on Box 2, which pulls data from the Oracle Box into the staging database on Box 3.

    b) A DTS fires on Box 2, which pulls data from the Oracle Box into files on Box 3 and then the files are loaded into the staging database.

    SQL = Scarcely Qualifies as a Language

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply