New SQL Server and Photos

I heard that you can store photos with the row in the new SQL Server (a
photo data type?). Is this true? If so, is there a speed advantage if
you have an e-commerce platform with thousands of products?

Help appreciated.
Thanks,
Frank

*** Sent via Developersdex http://www.developersdex.com ***I'm not sure what you're referring to - MSSQL already supports the
image data type for storing up to 2GB of binary data in a row (which
could be photos or anything else).

Although I haven't worked with a system like that myself, previous
newsgroup posts seem to suggest that many people prefer to store only a
path to the binary file in the database, and leave the file itself in
the filesystem.

Simon|||> Although I haven't worked with a system like that myself, previous
> newsgroup posts seem to suggest that many people prefer to store only
a
> path to the binary file in the database, and leave the file itself in
> the filesystem.

That's been my approach. The file system's on the whole a much more
efficient way to deal with such things, provided you've guarded against
path-traversal security problems and the like. For one, you're
funnelling less data through SQL Server itself, and two, NTFS is built
to handle exactly this kind of work in the first place.

There may be some circumstances where you need to have the data in the
row -- I think TerraServer uses this approach -- but most people are
not going to be building TerraServer!

New Database Server Advice

We are looking to upgrade our database server to gain additional speed.
I've come up with an idea of what we should get but wanted to get advice
from some experts to make sure my assumptions are accurate. Basically I
don't want to spend a bunch of my boss's money and not see any results. :-)
We are using MS SQL 2K on Window Server 2003
Our current server is a Zeon 3Ghz with 4G RAM
We have two main databases each about 25Gigs
The OS runs on one partition and the entire database runs on a single
partition (part of a Serial ATA RAID)
From my research mostly using perfmon, the bottle neck is always the disk,
we commonly have items queued and when the queue increases performance is
noticeably down. CPU speed has never been an issue. Therefore in a new
server I believe we should:
1. Use the maximum amount of RAM the server supports
2. Partition the databases by putting larger more active tables on they're
own physical drive as well as a sperate drive for SQL logs.
If this seems reasonable, I have a couple questions:
1. Is there a large performance difference between a SCSI RAID and Serial
ATA raid?
2. To evaluate disk systems should I look at drive RPM's, seek time, and
transfer rates? Am I missing something with this?
3. In addition to using separate drives when partitioning the database,
should I try to have separate interfaces to the motherboard. i.e. would it
make more sense to have two SCSI interfaces each with two drives instead of
one SCSI interface with four drives (from a performance standpoint) (Does
this logic apply to SATA systems?)
As a side note: I use the profiler tool with the SQLProfilerTuning Template,
then the index tuning wizard with default settings to generate our indexes.
If there is more I should be doing here, please let me know.
Any advice on any part of this is appreciated!1) Performace of disks has many factors, including the type of PCI bus the
card sits on, drive rpm, drive cache, I/O card cache, etc.
2) See 1 above. Get the maximum I/O card cache the card can have.
3) More interfaces is better up to the point where they exist on the same
PCI bus and saturate it.
4) More spindles is almost always better.
5) Go for at least 2 cores, preferably 4 or more. Do NOT enable
hyperthreading if you get CPUs that have it.
6) Avoid RAID 5 if possible for anything that has high writes, which always
includes tempdb and all transaction logs.
7) I avoid ITW. Have seen too many bad things come out of it.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Bishop" <nospam@.nospam.com> wrote in message
news:Ogk%235o1NIHA.3516@.TK2MSFTNGP02.phx.gbl...
> We are looking to upgrade our database server to gain additional speed.
> I've come up with an idea of what we should get but wanted to get advice
> from some experts to make sure my assumptions are accurate. Basically I
> don't want to spend a bunch of my boss's money and not see any results.
> :-)
> We are using MS SQL 2K on Window Server 2003
> Our current server is a Zeon 3Ghz with 4G RAM
> We have two main databases each about 25Gigs
> The OS runs on one partition and the entire database runs on a single
> partition (part of a Serial ATA RAID)
> From my research mostly using perfmon, the bottle neck is always the disk,
> we commonly have items queued and when the queue increases performance is
> noticeably down. CPU speed has never been an issue. Therefore in a new
> server I believe we should:
> 1. Use the maximum amount of RAM the server supports
> 2. Partition the databases by putting larger more active tables on they're
> own physical drive as well as a sperate drive for SQL logs.
> If this seems reasonable, I have a couple questions:
> 1. Is there a large performance difference between a SCSI RAID and Serial
> ATA raid?
> 2. To evaluate disk systems should I look at drive RPM's, seek time, and
> transfer rates? Am I missing something with this?
> 3. In addition to using separate drives when partitioning the database,
> should I try to have separate interfaces to the motherboard. i.e. would
> it make more sense to have two SCSI interfaces each with two drives
> instead of one SCSI interface with four drives (from a performance
> standpoint) (Does this logic apply to SATA systems?)
> As a side note: I use the profiler tool with the SQLProfilerTuning
> Template, then the index tuning wizard with default settings to generate
> our indexes. If there is more I should be doing here, please let me know.
> Any advice on any part of this is appreciated!
>|||Thank You, this will give me plenty to look into!
Any thoughts on if ECC memory is really worth the money. I have several
servers that don't use ECC memory that get hit hard and never have problems?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13ldpj8nb3i5nf0@.corp.supernews.com...
> 1) Performace of disks has many factors, including the type of PCI bus the
> card sits on, drive rpm, drive cache, I/O card cache, etc.
> 2) See 1 above. Get the maximum I/O card cache the card can have.
> 3) More interfaces is better up to the point where they exist on the same
> PCI bus and saturate it.
> 4) More spindles is almost always better.
> 5) Go for at least 2 cores, preferably 4 or more. Do NOT enable
> hyperthreading if you get CPUs that have it.
> 6) Avoid RAID 5 if possible for anything that has high writes, which
> always includes tempdb and all transaction logs.
> 7) I avoid ITW. Have seen too many bad things come out of it.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Bishop" <nospam@.nospam.com> wrote in message
> news:Ogk%235o1NIHA.3516@.TK2MSFTNGP02.phx.gbl...
>> We are looking to upgrade our database server to gain additional speed.
>> I've come up with an idea of what we should get but wanted to get advice
>> from some experts to make sure my assumptions are accurate. Basically I
>> don't want to spend a bunch of my boss's money and not see any results.
>> :-)
>> We are using MS SQL 2K on Window Server 2003
>> Our current server is a Zeon 3Ghz with 4G RAM
>> We have two main databases each about 25Gigs
>> The OS runs on one partition and the entire database runs on a single
>> partition (part of a Serial ATA RAID)
>> From my research mostly using perfmon, the bottle neck is always the
>> disk, we commonly have items queued and when the queue increases
>> performance is noticeably down. CPU speed has never been an issue.
>> Therefore in a new server I believe we should:
>> 1. Use the maximum amount of RAM the server supports
>> 2. Partition the databases by putting larger more active tables on
>> they're own physical drive as well as a sperate drive for SQL logs.
>> If this seems reasonable, I have a couple questions:
>> 1. Is there a large performance difference between a SCSI RAID and Serial
>> ATA raid?
>> 2. To evaluate disk systems should I look at drive RPM's, seek time, and
>> transfer rates? Am I missing something with this?
>> 3. In addition to using separate drives when partitioning the database,
>> should I try to have separate interfaces to the motherboard. i.e. would
>> it make more sense to have two SCSI interfaces each with two drives
>> instead of one SCSI interface with four drives (from a performance
>> standpoint) (Does this logic apply to SATA systems?)
>> As a side note: I use the profiler tool with the SQLProfilerTuning
>> Template, then the index tuning wizard with default settings to generate
>> our indexes. If there is more I should be doing here, please let me know.
>> Any advice on any part of this is appreciated!
>|||I think ECC memory is more about keeping your data integrity. If you
consider that important it is indeed worth the money. :-)
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Bishop" <nospam@.nospam.com> wrote in message
news:uWbQ%23H4NIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Thank You, this will give me plenty to look into!
> Any thoughts on if ECC memory is really worth the money. I have several
> servers that don't use ECC memory that get hit hard and never have
> problems?
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13ldpj8nb3i5nf0@.corp.supernews.com...
>> 1) Performace of disks has many factors, including the type of PCI bus
>> the card sits on, drive rpm, drive cache, I/O card cache, etc.
>> 2) See 1 above. Get the maximum I/O card cache the card can have.
>> 3) More interfaces is better up to the point where they exist on the same
>> PCI bus and saturate it.
>> 4) More spindles is almost always better.
>> 5) Go for at least 2 cores, preferably 4 or more. Do NOT enable
>> hyperthreading if you get CPUs that have it.
>> 6) Avoid RAID 5 if possible for anything that has high writes, which
>> always includes tempdb and all transaction logs.
>> 7) I avoid ITW. Have seen too many bad things come out of it.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Bishop" <nospam@.nospam.com> wrote in message
>> news:Ogk%235o1NIHA.3516@.TK2MSFTNGP02.phx.gbl...
>> We are looking to upgrade our database server to gain additional speed.
>> I've come up with an idea of what we should get but wanted to get advice
>> from some experts to make sure my assumptions are accurate. Basically I
>> don't want to spend a bunch of my boss's money and not see any results.
>> :-)
>> We are using MS SQL 2K on Window Server 2003
>> Our current server is a Zeon 3Ghz with 4G RAM
>> We have two main databases each about 25Gigs
>> The OS runs on one partition and the entire database runs on a single
>> partition (part of a Serial ATA RAID)
>> From my research mostly using perfmon, the bottle neck is always the
>> disk, we commonly have items queued and when the queue increases
>> performance is noticeably down. CPU speed has never been an issue.
>> Therefore in a new server I believe we should:
>> 1. Use the maximum amount of RAM the server supports
>> 2. Partition the databases by putting larger more active tables on
>> they're own physical drive as well as a sperate drive for SQL logs.
>> If this seems reasonable, I have a couple questions:
>> 1. Is there a large performance difference between a SCSI RAID and
>> Serial ATA raid?
>> 2. To evaluate disk systems should I look at drive RPM's, seek time, and
>> transfer rates? Am I missing something with this?
>> 3. In addition to using separate drives when partitioning the database,
>> should I try to have separate interfaces to the motherboard. i.e. would
>> it make more sense to have two SCSI interfaces each with two drives
>> instead of one SCSI interface with four drives (from a performance
>> standpoint) (Does this logic apply to SATA systems?)
>> As a side note: I use the profiler tool with the SQLProfilerTuning
>> Template, then the index tuning wizard with default settings to generate
>> our indexes. If there is more I should be doing here, please let me
>> know.
>> Any advice on any part of this is appreciated!
>>
>

New Database Server Advice

We are looking to upgrade our database server to gain additional speed.
I've come up with an idea of what we should get but wanted to get advice
from some experts to make sure my assumptions are accurate. Basically I
don't want to spend a bunch of my boss's money and not see any results. :-)
We are using MS SQL 2K on Window Server 2003
Our current server is a Zeon 3Ghz with 4G RAM
We have two main databases each about 25Gigs
The OS runs on one partition and the entire database runs on a single
partition (part of a Serial ATA RAID)
From my research mostly using perfmon, the bottle neck is always the disk,
we commonly have items queued and when the queue increases performance is
noticeably down. CPU speed has never been an issue. Therefore in a new
server I believe we should:
1. Use the maximum amount of RAM the server supports
2. Partition the databases by putting larger more active tables on they're
own physical drive as well as a sperate drive for SQL logs.
If this seems reasonable, I have a couple questions:
1. Is there a large performance difference between a SCSI RAID and Serial
ATA raid?
2. To evaluate disk systems should I look at drive RPM's, seek time, and
transfer rates? Am I missing something with this?
3. In addition to using separate drives when partitioning the database,
should I try to have separate interfaces to the motherboard. i.e. would it
make more sense to have two SCSI interfaces each with two drives instead of
one SCSI interface with four drives (from a performance standpoint) (Does
this logic apply to SATA systems?)
As a side note: I use the profiler tool with the SQLProfilerTuning Template,
then the index tuning wizard with default settings to generate our indexes.
If there is more I should be doing here, please let me know.
Any advice on any part of this is appreciated!
1) Performace of disks has many factors, including the type of PCI bus the
card sits on, drive rpm, drive cache, I/O card cache, etc.
2) See 1 above. Get the maximum I/O card cache the card can have.
3) More interfaces is better up to the point where they exist on the same
PCI bus and saturate it.
4) More spindles is almost always better.
5) Go for at least 2 cores, preferably 4 or more. Do NOT enable
hyperthreading if you get CPUs that have it.
6) Avoid RAID 5 if possible for anything that has high writes, which always
includes tempdb and all transaction logs.
7) I avoid ITW. Have seen too many bad things come out of it.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Bishop" <nospam@.nospam.com> wrote in message
news:Ogk%235o1NIHA.3516@.TK2MSFTNGP02.phx.gbl...
> We are looking to upgrade our database server to gain additional speed.
> I've come up with an idea of what we should get but wanted to get advice
> from some experts to make sure my assumptions are accurate. Basically I
> don't want to spend a bunch of my boss's money and not see any results.
> :-)
> We are using MS SQL 2K on Window Server 2003
> Our current server is a Zeon 3Ghz with 4G RAM
> We have two main databases each about 25Gigs
> The OS runs on one partition and the entire database runs on a single
> partition (part of a Serial ATA RAID)
> From my research mostly using perfmon, the bottle neck is always the disk,
> we commonly have items queued and when the queue increases performance is
> noticeably down. CPU speed has never been an issue. Therefore in a new
> server I believe we should:
> 1. Use the maximum amount of RAM the server supports
> 2. Partition the databases by putting larger more active tables on they're
> own physical drive as well as a sperate drive for SQL logs.
> If this seems reasonable, I have a couple questions:
> 1. Is there a large performance difference between a SCSI RAID and Serial
> ATA raid?
> 2. To evaluate disk systems should I look at drive RPM's, seek time, and
> transfer rates? Am I missing something with this?
> 3. In addition to using separate drives when partitioning the database,
> should I try to have separate interfaces to the motherboard. i.e. would
> it make more sense to have two SCSI interfaces each with two drives
> instead of one SCSI interface with four drives (from a performance
> standpoint) (Does this logic apply to SATA systems?)
> As a side note: I use the profiler tool with the SQLProfilerTuning
> Template, then the index tuning wizard with default settings to generate
> our indexes. If there is more I should be doing here, please let me know.
> Any advice on any part of this is appreciated!
>
|||Thank You, this will give me plenty to look into!
Any thoughts on if ECC memory is really worth the money. I have several
servers that don't use ECC memory that get hit hard and never have problems?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13ldpj8nb3i5nf0@.corp.supernews.com...
> 1) Performace of disks has many factors, including the type of PCI bus the
> card sits on, drive rpm, drive cache, I/O card cache, etc.
> 2) See 1 above. Get the maximum I/O card cache the card can have.
> 3) More interfaces is better up to the point where they exist on the same
> PCI bus and saturate it.
> 4) More spindles is almost always better.
> 5) Go for at least 2 cores, preferably 4 or more. Do NOT enable
> hyperthreading if you get CPUs that have it.
> 6) Avoid RAID 5 if possible for anything that has high writes, which
> always includes tempdb and all transaction logs.
> 7) I avoid ITW. Have seen too many bad things come out of it.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Bishop" <nospam@.nospam.com> wrote in message
> news:Ogk%235o1NIHA.3516@.TK2MSFTNGP02.phx.gbl...
>
|||I think ECC memory is more about keeping your data integrity. If you
consider that important it is indeed worth the money. :-)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Bishop" <nospam@.nospam.com> wrote in message
news:uWbQ%23H4NIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Thank You, this will give me plenty to look into!
> Any thoughts on if ECC memory is really worth the money. I have several
> servers that don't use ECC memory that get hit hard and never have
> problems?
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13ldpj8nb3i5nf0@.corp.supernews.com...
>

New Database Server Advice

We are looking to upgrade our database server to gain additional speed.
I've come up with an idea of what we should get but wanted to get advice
from some experts to make sure my assumptions are accurate. Basically I
don't want to spend a bunch of my boss's money and not see any results. :-)
We are using MS SQL 2K on Window Server 2003
Our current server is a Zeon 3Ghz with 4G RAM
We have two main databases each about 25Gigs
The OS runs on one partition and the entire database runs on a single
partition (part of a Serial ATA RAID)
From my research mostly using perfmon, the bottle neck is always the disk,
we commonly have items queued and when the queue increases performance is
noticeably down. CPU speed has never been an issue. Therefore in a new
server I believe we should:
1. Use the maximum amount of RAM the server supports
2. Partition the databases by putting larger more active tables on they're
own physical drive as well as a sperate drive for SQL logs.
If this seems reasonable, I have a couple questions:
1. Is there a large performance difference between a SCSI RAID and Serial
ATA raid?
2. To evaluate disk systems should I look at drive RPM's, seek time, and
transfer rates? Am I missing something with this?
3. In addition to using separate drives when partitioning the database,
should I try to have separate interfaces to the motherboard. i.e. would it
make more sense to have two SCSI interfaces each with two drives instead of
one SCSI interface with four drives (from a performance standpoint) (Does
this logic apply to SATA systems?)
As a side note: I use the profiler tool with the SQLProfilerTuning Template,
then the index tuning wizard with default settings to generate our indexes.
If there is more I should be doing here, please let me know.
Any advice on any part of this is appreciated!1) Performace of disks has many factors, including the type of PCI bus the
card sits on, drive rpm, drive cache, I/O card cache, etc.
2) See 1 above. Get the maximum I/O card cache the card can have.
3) More interfaces is better up to the point where they exist on the same
PCI bus and saturate it.
4) More spindles is almost always better.
5) Go for at least 2 cores, preferably 4 or more. Do NOT enable
hyperthreading if you get CPUs that have it.
6) Avoid RAID 5 if possible for anything that has high writes, which always
includes tempdb and all transaction logs.
7) I avoid ITW. Have seen too many bad things come out of it.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Bishop" <nospam@.nospam.com> wrote in message
news:Ogk%235o1NIHA.3516@.TK2MSFTNGP02.phx.gbl...
> We are looking to upgrade our database server to gain additional speed.
> I've come up with an idea of what we should get but wanted to get advice
> from some experts to make sure my assumptions are accurate. Basically I
> don't want to spend a bunch of my boss's money and not see any results.
> :-)
> We are using MS SQL 2K on Window Server 2003
> Our current server is a Zeon 3Ghz with 4G RAM
> We have two main databases each about 25Gigs
> The OS runs on one partition and the entire database runs on a single
> partition (part of a Serial ATA RAID)
> From my research mostly using perfmon, the bottle neck is always the disk,
> we commonly have items queued and when the queue increases performance is
> noticeably down. CPU speed has never been an issue. Therefore in a new
> server I believe we should:
> 1. Use the maximum amount of RAM the server supports
> 2. Partition the databases by putting larger more active tables on they're
> own physical drive as well as a sperate drive for SQL logs.
> If this seems reasonable, I have a couple questions:
> 1. Is there a large performance difference between a SCSI RAID and Serial
> ATA raid?
> 2. To evaluate disk systems should I look at drive RPM's, seek time, and
> transfer rates? Am I missing something with this?
> 3. In addition to using separate drives when partitioning the database,
> should I try to have separate interfaces to the motherboard. i.e. would
> it make more sense to have two SCSI interfaces each with two drives
> instead of one SCSI interface with four drives (from a performance
> standpoint) (Does this logic apply to SATA systems?)
> As a side note: I use the profiler tool with the SQLProfilerTuning
> Template, then the index tuning wizard with default settings to generate
> our indexes. If there is more I should be doing here, please let me know.
> Any advice on any part of this is appreciated!
>|||Thank You, this will give me plenty to look into!
Any thoughts on if ECC memory is really worth the money. I have several
servers that don't use ECC memory that get hit hard and never have problems?
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13ldpj8nb3i5nf0@.corp.supernews.com...
> 1) Performace of disks has many factors, including the type of PCI bus the
> card sits on, drive rpm, drive cache, I/O card cache, etc.
> 2) See 1 above. Get the maximum I/O card cache the card can have.
> 3) More interfaces is better up to the point where they exist on the same
> PCI bus and saturate it.
> 4) More spindles is almost always better.
> 5) Go for at least 2 cores, preferably 4 or more. Do NOT enable
> hyperthreading if you get CPUs that have it.
> 6) Avoid RAID 5 if possible for anything that has high writes, which
> always includes tempdb and all transaction logs.
> 7) I avoid ITW. Have seen too many bad things come out of it.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Bishop" <nospam@.nospam.com> wrote in message
> news:Ogk%235o1NIHA.3516@.TK2MSFTNGP02.phx.gbl...
>|||I think ECC memory is more about keeping your data integrity. If you
consider that important it is indeed worth the money. :-)
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Bishop" <nospam@.nospam.com> wrote in message
news:uWbQ%23H4NIHA.4656@.TK2MSFTNGP03.phx.gbl...
> Thank You, this will give me plenty to look into!
> Any thoughts on if ECC memory is really worth the money. I have several
> servers that don't use ECC memory that get hit hard and never have
> problems?
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13ldpj8nb3i5nf0@.corp.supernews.com...
>