Moving a SQL 2000 installation? (1 Viewer)

[Grunners]

Hi all,

I've done a bit of searching on this one and I reckon it might be quite easy, however... Some reassurance from people who use this particular forum would be much appreciated!

I've got a primary partition 'K' on a physical drive and then an extended partition with a logical drive 'J'. They're about 60Gb each and SQL 2000 is installed on 'J'. This would be really easy if SQL was on K as I could simply delete the extended partition and resize the primary partition, but then hindsight's a wonderful thing! ("640k will be enough for anyone" comes to mind)

A new drive is in the server, just the one primary partition. My question...

If I simply stop SQL server and all related services, move the files to the new (and larger) drive and mount that as 'J' will it all work when I start the services again? I can simply unmount the old J drive and delete the partition.

I'm not wanting to move the installation to a different drive letter you understand, just move it to a larger drive and assign the old/existing drive letter.

Any comments MUCH appreciated!

 

[SQL_Hell]

Hi there,

I have never done this, but so long as you stop the services and ensure the drive letter is the same then I think it will be ok.

However make sure you backup all your databases, including the master and msdb system databases, should you need to re-install then you will need master and msdb to return the server to it's original state. Make sure the backups are on tape /disk / backup server.

also make a note of your service pack level.

 

[Grunners]

Mr Hell!

Thanks very much for your reply.

After stewing on it for a while the only other thing I can come up with is if somewhere deep in the registry is a setting that tells SQL which piece of physical hardware the drive is. I.e. I'm moving to another drive of the same letter but it'll be a different array/virtual drive/SCSI ID etc.

I think it may be best to test this on a spare server before the big day - or night!

Thanks again.

 

[pdx_man]

Why are you wanting to move the installation? I would leave the installation alone and relocate the data and log files.

But ... If you are really wanting to move the installation, detach all of the databases, move the data and log files to a drive different from the new installation, uninstall SQL Server, re-install SQL Server and then attach the data and log files.

Ideally, you would have 3 seperate drives:

C:\ Operating System & SQL Application
D:\ Data Files (*.mdf)
E:\ Log Files (*.ldf)

This helps with contention issues of data and log files being written to at the same time. There are a lot of write-ups on the net about the best methods of installing SQL and the location of the data and log files. I am a big fan of www.sql-server-performance.com.

 

[Grunners]

PDX,

I've been thinking along those lines and it's probably the option I'll go for, i.e. relocating the data and log files. I guess my brain likes to have everything in it's own space - access databases on one drive, sql on another and documents etc on another.

This said it's daft and if I can increase performance then I'll go this route.

Many thanks for the reply and I WILL be following the link!

 

[SQL_Hell]

I agree with what PDX has said here, within my enviroment at work I use the same methodology.

We have data files on D drive and log files on T drive (cunning naming convention I know ).
I also move my system databases (master model msdb) to D and T drives and typically use raid 5 for data and raid 1 (or raid 10) for log files. You can also consider moving the tempdb to another drive as well.

here is a nice article on sqlserver performance.com

http://www.sql-server-performance.com/rc_hardware_planning.asp

I think testing on a spare server is good idea, as it will give you practice for the actual move.

Good luck

 

[Grunners]

Many thanks to you both.

I've decided to take the easy route however. I'm getting a guru in for a couple of hours to go through it first!

Testing on the spare server went well though. Just using Paragon hard disk manager (that can cope with Raid 1) enabled me to move the data/log files around various drives as long as I finally mounted it with the same letter it started with.

However now I'm reading the forums you've both suggested, and realising I can get a bit of a perfomance boost - I want to do it properly. Heck I've learn't more in the last 24 hours than I have all week! (Must work harder... )

Kind regards