Post reply

'Near Real Time' Reporting Copy of OLTP database

  • February 23, 2010 at 7:06 am

    #216001

    I need a read only copy of an OLTP database on separate server for reporting, but the requirement is for ‘Near Real Time’ reports. The database contain several hundreds tables, of which only a few dozen are of interest to reporting. Using SQL2008 what are the best options for minimal loading on the production server and high availability and minimal latency on the reporting copy.

    We have used log shipping in the past on SQL 2000, but this periodically takes the reporting copy offline. Are there now better options in SQL 2008?

    Thanks,

    Martin

  • February 23, 2010 at 7:10 am

    #1123587

    only a few dozen tables out of the database - gotta be replication.

    ---------------------------------------------------------------------

  • February 23, 2010 at 1:19 pm

    #1123856

    Agreed, replication is the way to go.

    Transactional replication is easy to setup and get running and provides (under normal circumstances) pretty low latency copies of the data that you can use for reporting purposes.

    There are a couple of caveats such as the requirement of primary keys, see http://msdn.microsoft.com/en-us/library/ms151198.aspx to get started.


    Shamless self promotion - read my blog http://sirsql.net

  • February 23, 2010 at 1:59 pm

    #1123877

    I'm going to go with Transactional Replication as well.

    CEWII

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

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