SQL Server 2005 Replication Question

Discussion in 'microsoft.public.sqlserver.replication' started by Fraz, Apr 28, 2010.

  1. Fraz

    Fraz Guest

    I am looking into possible options for replicating data for reporting
    purposes. I have found transactional replication to work best for realtime
    reporting, source used for user transactions and target for reporting. I have
    come across a large vendor database 50GB on SQL Server 2005 where reports are
    taking too long and we are trying to separate it by using transactional
    replication but most of the key tables don't have primary keys and we don't
    want to change the database schema by adding PK. What are other options we
    could have; log shipping, DB Mirroring the target is not usable for
    reporting. Any SQL Server built-in tool or third party tool that could
    achieve same results as transactional replication but most of the tables
    don't have PKs. Any suggestion on this is appreciated. Thanks.
     
    Fraz, Apr 28, 2010
    #1
    1. Advertisements

  2. Fraz

    Chris Guest

    Log shipping is only sql option, secondary db is in read only mode. However
    updating secondary by restoring a log will disconnect all users. If you are
    only looking to restore every 2 hours or so or nightly this may work, if
    users do not like the latency, well, take the time to add pks.

    you might want to consider adding identity column to end of table, it is
    self populating and most likely will not break your app.

    Trans repl is the way to go!

    chris
    sqldatabasesolutions.com
     
    Chris, May 6, 2010
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.