SQL Configuration

[Neil]

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

[Neil]

SQL Configuration - RAM

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.

[=?iso-8859-9?Q?Ekrem_=D6nsoy?=]

Re: SQL Configuration - RAM

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@nospam. netwrote in message
news:UGwMi.5682 7$YL5.48655@new ssvr29.news.pro digy.net...

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.
>

[Neil]

Re: SQL Configuration - RAM

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@btegitim .comwrote in message
news:1D4A1157-F7D4-42EA-9B28-436D8F421E2D@mi crosoft.com...

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@nospam. netwrote in message
news:UGwMi.5682 7$YL5.48655@new ssvr29.news.pro digy.net...

>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.
>>

>

[Erland Sommarskog]

Re: SQL Configuration

Neil (nospam@nospam. net) writes:

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.

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Greg D. Moore \(Strider\)]

Re: SQL Configuration - RAM

"Neil" <nospam@nospam. netwrote in message
news:cTxMi.5417 6$Um6.5889@news svr12.news.prod igy.net...

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
>

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

[Neil]

Re: SQL Configuration

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@sommars kog.sewrote in message
news:Xns99BDF02 97F9BAYazorman@ 127.0.0.1...

Neil (nospam@nospam. net) writes:

>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
>configuratio n, and also if there's any performance hit from having the
>program and the database on two drives of the same machine.

>
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@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

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

[Neil]

Re: SQL Configuration - RAM

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_delete th1s@greenms.co mwrote in message
news:13g65tfdj2 ldm20@corp.supe rnews.com...

"Neil" <nospam@nospam. netwrote in message
news:cTxMi.5417 6$Um6.5889@news svr12.news.prod igy.net...

>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
>>

>
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

>
>

[Greg D. Moore \(Strider\)]

Re: SQL Configuration - RAM

"Neil" <nospam@nospam. netwrote in message
news:8OFMi.1576 $oA2.30@nlpi068 .nbdc.sbc.com.. .

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.

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

[Neil]

Re: SQL Configuration - RAM

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_delete th1s@greenms.co mwrote in message
news:13g6uqbd3m tkefe@corp.supe rnews.com...

"Neil" <nospam@nospam. netwrote in message
news:8OFMi.1576 $oA2.30@nlpi068 .nbdc.sbc.com.. .

>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.

>
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

>
>

[Erland Sommarskog]

Re: SQL Configuration

Neil (nospam@nospam. net) writes:

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.

More memory is not going to hurt, but the database size you indicate
certainly does not scare me.

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:

I meant 50 GB in total. You are not going to fill up 36 GB on the spot,
but the strategy of Microsoft definitely is to bury a lot of stuff under
C:\Windows.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Neil]

Re: SQL Configuration

Well, the sys admin went ahead and moved SQL Server to the D drive, when I
questioned him about it, even though I told him I'd get back to him about
it. So now SQL's on the D drive with 204 GB. But you said that most of it
will still remain on the C drive anyway. But maybe having the parts that are
on the D drive there will help the system disk.

Thanks.

"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns99BEF25 D5AE6DYazorman@ 127.0.0.1...

Neil (nospam@nospam. net) writes:

>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.

>
More memory is not going to hurt, but the database size you indicate
certainly does not scare me.
>

>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:

>
I meant 50 GB in total. You are not going to fill up 36 GB on the spot,
but the strategy of Microsoft definitely is to bury a lot of stuff under
C:\Windows.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

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