SQL Server Performance

Reporting off logshipped db

Discussion in 'SQL Server Log Shipping' started by sql_machine, May 7, 2006.

  1. sql_machine New Member

    This is the first time I encountered issue with db on standby being unavailable for reporting when log is being applied. In the past the interval was larger, so I think it seemed like there's no impact. Anyhow, currently the log is being applied continuously, so often the database is blocked. Any similar experiences[?]

    I think that sql 2005 snapshot will be a good candidate for this in the future...
  2. mmarovic Active Member

    Yes, that's consequence of how log shipping works. I read recently an article claiming mirroring won't help with reporting as much either. The reason is that for each report principal database is queried too, so it doesn't take load from the production server much.

    Here is the link from microsoft (not the article I mentioned above):http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

    quote:Database Snapshots and the Mirror Database

    Because the mirror database is in a recovering state, it is not accessible and not readable. With SQL Server 2005 Enterprise Edition and Developer Edition, you can create database snapshots to read the mirror database at a point in time. Database snapshots provide a read-only view of a database, exposing data that is consistent at the point of the snapshot creation.

    You access the database snapshot just as though it were another database. When you query a database snapshot, you read original versions of any database data that has been changed after the snapshot's creation from the database snapshot's file, and you read unchanged data from the original database. The end effect is that you see database data current at the point in time that you created the snapshot. (See the topic "Using Database Snapshots with Database Mirroring" in SQL Server Books Online for more information.)

    Because database snapshots do require some overhead on the mirror server, you need to be careful about how they might impact database mirroring performance. You can only mirror to one database, so if you need to scale out to many read-only reporting servers, transactional replication is a better choice. (For more information, see "Database Mirroring and Replication" in the Implementation section later.)
  3. satya Moderator

    Good to check the hardware contention during copy & load process of log shipping.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page