August 26, 2003 at 8:25 am
We’re running out of hard drive space (sound familiar?) and need to reconfigure our servers. This is on our Production servers, so setting up new servers is not an option, and requiring days (let alone hours) of downtime will be very unpalatable to upper management.
What we have is an Active/Passive W2k cluster hooked up to a SAN with a number of cluster share volumes. The SQL files and system tables (all but tempdb, which gets its own disk set) reside on one of these volumes.
What we need to do is to move all the SQL Server files and system tables from this one volume to another (new) volume, without losing any information (user table location, SQL Agent jobs, SQL Error log files, extended stored procedure DLLs... the mind boggles). Obviously you can’t just copy (move) the files, because of the {Microsoft, COM object, registry} morass.
My current best guess on how to do this is to save the system files (combo of backups and copied .mdf/.ldf files), uninstall SQL Server, reinstall it in the desired location, and then restore/overwrite with the proper system files. I did something very like this once before [SQL Server 7.0 + SQL 2000 named instance on a box --- concatenated down to SQL Server 2000 default instance on the same box], and while it did work it was a colossal pain to work out. Worse, we’re not doing it, the techs of the firm that host our servers will be doing it, and-- based on precedent--I just can’t find it in myself to trust nameless and faceless individuals to do something this complex.
Has anyone ever done anything like this before? What approach or methodology would you take? Is there some MS utility that will do all this for you? Failing anything else, my current best plan of attack is: pay for Microsoft Technical Support and let them deal with it. But I hate admitting defeat this way.
Philip
August 26, 2003 at 9:02 am
You really don't have to move everything unless you have other reasons. Creating second database file on new driver for your user databases or move them to new driver with detach/attach.
August 26, 2003 at 9:55 am
I'm not concerned with our "user defined" databases--they're easy to shuffle around. (We virtually moved 40G of Production data physically across the country in under 5 minutes last year.) Nor am I worried about the system databases (master, msdb, model, tempdb); they're tricky, but doable.
I'm worried about the system files, the actual SQL Server executables, DLLs, log files, etc. found under \Program Files\Microsoft SQL Server.
The reason is, we're reconfiguring everything. Specifically, we add a bunch of new drives [drives/raid groups/luns/NT mappings], move everything on to these, blow away the old NT mappings and luns, format them, reconfigure them, and then move assorted files back to these "fresh" volumes. (This is all planning for the future, when our databases get seriously big.) Given the size of everything, we can't just set up a new system ($$$$$) and copy stuff over. And we get to do it twice, once for our OLTP cluster and once for our Reporting cluster.
[A note of sanity: the next few major database projects involve data archiving and purging, optimzed storage of "blob data", and a few other "hindsight is 20-20" modifications to our system. With these in place, we should eventually achieve steady state before we run out of hard drive space again.]
August 26, 2003 at 10:06 am
quote:
I'm worried about the system files, the actual SQL Server executables, DLLs, log files, etc. found under \Program Files\Microsoft SQL Server.
Uninstall and reinstall seems the only option.
August 27, 2003 at 7:48 am
Stop services rename the drive, name the new volume to the orig drive, restart services.
John Zacharkan
John Zacharkan
August 27, 2003 at 8:21 am
Binary files shouldn't be on the shared media... not on a standard install, or am I missing something? Moving system databases should function relatively similar to the non-clustered instructions... haven't tried it, though.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
August 27, 2003 at 10:07 am
Hey, Brian's right--the (key) system files aren't on the cluster. <Golux>I knew that once, but I forgot it.</Golux>
By the terms of our SLA, we have no access to the local drives of the cluster's boxes. They have set up some system-type files on the cluster share (SAN) volumes, but I cannot view their contents and so am not entirely sure what's going on.
However, with Brian pointing out the obvious, the problem is largely reduced to just shuffling the system tables, and that problem is easy enough to resolve.
Philip
August 27, 2003 at 12:15 pm
If space is your only issue, which is what it seems, then you only need to scale out your existing SAN implementation, add those volumes to your clusterdependencies, then add a new file to your filegroup that exists on this new volume. No reboots required, depending on your storage infrastructure...
Good luck!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply