SQLskills Training, Day 2: Here’s Jonathan

Today starts the second day of SQLskills training, and this day is all Jonathan ( b | t ).  That means we’ll be learning about things like SANs, the SQLOS and Extended Events.  It’s a big day.  One thing I’m looking forward to is hearing Jonathan explain how a SAN takes a backup of a database (quickly) using snapshots.  I mentioned this last week on Twitter and it sparked a great discussion with Denny Cherry ( b | t ), Allan Hirt ( b | t ), Argenis Fernandez ( b | t ) and Amit Banerjee ( b | t ) about how it works.  I’ll do my best to write it up today, but don’t hold me to it as we’re heading out to dinner today after SQLSentry does their presentation.  It will be a long day 🙂

10:00 PM

This morning Jonathan covered SANs and IO testing.  He has pictures of SANs and the paths between servers and storage in his slide deck.  I love those kinds of pictures.  In the afternoon he covered the SQLOS, and that material is still swimming around in my brain.  There are demos coming later in the week that will solidify what we covered, so I’m waiting until then to post any more.  However, that doesn’t mean I don’t have a lot to say!

In covering SANs, there were a few slides about Snapshots…not database snapshots, as you can take in SQL Server Enterprise Edition, but SAN Snapshots.  My beef with SAN Snapshots is this: they are not a backup of your database.  I define a backup of a database as a copy of the database that you could restore and access.  That means every single used page in the database should exist in the backup file.  I get frustrated when I talk to customers who want to use SAN Snapshots as backups, because they tell me that the Snapshot is the backup.  If we happen to be onsite when the SAN vendor is there, and I ask how the Snapshot is backup, I don’t get a straight answer.

Here is what I can now explain to the customer…  SQL Server supports VSS, which is Volume Shadow Copy Service (read more here, and there are better articles that Amit sent me that I need to find).  A third party application, such as a SAN vendor, can utilize VSS to create a snapshot.  When you initiate the snapshot, a quiesce command is issued by the SAN which freezes the I/O to a specific SQL Server database.  Once the I/O is frozen, the SAN gets a crash consistent snapshot point (this occurs within a few seconds ideally).  When that is finished, SQL Server will “thaw” the I/O, which releases the database so that the files can be written to again. 

Now, once you have the snapshot, it works just like a SQL Server snapshot.  It still points to the original database pages in the database.  When a page changes, that page is copied to the snapshot.  The more pages that change as you issue transactions against the database, the more pages are copied to the snapshot.  At this point, the snapshot is not a backup of the database.

You can even mount that database to a SQL Server instance on a different server and connect to it.  You can query it!  Hello reporting database that you can refresh each night, thank you Pramod for mentioning it.  Note that if you do this, while you are no longer using production CPU and Memory, you are still issuing I/O against the production database.  If you can login to and query the database, that snapshot is still not a backup of the database.  But what you can do at that point is back it up via SQL Server or another third party tool.  And at that point, you have a backup.

In addition, you can have the SAN use the snapshot to create a copy of the database, and in that case it will read all the pages from the active database (to which the snapshot points) and you will get a point in time copy (or backup) of the database.

The take home message?  You still need regular SQL Server backups!  A SAN snapshot does NOT give you point in time recovery.  It gives you the ability to recover to a specific point in time.  And, as Jonathan reminded us, if you lose the SAN, there goes that “backup.” 

Of course I had to bring this up at dinner tonight, and Denny said, “Anything that happens instantaneously is not a backup.”  Well said, Denny, well said.

 Other random things I learned today:

• Greg, who works at the Courtyard, is awesome.
• If you open a bag of Skittles next to a bag of M&Ms, and you happen to grab one of each from the little pile, it does NOT make for a good flavor combination.
• Robert Davis ( b | t ) has really good analogies, but I think he needs to watch a few episodes of Phineas and Ferb.