| Author |
Topic |
|
sqln00b
Starting Member
5 Posts |
 Posted - 2006-02-20 : 13:55:54
|
| I've been reading posts on here and searching Google about this. My SQL server is setup where program/binaries are in c:\program files, and the data directory is in D:. This server is a SQL cluster.I added some SAN storage this weekend and have presented the new drive letters to the SQL server. I've made them cluster/SQL aware, and now I want to move all the data off of D: to E:. I've read a couple posts on a Microsoft users newsgroup where people have just shutdown SQL services, copied the entire data directory with all of it's subfolders to the new drive, and then renamed the drive letters. Then supposedly you just turn SQL back up, and it doesn't know that anything has changed. Seems MUCH easier than doing detach/attach to everything and still wondering if everything was taken care of.Anyone here have experience doing this? My EMC engineer warned that cluster keeps track of drives based on disk signatures and not drive letters, but from what I've read, SQL is only looking at the drive letter. Thoughts?edit: here is one of the posts I found:http://groups.google.com/group/microsoft.public.windows.server.clustering/msg/4058b1b99f07de39 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-02-20 : 15:26:08
|
| you won't be able to rename your drive letters becuase part of the cluster software is installed on your shared D: drive. You will completely jack up your cluster if you do this.Microsoft has a utility that actually you can download that will allow you to swap cluster disks around, but this utility is normally used when you have a drive failure in a cluster. Anyway, using that tool is just as much work (actually more work) than using the detach/attach method.So, use the detach/attach method instead as it is far simpler. -ec |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-02-20 : 15:27:34
|
| also, the detach/attach syntax is very simple TSQL. Why are you trying to avoid a 10 second operation? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-02-20 : 15:33:44
|
| +1 to the detach / attach methodIt is easy, and relativly fast, assuming you've got a maintenence window in which to perform the detach / attach.Those that mess with the magic of MS Clustering will probably have problems. Doing anything that may mess up your cluster is never a good idea. If you ever have an issue with your cluster, in the back of your mind will always be "I wonder if that was because of that disk config change we did."Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
sqln00b
Starting Member
5 Posts |
Posted - 2006-02-20 : 16:15:31
|
| Ahh shoot you guys are no fun. Haha. Okay, so this is the method you're talking about right?http://support.microsoft.com/default.aspx?scid=kb;en-us;224071So once I do this detach/attach thing, it will move all the SQL data? There are several folders in the D: SQL directory like: DataFTDATAJOBSLOGREPLDATAThis is why I was looking for a way to just move everything in one swoop because I was afraid just moving DB's wouldn't get everything. The existing drive array is going bye-bye. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-02-20 : 17:27:26
|
| go ahead, do the drive switch thing you want to do. But I would have a resume ready just in case... :)detach/attach is as follows:1. detach your database(s)2. move datafiles to new location3. attach database(s) - note, you will of course be changing the datafile pathsThis will of course take a downtime, but the dowtime is short. Just the amount of time it takes to physically copy the datafiles from one drive to another. The attach command is very straightforward and fast.You might just want to create a dummy database on this same server. One that you can take offline at will. take that database offline and move it just to go through the steps needed for the real deal. That way you can get all the syntax down and not have to scramble. Oh, and before you do anything make sure you take a backup.-ec |
|
|
|
sqln00b
Starting Member
5 Posts |
Posted - 2006-02-20 : 18:10:20
|
| so what about all the other folders in that SQL directory I mentioned? What's in there and what happens after I detach/attach and then take the original drive offline? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-20 : 18:33:04
|
| "what about all the other folders in that SQL directory I mentioned?"Yeah, all your backups and other file-based jobs will need adjusting ..."Ahh shoot you guys are no fun"... all ours are parameter driven - based on a single central database table <SmugGrin>!Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-02-20 : 19:31:44
|
quote:
Originally posted by sqln00b
so what about all the other folders in that SQL directory I mentioned? What's in there and what happens after I detach/attach and then take the original drive offline?
here is a KB article with a link to the ClusterRecovery utility that I mentioned in an earlier message. It will do most of the work necessary to swap out your smaller D: drive for a larger D: drive.http://support.microsoft.com/kb/305793This tool does not copy any data, you will need to copy the contents of your original D: drive to the new drive prior to running this utility. Also, you will need to take your SQL server completely offline before you can copy the contents of your orignal D: drive to the new D: drive.Anyway, I actually recommend against you doing this, since it seems you are a junior DBA and have never done this kind of thing before. The detach/attach method is probably sufficient.-ec |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2006-02-21 : 00:53:26
|
| If your data files are large and you need to limit down time as much as possible, you might want to look at using backup/restore. If I need to keep down time as low as possible, I usually take my last full backup and restore the database to the new drive location with a different name. Then when it gets close to cut over time I take a diff and apply that in standby mode. Then at cut over time shut down the applications, take a tran log backup and apply it to the new database with recovery. Then either detatch or rename the old database and rename the new database and you're right back up. This also gives you the ability to quickly backout the change if you come up and see a problem when you test the new configuration. None of this is necessary of course if you have a relatively small database you're moving... I think this method becomes worth while when you start to get past 10 to 15 GB worth of database files.Sounds like you're mainly concerned with the data folder. By default that's where all of your database files wind up. The other folder support other features and you only need to worry about moving them if you are using them. FTData for example is the default folder for full-text indexes and REPLData is the default folder for replication. If you are not using these features you don't need to worry about them. HTH.Jeff Banschbach, MCDBA |
|
|
|
sqln00b
Starting Member
5 Posts |
Posted - 2006-02-21 : 04:08:20
|
quote:
here is a KB article with a link to the ClusterRecovery utility that I mentioned in an earlier message. It will do most of the work necessary to swap out your smaller D: drive for a larger D: drive.http://support.microsoft.com/kb/305793
You know, I saw this before when looking at KB's, but never followed the link and read the help file that comes with it. MS virtually recommends this method as a way of migrating your data to a new drive/lun/array/whatever. So I decided to give it a shot and while the documentation was a bit lacking on what do do with the second node of the cluster while you're following all of the steps, I got it up and running and SQL is flying on the SAN now! A full db backup took 25-30 minutes as opposed to an hour and a half like before. (the old scsi array was mucho slow)quote:
Anyway, I actually recommend against you doing this, since it seems you are a junior DBA and have never done this kind of thing before.
lol, "junior DBA?" I'm honored. No, that's far too much of a title to give me. Retard-DBA or non-DBA would be more like it. I know virtually nothing about managing SQL let alone what goes on inside of it. I'm just a server/network admin who is rusty on clusters and new to SANs trying to figure out how to move some data (hence my hardware/OS preferred method versus "inside SQL" method) for his application folks who know even less about SQL admin.If you have a SAN, I would recommend this method. Also, this shows that SQL doesn't care about anything other than the drive letter for where it's data lives. I bet aside from the disk signatures that clusterrecovery had to move, if it was a standalone SQL server install, it'd be as easy as shutting down SQL services and changing drive letters.  edit: and thanks a million to everyone!! |
|
|
|
CGSJohnson
Starting Member
2 Posts |
Posted - 2006-12-21 : 13:41:46
|
| I was just about to ask that question...if there is no clustering, is it just as simple as copying all the files to the new drives and then re-naming the old drives to something else and then re-naming the new drives to the original name of the old drives? Thanks.Chris |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-12-21 : 14:00:54
|
quote:
Originally posted by CGSJohnson
I was just about to ask that question...if there is no clustering, is it just as simple as copying all the files to the new drives and then re-naming the old drives to something else and then re-naming the new drives to the original name of the old drives? Thanks.Chris
Yes, but you must stop the SQL Server service during these steps. We've done this loads of times on our non-clustered boxes.Tara Kizer |
|
|
|
CGSJohnson
Starting Member
2 Posts |
Posted - 2006-12-21 : 15:48:23
|
| Yeah, I forgot to mention the stopping of the SQL services.Sweet! Thanks!Chris |
|
|
|
|
trick for moving SQL db folders to new drive?