CODEWITHSUNDEEP
sqlsql-serversql-server-2012
goofyui
goofyui

Reputation: 3492

Whether snapshot database has instant records same as the source database in SQL?

Whether snapshot database has instant records same as the source database in SQL?

This is a small production database.

We are considering to have snapshot database in the same server only for reporting purpose. I wonder, whether snapshot database will have instant records or time lag in the records.

I have worked on replication databases which takes about 5 or 10 mins to get the fresh data records.

Upvotes: 0

Views: 155

Answers (1)

RThomas
RThomas

Reputation: 10880

No, a database snapshot is purely a point in time view of your active database. Not only will it not be instant, it will not ever catch up. It is purely a point in time view of data as it was.

In other words, the more time that lapses between the time the snapshot is taken and the time your query runs against the snapshot, the greater the difference will potentially be between the snapshot and the original query.

This is also evident in the way the snapshot is managed on disk. Snapshots maintain point in time views by referencing original copies of the database pages. As modifications come in post snapshot, a copy of the page is made to maintain the state of the snapshot. Hence, a snapshot on disk is very small at the time that it is taken, but will continue to grow larger and larger as time passes as it continues to keep an exact version of the original state of the database at the moment the snapshot was taken.

As quoted in the documentation. A database snapshot is a read-only, static view of a SQL Server database (the source database).

Upvotes: 1

Related Questions