SQL Server Performance

Database Snapshot

Discussion in 'SQL Server 2005 Database Mirroring' started by Fais, Dec 11, 2007.

  1. Fais New Member

    Hi
    all,To
    get current data from our Mirrored Database, we can use snapshot.But
    ,right now, we have logshipped database, so for reports we are getting almost current data.So
    , i wanted to know, how current data i can get from Database Snapshots for the Reports.Thanks
    .
  2. satya Moderator

    Database snapshots are available only in the Enterprise Edition of Microsoft SQL Server 2005. All recovery models support database snapshots.
    Unlike the mirror database itself, a database snapshot is accessible to clients. As long as the mirror server is communicating with the principal server, you can direct reporting clients to connect to a snapshot. Note that because a database snapshot is static, new data is not available. To make relatively recent data available to your users, you must create a new database snapshot periodically and have applications direct incoming client connections to the newest snapshot.
    But when you are using Mirror server for Snapshot backups you need to check for license.
  3. Fais New Member

    I have one scenario.
    I want to use these database snapshots for my reporting.Can you give some logic how can i acheive this.
    Because if today morning, if i create database snapshot called DB1_snap, which is going to be used by reports in thier data source,
    then to get new updated data, we need to create another database snapshot in the evening, but with different name.
    So, my question is , if i change the name of the database snapshot everyday, then how the reports going to use that.
    Any work around for this.
    Thanks.
  4. satya Moderator

    <P mce_keep="true">Updated Books online has articulated this nicely:</P><P mce_keep="true">Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. A snapshot persists until it is explicitly dropped by the database owner.</P><P>Unlike default behavior for user databases, a database snapshot is created with the ALLOW_SNAPSHOT_ISOLATION database option set ON regardless of the setting of this option on the primary database or the model system database.</P><P>Snapshots can be used for reporting purposes. Also, in the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot's creation. </P><DIV class=alert><TABLE class="" cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR><TH class="" align=left><IMG class=note src="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/local/note.gif">Important: </TH></TR><TR><TD class="">Reverting does not work on an offline or corrupted database. Therefore, taking regular backups and testing your restore plan are necessary to protect a database.</TD></TR></TBODY></TABLE></DIV>
  5. Fais New Member

    Hi Satya,
    Here, my question is, Do i have to create a job, which drops the existing old Database Snapshot ( this name is hardcoded in the reports data source) & creates the new snapshots periodically, to get current data.
    Can you give some logic for using Database snapshots for Reporting purpose. ( like we cannot have two snapshots with the same name).
    Thanks.
  6. satya Moderator

    Yes you can do that, as for mroe information you can refer to the books online.

Share This Page