Archive for the ‘Snapshot’ Category

How to query a mirrored database using a snapshot?

If you looked at the mirrored database in the Mirror server, you will find that the mirrored database has the keywords “(Mirror, Synchronized / Restoring…)” appended to it. As we all know, if a database is restoring, you can’t read from it. Then, how can you query against it if you wanted to run reports against or just to check if the updates from the Principal database did actually get mirrored over to the mirrored database? Yes. You can if you first create a database snapshot and then query against the snapshot.
The keyword snapshot may have given away or hinted on what it means. A database snapshot is a read-only copy of a database that is taken at the point of time when the snapshot is taken. It is similar to taking a photo of yourself when you were twenty-two years old. You wish you were always twenty-two whenever you looked at the photo of which remind you of your fond memories of your younger days. But, again, it is only a photo, you have aged since then. The hard-partying days are over for you. Let’s get focused on SQL Server. Here are some facts about the database snapshots:

  • Available only for the Enterprise and Developer editions of SQL Server 2005+
  • Must be created in the same instance as the source
  • You can create multiple database snapshots for a database
  • Usually used for reporting, restore point, and just for testing purposes
  • You must use T-SQL to create a database snapshot
  • Read-only copy of the source
  • Use sparse files to store data

Sparse files are a feature of the NTFS file system. When created initially, a spare file takes up little disk space. It grows as changes in the source database increases. When you create a snapshot, the data pages are not copied over to the snapshot, when the data pages become dirty or changes occurred then the data pages will get copied over to the database snapshot. It is similar to the photo you had taken when you were twenty-two. You were young and looked great on the photo. But, it is only a snapshot. As you age, you gain more experience and more mileage on your body. Let’s just say things also changed, shall we?

In the following steps, we will create a database snapshot for the mirrored database and then query against it. Here are the overview steps before the implementation steps as always:
1. Query against the system view sys.master_files to view the logical file name of your mirrored database.
2. Make sure you have enough disk space in your server.
3. Create the snapshot.
4. Query against the snapshot.
5. Drop the snapshot.

Implementation steps:
1. Query against the system view sys.master_files to view the logical file name of your mirrored database.

-- Find the logical file name for the data file for the mirrored database
-- Make a note of it as it will be need for the creation of the snapshot
SELECT name AS LogicalName
, type_desc
, physical_name
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks' -- replace with your mirrored database here

2. Make sure you have enough disk space in your server.

-- Make sure you have enough disk space
XP_FIXEDDRIVES

3. Create the snapshot.

-- Create the snapshot
CREATE DATABASE AdventureWorks_dbss01242013 ON -- replace with your mirrored database here
(
NAME = AdventureWorks_Data
, FILENAME = 'C:\Snapshot\AdventureWorks_dbss01242013.ss'
)
AS SNAPSHOT OF AdventureWorks -- replace with your mirrored database here
GO

4. Query against the snapshot.

-- You can query the database snapshot similar to how you query a regular database
USE AdventureWorks_dbss01242013
GO
SELECT *
FROM HumanResources.Employee
GO

5. Drop the snapshot.

-- Drop a database snapshot
USE master
GO
DROP DATABASE AdventureWorks_dbss01242013

REFERENCES:

How to: Create a Database Snapshot
http://msdn.microsoft.com/en-us/library/ms175876(v=sql.105).aspx

Understanding Sparse File Sizes in Database Snapshots
http://msdn.microsoft.com/en-us/library/ms175823(v=sql.105).aspx

Drop a Database Snapshot
http://msdn.microsoft.com/en-us/library/ms190220(v=sql.105).aspx