SQL Server with Mr. Denny

Dec 7 2009   11:00AM GMT

What you can and can’t do with SAN replication

Denny Cherry Denny Cherry Profile: Denny Cherry


SAN replication is a very power tool which will let you get real time (or near real time) backups of your database on another location on your storage array, to another storage array at the local site, or to a remote storage array at another site.

However like all technologies there are some things which it is really good at, and some things which it can’t/shouldn’t be used for.  (Everything here is going to be SAN vendor dependent.  This includes features, modules you have to buy, terminology, and probably some other stuff I’m not thinking of.)

What it’s good for

SAN Replication is great for getting a real time (or near real time) backup of a LUN or several LUNs to another site or another array for local backup and DR.

SAN Replication is great for setting up a geographically distributed cluster (it’s required for it in fact).  This allows you to have the nodes of your cluster spread out between two (or more if you are really brave and/or crazy) different facilities.  Typically if you are doing synchronous replication between the arrays (this is at the storage level, not the SQL Server level) then 100 km is about as far as you want to put the facilities.  Otherwise the latency of waiting for the data to travel between the facilities starts to get to high.  If you are using asynchronous replication you can place the sites further apart as the data will commit to the local SAN, then be replication to the remote SAN after the fact.

Making a copy of your database for your DEV/QA systems.  If your DEV or QA systems are setup on the SAN, the fastest way to refresh those systems would be to make a clone of the production database, and attach the clone to the Dev/QA systems.  You could even script this out (depending on SAN vendor) so that it happens automatically or an the click of a button.

What it isn’t good for

Setting up a read only copy of the database.  While the clone or snapshot or mirror that you setup is possible mountable, the SQL Server won’t like working with those files.  Even if the SQL Server is able to mount the files as read only, it isn’t going to like the blocks that make up the files changing when it isn’t changing them.  It’s a sure fire way to have the database go suspect as soon as the checksums in memory and the checksums on disk don’t match.

That’s pretty much the only think I can think of that they aren’t all that good for.

That being said, depending on your SAN vendor and the options they provide you you can make a copy of the database, and make it readable.  If you take a clone of the LUN (I’m using EMC terminology here as that’s what I know best) which gives you a full copy which is contently updated.  You can then take a snapshot of that clone, make the snapshot writeable and mount the snapshot to the system which you want to read the data from.  The SQL Server can then mount the databases on this write-able LUN as a write-able database(s) (or read only depending on how you setup the instance to attach it/them).  Doing this will increase the IO on the backup LUN(s) as the written to blocks are copied to the clone, with the original blocks being copied to the snapshot so that the snapshot stays consistent.

Hopefully this give you some good ideas on some things you can do with your storage array.  The capabilities of your Storage Array may be different, so check with your Storage Admin as to what your array can handle.


2  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • JimWalsh
    Things have changed since 2009. Now SQL clusters are much more powerful. SANs are becoming redundant for DB replication?
    10 pointsBadges:
  • Denny Cherry
    Yeah, things have changed a little bit. These days, most databases and applications should be using Always On Availabilty Groups for Site to Site mirroring (and local HA as well).  There are some systems which don't support AGs today and those need FCIs with SAN replication, but those are fewer and fewer every day.
    69,125 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: