SQL Server, Analytics, .Net, Machine Learning, R, Python
Mitch Wheat has been working as a professional programmer since 1984, graduating with a honours degree in Mathematics from Warwick University, UK in 1986. He moved to Perth in 1995, having worked in software houses in London and Rotterdam. He has worked in the areas of mining, electronics, research, defence, financial, GIS, telecommunications, engineering, and information management. Mitch has worked mainly with Microsoft technologies (since Windows version 3.0) but has also used UNIX. He holds the following Microsoft certifications: MCPD (Web and Windows) using C# and SQL Server MCITP (Admin and Developer). His preferred development environment is C#, .Net Framework and SQL Server. Mitch has worked as an independent consultant for the last 10 years, and is currently involved with helping teams improve their Software Development Life Cycle. His areas of special interest lie in performance tuning
Sunday, June 11, 2006
SQL Server 2005 Database Snapshots
I recently had one of those “ahha!” moments with the new SQL Server 2005 Database Snapshot feature (not to be confused with the new transaction Snapshot Isolation mode). Dr Greg Low gave an overview of this great feature at the Perth .Net User Group last year, and I was going over some notes and e-learning material.
When you create a snapshot of a database, SQL Server 2005 efficiently creates a NTFS sparse file that initially contains effectively no data.
When you read data from the snapshot, SQL Server checks to see if the page the data resides upon exists in the snapshot. If it does, it serves the page from the snapshot; otherwise it serves the page from the original database.
How do pages appear in the snapshot? Each time a write is made to the original database, SQL Server checks if the page is already in the snapshot, if not it copies the page into the snapshot BEFORE the write is made to the original, thus preserving the point in time snapshot of the data. For some reason, I had wrongly assumed the page was copied after the write, but that just did not make much sense.
Database snapshots can be applied wherever you want to preserve a point in time state of a database. An excellent example of using a snapshot is point in time reporting. This is just one of many reasons why you should consider upgrading to SQL Server 2005.
MSN, Email: mitch døt wheat at gmail.com