We are running SQL 7 on a server, and are moving to a new server and will be
upgrading to SQL 2005 at the same time. Currently, both the old and the new
servers have two drives, one for programs and one for data.

With the current configuration, SQL 7 and the data are both installed on the
data drive, in the MSSQL7 directory. Our sys admin wants to install SQL 2005
on the program drive of the new server, while putting our databases on the
data drive. I argue that if SQL itself is on the program drive, then the
system databases will be in one place, while our databases will be in
another. So I'd prefer to have SQL 2005 installed on the data drive, as it
is now.

I'm interested in any feedback regarding what you guys think is the better
configuration, and also if there's any performance hit from having the
program and the database on two drives of the same machine.

Thanks,

Neil

We are migrating to a new server, running Server 2003 SP2, and SQL Server
2005. The server has 2 GB of RAM, which seems a bit low to me. Any thoughts?

Thanks.

Hello Neil!


It always depends...

Nobody knows about your environment's workload. 2GB of RAM might be enough
for 80 people over 100 but may not be enough for other 20 people.
It's your DBA who is gonna decide if it's OK for your environment or not. He
or whoever your DBA, needs to analyse your needs.


--
Ekrem Önsoy



"Neil" <nospam (AT) nospam (DOT) net> wrote

Well, we kind of don't have a DBA. :-(

We have the system admin, who sets up the boxes, installs the client and
network software, etc., but knows next to nothing about SQL Server.

Then we have me, the developer, using SQL Server as the back end to our
database (Access), and proficient in developing in SQL Server, but somewhat
weak when it comes to administrating it.

So that's the reality of the situation.......

As for users, we have about 30 users connected over a LAN; about another
10-15 connected through a WAN; and are going to have a new location opening
with another, I'd guess, 5-10 users at most over the WAN. So total users is
about 50.

Thanks for your assistance!

Neil


"Ekrem Önsoy" <ekrem (AT) btegitim (DOT) com> wrote

Neil (nospam (AT) nospam (DOT) net) writes:
First make sure that the sysadmin makes the system drive big enough. SQL
2005 takes quite a toll on the system disk, not the least the system disk.
Make sure that there is at least 50 GB.

Next, it's difficult to not install most of SQL 2005 on the system disk, so
don't fiddle with that. It's not an issue anyway.

What is more important is how you place your data files. You have two
disks, and you have a data file, a log file, and then you have two files for
tempdb. Put the data and log file for the database on separate drives
for better odds in case of a crash. And put the log file for tempdb
where the data file for the database and vice versa. Or get more
disks to spread out over.

As for the memory, 2GB is not that impressing, but depending on how big
the active part of the database is, how well-tuned it is etc, it may
be sufficient.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Neil" <nospam (AT) nospam (DOT) net> wrote

At my previous employeer we had a system with 4 gig of RAM (so only 2 gig
available to SQL Server) that served easily 14 million transaction a day.
And that was on now 8 year old hardware.

So, "it depends".




--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Thanks, Erland.

First, re. the 2 GB of RAM, considering that the db is not huge (two main
tables have about 60,000 records each; a few other tables with a few
thousand records; and then a bunch of smaller records); there are only about
50 users max; most of the activity is read-only, very little writing; and
the hardware is brand new; do you think that would be enough, or should I
push for more? I wouldn't count on the database being well-tuned.

Second, re. the size of the disks, when you said "make sure there is at
least 50 GB," wasn't sure if you meant 50 GB total, or 50 GB free space
after SQL Server is installed. Here are the specs for the disks:

C: RAID 0+1 36 GB (2 - 36 GB 10,000 RPM SAS)
D: RAID 5 204 GB (4 - 72 GB 10,000 RPM SAS)

Your input is appreciated. Thanks,

Neil


"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Well, we certainly don't have anywhere near 14 million transactions a day
(most of the activity is reads, and, even that is not a huge amount). But
with only 2 GB RAM, what would be left for SQL, 1 GB? That doesn't seem like
a lot.

"Greg D. Moore (Strider)" <mooregr_deleteth1s (AT) greenms (DOT) com> wrote

"Neil" <nospam (AT) nospam (DOT) net> wrote

Again, depends a lot on your DB and its needs. If the DB is 10 GB in size
but 90% of all queries involve 250MB of data (which is not uncommon) then
you'll be fine.

So it's really hard to say. From a previous post of yours, I'd say it
sounds like it should be fine. But memory is fairly cheap, toss in another
2GB if you're concerned (above that you probably need to upgrade your OS/SQL
versions, which is not so cheap.)


--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Right, forgot to mention the size of the DB (but did mention the general
number of records in another post here). The db is about half a gig in size.
So not very big at all.

Re. upgrade OS/SQL, my original post stated: "We are migrating to a new
server, running Server 2003 SP2, and SQL Server
2005." That is what we are upgrading to. You're saying that's not
sufficient?

Thanks!

Neil


"Greg D. Moore (Strider)" <mooregr_deleteth1s (AT) greenms (DOT) com> wrote