Newbie Question - moving DB to new SQL Server

I need to move our existing DB from our old production server to a new
server.
More information:
- We are an 8-5, M-F operation, so downtime is allowable after-hours.
- The DB is about 14GB
- The current production server runs everything on a single system volume.
- The new server has a RAID1 set for the system, and a RAID5 set for the DB
files.
I understand from my reading so far that detach/attach might be the best
option for us. What I haven't found yet is a clear discussion of the impact
of changing the location of the DB files relative to where they used to
reside. In other words, I can't tell if detach/attach will make it either
automatic (best-case) or reasonably doable (perfectly acceptable) to update
all pointers to the new path of the database files.
Does anyone have links to resources that discuss this? My searches have
yeilded some good stuff so far, but still not quite what I'm looking for.
If it matters, the old server runs 2000 Server, the new server is running
Server 2003. The new server will have a clean install of SQL 2000 SP3.
Thanks in advance,
Bryan
Hi,
See the below URL's
http://support.microsoft.com/default...b;en-us;224071
http://support.microsoft.com/default...b;en-us;314546
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q240872
Thanks
Hari
SQL Server MVP
"Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
news:%23$%23Lsx6lFHA.360@.TK2MSFTNGP09.phx.gbl...
>I need to move our existing DB from our old production server to a new
>server.
> More information:
> - We are an 8-5, M-F operation, so downtime is allowable after-hours.
> - The DB is about 14GB
> - The current production server runs everything on a single system volume.
> - The new server has a RAID1 set for the system, and a RAID5 set for the
> DB files.
> I understand from my reading so far that detach/attach might be the best
> option for us. What I haven't found yet is a clear discussion of the
> impact of changing the location of the DB files relative to where they
> used to reside. In other words, I can't tell if detach/attach will make
> it either automatic (best-case) or reasonably doable (perfectly
> acceptable) to update all pointers to the new path of the database files.
> Does anyone have links to resources that discuss this? My searches have
> yeilded some good stuff so far, but still not quite what I'm looking for.
> If it matters, the old server runs 2000 Server, the new server is running
> Server 2003. The new server will have a clean install of SQL 2000 SP3.
> Thanks in advance,
> Bryan
>
|||"Bryan L" wrote:

> I need to move our existing DB from our old production server to a new
> server.
> More information:
> - We are an 8-5, M-F operation, so downtime is allowable after-hours.
> - The DB is about 14GB
> - The current production server runs everything on a single system volume.
> - The new server has a RAID1 set for the system, and a RAID5 set for the DB
> files.
> I understand from my reading so far that detach/attach might be the best
> option for us. What I haven't found yet is a clear discussion of the impact
> of changing the location of the DB files relative to where they used to
> reside. In other words, I can't tell if detach/attach will make it either
> automatic (best-case) or reasonably doable (perfectly acceptable) to update
> all pointers to the new path of the database files.
> Does anyone have links to resources that discuss this? My searches have
> yeilded some good stuff so far, but still not quite what I'm looking for.
> If it matters, the old server runs 2000 Server, the new server is running
> Server 2003. The new server will have a clean install of SQL 2000 SP3.
> Thanks in advance,
> Bryan
>
Hi Brian,
Just a quick question, is there any reason why you're using RAID 5 on your
new server for your database files? I believe (and I may be wrong so anyone
else who knows better please do correct me) that RAID 1+0 is the best way to
go for database files.
Our SQL Servers here are all operating on RAID 1+0 in 3 sets of 2 discs.
One for OS; one for SQL Server .exe files and data files, and one for
Transaction Log files. This seems to serve us pretty well from a performance
point of view.
Cheers,
Ian
|||did you hear "=?Utf-8?B?SWFuIE11cnBoeQ==?="
<IanMurphy@.discussions.microsoft.com> say in
news:7A234F86-809B-4C2E-8F85-7B189450CE27@.microsoft.com:

> Just a quick question, is there any reason why you're using RAID 5 on
> your new server for your database files? I believe (and I may be
> wrong so anyone else who knows better please do correct me) that RAID
> 1+0 is the best way to go for database files.
while I agree, there is a cost issue that may prevent RAID 1+0 (or 0+1 -
they are different). 3 RAID 5 disks will be cheaper than 12 (in your
case) RAID 1+0 disks.
Also some SAN solutions mitigate the need by doing a virtual mirroring or
striping before adding in a RAID 5 volume.
my 2
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||did you hear "Hari Prasad" <hari_prasad_k@.hotmail.com> say in
news:e4sYt26lFHA.1372@.TK2MSFTNGP10.phx.gbl:

> Hi,
> See the below URL's
> http://support.microsoft.com/default...b;en-us;224071
> http://support.microsoft.com/default...b;en-us;314546
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q240872
>
> Thanks
> Hari
> SQL Server MVP
additionally:
http://vyaskn.tripod.com/moving_sql_server.htm
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||> Hi Brian,
> Just a quick question, is there any reason why you're using RAID 5 on your
> new server for your database files? I believe (and I may be wrong so
> anyone
> else who knows better please do correct me) that RAID 1+0 is the best way
> to
> go for database files.
> Our SQL Servers here are all operating on RAID 1+0 in 3 sets of 2 discs.
> One for OS; one for SQL Server .exe files and data files, and one for
> Transaction Log files. This seems to serve us pretty well from a
> performance
> point of view.
> Cheers,
> Ian
Yep. The reason is, the server was ordered and configured with a RAID 5 set
before I became fully aware that RAID 1+0 was being used in the Enterprise.
I would not at all mind running RAID 1+0 instead. I actually ordered two
new servers, and I can't remember exactly how they came configured; it may
be that I can rearrange the RAID levels and swap drives around so I can run
RAID 1+0 for my DB Files. I'm afraid, though I can't manage another 1+0 set
just for the transaction logs; would it be better to keep those on the 1+0
set with the DB files, or put them on the OS RAID1 volume?
Although drives are cheap, I don't think management would look too kindly on
ordering additional new drives for the new servers we just barely began
using. We are supporting fewer than 30 users; is there threshold below
which separating the logs out to a separate volume doesn't really gain you
much in performance?
Bryan
|||...just a brief comment...
We are currently looking at a reconfguration/upgrade of our Storage used for
our major SQL server. We are using an IBM FastT 650 Turbo storage and had a
meeting with a storage expert from IBM. He gave us some recommendations of
how we could reconfigure the storage, but on top of that he said that the
performance difference between RAID 5 and RAID 1/10/0+1 is getting smaller
and smaller with a modern storage. We actually ended up putting our Database
on a RAID 5 arrary and then the logs on a RAID 1. Actually he wouldn't be
afraid of having the logfiles on a RAID 5 array as well. Placing the log
files on a RAID 10/0+1 would in his opinion be overkill from a performance
point of view.
This might be different depending on how the usage are on your system (we
have far more reads than writes...) and also if you are using internal
disk/controllers and if you are using an external storage.
Regards
Steen
Bryan L wrote:
> Yep. The reason is, the server was ordered and configured with a
> RAID 5 set before I became fully aware that RAID 1+0 was being used
> in the Enterprise. I would not at all mind running RAID 1+0 instead. I
> actually ordered two new servers, and I can't remember exactly how
> they came configured; it may be that I can rearrange the RAID levels
> and swap drives around so I can run RAID 1+0 for my DB Files. I'm
> afraid, though I can't manage another 1+0 set just for the
> transaction logs; would it be better to keep those on the 1+0 set
> with the DB files, or put them on the OS RAID1 volume?
> Although drives are cheap, I don't think management would look too
> kindly on ordering additional new drives for the new servers we just
> barely began using. We are supporting fewer than 30 users; is there
> threshold below which separating the logs out to a separate volume
> doesn't really gain you much in performance?
> Bryan