SQL Server 2008 & LUN Cloning/Fracturing

[Canelek]

Any of y'all managed storage folks have any experience working with DBAs on cloning LUNs containing SQL Server database files?

I have recently been testing along with a VMware and SAN colleague. So far, the test was simply cloning/fracturing one LUN and copying it to another location attached via virtual HBA to my second test SQL Server instance. I keep all user and system databases on the single LUN as phase 1 testing...phase 2 will involve cloning LUNS from different instance locations and attaching them to a single target...of course, in this instance, I would keep system databases out of the cloning process...

So, when the initial test was done (all system and user databases included in the clone), all I had to do was restart the SQL Server services to initialize the instance and reset tempDB, drop and add the SQL Server instance name (since master database was included), restart the services again, and all seemed fine. Oh, also had to update msdb database information with the new instance name...

So, that seems to cover the bases for the 1 to 1 clone...

Possible issues, etc... it seems pretty straightforward, actually. On my end (DBA), I just have to architect the file locations correctly across the LUNs. On one hand, I can keep system databases out of the equation, and simply script to loop through the MDFs and LDFs in the freshly-cloned LUN and attach the new database files to the SQL Server instance. On the other hand, I can do what I had mentioned before--perhaps using startup procs and AutoSys to automate the process...

We have a EMC SAN, btw...


I guess my question is: Am I missing something? Is there a super-simple way to do this other than what I just spewed out?

Thanks!

[pyrella]

From the system side, it just shouldn't matter - a disk is a disk. Most important thing will be drive letter and path. When you go to reattach your DB - you usually specify drive n path anyways - if it happens to be the same on the new box, you're good to go?

Or maybe I'm not understanding the question fully?

Perhaps set up a publisher/subscriber link for cloning your data or some other form of site replication? You can also do snapshots of one and mount on another. If looking for HA beyond just what vmware will offer with vmotion for hardware HA - there's now support for doing MS cluster with internal heartbeat, and the older method of doing the shadow cloning so you can do windows/sql updates while still maintaining 5 9's of uptime.

[Canelek]

In scenario 1 (1 to 1 LUN cloning), the drive letter has to be the same, since MDF/LDF file paths are recorded in the master system database. For cloning LUNs from multiple environments to attach to 1 instance, I can code my scripts to simply find where the files are and dynamically build the attach procs.

As for using SQL Replication, I am not a huge fan due to the administrative overhead. Plus, transactional Replication has to have individual objects within a database "published" in order to be a part of replication.

My interest in taking advantage of a hardware solution lies in the need for QA/UAT SQL Server instances to be "refreshed" in a quick fashion (this is discounting additional steps to obfuscate/scrub sensitive data, of course). As it stands now, I have to get a refresh list, build my restore scripts, move my backup files, restore, integrity check, resove any SID issues with SQL Logins, change database ownership, recovery models, blah blah etc.

My hope is to design the flow so that we take full advantage of IO speeds with the SAN infrasctructure. VMoion is cool as well, but most of our heavy-hitting SQL Server instances are physical. My total data load is around 8 TB or so...

[pyrella]

Sounds like snapshots that can be seen by the QA boxes may be the way to go. Our DBA's have been using our backup process as their cloning tool of choice - just restore to a different target - 8TB across the wire will be painful tho unless you're on 10GB.

[Canelek]

You mean VMware snapshots? I would be down for giving that a whirl. I really appreciate the input--I hate to have to trial and error on something like this. SQL Server DBAs tend to be leery of automation outside of our own processes due to past iterations being severely limited.

Slowly, but surely learning that even Microsoft can play nice these days...

That said, I am just happy that IBM isn't forcing us to go DB2, etc...at least not yet!

However, we are having to migrate to Lotus Notes in July...that will be odd for me. At least it is all SMTP on my end of things, so any email usage coming from my world doesn't need overhaul (until the new re-branding happens...)

IBM--Building a Smarter Planet (at the expense of its employees)

[pyrella]

Hmm if the data/drives in your luns are part of the vmdk you should be able to use and migrate snapshots - but if they are raw attached then the only thing you'll get out of the VM snapshot is your OS and apps - all data drives would still need to be connected (whether it be a cloned lun or whatever)

Sounds like ultimately your process is about as refined as it will be in regards to your many to one portion of things.

[Ashur]

Talk to your storage architects about it... and move to Unix you nut!

[Canelek]

Thanks for sanity check, Py. I think I have a pretty decent plan moving forward. Now I need to wait until DCO gets a new VMware guru since they shitcanned the last one! NEED LAB PRS!

And Dale --> NO

[pyrella]

VMWare guys pointed this out to me in a meeting yesterday as we just recently updated our CX array to the latest firmware:

http://virtualgeek.typepad.com/virtual_ ... o-you.html

long story short - cloning/moving/etc can happen much much faster now due to being able to work around lock files and the like. Only caveat is target and source need to have same block size as far as I can tell.

What process did you end up settling on?

[Canelek]

My personal preference would be single server clone since I can just rename the SQL server instance and restart svcs and that's it. Many to one would involve some extra search/attach scripting and also would lead to the users asking for more and more. Need more testing either way.

[Ashur]

The problem w/ DB2 is the DB permissions are all tied up at the OS level, which is a right royal pain in the ass (depending upon how IDs are administered). That said, Unix stability is the win.

[Sabek]

The problem w/ DB2 is the DB permissions are all tied up at the OS level, which is a right royal pain in the ass (depending upon how IDs are administered). That said, Unix stability is the win.

Not having to reboot a UNIX box weekly is a plus

[Canelek]

Since Windows Server 2003, that whole frequesnt rebooting thing has faded quite a bit. It is more of a legacy reputation. I have found that Windows Server 2008 R2 is pretty damn stable. SQL Server 2008 R1 is very stable. Most of the performance issues I see are based upon shitty coding (excessive (nolock), nested subqueries/aggregations, etc.). This can cause quite a long blocking chain.

We will eventually get some DB2 RDBMS systems, just because we are owned by IBM. I'll hire a DB2 DBA when we get to that point.

I don't have a problem with Unix--it is just that I have been doing this stuff for so damn long, it would be silly for me to ditch Microsoft and be a n00b DBA again! --> I'll just hire people as needed, when we bring in the non-MS shit.