Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Database Snapshots in SQL Server 2005 Expand / Collapse
Author
Message
Posted Sunday, November 26, 2006 10:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2008 8:39 PM
Points: 4, Visits: 2
Comments posted here are about the content posted at temp


Regards

Arvinder
arvinder-khosla.blogspot.com
Post #325517
Posted Monday, November 27, 2006 5:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 14, 2009 7:57 AM
Points: 62, 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.

Post #325601
Posted Monday, November 27, 2006 7:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:53 PM
Points: 209, Visits: 367

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?

Post #325636
Posted Monday, November 27, 2006 2:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 8, 2009 7:22 PM
Points: 14, Visits: 115
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?
Post #325792
Posted Tuesday, November 28, 2006 12:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:48 AM
Points: 102, 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

 

 

Post #325875
Posted Tuesday, November 28, 2006 12:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:48 AM
Points: 102, 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

 

 

Post #325876
Posted Tuesday, November 28, 2006 12:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:48 AM
Points: 102, 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

Post #325877
Posted Tuesday, November 28, 2006 12:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:48 AM
Points: 102, 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

Post #325880
Posted Tuesday, November 28, 2006 10:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 7, 2013 6:12 PM
Points: 135, 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?

Post #326284
Posted Wednesday, November 29, 2006 12:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 14, 2009 7:57 AM
Points: 62, 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.

Post #326297
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse