SQL 2005 – Snapshots and Mirroring | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL 2005 – Snapshots and Mirroring

Greetings, Because this post involved two features offered by SQL 2005, I decided to post to generic figuring it would receive a larger audience. Issue:
I have an inquiry as to what to expect from database snapshots taken of a mirror partner database. We are noticing that at least one query that is run at different times againt a snapshot is returning different result sets. This is not the expected behavior. We’d like to know what we are missing and/or why we might be seeing this behavior. Background:
Currently, we have a production SQL server that backends are core billing. It is a Clustered SQL 2005 Enterprise Edition running on Windows 2003 Enterprise. The server is configured as the principle in an asynchronous mirror configuration to another data center. The mirroring feature works wonderfully and does a great job of recovering after network outages, and what not. On the partner in the mirror configuration, the database is readonly. Obviously, this is by design. We are developing a warehousing mechanism for reporting purposes that requires relatively fresh data. In order to be able to get to the data needed to generate important reporting, etc, and not have to use production, we decided to setup database snapshot on the readonly partner instance of the production database in question. We have a scheduled job that calls a stored procedure to create a new snapshot of the mirrored DB every 6 hours. The stored procedure also "rolls off" (or drops) previous snapshot older than 24 hours. This leaves us with four snapshots at any given time. Usage:
We present these snapshots as "vintages" to issue standard report queries over the past day. Let us assume these snapshots have names: PRODDATA_082206_0000 through PRODDATA_082206_1800. If we were to issue a single query, for example, to all of these snapshots at different times, say 30min to 2hours, we see a change in the data. Attached below is some sample data. Any ideas as to why this is happening? This is very bad, obviously, if we are depending on snapshot to be.. um snapshots of data at the taken time. Time 1:11pm1:39pm1:48pm2:10pm2:16pm2:38pm4:43pm5:56pm
productQtyQtyQtyQtyQtyQtyQtyQty
846514651465146514651465146514651
1017411741174117411741174117411741
1675675675675675675677680
243364336433643364336433743374338
8404404404404404404404404
1011111111
191515151515151515
1205205205205205205203200
2316316316316316315315314
8118118118118118118118118
1911111111
1365365365365365365365365
2148148148148148148148148
8102102102102102102102102
19349349349349349349349349
1118189118193118193118197118197118202118231118257
23235432356323563235732358323583236432368
836233623362436243624362436263628
105252525252525252
1942484250425042504250425042534254
11122311219112191121511215112101118111155
229952993299329922991299129852981
8927927926926926926924922
19850848848848848848845844
11050710509105091050910509105091051710525
21789317893178931789517896178961789917900
511111111
814681469146914691469146914691470
19458458458458458458458458
119281926192619261926192619181910
258825882588258805879587958765875
8963962962962962962962961
192525252525252525
125002500250025002500250025002500
2923923923923923923923923
19768768768768768768768768
1161161161161161161161161
26464646464646464
81414141414141414
1011111111
1999999999 Any help would be most grealy appreciated. Please let me know if there is anything else I left out that would assist in resolving/answering this confusion. KRA
"It is too late for the pebbles to vote, the avalanche has already started."
Nevermind! I examined the query the reporters were using and sure enough they were using getdate() for comparisons. Anyhow, I apologize for the wasted post! KRA
"It is too late for the pebbles to vote, the avalanche has already started."
Not at all, I think as it helps others in case they crash into similar issue [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />].<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
]]>