Solved: SMSQL - SnapInfo Disk 101

israelmmi · ‎2010-09-02

I had some basic questions about how the SnapInfo disk works with SMSQL:

The following link http://communities.netapp.com/message/31591 explained this point:
- In full model, all the tx logs are stored in Snapinfo for the duration of snapshot retention, so your first calculation is appropriate.
- With Simple model, the tx logs won't be placed in Snapinfo, so the Snapinfo LUN can be much smaller.
- When I browse to my SnapInfo LUN on my server I see the path \LogBackup\LOGINST__SQL1\LOGDB__Insight_v50_0_14197321 for example which is the path to where I keep the logs for my InsightManager DB Tx Logs, and there I see a bunch of files of different sizes. That database is using Full Recovery Mode so that makes sense. Similarly, the DBs which are using Simple Recovery Mode I do not see there.
My Questions:
1. Databases which are using Simple Recovery Mode, where are their log backups stored - on the Log Volume itself as part of the Volume snapshot that SMSQL takes?
2. Possibly related to the previous question - what is actually being stored on a Tx Log Volume Snapshot (assuming that there is only 1 LUN on it which is totally dedicated to Tx Logs)? Since the Full Recovery Mode log backups are stored on the SnapInfo LUN (as per above), is the Log Volume Snapshot just keeping the changes blocks of data on the disks for that LUN. Or, does the Snapshot only contain the Log changes from Simple Recovery Databases only?
3. What would happen if I delete all data (Tx Log Backups) from the SnapInfo LUN, but the snapshots from the Log & Data Volumes are in tact. Can I still use SMSQL to restore the databases?
4. As per this post - http://communities.netapp.com/message/5692:
  - Taken from TR-3696 - "When using SnapManager for SQL Server, a variation of this design can be used. Rather than having the transaction logs on a completely dedicated LUN, they can instead be placed in the root of the SnapInfo LUN. This design would then change from having five volumes to having four volumes. This is a good design because, taking the separate SnapManager for SQL Server schedules described above, SnapInfo would get backed up each time the logs are backed up."
  - Is this just a "good design" or actually reccomended?
  - Why would you want to backup the SnapInfo LUN/Volume, if at the end of the day it is just a backup. Isn't it just taking a snapshot of your log backups?
  - I usually backup all my LUNs (from a Snapshot copy) every so often to tape. Is there any reason to backup my SnapInfo LUN to tape, except for the possibility where I would need to restore a historical database + Tx Log from a point in time copy?
5. When restoring a database - does SMSQL do something special that requires me to use it, as opposed to for example, just mounting the snapshot data & log volumes and manually attaching the databases. I know it is more work to do it that way, and SMSQL does things like rename the database, etc. - but assuming I was bored, could I do it that way?

Thanks,

Reuvy

qzhang · ‎2010-09-02

Please see below inline:

1. My Questions:

1. Databases which are using Simple Recovery Mode, where are their log backups stored - on the Log Volume itself as part of the Volume snapshot that SMSQL takes?

Yes, the log is backed up inside the snapshot.

2. Possibly related to the previous question - what is actually being stored on a Tx Log Volume Snapshot (assuming that there is only 1 LUN on it which is totally dedicated to Tx Logs)?

It is same ldf file as your live ldf file when the snapshot was created, but the database was in quiesced mode.

Since the Full Recovery Mode log backups are stored on the SnapInfo LUN (as per above), is the Log Volume Snapshot just keeping the changes blocks of data on the disks for that LUN. Or, does the Snapshot only contain the Log changes from Simple Recovery Databases only?

The key to understand here is to know the difference between full backup and log backup. From the end user's perspective, snapshot captures the current state of files when the snapshot was created. The log volume snapshot is part of full database backup (snapshot based). There is no different between full recovery mode and simple recovery mode in terms of how the data was captured in a full database backup. It is correct that log backup is stored in snapinfo folder, but full backup (snapshot based) will have log volume snapshotted as part of the full backup.

3. What would happen if I delete all data (Tx Log Backups) from the SnapInfo LUN, but the snapshots from the Log & Data Volumes are in tact. Can I still use SMSQL to restore the databases?

Yes. You just don't have log backup to recover a database to a point other than the one when the snapshot (full backup) was created.

4. As per this post - http://communities.netapp.com/message/5692: <http://communities.netapp.com/message/5692>

§ Taken from TR-3696 - "When using SnapManager for SQL Server, a variation of this design can be used. Rather than having the transaction logs on a completely dedicated LUN, they can instead be placed in the root of the SnapInfo LUN. This design would then change from having five volumes to having four volumes. This is a good design because, taking the separate SnapManager for SQL Server schedules described above, SnapInfo would get backed up each time the logs are backed up."

