Best SAN configuration for partitioned table

Hi,
I wondered if anyone had any thoughts on the best way to go about
splitting up a san array for a 500gb fact table. Its going to need to
be partitioned to allow for the overnight processing to complete on
time but what is the best way to split the san array for it.
I have about 10-13 san disks available for the table which leaves me
enough space on the other disks for the other database objects and
tempdb, logs etc.
The table will be partitioned into 13 logical weeks but would it be
best to allocate one disk per partition or have a 13 disk raid group
and put all 13 partitions on that and have it striped?
Any thoughts?
Thanks
Ian.
I am *far* from an expert, but here's my thoughts...
For having each partition on a separate disk or spindle, the question is are
you going to be using that partitioned table in parallel? Meaning, are you
going to be accessing or modifying several, if not all 13 weeks, at the same
time? If so, then it would make sense (depending on the processing power of
the SAN - EMC's DMX would be able to handle this all in parallel) to
separate the partitions into distinct spindles.
If you're not sure about parallel operations, then I would probably through
them into a JBOD (Just a Bunch Of Disks) set up like your second
alternative.
Here's my school of thought on this: You separate your TEMPDB (Very
important in SQL Server 2005), your LOGS, your INDEXES, and your TABLES onto
separate sets of spindles. That's usually a good starting point for an EMC
type of SAN. From there, you would then get into further tuning to see if
you would receive any additional benefit of separating objects (Tables,
Partitions, Indexes, etc) onto distinct spindles... remember you're
increasing management of the disk system when you do that, so it's helpful
to determine the benefit, if any.
FYI, I originated from the Oracle School of Thought, so it might clash with
some SQL Server admins ;)
JASON
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:1186730967.614276.138730@.z24g2000prh.googlegr oups.com...
> Hi,
> I wondered if anyone had any thoughts on the best way to go about
> splitting up a san array for a 500gb fact table. Its going to need to
> be partitioned to allow for the overnight processing to complete on
> time but what is the best way to split the san array for it.
> I have about 10-13 san disks available for the table which leaves me
> enough space on the other disks for the other database objects and
> tempdb, logs etc.
> The table will be partitioned into 13 logical weeks but would it be
> best to allocate one disk per partition or have a 13 disk raid group
> and put all 13 partitions on that and have it striped?
> Any thoughts?
> Thanks
> Ian.
>
|||Hi Ian,
thanks for your question around distributing data across spindles on a SAN.
Based on our experiences with many SQL 2005 data warehousing customers, I
would encourage you to distribute your data across all spindles. This should
give you good IO parallelism in case your query touches only one partition,
and it should also give you similarly high parallelism for a query that
touches many partitions. Depending on how large your data in a single
partition is and how many cores you have in your system, you want to avoid
cases where you exercise only one spindle and have many cores idle waiting
for data from the IO subsystem.
As Jason pointed out, it certainly makes sense to keep log and tempdb on
separate sets of spindles. You might want to experiment with index and data
on the same set of spindles. Usually, this already works sufficiently well as
compared to separate index and data. AS always, further tuning may be needed
depending on the characteristics of your workload.
Hope this makes sense and helps you with your SAN configuration.
Best regards,
Torsten Grabs
Program Manager
Microsoft SQL Server Query Processor
"Jason Fay" wrote:

> I am *far* from an expert, but here's my thoughts...
> For having each partition on a separate disk or spindle, the question is are
> you going to be using that partitioned table in parallel? Meaning, are you
> going to be accessing or modifying several, if not all 13 weeks, at the same
> time? If so, then it would make sense (depending on the processing power of
> the SAN - EMC's DMX would be able to handle this all in parallel) to
> separate the partitions into distinct spindles.
> If you're not sure about parallel operations, then I would probably through
> them into a JBOD (Just a Bunch Of Disks) set up like your second
> alternative.
> Here's my school of thought on this: You separate your TEMPDB (Very
> important in SQL Server 2005), your LOGS, your INDEXES, and your TABLES onto
> separate sets of spindles. That's usually a good starting point for an EMC
> type of SAN. From there, you would then get into further tuning to see if
> you would receive any additional benefit of separating objects (Tables,
> Partitions, Indexes, etc) onto distinct spindles... remember you're
> increasing management of the disk system when you do that, so it's helpful
> to determine the benefit, if any.
> FYI, I originated from the Oracle School of Thought, so it might clash with
> some SQL Server admins ;)
> --
> JASON
>
> "ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
> news:1186730967.614276.138730@.z24g2000prh.googlegr oups.com...
>
>

Best SAN configuration for partitioned table

Hi,
I wondered if anyone had any thoughts on the best way to go about
splitting up a san array for a 500gb fact table. Its going to need to
be partitioned to allow for the overnight processing to complete on
time but what is the best way to split the san array for it.
I have about 10-13 san disks available for the table which leaves me
enough space on the other disks for the other database objects and
tempdb, logs etc.
The table will be partitioned into 13 logical weeks but would it be
best to allocate one disk per partition or have a 13 disk raid group
and put all 13 partitions on that and have it striped?
Any thoughts?
Thanks
Ian.I am *far* from an expert, but here's my thoughts...
For having each partition on a separate disk or spindle, the question is are
you going to be using that partitioned table in parallel? Meaning, are you
going to be accessing or modifying several, if not all 13 weeks, at the same
time? If so, then it would make sense (depending on the processing power of
the SAN - EMC's DMX would be able to handle this all in parallel) to
separate the partitions into distinct spindles.
If you're not sure about parallel operations, then I would probably through
them into a JBOD (Just a Bunch Of Disks) set up like your second
alternative.
Here's my school of thought on this: You separate your TEMPDB (Very
important in SQL Server 2005), your LOGS, your INDEXES, and your TABLES onto
separate sets of spindles. That's usually a good starting point for an EMC
type of SAN. From there, you would then get into further tuning to see if
you would receive any additional benefit of separating objects (Tables,
Partitions, Indexes, etc) onto distinct spindles... remember you're
increasing management of the disk system when you do that, so it's helpful
to determine the benefit, if any.
FYI, I originated from the Oracle School of Thought, so it might clash with
some SQL Server admins ;)
JASON
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:1186730967.614276.138730@.z24g2000prh.googlegroups.com...
> Hi,
> I wondered if anyone had any thoughts on the best way to go about
> splitting up a san array for a 500gb fact table. Its going to need to
> be partitioned to allow for the overnight processing to complete on
> time but what is the best way to split the san array for it.
> I have about 10-13 san disks available for the table which leaves me
> enough space on the other disks for the other database objects and
> tempdb, logs etc.
> The table will be partitioned into 13 logical weeks but would it be
> best to allocate one disk per partition or have a 13 disk raid group
> and put all 13 partitions on that and have it striped?
> Any thoughts?
> Thanks
> Ian.
>|||Hi Ian,
thanks for your question around distributing data across spindles on a SAN.
Based on our experiences with many SQL 2005 data warehousing customers, I
would encourage you to distribute your data across all spindles. This should
give you good IO parallelism in case your query touches only one partition,
and it should also give you similarly high parallelism for a query that
touches many partitions. Depending on how large your data in a single
partition is and how many cores you have in your system, you want to avoid
cases where you exercise only one spindle and have many cores idle waiting
for data from the IO subsystem.
As Jason pointed out, it certainly makes sense to keep log and tempdb on
separate sets of spindles. You might want to experiment with index and data
on the same set of spindles. Usually, this already works sufficiently well a
s
compared to separate index and data. AS always, further tuning may be needed
depending on the characteristics of your workload.
Hope this makes sense and helps you with your SAN configuration.
Best regards,
Torsten Grabs
Program Manager
Microsoft SQL Server Query Processor
"Jason Fay" wrote:

> I am *far* from an expert, but here's my thoughts...
> For having each partition on a separate disk or spindle, the question is a
re
> you going to be using that partitioned table in parallel? Meaning, are yo
u
> going to be accessing or modifying several, if not all 13 weeks, at the sa
me
> time? If so, then it would make sense (depending on the processing power
of
> the SAN - EMC's DMX would be able to handle this all in parallel) to
> separate the partitions into distinct spindles.
> If you're not sure about parallel operations, then I would probably throug
h
> them into a JBOD (Just a Bunch Of Disks) set up like your second
> alternative.
> Here's my school of thought on this: You separate your TEMPDB (Very
> important in SQL Server 2005), your LOGS, your INDEXES, and your TABLES on
to
> separate sets of spindles. That's usually a good starting point for an EM
C
> type of SAN. From there, you would then get into further tuning to see if
> you would receive any additional benefit of separating objects (Tables,
> Partitions, Indexes, etc) onto distinct spindles... remember you're
> increasing management of the disk system when you do that, so it's helpful
> to determine the benefit, if any.
> FYI, I originated from the Oracle School of Thought, so it might clash wi
th
> some SQL Server admins ;)
> --
> JASON
>
> "ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
> news:1186730967.614276.138730@.z24g2000prh.googlegroups.com...
>
>sql

Best SAN configuration for partitioned table

