I need to purchase a new server to run our primary SQL based application on.
I could use advice on the following:

First some facts about our existing install:
SQL 2000 Std.
Database is about 40GB
OS on RAID 1 partition
SQL program, Data and Logs on RAID 5 partition
Planning to put OS on RAID 1 partition, logs on different RAID 1 partition,
SQL on RAID 5 partition on a rack server with 10 HDD - 2/2/6 (hard to find .
.. . )

1. How do I determine how active my database is (reads/writes)? How do I
use that determination to decide how big to make the partition the logs will
reside on?

2. I'm reading that RAID 5 is not good enough, I should be looking at RAID
10. Our application runs a little slow now and aside from the fact that the
logs are residing with the application and data I'm not sure what else it
could be (the server was oversized for what the software developer suggested).

3. Why does tempdb need to go on its own partition and can it share space
with the logs?

4. Do I simply drop the tempdb database file on a partition of its own and
then attach it through Enterprise manager to accomplish putting it on its own
partition?

5. Do the logs and tempdb need to go on a mirrored partition? They'll be
backed up every night.

6. I glanced at an article about running multiple instances of SQL but
don't know if this is something I should be looking at. Should I?

7. We added a couple of SQL databases to our server once it was set up -
they were just added under 'Databases'. Under what circumstances should I
set up different SQL server groups or different SQL servers (in EM)?

8. Will I have an issue adding my SQL 2000 databases to SQL 2005 on the new
server?

Thanks for the . Sorry about all of the questions but I don't have much
choice other than do to this project on my own - can't get approval for a SQL
consultant.

Po

See answers in-line:

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"powlaz" <powlaz@discussions.microsoft.com> wrote in message
news:1D9B0E6F-1340-44A1-BE71-6A4E56CA9528@microsoft.com...
>I need to purchase a new server to run our primary SQL based application
>on.
> I could use advice on the following:
>
> First some facts about our existing install:
> SQL 2000 Std.
> Database is about 40GB
> OS on RAID 1 partition
> SQL program, Data and Logs on RAID 5 partition
> Planning to put OS on RAID 1 partition, logs on different RAID 1
> partition,
> SQL on RAID 5 partition on a rack server with 10 HDD - 2/2/6 (hard to find
> .
> . . )
>
> 1. How do I determine how active my database is (reads/writes)? How do I
> use that determination to decide how big to make the partition the logs
> will
> reside on?

First off you should not use the term partition. A partition is just a
logical seperation and you want a physical one at the array level. You want
a Raid 1 that is a single partition with nothing on it but Log files. You
decide how large of drives to use in the Raid 1 by the size of the exisitng
log files plus some room for growth. You can use Perfmon counters like Batch
requests per second and Transactions per second to get an idea of activity
but it is tough to break down into reads vs writes.


> 2. I'm reading that RAID 5 is not good enough, I should be looking at
> RAID
> 10. Our application runs a little slow now and aside from the fact that
> the
> logs are residing with the application and data I'm not sure what else it
> could be (the server was oversized for what the software developer
> suggested).

I/O is just one reason for slowness. You need to find out where the bottle
necks are before you start throwing hardware at it. A good place to start
would be looking at the file & wait stats.
http://www.sqlmag.com/Article/Articl...ver_96513.html
http://www.sqlmag.com/Articles/Artic...6746.html?Ad=1

> 3. Why does tempdb need to go on its own partition and can it share space
> with the logs?

Again not a partition but an array. You want physical seperation. But this
is only necessary if you have lots of tempdb activity. Again you can find
this out with the filestats and thru the perfmon counters. But you can
share the tempdb log file with the user db log files on the Raid 1.

> 4. Do I simply drop the tempdb database file on a partition of its own
> and
> then attach it through Enterprise manager to accomplish putting it on its
> own
> partition?
You use ALTER DATABASE to move it.
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408.aspx Moving system dbs
2005


> 5. Do the logs and tempdb need to go on a mirrored partition? They'll be
> backed up every night.

You don't backup tempdb as they are rebuilt from scratch each time the
server restarts. But you do want them to be on an array with redundancy. See
previous answers.

> 6. I glanced at an article about running multiple instances of SQL but
> don't know if this is something I should be looking at. Should I?

Not unless you have a valid reason and have thought it out. If you don't
know why you should you should probably stick with a single instance.

> 7. We added a couple of SQL databases to our server once it was set up -
> they were just added under 'Databases'. Under what circumstances should I
> set up different SQL server groups or different SQL servers (in EM)?

Personal preference but I usually separate dev from prod etc.

> 8. Will I have an issue adding my SQL 2000 databases to SQL 2005 on the
> new
> server?
No

> Thanks for the . Sorry about all of the questions but I don't have
> much
> choice other than do to this project on my own - can't get approval for a
> SQL
> consultant.

Sounds like you could really use a good one to get you started on the right
foot.

>
> Po