SAN Snapshots of SQL Server databases

By tom on March 20th, 2009

Some time ago I had to advice a client about a new SAN infrastructure and associated to this, look for a new backup and restore strategy for Exchange server and SQL server.

While researching this I saw some benefits that come with SAN snapshots:

  1. Very quick full backups and restores:
    Modern SAN/NAS devices are able to create a snapshot of a LUN within seconds. This is because actually not all data is being copied, but just the reference pointers to the data (you can compare this with a book, where only the table of contents is copied, not every page in the book)
     
    This also means that restoring a snapshot is as fast as taking the snapshot: only the reference pointers to the data are being restored (or to complete the analogy: the table of contents of a book is being restored, after pages have been added to the book)
  2. Space efficient backups:
    Because (as stated in point 1) only the reference pointers are backed up, the size of the backup is just fractions of the whole file. This means that a full database snapshot only takes a couple of MB’s even if your datafiles are GB’s large.
  3. Create database clones very quickly:
    Almost every DBA gets from time to time the question to put a copy of a production database available for testing. Normally you would have to restore a copy of the whole database. When using snapshots this no longer is the case: you can have a previous snapshot available in seconds, without having the need for all the additional storage space.

Underneath I’ve copied an example of how NetApp is integrated with SQL Server.
(SMSQL stands for NetApp’s SnapManager for SQL).

In a next post, I’ll describe the whole process of setting up a backup process using snapshots using NetApp hardware and NetApp’s SnapDrive and SnapManager.

Don’t hesitate to comment.

Tom

SQL Server 2008 and virtualization

By tom on March 20th, 2009

Recently I had the chance to play around with some virtual machines in a farm that was being used for stress tests. This design farm consists of 2 HP DL580 G5 machines with 4 quad-core CPU’s and 64GB memory, running ESX 3.5

I installed SQL 2008 on a vm (4vCPU, 8GB memory) and ran some benchmark tests. Some of the tests were performed when the farm was under heavy load, the other part of the tests were done while the virtualized SQL server could make full use of his given resources.

As you can see in the graph above, the number of transactions per second were quite good when the farm’s resources weren’t overcommitted, but this quickly degraded when the stress test was running.

The response times degrade also very quickly when available resources were overcommitted.

I am not against virtualizing SQL, but keep in mind that performance can quickly decrease when resources on the hosts are overcommitted. Try to test as much as possible, so you know how much workload your server can sustain in the worst case.

And if you have the option: ask to place your virtual SQL server in a farm where there is a QoS program, so that you are always sure that your server can make use of his full resources.

Please feel free to comment!

Tom