Hi,
I wondered if anyone had any thoughts on the best way to go about
splitting up a san array for a 500gb fact table. Its going to need to
be partitioned to allow for the overnight processing to complete on
time but what is the best way to split the san array for it.
I have about 10-13 san disks available for the table which leaves me
enough space on the other disks for the other database objects and
tempdb, logs etc.
The table will be partitioned into 13 logical weeks but would it be
best to allocate one disk per partition or have a 13 disk raid group
and put all 13 partitions on that and have it striped?
Any thoughts?
Thanks
Ian.I am *far* from an expert, but here's my thoughts...
For having each partition on a separate disk or spindle, the question is are
you going to be using that partitioned table in parallel? Meaning, are you
going to be accessing or modifying several, if not all 13 weeks, at the same
time? If so, then it would make sense (depending on the processing power of
the SAN - EMC's DMX would be able to handle this all in parallel) to
separate the partitions into distinct spindles.
If you're not sure about parallel operations, then I would probably through
them into a JBOD (Just a Bunch Of Disks) set up like your second
alternative.
Here's my school of thought on this: You separate your TEMPDB (Very
important in SQL Server 2005), your LOGS, your INDEXES, and your TABLES onto
separate sets of spindles. That's usually a good starting point for an EMC
type of SAN. From there, you would then get into further tuning to see if
you would receive any additional benefit of separating objects (Tables,
Partitions, Indexes, etc) onto distinct spindles... remember you're
increasing management of the disk system when you do that, so it's helpful
to determine the benefit, if any.
FYI, I originated from the Oracle School of Thought, so it might clash with
some SQL Server admins ;)
--
JASON
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:1186730967.614276.138730@.z24g2000prh.googlegroups.com...
> Hi,
> I wondered if anyone had any thoughts on the best way to go about
> splitting up a san array for a 500gb fact table. Its going to need to
> be partitioned to allow for the overnight processing to complete on
> time but what is the best way to split the san array for it.
> I have about 10-13 san disks available for the table which leaves me
> enough space on the other disks for the other database objects and
> tempdb, logs etc.
> The table will be partitioned into 13 logical weeks but would it be
> best to allocate one disk per partition or have a 13 disk raid group
> and put all 13 partitions on that and have it striped?
> Any thoughts?
> Thanks
> Ian.
>|||Hi Ian,
thanks for your question around distributing data across spindles on a SAN.
Based on our experiences with many SQL 2005 data warehousing customers, I
would encourage you to distribute your data across all spindles. This should
give you good IO parallelism in case your query touches only one partition,
and it should also give you similarly high parallelism for a query that
touches many partitions. Depending on how large your data in a single
partition is and how many cores you have in your system, you want to avoid
cases where you exercise only one spindle and have many cores idle waiting
for data from the IO subsystem.
As Jason pointed out, it certainly makes sense to keep log and tempdb on
separate sets of spindles. You might want to experiment with index and data
on the same set of spindles. Usually, this already works sufficiently well as
compared to separate index and data. AS always, further tuning may be needed
depending on the characteristics of your workload.
Hope this makes sense and helps you with your SAN configuration.
Best regards,
Torsten Grabs
Program Manager
Microsoft SQL Server Query Processor
"Jason Fay" wrote:
> I am *far* from an expert, but here's my thoughts...
> For having each partition on a separate disk or spindle, the question is are
> you going to be using that partitioned table in parallel? Meaning, are you
> going to be accessing or modifying several, if not all 13 weeks, at the same
> time? If so, then it would make sense (depending on the processing power of
> the SAN - EMC's DMX would be able to handle this all in parallel) to
> separate the partitions into distinct spindles.
> If you're not sure about parallel operations, then I would probably through
> them into a JBOD (Just a Bunch Of Disks) set up like your second
> alternative.
> Here's my school of thought on this: You separate your TEMPDB (Very
> important in SQL Server 2005), your LOGS, your INDEXES, and your TABLES onto
> separate sets of spindles. That's usually a good starting point for an EMC
> type of SAN. From there, you would then get into further tuning to see if
> you would receive any additional benefit of separating objects (Tables,
> Partitions, Indexes, etc) onto distinct spindles... remember you're
> increasing management of the disk system when you do that, so it's helpful
> to determine the benefit, if any.
> FYI, I originated from the Oracle School of Thought, so it might clash with
> some SQL Server admins ;)
> --
> JASON
>
> "ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
> news:1186730967.614276.138730@.z24g2000prh.googlegroups.com...
> > Hi,
> >
> > I wondered if anyone had any thoughts on the best way to go about
> > splitting up a san array for a 500gb fact table. Its going to need to
> > be partitioned to allow for the overnight processing to complete on
> > time but what is the best way to split the san array for it.
> >
> > I have about 10-13 san disks available for the table which leaves me
> > enough space on the other disks for the other database objects and
> > tempdb, logs etc.
> >
> > The table will be partitioned into 13 logical weeks but would it be
> > best to allocate one disk per partition or have a 13 disk raid group
> > and put all 13 partitions on that and have it striped?
> >
> > Any thoughts?
> >
> > Thanks
> >
> > Ian.
> >
>
>

Best Practice Analyzer & Custom Best Practices

Hi All,
The BPA is a great tool that can be used to audit an environment for
adherenace to proper configuration and basic T-SQL coding best practices.
Does anyone know if the BPA tool can be customized to include custom
best-practice rules that are in-addition to the standard best practices
included with the tool? For example, say for example if an orgainzation
would like to add custom rules that look for the existance of specific
UserID, specific SQLAgent Jobs, etc.
Thanks in advance for your assistance.
Dave
This is a feature request that the SQL team are well aware of but as you can
imagine with SQL2005 nearing the end of a long road (near being a relative
term!) it might be a while before a version of SQLBPA is released that
supports user defined rules.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"DBADave" <DBADave@.discussions.microsoft.com> wrote in message
news:405E1559-6CB7-4371-822B-CE4C523A5EF3@.microsoft.com...
> Hi All,
> The BPA is a great tool that can be used to audit an environment for
> adherenace to proper configuration and basic T-SQL coding best practices.
> Does anyone know if the BPA tool can be customized to include custom
> best-practice rules that are in-addition to the standard best practices
> included with the tool? For example, say for example if an orgainzation
> would like to add custom rules that look for the existance of specific
> UserID, specific SQLAgent Jobs, etc.
> Thanks in advance for your assistance.
> Dave

Best Performance Strip setting RAID

We are running SQL6.5 and plan to upgrade to SQL2000. I'm wondering what's the best stripe size for the RAID 5 configuration. 8,16,32 or 64 kb.
The database is 90% used for read actions. Only during night complete refill of data and write actions only for statistics. Any advise is welcome on this subjectSince SQL server pages are 64K the raid stripe settings must also be set to 64K|||SQL 6.5 uses 2k pages and SQL 2000 uses 8k pages.

The stripe size of your RAID drives does NOT have to follow the page size, however I would use a RAID stripe size >= to my page size.

Best performance for SQL2000

Hi
i must configure a RAID on external storage disk array for my SQL 2000
cluster ad have think this configuration :
1- DataFile on separate RAID5 LUN
2- LogFile on another separate RAID5 LUN
3- Quorum on another separate RAID1 LUN
This configugation is good for performace (datafile e logfile separated) and
security (Quorum on Mirror) !'!
Thanks in advanceMake the Log on a RAID 1 or a 10 and not a 5. Raid 5 has too many writes
for peak performance of logs.Put the extra disks into the data raid 5 or
make it a raid 10 for better performance.
--
Andrew J. Kelly SQL MVP
<io.com> wrote in message news:OfbXh0YoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Hi
> i must configure a RAID on external storage disk array for my SQL 2000
> cluster ad have think this configuration :
> 1- DataFile on separate RAID5 LUN
> 2- LogFile on another separate RAID5 LUN
> 3- Quorum on another separate RAID1 LUN
> This configugation is good for performace (datafile e logfile separated)
and
> security (Quorum on Mirror) !'!
> Thanks in advance
>|||Ok therefore :
datafile RAID5
logfile RAID1
quorum RAID1
it's ok ?
thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uFE6MNaoEHA.324@.TK2MSFTNGP11.phx.gbl...
> Make the Log on a RAID 1 or a 10 and not a 5. Raid 5 has too many writes
> for peak performance of logs.Put the extra disks into the data raid 5 or
> make it a raid 10 for better performance.
> --
> Andrew J. Kelly SQL MVP
>
> <io.com> wrote in message news:OfbXh0YoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> > Hi
> >
> > i must configure a RAID on external storage disk array for my SQL 2000
> > cluster ad have think this configuration :
> >
> > 1- DataFile on separate RAID5 LUN
> > 2- LogFile on another separate RAID5 LUN
> > 3- Quorum on another separate RAID1 LUN
> >
> > This configugation is good for performace (datafile e logfile separated)
> and
> > security (Quorum on Mirror) !'!
> >
> > Thanks in advance
> >
> >
>|||Yes
--
Andrew J. Kelly SQL MVP
<io.com> wrote in message news:ORYQO1aoEHA.1608@.TK2MSFTNGP15.phx.gbl...
> Ok therefore :
> datafile RAID5
> logfile RAID1
> quorum RAID1
> it's ok ?
> thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uFE6MNaoEHA.324@.TK2MSFTNGP11.phx.gbl...
> > Make the Log on a RAID 1 or a 10 and not a 5. Raid 5 has too many
writes
> > for peak performance of logs.Put the extra disks into the data raid 5 or
> > make it a raid 10 for better performance.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > <io.com> wrote in message news:OfbXh0YoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> > > Hi
> > >
> > > i must configure a RAID on external storage disk array for my SQL 2000
> > > cluster ad have think this configuration :
> > >
> > > 1- DataFile on separate RAID5 LUN
> > > 2- LogFile on another separate RAID5 LUN
> > > 3- Quorum on another separate RAID1 LUN
> > >
> > > This configugation is good for performace (datafile e logfile
separated)
> > and
> > > security (Quorum on Mirror) !'!
> > >
> > > Thanks in advance
> > >
> > >
> >
> >
>|||Even better:
datafile RAID10
logfile RAID1
quorum RAID1
Regards
Mike
"io.com" wrote:
> Ok therefore :
> datafile RAID5
> logfile RAID1
> quorum RAID1
> it's ok ?
> thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uFE6MNaoEHA.324@.TK2MSFTNGP11.phx.gbl...
> > Make the Log on a RAID 1 or a 10 and not a 5. Raid 5 has too many writes
> > for peak performance of logs.Put the extra disks into the data raid 5 or
> > make it a raid 10 for better performance.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > <io.com> wrote in message news:OfbXh0YoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> > > Hi
> > >
> > > i must configure a RAID on external storage disk array for my SQL 2000
> > > cluster ad have think this configuration :
> > >
> > > 1- DataFile on separate RAID5 LUN
> > > 2- LogFile on another separate RAID5 LUN
> > > 3- Quorum on another separate RAID1 LUN
> > >
> > > This configugation is good for performace (datafile e logfile separated)
> > and
> > > security (Quorum on Mirror) !'!
> > >
> > > Thanks in advance
> > >
> > >
> >
> >
>
>

