1

In the process of getting some new magic and high speed SAN devices I was told by those crazy magic-SAN-things-administrating IT people that it will be obsolete to perform ANY Sql-Server Backups in future, as soon we start using this netapp snapmanager snapshot magic. Unfortunately I was not involved in product selection process or any meetings or demos.

So the one person that still is very sceptic about this and not yet able to be a part of all those excited and euphoric celebrations is - me... :-)

I do not really know much about database snapshots and (because) did not really use it so far, and I do not know if this netapp snapshot is the same snapshot/technology that sql supports... But if so, I fear that any of those snapshots are pretty useless as soon we have lost our source database, right?

So, is it really true, that we can replace database backups, transaction log backups and any other backups using those products and technology and still stay able to respond to point in time recovery requests for our databases?

1

"Just" making a snapshot of volume on your Netapp can not be considered the correct way of making a backup of SQL server databases.

However, Netapp offers Snap manager for SQL Server

It's not a free Netapp option, so you need additional licenses. But with this solution you are able to create backups in the correct way. Since Snap manager interacts with SQL Server to coordinate an transactional consistent backup.

Snap manager does have certain demands with the disk layout. You need seperate LUNS for your system databases, your temdb, your user db data files and your user db log files. Additionally you'll need an extra lun for snapinfo. So for existing SQL Servers that migrate to Netapp or for server where you want to use Snap manager as a backup strategy this might be an extra hurdle to take.

However, especially when you have large databases, using storage based snapshots can be very benificial in regards to backup and restore times. Also consider scenario's where you need to refresh copies of acceptance and test. This can be a lot faster with snapshot technology.

Further reading on how Snap manager works.

  • Solved it exactly this way. – Magier Mar 31 '17 at 5:53
  • @Magier great to hear! – Edward Dortland Apr 7 '17 at 14:03
4

Not at all. A SAN snapshot is a bitwise copy of raw disk state. If and only if all the responsibility for business continuity is on SAN storage team, this might be acceptable. For most of the business cases, this will present such RPOs and RTOs that the solution is not going to be feasible. What's more, restoring a SAN snapshot to a stable state is problematic. If the server was up and running during the snapshot, the server will return to state like someone hit a reset button. That is, a dirty filesystem, incomplete transactions and stuff. To get a clean snapshot, the server must be shut down a priori - and that's a business outage.

With SAN snapshotting only, you'd miss at least point in time recovery, as all the databases must be in simple recovery model due missing log backups. This is a killer for most recovery objectives.

Denny Cherry has written a whole article about failure points.

  • Adding to that - You have no way of knowing if the snapshot is consistent or not until you restore the snapshot and run checkdb on that. The snaphot backup providers, netapp / veeam to name some have nothing in place that does a backup that checks the data pages checksums – Spörri Oct 11 '16 at 9:30
  • @Spörri You could argue if this is a bad thing. I would be very happy if more customers of mine would more frequently restore their backups and run a consistency check against that. At least with snapshot technology you can take away the pain of the mightly long restore times or simply the space needed which prevent you from doing this frequenty. – Edward Dortland Oct 11 '16 at 10:03
  • And I am much more happy being able to run backups that do check the page checksums and being able to verify the backup check-sums without a full restore. There are other issues with snapshot backups of databases the dirty shutdown one mentioned in the original answer, the loss of point in time restore functionality - which can be an essiental feature. The need to always do full restores and not being able to do page restores etc. – Spörri Oct 11 '16 at 10:20
  • @Spörri the original answer is incorrect with regards to dirty shutdown and loosing point in time restores. Both statements are false when using Snap manager for SQL Server. – Edward Dortland Oct 11 '16 at 18:50
  • @EdwardDortland I'm not familiar with Neapp's solution. I've worked mainly with HP EVAs and there the only snapshot is raw LUN state. Does Netapp manage full recovery mode's transaction logs as well? – vonPryz Oct 12 '16 at 5:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.