§ Is this just a "good design" or actually reccomended?

If you read the whole thread, the above statement was misworded, and should have been corrected with updated TR. In summary, the log LUN cannot be shared by the snapinfo LUN, and I don't believe this is a good design, or recommended.

§ Why would you want to backup the SnapInfo LUN/Volume, if at the end of the day it is just a backup. Isn't it just taking a snapshot of your log backups?

§ I usually backup all my LUNs (from a Snapshot copy) every so often to tape. Is there any reason to backup my SnapInfo LUN to tape, except for the possibility where I would need to restore a historical database + Tx Log from a point in time copy?

You want to backup your snapinfo folder, because in addition to the log backups, it also includes full database backup metadata files, which are required for full backup restore. If you only have snapshots, you still cannot do a restore without backup metadata saved in snapinfo folder.

5. When restoring a database - does SMSQL do something special that requires me to use it, as opposed to for example, just mounting the snapshot data & log volumes and manually attaching the databases. I know it is more work to do it that way, and SMSQL does things like rename the database, etc. - but assuming I was bored, could I do it that way?

You can mount snapshot, copy the files, and then attach the database manually, or do LUN restore yourself, attach the database manually. But with that, you cannot recover a database to a point in time or up to minute by applying log backups after full backup was restored.

Thanks,

-Qing

View solution in original post

qzhang · ‎2010-09-02

Please see below inline:

1. My Questions:

1. Databases which are using Simple Recovery Mode, where are their log backups stored - on the Log Volume itself as part of the Volume snapshot that SMSQL takes?

Yes, the log is backed up inside the snapshot.

2. Possibly related to the previous question - what is actually being stored on a Tx Log Volume Snapshot (assuming that there is only 1 LUN on it which is totally dedicated to Tx Logs)?

It is same ldf file as your live ldf file when the snapshot was created, but the database was in quiesced mode.

Since the Full Recovery Mode log backups are stored on the SnapInfo LUN (as per above), is the Log Volume Snapshot just keeping the changes blocks of data on the disks for that LUN. Or, does the Snapshot only contain the Log changes from Simple Recovery Databases only?

The key to understand here is to know the difference between full backup and log backup. From the end user's perspective, snapshot captures the current state of files when the snapshot was created. The log volume snapshot is part of full database backup (snapshot based). There is no different between full recovery mode and simple recovery mode in terms of how the data was captured in a full database backup. It is correct that log backup is stored in snapinfo folder, but full backup (snapshot based) will have log volume snapshotted as part of the full backup.

3. What would happen if I delete all data (Tx Log Backups) from the SnapInfo LUN, but the snapshots from the Log & Data Volumes are in tact. Can I still use SMSQL to restore the databases?

Yes. You just don't have log backup to recover a database to a point other than the one when the snapshot (full backup) was created.

4. As per this post - http://communities.netapp.com/message/5692: <http://communities.netapp.com/message/5692>

§ Taken from TR-3696 - "When using SnapManager for SQL Server, a variation of this design can be used. Rather than having the transaction logs on a completely dedicated LUN, they can instead be placed in the root of the SnapInfo LUN. This design would then change from having five volumes to having four volumes. This is a good design because, taking the separate SnapManager for SQL Server schedules described above, SnapInfo would get backed up each time the logs are backed up."

§ Is this just a "good design" or actually reccomended?

If you read the whole thread, the above statement was misworded, and should have been corrected with updated TR. In summary, the log LUN cannot be shared by the snapinfo LUN, and I don't believe this is a good design, or recommended.

§ Why would you want to backup the SnapInfo LUN/Volume, if at the end of the day it is just a backup. Isn't it just taking a snapshot of your log backups?

§ I usually backup all my LUNs (from a Snapshot copy) every so often to tape. Is there any reason to backup my SnapInfo LUN to tape, except for the possibility where I would need to restore a historical database + Tx Log from a point in time copy?

You want to backup your snapinfo folder, because in addition to the log backups, it also includes full database backup metadata files, which are required for full backup restore. If you only have snapshots, you still cannot do a restore without backup metadata saved in snapinfo folder.

5. When restoring a database - does SMSQL do something special that requires me to use it, as opposed to for example, just mounting the snapshot data & log volumes and manually attaching the databases. I know it is more work to do it that way, and SMSQL does things like rename the database, etc. - but assuming I was bored, could I do it that way?

You can mount snapshot, copy the files, and then attach the database manually, or do LUN restore yourself, attach the database manually. But with that, you cannot recover a database to a point in time or up to minute by applying log backups after full backup was restored.

Thanks,

-Qing

israelmmi · ‎2010-09-06

Thanks Qing, that was really helpful.