Best Development Machine Configuration Recommendations

What is the best / recommended setup for a development system where you want
to develop using .NET 2.0 using VS 2005 against both SSRS 2000 and SSRS
2005?VS 2005 does not create reports that are compatible with SRS 2000. However,
the SRS 2000 report designer works against both SRS 2000 and 2005.
Thanks
Tudor
"Jim" wrote:
> What is the best / recommended setup for a development system where you want
> to develop using .NET 2.0 using VS 2005 against both SSRS 2000 and SSRS
> 2005?
>
>|||But, and this is important, those reports cannot provide for any of the 2005
features like end user sorting, multi-valued parameters etc.
I suggest installing both development environments. To do this you need VS
2003 and VS 2005. They will install side by side and both be usable (that is
what I have done).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tudor Trufinescu (MSFT)" <TudorTrufinescuMSFT@.discussions.microsoft.com>
wrote in message news:BEF6807F-D848-4C1E-AC6A-32706D9B8F33@.microsoft.com...
> VS 2005 does not create reports that are compatible with SRS 2000.
> However,
> the SRS 2000 report designer works against both SRS 2000 and 2005.
> Thanks
> Tudor
> "Jim" wrote:
>> What is the best / recommended setup for a development system where you
>> want
>> to develop using .NET 2.0 using VS 2005 against both SSRS 2000 and SSRS
>> 2005?
>>

Best configuration for SQL Server 2005

Hi
I am having a little trouble finding what is the ideal configuration
for running SQL Server 2005.
Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
Operating System will be Windows 2003 Standard Server R2
I can have any number of hard drives for either a Raid 1 or Raid 5
configuration or whatever, any comments appreciated?
How should the Hard Disk Drives be set up for a 10Gb Database?
Thanks
DominicHi
1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
2) Put tempdb database on different phsyical disk
3) Add much more RAM as you can and as it allowed by OS
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx --Per
formance
2005
http://www.microsoft.com/technet/pr...ce/default.mspx -
--Best
Practices 2005
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/pr...orma
nce
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.
.--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Excellent thanks
Dominic|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/pr...orma
nce
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.
.--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Hi
What is difference between tempDb and .mdf files?
Regards
Dominic|||Hi
> What is difference between tempDb and .mdf files?
Tempdb is a database (is a workspace) .It used for temporary tables
explicity created by users
.MDF is a Primary data file . Each database has .MDF and .LDF file . You ca
n
add additional data file (called secnadary .NDF)
<lorenzdominic_@.hotmail.com> wrote in message
news:1180318236.636287.270400@.j4g2000prf.googlegroups.com...
> On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> What is difference between tempDb and .mdf files?
> Regards
> Dominic
>|||In addition to Uri's comments:
- Make sure you have a battery-backed caching RAID controller with lots of
cache, and configure write caching on about half of the memory.
- Use 15k SCSI disks set up with Raid 1.
- Consider partitioning the database and any big tables.
The first point will gain you more than any other tweak you make.
Finally, buy and read the MS book "SQL Server 2005: Inside the Storage
Engine"
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||If you rely on what you can get from this group for something so major as
this (with so little information given to us) you are definitely going to
get suboptimal results. Hire a professional to assist you in this matter.
He/she can evaluate MANY more things than can be easily put forth in a
newsgroup and get you off on the right foot with the new system.
TheSQLGuru
President
Indicium Resources, Inc.
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>

Best configuration for SQL Server 2005

Hi
I am having a little trouble finding what is the ideal configuration
for running SQL Server 2005.
Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
Operating System will be Windows 2003 Standard Server R2
I can have any number of hard drives for either a Raid 1 or Raid 5
configuration or whatever, any comments appreciated?
How should the Hard Disk Drives be set up for a 10Gb Database?
Thanks
DominicHi
1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
2) Put tempdb database on different phsyical disk
3) Add much more RAM as you can and as it allowed by OS
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx --Performance
2005
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx --Best
Practices 2005
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
> > Hi
> > I am having a little trouble finding what is the ideal configuration
> > for running SQL Server 2005.
> > Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> > Operating System will be Windows 2003 Standard Server R2
> > I can have any number of hard drives for either a Raid 1 or Raid 5
> > configuration or whatever, any comments appreciated?
> > How should the Hard Disk Drives be set up for a 10Gb Database?
> > Thanks
> > Dominic- Hide quoted text -
> - Show quoted text -
Excellent thanks
Dominic|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>
> > Hi
> > I am having a little trouble finding what is the ideal configuration
> > for running SQL Server 2005.
> > Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> > Operating System will be Windows 2003 Standard Server R2
> > I can have any number of hard drives for either a Raid 1 or Raid 5
> > configuration or whatever, any comments appreciated?
> > How should the Hard Disk Drives be set up for a 10Gb Database?
> > Thanks
> > Dominic- Hide quoted text -
> - Show quoted text -
Hi
What is difference between tempDb and .mdf files?
Regards
Dominic|||Hi
> What is difference between tempDb and .mdf files?
Tempdb is a database (is a workspace) .It used for temporary tables
explicity created by users
.MDF is a Primary data file . Each database has .MDF and .LDF file . You can
add additional data file (called secnadary .NDF)
<lorenzdominic_@.hotmail.com> wrote in message
news:1180318236.636287.270400@.j4g2000prf.googlegroups.com...
> On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Hi
>> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
>> 2) Put tempdb database on different phsyical disk
>> 3) Add much more RAM as you can and as it allowed by OS
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
>> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
>> Practices 2005
>> <lorenzdomin...@.hotmail.com> wrote in message
>> news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
>>
>> > Hi
>> > I am having a little trouble finding what is the ideal configuration
>> > for running SQL Server 2005.
>> > Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
>> > Operating System will be Windows 2003 Standard Server R2
>> > I can have any number of hard drives for either a Raid 1 or Raid 5
>> > configuration or whatever, any comments appreciated?
>> > How should the Hard Disk Drives be set up for a 10Gb Database?
>> > Thanks
>> > Dominic- Hide quoted text -
>> - Show quoted text -
> Hi
> What is difference between tempDb and .mdf files?
> Regards
> Dominic
>|||In addition to Uri's comments:
- Make sure you have a battery-backed caching RAID controller with lots of
cache, and configure write caching on about half of the memory.
- Use 15k SCSI disks set up with Raid 1.
- Consider partitioning the database and any big tables.
The first point will gain you more than any other tweak you make.
Finally, buy and read the MS book "SQL Server 2005: Inside the Storage
Engine"
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>|||If you rely on what you can get from this group for something so major as
this (with so little information given to us) you are definitely going to
get suboptimal results. Hire a professional to assist you in this matter.
He/she can evaluate MANY more things than can be easily put forth in a
newsgroup and get you off on the right foot with the new system.
--
TheSQLGuru
President
Indicium Resources, Inc.
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegroups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>

Best configuration for SQL Server 2005

Hi
I am having a little trouble finding what is the ideal configuration
for running SQL Server 2005.
Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
Operating System will be Windows 2003 Standard Server R2
I can have any number of hard drives for either a Raid 1 or Raid 5
configuration or whatever, any comments appreciated?
How should the Hard Disk Drives be set up for a 10Gb Database?
Thanks
Dominic
Hi
1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
2) Put tempdb database on different phsyical disk
3) Add much more RAM as you can and as it allowed by OS
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx --Performance
2005
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx --Best
Practices 2005
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
>
>
>
>
> - Show quoted text -
Excellent thanks
Dominic
|||On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> 1) Separate .LDF (Log File) and .MDF (Data File) into phyical disks
> 2) Put tempdb database on different phsyical disk
> 3) Add much more RAM as you can and as it allowed by OS
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx--Performance
> 2005http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default...--Best
> Practices 2005
> <lorenzdomin...@.hotmail.com> wrote in message
> news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
>
>
>
>
> - Show quoted text -
Hi
What is difference between tempDb and .mdf files?
Regards
Dominic
|||Hi
> What is difference between tempDb and .mdf files?
Tempdb is a database (is a workspace) .It used for temporary tables
explicity created by users
..MDF is a Primary data file . Each database has .MDF and .LDF file . You can
add additional data file (called secnadary .NDF)
<lorenzdominic_@.hotmail.com> wrote in message
news:1180318236.636287.270400@.j4g2000prf.googlegro ups.com...
> On May 27, 4:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> What is difference between tempDb and .mdf files?
> Regards
> Dominic
>
|||In addition to Uri's comments:
- Make sure you have a battery-backed caching RAID controller with lots of
cache, and configure write caching on about half of the memory.
- Use 15k SCSI disks set up with Raid 1.
- Consider partitioning the database and any big tables.
The first point will gain you more than any other tweak you make.
Finally, buy and read the MS book "SQL Server 2005: Inside the Storage
Engine"
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>
|||If you rely on what you can get from this group for something so major as
this (with so little information given to us) you are definitely going to
get suboptimal results. Hire a professional to assist you in this matter.
He/she can evaluate MANY more things than can be easily put forth in a
newsgroup and get you off on the right foot with the new system.
TheSQLGuru
President
Indicium Resources, Inc.
<lorenzdominic_@.hotmail.com> wrote in message
news:1180246075.190524.45060@.q19g2000prn.googlegro ups.com...
> Hi
> I am having a little trouble finding what is the ideal configuration
> for running SQL Server 2005.
> Basically I have a 2 x Quad Core Processor Computer with 4Gb Ram.
> Operating System will be Windows 2003 Standard Server R2
> I can have any number of hard drives for either a Raid 1 or Raid 5
> configuration or whatever, any comments appreciated?
> How should the Hard Disk Drives be set up for a 10Gb Database?
> Thanks
> Dominic
>

Best configuration for Analysis Services

I am looking for opinions on what server configuration is best for Analysis
Services 2000.
Should we emphasize CPU speed/multiple CPUS, RAM?
We have a SAN attached via a Gigabit LAN.
Thanks.See if this helps
http://www.microsoft.com/technet/tr...ze/ANSvcsPG.asp
There is a section on hardware resources, as Tom Chester has quoted multiple
places, Analysis Services is very heavy on RAM and SQL2K AS can only use
3gb.
Ray Higdon MCSE, MCDBA, CCNA
--
"Bruce Lester" <bruce_lester@.email.com> wrote in message
news:wDy_b.391402$na.741449@.attbi_s04...
> I am looking for opinions on what server configuration is best for
Analysis
> Services 2000.
> Should we emphasize CPU speed/multiple CPUS, RAM?
> We have a SAN attached via a Gigabit LAN.
> Thanks.
>

Best Configuration for a 3 Node SQL 2000 Cluster on Windows 2003?

Ok, I've got the cluster setup and running, but having never done this,
I'm not sure if I'm setting up SQL right... We're trying to migrate
our multitude of SQL Server running on older hardware to the new
cluster, but I want to make sure we don't shoot ourselves in the foot.
Here is what we've got:
Specs:
3 HP BL20P Blade Server (Twin 3.6GHz Xeon, 4GB Ram)
1 HP MSA1000 w/ Twin Fibre Switches (Dual Path Redundancy)
Current Setup:
Windows 2003 Enterprise, 20GB C:, 10GB D: (Pagefile), 37GB E: Data
MSA1000 is currently configured with 4 36GB Arrays (Quorum, 2 for Trans
Logs, and 1 for Backups) and 2 120GB Arrays (Database Data), but there
is about 1.2TB left on the controller for additional space.
Followed all the instructions, Public IPs, Private IPs, etc...
Installed SQL on the first two nodes (SQLCL01 & SQLCL02) and have two
Virtual Servers (Same name, actual server name is longer and unique),
and then two instances, INST1 on SQLCL01, and INST2 on SQLCL02.
SQLCL03 is the failover server which of course is identical to the
first two. We're never expecting to have 2 fail, but we may add a 4th
Server/Node in the future (we have 5 additional slots in our two Blade
Chassis).

>From what I've read you "can" install up to 16 instances into a single
cluster, but obviously I don't know if #1 I should try and install more
than 2 instances, or #2 if I even can. I've tried rerunning the SQL
Setup just to see, and all it lets me do it "modify" the current
install or remove it, it won't let me add another instance.
So... What am I looking at here? Is this the optimum configuration
for now, or can I do more? What about memory? Should I limit each SQL
Server instance to a certain level of RAM, say 3GB? Or possibly 2GB,
incase both the two main servers ever fail and everything gets forced
to the 3rd? These servers will pretty much only be used for SQL,
nothing else, so there isnt' too much worry about applications battling
for memory.
Thanks in advance. I know some of these questions may seem rather
newbish, but I've installed and administered SQL2k before, but never a
cluster... so this is new ground for me and the documentation out
there is not very helpful, most of it refers to SQL2k on Windows 2000,
not Windows Server 2003.
Jon Casimir
Lotsa comments inline.
<kazsmir@.gmail.com> wrote in message
news:1125515891.775160.31810@.z14g2000cwz.googlegro ups.com...
> Ok, I've got the cluster setup and running, but having never done this,
> I'm not sure if I'm setting up SQL right... We're trying to migrate
> our multitude of SQL Server running on older hardware to the new
> cluster, but I want to make sure we don't shoot ourselves in the foot.
> Here is what we've got:
> Specs:
> 3 HP BL20P Blade Server (Twin 3.6GHz Xeon, 4GB Ram)
> 1 HP MSA1000 w/ Twin Fibre Switches (Dual Path Redundancy)
> Current Setup:
> Windows 2003 Enterprise, 20GB C:, 10GB D: (Pagefile), 37GB E: Data
> MSA1000 is currently configured with 4 36GB Arrays (Quorum, 2 for Trans
> Logs, and 1 for Backups) and 2 120GB Arrays (Database Data), but there
> is about 1.2TB left on the controller for additional space.
>
I am not a big fan of blade servers as cluster nodes. Too many single
failure points for what is intended to be a highly available system.
A well-tuned, dedicated SQL server should have minimal need for a paging
file. If you are paging heavily, you have something tuned wrong.
Backups should never be stored on the same host computer or storage array as
the primary data store, even if they are on separate physical disks. Backup
across the net to a file share for immediate use and archive those files to
tape for longer retention periods.

> Followed all the instructions, Public IPs, Private IPs, etc...
> Installed SQL on the first two nodes (SQLCL01 & SQLCL02) and have two
> Virtual Servers (Same name, actual server name is longer and unique),
> and then two instances, INST1 on SQLCL01, and INST2 on SQLCL02.
> SQLCL03 is the failover server which of course is identical to the
> first two. We're never expecting to have 2 fail, but we may add a 4th
> Server/Node in the future (we have 5 additional slots in our two Blade
> Chassis).
This doesn't sound right. You should have each Virtual Server\Instance
combination installed on all nodes on the cluster so you can fail over as
needed. During install time you can select which cluster nodes to install
SQL to. You can set the preferred node order of each Virtual Server later
independently so they start and fail where you choose.
On a multi-node, multi-instance cluster, I usually only worry about
first-order failures. If I have more than one instance go south on me, it
is usually the entire cluster that bombs. If I have one instance with a
problem, somebody competent better be standing in front of it fixing the
problem within 30 minutes. You can adjust memory settings and failover
order at that time.

> cluster, but obviously I don't know if #1 I should try and install more
> than 2 instances, or #2 if I even can. I've tried rerunning the SQL
> Setup just to see, and all it lets me do it "modify" the current
> install or remove it, it won't let me add another instance.
>
SQL won't let you add a new virtual server unless there is at least one
unassigned cluster disk resource to anchor the instance. Whether you
"should" install more instances is another matter. Each instance looks and
acts like a separate server on the network. Generally, multiple instances
in a cluster are used to manage security and performance. In my history, I
find that one instance per node + one spare is an optimal configuration, but
your needs with this server consolidation project may vary.

> So... What am I looking at here? Is this the optimum configuration
> for now, or can I do more? What about memory? Should I limit each SQL
> Server instance to a certain level of RAM, say 3GB? Or possibly 2GB,
> incase both the two main servers ever fail and everything gets forced
> to the 3rd? These servers will pretty much only be used for SQL,
> nothing else, so there isnt' too much worry about applications battling
> for memory.
Since you have paid for Enterprise Edition anyway you should max out the
memory, although your choice of blade servers as hosts may limit that
expansion capability. You will need to consider what happens during a
failover so that you can tolerate "stacking" multiple instances on the same
nost node.

> Thanks in advance. I know some of these questions may seem rather
> newbish, but I've installed and administered SQL2k before, but never a
> cluster... so this is new ground for me and the documentation out
> there is not very helpful, most of it refers to SQL2k on Windows 2000,
> not Windows Server 2003.
Most of the considerations for Windows 2000 clustering apply to Windows
2003, except for some installation gotchas. Unless you are sure something
from Windows 2000 doesn't apply, assume it does.
Now is the best time to ask "dumb" questions. Later, when your "highly
available" database solution that you bet your job on is down is the worst
time.

> Jon Casimir
>
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP