May 31, 2013 at 7:14 am
Hello,
We have the following situation. We have a reporting layer (let us call it INST1) which will be truncated and loaded with new data every 6 hours. We have users 24 X 7 and in order to keep the down time very minimal, we have another identical instance of this layer (let us call it inst2) and we plan to take the snapshot of the inst1 into the inst2 before we truncate the inst1 data. Users will always connect to inst2. The data size is 200 GB.
My questions are:
1) Will snap shots work if the source database is truncated after the snapshot is taken.
2) If yes, how much time it could take for the snapshots to complete for 200 GB data.
3) Will there be any other performance issues that we should consider?
Thank You,
Rajeev.
May 31, 2013 at 7:28 am
rajeev.training (5/31/2013)
1) Will snap shots work if the source database is truncated after the snapshot is taken.
If you mean TRUNCATE TABLE, yes. If you mean something else, maybe explain what you mean
2) If yes, how much time it could take for the snapshots to complete for 200 GB data.
Snapshots are near-instantaneous to create, because there's nothing in them when they're created. Read up on "copy-on-write"
3) Will there be any other performance issues that we should consider?
Potentially, yes. Snapshots add overhead to all data modifications in the source database.
Database snapshots have to be on the same instance as the source database, they cannot be backed up, restored, detached or attached.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2013 at 7:34 am
Thank You Gail for the quick response.
1) We do not just truncate one table, the whole instance is wiped off. I mean truncate all the tables and load the database with new data every 6 hours.
2) If yes, how much time it could take for the snapshots to complete for 200 GB data.
Snapshots are near-instantaneous to create, because there's nothing in them when they're created. Read up on "copy-on-write"
===> Well, when we truncate all the tables in the source database after the snapshot, then all the pages in the source are modified from the time of the snapshot and so I expect to have the source data copied into the sparse files completely before the truncate and I would like to know how long it takes typically for a data size of 200 GB.
3) Will there be any other performance issues that we should consider?
Potentially, yes. Snapshots add overhead to all data modifications in the source database.
Database snapshots have to be on the same instance as the source database, they cannot be backed up, restored, detached or attached.
Thank You,
Rajeev.
May 31, 2013 at 7:41 am
rajeev.training (5/31/2013)
Well, when we truncate all the tables in the source database after the snapshot, then all the pages in the source are modified from the time of the snapshot and so I expect to have the source data copied into the sparse files completely before the truncate and I would like to know how long it takes typically for a data size of 200 GB.
You'll have to test and see, it'll depend on the IO subsystem, concurrent load and a whole bunch of factors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply