locked
Database Snapshot for Reports RRS feed

  • Question

  • 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.

    Tuesday, December 11, 2007 3:30 PM

Answers

  • How long do you need the Snapshot you created in the morning ? if you dont need it then in the evening you can create a new snapshot with the same name ! i. before creating new snapshot you can drop the existing snapshot and create this with the same name. Also you can multiple snapshots for a single database and query them whichever is needed for you !

    - Deepak

    Wednesday, December 12, 2007 12:53 AM

All replies

  • Hi,
        If you have configured database mirroring your mirror db will be marked as Restoring state and you cannot read any data from it. If you wish to read the data from mirror db you need to take a database snapshot of your mirror db and read the datas or use it for reporting purposes.

    eg.) if you update a table in principal server @8:30 PM and to verify it you go to mirror server and take snapshot. You can query the snapshot as you query your database.
    Pls note that the snapshot will contain only the datas that were present at the time the snapshot was taken. If you take snapshot @8:35PM you can see the updated data in the mirror server.

    If you perform some update once again @principal say @8:40PM and try to see this snapshot (taken @8:35PM) it will not reflect the changes. You need to take another snapshot to read the data changes that had happened @8:40PM.
    The snapshot provides a read-only static view of a database. Refer http://sql-articles.com/articles/snapshot/snapp.htm


    So, i wanted to know, how current data i can get from Database Snapshots for the Reports.



    To get the current data you need to take a snapshot currently. But in log shipping it is different as the secondary db will be in Read - Only mode so that you can get the current data changes as well !

    - Deepak
    Tuesday, December 11, 2007 4:12 PM
  • Hi Deepak,

     

    Nice Article, very much helpful.

     

    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.

     

     

     

    Tuesday, December 11, 2007 6:05 PM
  • If I use Transactional replication on my DR  server( for generating reports) and having Database Mirroring ( for HighAvailability) ,  --- Is this kind of setup , creates any stress on the server.

     

    Please throw your ideas.

     

    Thanks.

     

    Tuesday, December 11, 2007 8:03 PM
  • How long do you need the Snapshot you created in the morning ? if you dont need it then in the evening you can create a new snapshot with the same name ! i. before creating new snapshot you can drop the existing snapshot and create this with the same name. Also you can multiple snapshots for a single database and query them whichever is needed for you !

    - Deepak

    Wednesday, December 12, 2007 12:53 AM

  • How frequent can we take a db snapshot on the mirror to have near real-time reporting.
    And any way to redirect the connected users from the old snapshot to the new one
    Saturday, July 12, 2008 2:48 PM
  • Hi Sri_au

     

    welcome to msdn.

     

    This is exactly how frequent i can take a backup of my database, answer is the same. depends on you there is no rule.

    Once snapshot complted execute another one but need to consider the availablity of the sanpshot coz while creating and dropping will not accesible.

     

    BR
    Praveen barath

     

    Mark as answer if it helps

     

    Tuesday, July 29, 2008 12:02 AM
    Answerer