May 5, 2015 at 1:41 am
Hi All,
Can we backup our cluster databases directly to tape using native backups (without using any third party tool) ?
It's SQL Server 2012 two node Active/Passive cluster.
One of the DB will be huge in size, hence checking if we can directly backup from the cluster instance to a tape.
Thanks,
San.
May 5, 2015 at 7:06 am
You absolutely can. Create a backup device and then backup to it. It can be a tape. But, understand, going directly to tape is much slower which could have all sorts of implications on your system in general and on any recovery process as well. Most people I know backup to disk first, then copy to tape. The exception being very large databases (5-10tb +) which usually use SAN systems to snapshot the disks rather than backup at all.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 5, 2015 at 7:39 pm
The exception being very large databases (5-10tb +) which usually use SAN systems to snapshot the disks rather than backup at all.
Snapshot of the disks is being done by respective team I believe.
So are you saying, we don't need database backups from sql server instance if the snapshots of the disks is done ?
And also in case of tapes in two node cluster, do we need to attach the tape device to both the nodes ?
Thanks alot.
San.
May 6, 2015 at 4:06 am
Joy Smith San (5/5/2015)
Snapshot of the disks is being done by respective team I believe.
So are you saying, we don't need database backups from sql server instance if the snapshots of the disks is done ?
If you're in the extremely large database sphere, you may find that backups running for 26 hours just doesn't work. Especially when restores can take 30 hours or more. Few businesses find this acceptable. So yeah, for very large databases, SAN snapshot & recovery is frequently the answer. BUT, this does mean point in time restores go away and this has lots of implications. This must be discussed with the business and planned for in regards to your recovery point objectives and recovery time objectives. Further, you have to make sure that your SAN snapshot capabilities are compatible with SQL Server. Some of them take this into account. Some don't. You need to test a restore from the snapshot to ensure you're not just corrupting your databases.
This is absolutely not a minor task.
And also in case of tapes in two node cluster, do we need to attach the tape device to both the nodes ?
Thanks alot.
San.
If you're in a cluster, you can't attach the tape directly to one of the machines and expect it to work in a failover situation. If the system is failed over because the server went down, nothing will be accessible. It would have to be attached to a different machine in this situation and accessed remotely (making it even slower).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 8, 2015 at 9:56 am
From Books online
SQL Server Books Online
Note:Support for tape backup devices will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Backing up SQL Server data to tape requires that the tape drive or drives be supported by the Microsoft Windows operating system. Additionally, for the given tape drive, we recommend that you use only tapes recommended by the drive manufacturer. For more information about how to install a tape drive, see the documentation for the Windows operating system.
When a tape drive is used, a backup operation may fill one tape and continue onto another tape. Each tape contains a media header. The first media used is called the initial tape. Each successive tape is known as a continuation tape and has a media sequence number that is one higher than the previous tape. For example, a media set associated with four tape devices contains at least four initial tapes (and, if the database does not fit, four series of continuation tapes). When appending a backup set, you must mount the last tape in the series. If the last tape is not mounted, the Database Engine scans forward to the end of the mounted tape and then requires that you change the tape. At that point, mount the last tape.
Tape backup devices are used like disk devices, with the following exceptions:
The tape device must be connected physically to the computer that is running an instance of SQL Server. Backing up to remote tape devices is not supported.
If a tape backup device is filled during the backup operation, but more data still must be written, SQL Server prompts for a new tape and continues the backup operation after a new tape is loaded.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 11, 2015 at 3:25 am
Thanks Grant & Perry
If you're in the extremely large database sphere, you may find that backups running for 26 hours just doesn't work. Especially when restores can take 30 hours or more. Few businesses find this acceptable. So yeah, for very large databases, SAN snapshot & recovery is frequently the answer. BUT, this does mean point in time restores go away and this has lots of implications. This must be discussed with the business and planned for in regards to your recovery point objectives and recovery time objectives. Further, you have to make sure that your SAN snapshot capabilities are compatible with SQL Server. Some of them take this into account. Some don't. You need to test a restore from the snapshot to ensure you're not just corrupting your databases.
This is absolutely not a minor task.
..Well, just one more doubt...
Storage is is saying they are taking an Image of the disk which is crash consistent only.
In this case, is there anything to be done from SQL server end so that SQL will work fine when the SAN image is restored in case of disaster ?
Thanks again.
San.
May 11, 2015 at 3:35 am
Joy Smith San (5/11/2015)
Thanks Grant & PerryIf you're in the extremely large database sphere, you may find that backups running for 26 hours just doesn't work. Especially when restores can take 30 hours or more. Few businesses find this acceptable. So yeah, for very large databases, SAN snapshot & recovery is frequently the answer. BUT, this does mean point in time restores go away and this has lots of implications. This must be discussed with the business and planned for in regards to your recovery point objectives and recovery time objectives. Further, you have to make sure that your SAN snapshot capabilities are compatible with SQL Server. Some of them take this into account. Some don't. You need to test a restore from the snapshot to ensure you're not just corrupting your databases.
This is absolutely not a minor task.
..Well, just one more doubt...
Storage is is saying they are taking an Image of the disk which is crash consistent only.
In this case, is there anything to be done from SQL server end so that SQL will work fine when the SAN image is restored in case of disaster ?
Thanks again.
San.
No. SQL Server is ignorant of underlying storage systems like this. The backup must be transactionally aware. If it's not using Virtual Shadowcopy as part of it's snapshot process, then when a restore is attempted, you have a high likelihood of corrupt databases, which makes the backups useless.
Not seeing your system or knowing anything about it, I can only advise that you ask for a test restore at some point and validate that it doesn't lead to corruption. The other option is to work with the SAN admin team to document exactly how the snapshot process is working and that it's compatible with transactions within SQL Server. Reputable SAN companies can absolutely assure you of this, or tell you it won't work. There shouldn't be any kind of doubt on this one.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 11, 2015 at 4:27 am
..OK..
Need to check if it's using Virtual Shadowcopy as part of it's snapshot process.
Thanks Grant.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply