Friday, April 27, 2012

SQL Development and Testing magic, brought to you by NetApp

I started supporting and deploying NetApp storage systems about 5 months ago.  I still am *very* far from being an authority on the subject, but I have been playing around with the kit and am learning an immense amount daily.  That said, I never seize to be amazed at how trivially easy NetApp technology make traditionally time-consuming and difficult tasks.  Take the following situation:  You have a big old production SQL DB, and your developers need to test against an up-to-date instance of it.  I bet the tired old way that your business is doing it is by taking a 500GB backup, pushing it across the network, or sneaker-netting it via USB or even worse restoring from tape (blegh!).

No more.  With a NetApp SAN I can do this in exactly 23 seconds (and this on a low-end FAS2040!).  It costs me zero storage*** on my SAN and all it takes is you putting on your PowerShell hat and typing one single line.  The only assumption I make here is that you are running SnapManager for Microsoft SQL Server on your SQL box (and you should!).  This gives us access to all the PowerShell goodness that makes the magic happen.  The following command (watch for wrappage):  

clone-backup -svr prod-sql-001 -Database big_prod_db -TargetDatabase yourdatabase -TargetServerInstance dev-sql-001 Backup sqlsnap__big_prod_db__snapshots

  1. What is happening behind the scenes, you ask?  Quite simple actually:
  2. It created a FlexClone of the Snapshot of the production store
  3. Created a DB on dev-sql-001
  4. Mounted the FlexClone as a mountpoint
  5. Attached the files to the database
  6. Made toast and coffee

But that's not all.  This example brought our development box up to the same point in time as production, but you can also specify a previous a point in time to go back to.  Of course you need to have snapshots of your chosen point in time.

Wow.

***It will start consuming space once you start making changes in dev, because you are diverging from the cloned snapshot.