Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Snapshots in SQL Server 2005


Database Snapshots in SQL Server 2005

Author
Message
arvinder-306327
arvinder-306327
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 2
Comments posted here are about the content posted at temp


Regards

Arvinder
arvinder-khosla.blogspot.com
Marino van der Heijden
Marino van der Heijden
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 17

Great article! It describes all the things you need to know to get started with snapshots.

I have a question though. Do I understand correctly that a snapshot will cause a LOT of overhead if you leave it running for days or, perhaps, weeks? I tend to use snapshots only in particular situations in which you exactly know what you want to do/find out.

Forgive me, I'm just trying to figure out when you can use snapshots best. for now, I think it might be useful in some situations. Especially for ad hoc reporting, problem solving, debugging and restoring a database after a dba or developer tested some small changes.


Mark Yelton
Mark Yelton
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 433

I was all ready to start trying this until I read:

3. Performance of the source database is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

How much of a performance hit are we talking about?


chung-327878
chung-327878
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 116
Great article. I wonder how does this compare to Log Shipping. Currently we are using Log Shipping for reporting and have issues of killing all users before applying the log update. Would Snapshot be a better choice for reporting?
srivathsani-296624
srivathsani-296624
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 125

Hi,

Thanks for the comments.Snapshots would be definitely a better alternative to Log shipping for reporting purposes.For databases that does not undergo large changes,if the user takes a snapshot,then the snapshots would be storing only those pages which have got changed.I think this answers your question.You can mail me at srivathsani_m@infosys.com for further issues.

Regards,

S.Srivathsani


srivathsani-296624
srivathsani-296624
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 125

Hi,

Thanks for the comments.Snapshots would be definitely a better alternative to Log shipping for reporting purposes.For databases that does not undergo large changes,if the user takes a snapshot,then the snapshots would be storing only those pages which have got changed.I think this answers your question.You can mail me at srivathsani_m@infosys.com for further issues.

Regards,

S.Srivathsani


srivathsani-296624
srivathsani-296624
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 125

Hi Mark Yelton,

3. Performance of the source database is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

The user needs to use the Snapshot feature judiciously.If the snapshot is taken for a database which undergoes lots of changes,then it would hit the performance of the source database.This is because the when the page gets updated,the original page has to be written to the sparse file.Snapshot is ideal for databases with not too many updates/deletes.

Please feel free to mail me at srivathsani_m@infosys.com for any further queries.

Regards,

S.Srivathsani


srivathsani-296624
srivathsani-296624
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 125

Hi Marino van der Heijden,

As you said its very true,Snapshots are ideal for scenarios like auditing,reporting etc.I am not quite clear with the statement that "If the snapshot is allowed to run for weeks".You mean to say that if we keep taking snapshots daily for a week or so.It would be a overhead on the server if the database undeergoes frequent changes.Otherwise,if the database is quite static,then it wouldnt be a overhead.

Regards,

S.Srivathsani


Bimal Fernando
Bimal Fernando
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 87

Hi, how does the unchanged data is accessed from the snapshot? are they accessed directly from the Source database? if so woudn't that be a problem for the source database performance if reporting or somthing else excessively happening on the snapshot database?

But if you have a log shipped database then it's a completely independent from the source and won't hinder the performance of the source database.

Am i right?


Marino van der Heijden
Marino van der Heijden
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 17

Thanks for the reply. What I meant with a "snapshot allowed to run for weeks" is a snapshot taken some weeks ago, that will generate double activity. Every change in the source database will also cause activity in the snapshot. Therefore I wonder if you should be careful with snapshots. Once you don't need a snapshot anymore, you should probably delete it immediately, right?

It's a fact that it will cause impact on performance. But a 'normal' server should be able to handle that, so that mustn't be a problem.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search