I need to create a snapshot of one of our mirror databases.
I have gone through a couple of links and it looks like querying can be achieved here by creating a snapshot at a mirror server.
However, I would need your help in understanding this for the DB in my case, where it is over 16 TBs and is used 365*7. Things like when you should create snap files and how much extra storage would be required in setting this snapshot database.
As far as I can understand, it should not be another 16 TB, apart from the reserved 16 TB of the mirrored database. Also how would one ensure that a snapshot is in sync with principal with acceptable refresh or lag period of 30 minutes?
Please provide some advices.
Best Answer
Ref:
It will depend on the rate of changes and the age of your snapshot.
Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page do not affect the contents of the snapshot. The same process is repeated for every page that is being modified for the first time. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken.
If your source database is fairly large and you are concerned about disk space usage, at some point you should replace an old snapshot with a new snapshot. The ideal lifespan of a snapshot depends on its growth rate and the disk space that is available to its sparse files. The disk space required by a snapshot depends on how many different pages in the source database are updated during the life of the snapshot. Therefore, if updates are mostly to a small subset of pages that are updated repeatedly, the growth rate will slow over time and the snapshot space requirements will remain relatively small. In contrast, when all of the original pages are eventually updated at least once, the snapshot will grow to the size of the source database. If the disk begins to fill up, the snapshots compete with each other for disk space. If the disk drive fills up, write operations to all the snapshots will fail.
Snapshot is a point in time state of your whole database. There is no sync with the source. You have to create a new snapshot every 30 mins if your acceptable lag is 30 minutes.
A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation.
For your case I would highly recommend you look at Always On Availability Groups where you can have read-only copy of your primary database and sync continuously. Details here.