January 6, 2009 at 12:35 am
Hi all
This may be an interesting one:
In creating a Database Snapshot from a Mirrored Database (for the purposes of offloading reporting) I would like the Database Snapshot to expose views to the reporting clients. Since each time a new Database Snapshot is created requires dropping the old one and connecting to the new one, how can I ensure that the appropriate view gets "transferred" from one Database Snapshot to the other?
Regards,
Tony
January 25, 2009 at 1:31 pm
Forgive me if I've misunderstood the question.
I don't see how the vew could be added to the snapshot, since snapshots are read-only copies of a database at a certain point in time. They can't be added to.
The only way to ensure that a view is present in a snapshot is to create the view in the original source database.
If this isn't possible for some reason then how about creating a separate resource database to hold all your reporting views, on the same server that your snapshot is held on.
Have the views query the tables in your snapshot, and then have your reporting clients connect to the resource database and utilise the views from there.
When your snapshot is regularly deleted and recreated, the views will still be good (assuming the snapshot keeps the same name).
January 25, 2009 at 11:52 pm
Andy is right that you can't create view on the snapshot db unless the views are created in the source database.
Where are these views located? Can you share more details to get accurate responses back?
On a side note, dropping a snapshot actually flushes the entire procedure cache in SQL Server 2005. So use it appropriately at appropriate (low activity) times. I wrote about that here.
Did you know, Dropping a snapshot flushes procedure cache in SQL Server 2005
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
January 26, 2009 at 3:49 am
Sankar Reddy (1/25/2009)
On a side note, dropping a snapshot actually flushes the entire procedure cache in SQL Server 2005.
I didn't know that, thanks Sankar, I learn something new every day!
January 26, 2009 at 5:39 am
Thanks Andy and Sankar
You both indicate that you can't create views on a Databse Snapshot and that views should be created on the source database.
My source database is a mirrored database which runs in a different mode to normal databases, which cannot be accessed directly (hence the need for a database snapshot in the first place!).
My question then becomes:
Can you create a view on a mirrored database?
If the answer is yes, then I am happy
If the answer is no, then I still have a problem because I need read only access to the database and need to have this controlled by a view. If I can't put the view either on the nirrored (source) database or the database snapshot, then this does not work as a reporting solution.
Any ideas, or clarificaion?
Best Regards,
Tony.
January 26, 2009 at 6:54 am
As you already wrote the mirrored database can not be accessed at all (not for read and not for write). A snapshot database can be used for read operations only. You can create a view in the original database (the one that is mirrored). Another option is to create another database on your server with views that use the objects in the snapshot database as there source (I tested this on SQL Server 2008, but I didn’t test it on SQL Server 2005).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply