SQL Configuration

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

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.

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" wrote in message
news:UGwMi.56827$YL5.48655@newssvr29.news.prodigy.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.
>

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" wrote in message
news:1D4A1157-F7D4-42EA-9B28-436D8F421E2D@microsoft.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" wrote in message
> news:UGwMi.56827$YL5.48655@newssvr29.news.prodigy.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.
>>
>

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SQL Configuration - RAM

"Neil" wrote in message
news:cTxMi.54176$Um6.5889@newssvr12.news.prodigy.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

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" wrote in message
news:Xns99BDF0297F9BAYazorman@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
>> 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@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

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)" wrote in message
news:13g65tfdj2ldm20@corp.supernews.com...
> "Neil" wrote in message
> news:cTxMi.54176$Um6.5889@newssvr12.news.prodigy.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
>
>

Re: SQL Configuration - RAM

"Neil" wrote 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

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)" wrote in message
news:13g6uqbd3mtkefe@corp.supernews.com...
> "Neil" wrote 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
>
>

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

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" wrote in message
news:Xns99BEF25D5AE6DYazorman@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@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx