Sql Cert - Snapshots

Front 1

Database snapshot

Back 1

read only, static view of a sql server database (source).
Transactionally consistent w/ the source db a of the moment of its creation. Always resides on the same server as the source instance. As the source is updated the snapshot is updated, longer it exists the more disk space it uses. Multiple snapshots can exist on a given source. Unrelated to snapshot backups, snapshot isolation of transactions or snapshot replication.

Front 2

db snapshots - features

Back 2

Operate at data-page level. Stores the original page, preserving he data records as the existed when the snapshot was created. Repeated for every page that is being modified for the first time.

Front 3

db snapshots - sparse files

Back 3

an empty file that contains no user data and has not been allocated disk space for the user data. As more pages are updated (in the source) the size of the file grows.

Front 4

db snapshots - benefits

Back 4

1. can be used fro reporting purposes
2. Maintain historical data
3. Using a mirror database that you are maintaining for availability purposes to offload reporting.
4. safeguarding data against administrative error
5. if error on source, db can be reverted to the state when the snapshot was created, data loss is confined to updates.
6. Safeguarding data against user error
7. managing a test db.

Front 5

db snapshots - prereqs

Back 5

1. Must be enterprise edition
2. source db must be online, unless the db is a mirror db w/in a db mirroring session
3. can be created on any primary or secondary db in an availability group. Replica role must be either primary or secondary, not in resolving state. Recommended state either synchronizing or synchronized when snapshot is created.

Front 6

db snapshots - prereqs cont..

Back 6

4. To create a db snapshot on a mirror database, the db must be in the synchronized mirroring state
5. source db cannot be configured as a scalable shared db.

Front 7

db snapshots - limits on Source DB

Back 7

if the db snapshot exists..
1. db cannot be dropped, detached, or restored
2. performance is reduced, due to increased I/O on the source db resulting from a copy-on-write operation to the snapshot every time a page is updated.
3. files cannot be dropped from the source db or from any snapshot.

Front 8

db snapshots - limits on DB snapshots

Back 8

1. must be created and remain on the same server instance as the source db.
2. always work on an entire db
3. are dependent on the source db and are not redundant storage. Not a substitute for backup and restore strategy.
4. if snapshot runs out of disk space during an update, it becomes suspect and must be deleted
5. read-only
6. model, master and tempdb snapshots are prohibited

Front 9

db snapshots - limits on db snapshots cont.,

Back 9

7. cannot change any specifications of the db snapshot files.
8. cannot drop files from a db snapshot
9. cannot back up or restore
10. cannot attach or detach
11. cannot create db snapshots on FAT32 file systems or RAW partitions. Sparse files used by NTFS file system.
12. Full-text indexing is not supported on db snapshots. Full-text catalogs are not propagated from the source db

Front 10

db snapshots - limits on db snapshots cont2.

Back 10

13. inherits the security constraints of its source db at the time of creation
14. always reflects the state of filegroups at the time of creation.
15. if source db becomes RECOVERY_PENDING, snapshots may become inaccessible. once resolved become accessible.
16. reverting is unsupported for read-only filegroups and for compressed filegroups.
17. in log shipping config, db snapshots can only be created on the primary db. if roles switch all db snapshots must be dropped

Front 11

db snapshots - limits on db snapshots cont3..

Back 11

18. cannot be configured as a scalable shared database
19. FILESTREAM filegroups are not supported
20. when statistics on a read-only snapshot are missing or stale, the DB engine creates and maintains temp statistics in tempdb.