Post reply

Best design/startegy to implemnt real-time data availability from DB "A" to DB "B"

  • August 17, 2008 at 3:45 pm

    #382289

    Hi,

    I am importing data by joining many tables from Database "A" to DB "B".

    And then use the data from 2 databses in SSRS reports.

    What would be the best method to implement real-time data availability of data from DB "A" to DB "B".

    Right now i'm uisng a job that runs SSIS package every 24 hours, to import data to DB "B", which creates some overhead.

    Please suggest on this.

    Thanks,

    - AR

    Alicia Rose

  • August 19, 2008 at 6:27 pm

    #860337

    Do you mean server when you say "database"? Eg are you referring to two seperate SQL Server instances?

    Regards,

    Jacob

  • August 19, 2008 at 7:23 pm

    #860352

    Transactional replication would likely be your best bet.

  • August 20, 2008 at 8:38 am

    #860662

    Hi Jacob and Toby,

    Its the same server that the databases resides in.

    The table that i mentined would be used as a base table for reporting.

    Do we set up transactional replication, when the databases are on the same server?

    Thanks,

    -A

    Alicia Rose

  • August 20, 2008 at 12:56 pm

    #860843

    Part of the reason to separate the reporting environment from the production environment is to separate resources so that reporting doesn't bog down production. If you have the objects on the same server contention can occur (CPU/Memory/IO) even if they are in separate databases.

    In any case if this is the situation you are in and there is no changing it I would either modify the DML stored procedures to operate on both databaes, have a view of the table on the other database, or define a trigger(s) for update/delete/insert on the table. Let me know if you want specifics on any of these methodologes.

Viewing 5 posts - 1 through 4 (of 4 total)

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