Is it better to divide an array into multiple partitions (or volumes) at the
OS level or into multiple logical drives at the hardware level? Does it mak
e
any difference to track alignment? Will one give greater future flexibility
over the other?
I have to install SQL Server on an HP Proliant server with 6 drives bays - I
would prefer more, but that is what I have available. I plan to create 3
RAID 1 arrays using the HP Array Configuration Manager to use as follows:
Array 1 (disks 0 & 1)
C:\ OS
S:\ SQL Server Installation
T:\ TempDB
Array 2 (disks 2 & 3)
P:\ Page File
D:\ Data
Array 3 (disks 4 & 5)
L:\ Transaction Logs
X:\ Backups
All partitions will be formatted with NTFS. I plan a stripe and cluster
size of 64KB for the data and TempDB partitions - all the others will be
default sizes. DB backups will be made to a local drive and then backed up
to tape over the network.
This configuration gives good separation for resillience. It should reduce
file fragmentation and I hope will give reasonable performance.
Thanks for any commentsWhy not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
partitions to split stff up if you want.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||Partitioning at the OS level does nothing to enhance or degrade performance.
The underlying RAID container has exactly the same IO capacity no matter how
you partition it. RAID containers can and do segment IO loads and can have
significant impact on performance.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||thanks Geoff
by RAID container I take it you mean a logical drive created at hearware
level with the array.
"Geoff N. Hiten" wrote:
> Partitioning at the OS level does nothing to enhance or degrade performanc
e.
> The underlying RAID container has exactly the same IO capacity no matter h
ow
> you partition it. RAID containers can and do segment IO loads and can hav
e
> significant impact on performance.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>|||Thanks Jason
I did consider this, but thought I would be better with the separation. Do
you think 1 RAID 10 array would perform better than 2 RAID 1?
"jason" wrote:
> Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still crea
te
> partitions to split stff up if you want.
>
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>|||Correct.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:D1EA04BC-EA2F-4F42-85B5-4057C9231E5C@.microsoft.com...[vbcol=seagreen]
> thanks Geoff
> by RAID container I take it you mean a logical drive created at hearware
> level with the array.
> "Geoff N. Hiten" wrote:
>|||You said "DB backups will be made to a local drive and then backed up to
tape over the network."
I don't know what way you'll use to copy your backups thru your network
however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
backup a database to a tape which is attached to a remote machine even if it
would be in the same network. The backup tape must be connected directly to
the SQL Server server to backup a database to tape.
Also, storing the OS and SQL Server binaries on the same drive would be OK
If tempdb is used in your environment frequently then you may consider
putting it on a dedicated disk which could be (if you don't need redundancy
or if you don't have enough resource for RAID1) RAID0.
However as Geoff mentioned, it would not make any difference to separate a
disk logically. You need physical disks to gain performance benefit for this
purpose.
You will want to give enough physical file space to your Transaction Log
file and Data files if you want less fragmentation.
Ekrem ?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||Thanks Ekrem
Our backup plan is to run SQL backups to disk, then backup these files using
an enterprise backup solution along with system state etc. This eliminates
some of the additional complexities of backing up across the network (the
backup is less likely to fail if there are network problems or the other
server isn't available) and also means we have a local backup if we need it
for a partial restore or corrupt database.
I would love to put TempDB on a separate disk, but that is not possible in
this server as it only has 6 drive bays. I think separating logs and data
probably has a higher priority. I would hesitate to put TempDB on RAID 0 as
it still needs to be present, even if there is no data to preserve, or SQL
Server will fail.
We desperately need to replace some very old hardware right now and we
already have this server. Buying replacement hardware is a project for the
future - that box will have more drives!
"Ekrem ?nsoy" wrote:
> You said "DB backups will be made to a local drive and then backed up to
> tape over the network."
> I don't know what way you'll use to copy your backups thru your network
> however I just wanted to stress that "BACKUP ... to TAPE" can not be used
to
> backup a database to a tape which is attached to a remote machine even if
it
> would be in the same network. The backup tape must be connected directly t
o
> the SQL Server server to backup a database to tape.
> Also, storing the OS and SQL Server binaries on the same drive would be OK
> If tempdb is used in your environment frequently then you may consider
> putting it on a dedicated disk which could be (if you don't need redundanc
y
> or if you don't have enough resource for RAID1) RAID0.
> However as Geoff mentioned, it would not make any difference to separate a
> disk logically. You need physical disks to gain performance benefit for th
is
> purpose.
> You will want to give enough physical file space to your Transaction Log
> file and Data files if you want less fragmentation.
> --
> Ekrem ?nsoy
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>|||Data and Log files have more priority for sure.
Ekrem ?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...[vbcol=seagreen]
> Thanks Ekrem
> Our backup plan is to run SQL backups to disk, then backup these files
> using
> an enterprise backup solution along with system state etc. This
> eliminates
> some of the additional complexities of backing up across the network (the
> backup is less likely to fail if there are network problems or the other
> server isn't available) and also means we have a local backup if we need
> it
> for a partial restore or corrupt database.
> I would love to put TempDB on a separate disk, but that is not possible in
> this server as it only has 6 drive bays. I think separating logs and data
> probably has a higher priority. I would hesitate to put TempDB on RAID 0
> as
> it still needs to be present, even if there is no data to preserve, or SQL
> Server will fail.
> We desperately need to replace some very old hardware right now and we
> already have this server. Buying replacement hardware is a project for
> the
> future - that box will have more drives!
>
> "Ekrem ?nsoy" wrote:
>|||Yes especially with the limited amount of drives. You are separating the
data and logs but you are also dividing the max throughput. BTW, in a small
hp config like this, I would set the stripe size in the ACU at 64k and the
cache to 0% read and 100% write(providing you have enough RAM). Sector align
with diskpart using a 128k offset as well.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:2BD76934-6814-46D7-BA51-EDBF09438BF0@.microsoft.com...[vbcol=seagreen]
> Thanks Jason
> I did consider this, but thought I would be better with the separation.
> Do
> you think 1 RAID 10 array would perform better than 2 RAID 1?
> "jason" wrote:
>
Showing posts with label partitions. Show all posts
Showing posts with label partitions. Show all posts
Monday, March 12, 2012
Hardware Logical Drives or OS Level Partitions?
Is it better to divide an array into multiple partitions (or volumes) at the
OS level or into multiple logical drives at the hardware level? Does it make
any difference to track alignment? Will one give greater future flexibility
over the other?
I have to install SQL Server on an HP Proliant server with 6 drives bays - I
would prefer more, but that is what I have available. I plan to create 3
RAID 1 arrays using the HP Array Configuration Manager to use as follows:
Array 1 (disks 0 & 1)
C:\ OS
S:\ SQL Server Installation
T:\ TempDB
Array 2 (disks 2 & 3)
P:\ Page File
D:\ Data
Array 3 (disks 4 & 5)
L:\ Transaction Logs
X:\ Backups
All partitions will be formatted with NTFS. I plan a stripe and cluster
size of 64KB for the data and TempDB partitions - all the others will be
default sizes. DB backups will be made to a local drive and then backed up
to tape over the network.
This configuration gives good separation for resillience. It should reduce
file fragmentation and I hope will give reasonable performance.
Thanks for any comments
Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
partitions to split stff up if you want.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments
|||Partitioning at the OS level does nothing to enhance or degrade performance.
The underlying RAID container has exactly the same IO capacity no matter how
you partition it. RAID containers can and do segment IO loads and can have
significant impact on performance.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments
|||thanks Geoff
by RAID container I take it you mean a logical drive created at hearware
level with the array.
"Geoff N. Hiten" wrote:
> Partitioning at the OS level does nothing to enhance or degrade performance.
> The underlying RAID container has exactly the same IO capacity no matter how
> you partition it. RAID containers can and do segment IO loads and can have
> significant impact on performance.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>
|||Thanks Jason
I did consider this, but thought I would be better with the separation. Do
you think 1 RAID 10 array would perform better than 2 RAID 1?
"jason" wrote:
> Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
> partitions to split stff up if you want.
>
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>
|||Correct.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:D1EA04BC-EA2F-4F42-85B5-4057C9231E5C@.microsoft.com...[vbcol=seagreen]
> thanks Geoff
> by RAID container I take it you mean a logical drive created at hearware
> level with the array.
> "Geoff N. Hiten" wrote:
|||You said "DB backups will be made to a local drive and then backed up to
tape over the network."
I don't know what way you'll use to copy your backups thru your network
however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
backup a database to a tape which is attached to a remote machine even if it
would be in the same network. The backup tape must be connected directly to
the SQL Server server to backup a database to tape.
Also, storing the OS and SQL Server binaries on the same drive would be OK
If tempdb is used in your environment frequently then you may consider
putting it on a dedicated disk which could be (if you don't need redundancy
or if you don't have enough resource for RAID1) RAID0.
However as Geoff mentioned, it would not make any difference to separate a
disk logically. You need physical disks to gain performance benefit for this
purpose.
You will want to give enough physical file space to your Transaction Log
file and Data files if you want less fragmentation.
Ekrem ?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments
|||Thanks Ekrem
Our backup plan is to run SQL backups to disk, then backup these files using
an enterprise backup solution along with system state etc. This eliminates
some of the additional complexities of backing up across the network (the
backup is less likely to fail if there are network problems or the other
server isn't available) and also means we have a local backup if we need it
for a partial restore or corrupt database.
I would love to put TempDB on a separate disk, but that is not possible in
this server as it only has 6 drive bays. I think separating logs and data
probably has a higher priority. I would hesitate to put TempDB on RAID 0 as
it still needs to be present, even if there is no data to preserve, or SQL
Server will fail.
We desperately need to replace some very old hardware right now and we
already have this server. Buying replacement hardware is a project for the
future - that box will have more drives!
"Ekrem ?nsoy" wrote:
> You said "DB backups will be made to a local drive and then backed up to
> tape over the network."
> I don't know what way you'll use to copy your backups thru your network
> however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
> backup a database to a tape which is attached to a remote machine even if it
> would be in the same network. The backup tape must be connected directly to
> the SQL Server server to backup a database to tape.
> Also, storing the OS and SQL Server binaries on the same drive would be OK
> If tempdb is used in your environment frequently then you may consider
> putting it on a dedicated disk which could be (if you don't need redundancy
> or if you don't have enough resource for RAID1) RAID0.
> However as Geoff mentioned, it would not make any difference to separate a
> disk logically. You need physical disks to gain performance benefit for this
> purpose.
> You will want to give enough physical file space to your Transaction Log
> file and Data files if you want less fragmentation.
> --
> Ekrem ?nsoy
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>
|||Data and Log files have more priority for sure.
Ekrem ?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...[vbcol=seagreen]
> Thanks Ekrem
> Our backup plan is to run SQL backups to disk, then backup these files
> using
> an enterprise backup solution along with system state etc. This
> eliminates
> some of the additional complexities of backing up across the network (the
> backup is less likely to fail if there are network problems or the other
> server isn't available) and also means we have a local backup if we need
> it
> for a partial restore or corrupt database.
> I would love to put TempDB on a separate disk, but that is not possible in
> this server as it only has 6 drive bays. I think separating logs and data
> probably has a higher priority. I would hesitate to put TempDB on RAID 0
> as
> it still needs to be present, even if there is no data to preserve, or SQL
> Server will fail.
> We desperately need to replace some very old hardware right now and we
> already have this server. Buying replacement hardware is a project for
> the
> future - that box will have more drives!
>
> "Ekrem ?nsoy" wrote:
|||Yes especially with the limited amount of drives. You are separating the
data and logs but you are also dividing the max throughput. BTW, in a small
hp config like this, I would set the stripe size in the ACU at 64k and the
cache to 0% read and 100% write(providing you have enough RAM). Sector align
with diskpart using a 128k offset as well.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:2BD76934-6814-46D7-BA51-EDBF09438BF0@.microsoft.com...[vbcol=seagreen]
> Thanks Jason
> I did consider this, but thought I would be better with the separation.
> Do
> you think 1 RAID 10 array would perform better than 2 RAID 1?
> "jason" wrote:
OS level or into multiple logical drives at the hardware level? Does it make
any difference to track alignment? Will one give greater future flexibility
over the other?
I have to install SQL Server on an HP Proliant server with 6 drives bays - I
would prefer more, but that is what I have available. I plan to create 3
RAID 1 arrays using the HP Array Configuration Manager to use as follows:
Array 1 (disks 0 & 1)
C:\ OS
S:\ SQL Server Installation
T:\ TempDB
Array 2 (disks 2 & 3)
P:\ Page File
D:\ Data
Array 3 (disks 4 & 5)
L:\ Transaction Logs
X:\ Backups
All partitions will be formatted with NTFS. I plan a stripe and cluster
size of 64KB for the data and TempDB partitions - all the others will be
default sizes. DB backups will be made to a local drive and then backed up
to tape over the network.
This configuration gives good separation for resillience. It should reduce
file fragmentation and I hope will give reasonable performance.
Thanks for any comments
Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
partitions to split stff up if you want.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments
|||Partitioning at the OS level does nothing to enhance or degrade performance.
The underlying RAID container has exactly the same IO capacity no matter how
you partition it. RAID containers can and do segment IO loads and can have
significant impact on performance.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments
|||thanks Geoff
by RAID container I take it you mean a logical drive created at hearware
level with the array.
"Geoff N. Hiten" wrote:
> Partitioning at the OS level does nothing to enhance or degrade performance.
> The underlying RAID container has exactly the same IO capacity no matter how
> you partition it. RAID containers can and do segment IO loads and can have
> significant impact on performance.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>
|||Thanks Jason
I did consider this, but thought I would be better with the separation. Do
you think 1 RAID 10 array would perform better than 2 RAID 1?
"jason" wrote:
> Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
> partitions to split stff up if you want.
>
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>
|||Correct.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:D1EA04BC-EA2F-4F42-85B5-4057C9231E5C@.microsoft.com...[vbcol=seagreen]
> thanks Geoff
> by RAID container I take it you mean a logical drive created at hearware
> level with the array.
> "Geoff N. Hiten" wrote:
|||You said "DB backups will be made to a local drive and then backed up to
tape over the network."
I don't know what way you'll use to copy your backups thru your network
however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
backup a database to a tape which is attached to a remote machine even if it
would be in the same network. The backup tape must be connected directly to
the SQL Server server to backup a database to tape.
Also, storing the OS and SQL Server binaries on the same drive would be OK
If tempdb is used in your environment frequently then you may consider
putting it on a dedicated disk which could be (if you don't need redundancy
or if you don't have enough resource for RAID1) RAID0.
However as Geoff mentioned, it would not make any difference to separate a
disk logically. You need physical disks to gain performance benefit for this
purpose.
You will want to give enough physical file space to your Transaction Log
file and Data files if you want less fragmentation.
Ekrem ?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments
|||Thanks Ekrem
Our backup plan is to run SQL backups to disk, then backup these files using
an enterprise backup solution along with system state etc. This eliminates
some of the additional complexities of backing up across the network (the
backup is less likely to fail if there are network problems or the other
server isn't available) and also means we have a local backup if we need it
for a partial restore or corrupt database.
I would love to put TempDB on a separate disk, but that is not possible in
this server as it only has 6 drive bays. I think separating logs and data
probably has a higher priority. I would hesitate to put TempDB on RAID 0 as
it still needs to be present, even if there is no data to preserve, or SQL
Server will fail.
We desperately need to replace some very old hardware right now and we
already have this server. Buying replacement hardware is a project for the
future - that box will have more drives!
"Ekrem ?nsoy" wrote:
> You said "DB backups will be made to a local drive and then backed up to
> tape over the network."
> I don't know what way you'll use to copy your backups thru your network
> however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
> backup a database to a tape which is attached to a remote machine even if it
> would be in the same network. The backup tape must be connected directly to
> the SQL Server server to backup a database to tape.
> Also, storing the OS and SQL Server binaries on the same drive would be OK
> If tempdb is used in your environment frequently then you may consider
> putting it on a dedicated disk which could be (if you don't need redundancy
> or if you don't have enough resource for RAID1) RAID0.
> However as Geoff mentioned, it would not make any difference to separate a
> disk logically. You need physical disks to gain performance benefit for this
> purpose.
> You will want to give enough physical file space to your Transaction Log
> file and Data files if you want less fragmentation.
> --
> Ekrem ?nsoy
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>
|||Data and Log files have more priority for sure.
Ekrem ?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...[vbcol=seagreen]
> Thanks Ekrem
> Our backup plan is to run SQL backups to disk, then backup these files
> using
> an enterprise backup solution along with system state etc. This
> eliminates
> some of the additional complexities of backing up across the network (the
> backup is less likely to fail if there are network problems or the other
> server isn't available) and also means we have a local backup if we need
> it
> for a partial restore or corrupt database.
> I would love to put TempDB on a separate disk, but that is not possible in
> this server as it only has 6 drive bays. I think separating logs and data
> probably has a higher priority. I would hesitate to put TempDB on RAID 0
> as
> it still needs to be present, even if there is no data to preserve, or SQL
> Server will fail.
> We desperately need to replace some very old hardware right now and we
> already have this server. Buying replacement hardware is a project for
> the
> future - that box will have more drives!
>
> "Ekrem ?nsoy" wrote:
|||Yes especially with the limited amount of drives. You are separating the
data and logs but you are also dividing the max throughput. BTW, in a small
hp config like this, I would set the stripe size in the ACU at 64k and the
cache to 0% read and 100% write(providing you have enough RAM). Sector align
with diskpart using a 128k offset as well.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:2BD76934-6814-46D7-BA51-EDBF09438BF0@.microsoft.com...[vbcol=seagreen]
> Thanks Jason
> I did consider this, but thought I would be better with the separation.
> Do
> you think 1 RAID 10 array would perform better than 2 RAID 1?
> "jason" wrote:
Hardware Logical Drives or OS Level Partitions?
Is it better to divide an array into multiple partitions (or volumes) at the
OS level or into multiple logical drives at the hardware level? Does it make
any difference to track alignment? Will one give greater future flexibility
over the other?
I have to install SQL Server on an HP Proliant server with 6 drives bays - I
would prefer more, but that is what I have available. I plan to create 3
RAID 1 arrays using the HP Array Configuration Manager to use as follows:
Array 1 (disks 0 & 1)
C:\ OS
S:\ SQL Server Installation
T:\ TempDB
Array 2 (disks 2 & 3)
P:\ Page File
D:\ Data
Array 3 (disks 4 & 5)
L:\ Transaction Logs
X:\ Backups
All partitions will be formatted with NTFS. I plan a stripe and cluster
size of 64KB for the data and TempDB partitions - all the others will be
default sizes. DB backups will be made to a local drive and then backed up
to tape over the network.
This configuration gives good separation for resillience. It should reduce
file fragmentation and I hope will give reasonable performance.
Thanks for any commentsWhy not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
partitions to split stff up if you want.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||Partitioning at the OS level does nothing to enhance or degrade performance.
The underlying RAID container has exactly the same IO capacity no matter how
you partition it. RAID containers can and do segment IO loads and can have
significant impact on performance.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||thanks Geoff
by RAID container I take it you mean a logical drive created at hearware
level with the array.
"Geoff N. Hiten" wrote:
> Partitioning at the OS level does nothing to enhance or degrade performance.
> The underlying RAID container has exactly the same IO capacity no matter how
> you partition it. RAID containers can and do segment IO loads and can have
> significant impact on performance.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> > Is it better to divide an array into multiple partitions (or volumes) at
> > the
> > OS level or into multiple logical drives at the hardware level? Does it
> > make
> > any difference to track alignment? Will one give greater future
> > flexibility
> > over the other?
> >
> > I have to install SQL Server on an HP Proliant server with 6 drives bays -
> > I
> > would prefer more, but that is what I have available. I plan to create 3
> > RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> >
> > Array 1 (disks 0 & 1)
> > C:\ OS
> > S:\ SQL Server Installation
> > T:\ TempDB
> >
> > Array 2 (disks 2 & 3)
> > P:\ Page File
> > D:\ Data
> >
> > Array 3 (disks 4 & 5)
> > L:\ Transaction Logs
> > X:\ Backups
> >
> > All partitions will be formatted with NTFS. I plan a stripe and cluster
> > size of 64KB for the data and TempDB partitions - all the others will be
> > default sizes. DB backups will be made to a local drive and then backed
> > up
> > to tape over the network.
> >
> > This configuration gives good separation for resillience. It should
> > reduce
> > file fragmentation and I hope will give reasonable performance.
> >
> > Thanks for any comments
>|||Thanks Jason
I did consider this, but thought I would be better with the separation. Do
you think 1 RAID 10 array would perform better than 2 RAID 1?
"jason" wrote:
> Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
> partitions to split stff up if you want.
>
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> > Is it better to divide an array into multiple partitions (or volumes) at
> > the
> > OS level or into multiple logical drives at the hardware level? Does it
> > make
> > any difference to track alignment? Will one give greater future
> > flexibility
> > over the other?
> >
> > I have to install SQL Server on an HP Proliant server with 6 drives bays -
> > I
> > would prefer more, but that is what I have available. I plan to create 3
> > RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> >
> > Array 1 (disks 0 & 1)
> > C:\ OS
> > S:\ SQL Server Installation
> > T:\ TempDB
> >
> > Array 2 (disks 2 & 3)
> > P:\ Page File
> > D:\ Data
> >
> > Array 3 (disks 4 & 5)
> > L:\ Transaction Logs
> > X:\ Backups
> >
> > All partitions will be formatted with NTFS. I plan a stripe and cluster
> > size of 64KB for the data and TempDB partitions - all the others will be
> > default sizes. DB backups will be made to a local drive and then backed
> > up
> > to tape over the network.
> >
> > This configuration gives good separation for resillience. It should
> > reduce
> > file fragmentation and I hope will give reasonable performance.
> >
> > Thanks for any comments
>|||Correct.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:D1EA04BC-EA2F-4F42-85B5-4057C9231E5C@.microsoft.com...
> thanks Geoff
> by RAID container I take it you mean a logical drive created at hearware
> level with the array.
> "Geoff N. Hiten" wrote:
>> Partitioning at the OS level does nothing to enhance or degrade
>> performance.
>> The underlying RAID container has exactly the same IO capacity no matter
>> how
>> you partition it. RAID containers can and do segment IO loads and can
>> have
>> significant impact on performance.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "andrew" <andrew@.discussions.microsoft.com> wrote in message
>> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>> > Is it better to divide an array into multiple partitions (or volumes)
>> > at
>> > the
>> > OS level or into multiple logical drives at the hardware level? Does
>> > it
>> > make
>> > any difference to track alignment? Will one give greater future
>> > flexibility
>> > over the other?
>> >
>> > I have to install SQL Server on an HP Proliant server with 6 drives
>> > bays -
>> > I
>> > would prefer more, but that is what I have available. I plan to create
>> > 3
>> > RAID 1 arrays using the HP Array Configuration Manager to use as
>> > follows:
>> >
>> > Array 1 (disks 0 & 1)
>> > C:\ OS
>> > S:\ SQL Server Installation
>> > T:\ TempDB
>> >
>> > Array 2 (disks 2 & 3)
>> > P:\ Page File
>> > D:\ Data
>> >
>> > Array 3 (disks 4 & 5)
>> > L:\ Transaction Logs
>> > X:\ Backups
>> >
>> > All partitions will be formatted with NTFS. I plan a stripe and
>> > cluster
>> > size of 64KB for the data and TempDB partitions - all the others will
>> > be
>> > default sizes. DB backups will be made to a local drive and then
>> > backed
>> > up
>> > to tape over the network.
>> >
>> > This configuration gives good separation for resillience. It should
>> > reduce
>> > file fragmentation and I hope will give reasonable performance.
>> >
>> > Thanks for any comments
>>|||You said "DB backups will be made to a local drive and then backed up to
tape over the network."
I don't know what way you'll use to copy your backups thru your network
however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
backup a database to a tape which is attached to a remote machine even if it
would be in the same network. The backup tape must be connected directly to
the SQL Server server to backup a database to tape.
Also, storing the OS and SQL Server binaries on the same drive would be OK
If tempdb is used in your environment frequently then you may consider
putting it on a dedicated disk which could be (if you don't need redundancy
or if you don't have enough resource for RAID1) RAID0.
However as Geoff mentioned, it would not make any difference to separate a
disk logically. You need physical disks to gain performance benefit for this
purpose.
You will want to give enough physical file space to your Transaction Log
file and Data files if you want less fragmentation.
--
Ekrem Ã?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||Thanks Ekrem
Our backup plan is to run SQL backups to disk, then backup these files using
an enterprise backup solution along with system state etc. This eliminates
some of the additional complexities of backing up across the network (the
backup is less likely to fail if there are network problems or the other
server isn't available) and also means we have a local backup if we need it
for a partial restore or corrupt database.
I would love to put TempDB on a separate disk, but that is not possible in
this server as it only has 6 drive bays. I think separating logs and data
probably has a higher priority. I would hesitate to put TempDB on RAID 0 as
it still needs to be present, even if there is no data to preserve, or SQL
Server will fail.
We desperately need to replace some very old hardware right now and we
already have this server. Buying replacement hardware is a project for the
future - that box will have more drives!
"Ekrem Ã?nsoy" wrote:
> You said "DB backups will be made to a local drive and then backed up to
> tape over the network."
> I don't know what way you'll use to copy your backups thru your network
> however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
> backup a database to a tape which is attached to a remote machine even if it
> would be in the same network. The backup tape must be connected directly to
> the SQL Server server to backup a database to tape.
> Also, storing the OS and SQL Server binaries on the same drive would be OK
> If tempdb is used in your environment frequently then you may consider
> putting it on a dedicated disk which could be (if you don't need redundancy
> or if you don't have enough resource for RAID1) RAID0.
> However as Geoff mentioned, it would not make any difference to separate a
> disk logically. You need physical disks to gain performance benefit for this
> purpose.
> You will want to give enough physical file space to your Transaction Log
> file and Data files if you want less fragmentation.
> --
> Ekrem Ã?nsoy
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> > Is it better to divide an array into multiple partitions (or volumes) at
> > the
> > OS level or into multiple logical drives at the hardware level? Does it
> > make
> > any difference to track alignment? Will one give greater future
> > flexibility
> > over the other?
> >
> > I have to install SQL Server on an HP Proliant server with 6 drives bays -
> > I
> > would prefer more, but that is what I have available. I plan to create 3
> > RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> >
> > Array 1 (disks 0 & 1)
> > C:\ OS
> > S:\ SQL Server Installation
> > T:\ TempDB
> >
> > Array 2 (disks 2 & 3)
> > P:\ Page File
> > D:\ Data
> >
> > Array 3 (disks 4 & 5)
> > L:\ Transaction Logs
> > X:\ Backups
> >
> > All partitions will be formatted with NTFS. I plan a stripe and cluster
> > size of 64KB for the data and TempDB partitions - all the others will be
> > default sizes. DB backups will be made to a local drive and then backed
> > up
> > to tape over the network.
> >
> > This configuration gives good separation for resillience. It should
> > reduce
> > file fragmentation and I hope will give reasonable performance.
> >
> > Thanks for any comments
>|||Data and Log files have more priority for sure.
--
Ekrem Ã?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...
> Thanks Ekrem
> Our backup plan is to run SQL backups to disk, then backup these files
> using
> an enterprise backup solution along with system state etc. This
> eliminates
> some of the additional complexities of backing up across the network (the
> backup is less likely to fail if there are network problems or the other
> server isn't available) and also means we have a local backup if we need
> it
> for a partial restore or corrupt database.
> I would love to put TempDB on a separate disk, but that is not possible in
> this server as it only has 6 drive bays. I think separating logs and data
> probably has a higher priority. I would hesitate to put TempDB on RAID 0
> as
> it still needs to be present, even if there is no data to preserve, or SQL
> Server will fail.
> We desperately need to replace some very old hardware right now and we
> already have this server. Buying replacement hardware is a project for
> the
> future - that box will have more drives!
>
> "Ekrem Ã?nsoy" wrote:
>> You said "DB backups will be made to a local drive and then backed up to
>> tape over the network."
>> I don't know what way you'll use to copy your backups thru your network
>> however I just wanted to stress that "BACKUP ... to TAPE" can not be used
>> to
>> backup a database to a tape which is attached to a remote machine even if
>> it
>> would be in the same network. The backup tape must be connected directly
>> to
>> the SQL Server server to backup a database to tape.
>> Also, storing the OS and SQL Server binaries on the same drive would be
>> OK
>> If tempdb is used in your environment frequently then you may consider
>> putting it on a dedicated disk which could be (if you don't need
>> redundancy
>> or if you don't have enough resource for RAID1) RAID0.
>> However as Geoff mentioned, it would not make any difference to separate
>> a
>> disk logically. You need physical disks to gain performance benefit for
>> this
>> purpose.
>> You will want to give enough physical file space to your Transaction Log
>> file and Data files if you want less fragmentation.
>> --
>> Ekrem Ã?nsoy
>>
>> "andrew" <andrew@.discussions.microsoft.com> wrote in message
>> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>> > Is it better to divide an array into multiple partitions (or volumes)
>> > at
>> > the
>> > OS level or into multiple logical drives at the hardware level? Does
>> > it
>> > make
>> > any difference to track alignment? Will one give greater future
>> > flexibility
>> > over the other?
>> >
>> > I have to install SQL Server on an HP Proliant server with 6 drives
>> > bays -
>> > I
>> > would prefer more, but that is what I have available. I plan to create
>> > 3
>> > RAID 1 arrays using the HP Array Configuration Manager to use as
>> > follows:
>> >
>> > Array 1 (disks 0 & 1)
>> > C:\ OS
>> > S:\ SQL Server Installation
>> > T:\ TempDB
>> >
>> > Array 2 (disks 2 & 3)
>> > P:\ Page File
>> > D:\ Data
>> >
>> > Array 3 (disks 4 & 5)
>> > L:\ Transaction Logs
>> > X:\ Backups
>> >
>> > All partitions will be formatted with NTFS. I plan a stripe and
>> > cluster
>> > size of 64KB for the data and TempDB partitions - all the others will
>> > be
>> > default sizes. DB backups will be made to a local drive and then
>> > backed
>> > up
>> > to tape over the network.
>> >
>> > This configuration gives good separation for resillience. It should
>> > reduce
>> > file fragmentation and I hope will give reasonable performance.
>> >
>> > Thanks for any comments|||Yes especially with the limited amount of drives. You are separating the
data and logs but you are also dividing the max throughput. BTW, in a small
hp config like this, I would set the stripe size in the ACU at 64k and the
cache to 0% read and 100% write(providing you have enough RAM). Sector align
with diskpart using a 128k offset as well.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:2BD76934-6814-46D7-BA51-EDBF09438BF0@.microsoft.com...
> Thanks Jason
> I did consider this, but thought I would be better with the separation.
> Do
> you think 1 RAID 10 array would perform better than 2 RAID 1?
> "jason" wrote:
>> Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still
>> create
>> partitions to split stff up if you want.
>>
>> --
>> Jason Massie
>> www: http://statisticsio.com
>> rss: http://feeds.feedburner.com/statisticsio
>>
>> "andrew" <andrew@.discussions.microsoft.com> wrote in message
>> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>> > Is it better to divide an array into multiple partitions (or volumes)
>> > at
>> > the
>> > OS level or into multiple logical drives at the hardware level? Does
>> > it
>> > make
>> > any difference to track alignment? Will one give greater future
>> > flexibility
>> > over the other?
>> >
>> > I have to install SQL Server on an HP Proliant server with 6 drives
>> > bays -
>> > I
>> > would prefer more, but that is what I have available. I plan to create
>> > 3
>> > RAID 1 arrays using the HP Array Configuration Manager to use as
>> > follows:
>> >
>> > Array 1 (disks 0 & 1)
>> > C:\ OS
>> > S:\ SQL Server Installation
>> > T:\ TempDB
>> >
>> > Array 2 (disks 2 & 3)
>> > P:\ Page File
>> > D:\ Data
>> >
>> > Array 3 (disks 4 & 5)
>> > L:\ Transaction Logs
>> > X:\ Backups
>> >
>> > All partitions will be formatted with NTFS. I plan a stripe and
>> > cluster
>> > size of 64KB for the data and TempDB partitions - all the others will
>> > be
>> > default sizes. DB backups will be made to a local drive and then
>> > backed
>> > up
>> > to tape over the network.
>> >
>> > This configuration gives good separation for resillience. It should
>> > reduce
>> > file fragmentation and I hope will give reasonable performance.
>> >
>> > Thanks for any comments|||Agreed on the relative priority of how to split data files.
I do disagree on backing up and restoring across the network. I do it all
the time, even with third-party compression tools, and have very little
trouble. Once you get the permissions correct, it is a snap. Here is a KB
article on how to do it with SQL 2000. The same permission rules apply to
SQL 2005. Getting the backup onto another system quickly to safeguard
against hardware failure is worth the extra hassle of setting it up.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...
> Thanks Ekrem
> Our backup plan is to run SQL backups to disk, then backup these files
> using
> an enterprise backup solution along with system state etc. This
> eliminates
> some of the additional complexities of backing up across the network (the
> backup is less likely to fail if there are network problems or the other
> server isn't available) and also means we have a local backup if we need
> it
> for a partial restore or corrupt database.
> I would love to put TempDB on a separate disk, but that is not possible in
> this server as it only has 6 drive bays. I think separating logs and data
> probably has a higher priority. I would hesitate to put TempDB on RAID 0
> as
> it still needs to be present, even if there is no data to preserve, or SQL
> Server will fail.
> We desperately need to replace some very old hardware right now and we
> already have this server. Buying replacement hardware is a project for
> the
> future - that box will have more drives!
>
> "Ekrem Ã?nsoy" wrote:
>> You said "DB backups will be made to a local drive and then backed up to
>> tape over the network."
>> I don't know what way you'll use to copy your backups thru your network
>> however I just wanted to stress that "BACKUP ... to TAPE" can not be used
>> to
>> backup a database to a tape which is attached to a remote machine even if
>> it
>> would be in the same network. The backup tape must be connected directly
>> to
>> the SQL Server server to backup a database to tape.
>> Also, storing the OS and SQL Server binaries on the same drive would be
>> OK
>> If tempdb is used in your environment frequently then you may consider
>> putting it on a dedicated disk which could be (if you don't need
>> redundancy
>> or if you don't have enough resource for RAID1) RAID0.
>> However as Geoff mentioned, it would not make any difference to separate
>> a
>> disk logically. You need physical disks to gain performance benefit for
>> this
>> purpose.
>> You will want to give enough physical file space to your Transaction Log
>> file and Data files if you want less fragmentation.
>> --
>> Ekrem Ã?nsoy
>>
>> "andrew" <andrew@.discussions.microsoft.com> wrote in message
>> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>> > Is it better to divide an array into multiple partitions (or volumes)
>> > at
>> > the
>> > OS level or into multiple logical drives at the hardware level? Does
>> > it
>> > make
>> > any difference to track alignment? Will one give greater future
>> > flexibility
>> > over the other?
>> >
>> > I have to install SQL Server on an HP Proliant server with 6 drives
>> > bays -
>> > I
>> > would prefer more, but that is what I have available. I plan to create
>> > 3
>> > RAID 1 arrays using the HP Array Configuration Manager to use as
>> > follows:
>> >
>> > Array 1 (disks 0 & 1)
>> > C:\ OS
>> > S:\ SQL Server Installation
>> > T:\ TempDB
>> >
>> > Array 2 (disks 2 & 3)
>> > P:\ Page File
>> > D:\ Data
>> >
>> > Array 3 (disks 4 & 5)
>> > L:\ Transaction Logs
>> > X:\ Backups
>> >
>> > All partitions will be formatted with NTFS. I plan a stripe and
>> > cluster
>> > size of 64KB for the data and TempDB partitions - all the others will
>> > be
>> > default sizes. DB backups will be made to a local drive and then
>> > backed
>> > up
>> > to tape over the network.
>> >
>> > This configuration gives good separation for resillience. It should
>> > reduce
>> > file fragmentation and I hope will give reasonable performance.
>> >
>> > Thanks for any comments|||Thanks guys, this is all really helpful stuff.
The whole area of storage config for SQL Server seems to be one where there
are lots of differing views out there on the web. It would be nice to have
the time to study the I/O patterns in detail over several weeks and
experiment with different setups, but it's difficult to justify that amount
of time in a busy schedule.
"Geoff N. Hiten" wrote:
> Agreed on the relative priority of how to split data files.
> I do disagree on backing up and restoring across the network. I do it all
> the time, even with third-party compression tools, and have very little
> trouble. Once you get the permissions correct, it is a snap. Here is a KB
> article on how to do it with SQL 2000. The same permission rules apply to
> SQL 2005. Getting the backup onto another system quickly to safeguard
> against hardware failure is worth the extra hassle of setting it up.
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/kb/555128
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...
> > Thanks Ekrem
> >
> > Our backup plan is to run SQL backups to disk, then backup these files
> > using
> > an enterprise backup solution along with system state etc. This
> > eliminates
> > some of the additional complexities of backing up across the network (the
> > backup is less likely to fail if there are network problems or the other
> > server isn't available) and also means we have a local backup if we need
> > it
> > for a partial restore or corrupt database.
> >
> > I would love to put TempDB on a separate disk, but that is not possible in
> > this server as it only has 6 drive bays. I think separating logs and data
> > probably has a higher priority. I would hesitate to put TempDB on RAID 0
> > as
> > it still needs to be present, even if there is no data to preserve, or SQL
> > Server will fail.
> >
> > We desperately need to replace some very old hardware right now and we
> > already have this server. Buying replacement hardware is a project for
> > the
> > future - that box will have more drives!
> >
> >
> > "Ekrem Ã?nsoy" wrote:
> >
> >> You said "DB backups will be made to a local drive and then backed up to
> >> tape over the network."
> >>
> >> I don't know what way you'll use to copy your backups thru your network
> >> however I just wanted to stress that "BACKUP ... to TAPE" can not be used
> >> to
> >> backup a database to a tape which is attached to a remote machine even if
> >> it
> >> would be in the same network. The backup tape must be connected directly
> >> to
> >> the SQL Server server to backup a database to tape.
> >>
> >> Also, storing the OS and SQL Server binaries on the same drive would be
> >> OK
> >> If tempdb is used in your environment frequently then you may consider
> >> putting it on a dedicated disk which could be (if you don't need
> >> redundancy
> >> or if you don't have enough resource for RAID1) RAID0.
> >>
> >> However as Geoff mentioned, it would not make any difference to separate
> >> a
> >> disk logically. You need physical disks to gain performance benefit for
> >> this
> >> purpose.
> >>
> >> You will want to give enough physical file space to your Transaction Log
> >> file and Data files if you want less fragmentation.
> >>
> >> --
> >> Ekrem Ã?nsoy
> >>
> >>
> >>
> >> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> >> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> >> > Is it better to divide an array into multiple partitions (or volumes)
> >> > at
> >> > the
> >> > OS level or into multiple logical drives at the hardware level? Does
> >> > it
> >> > make
> >> > any difference to track alignment? Will one give greater future
> >> > flexibility
> >> > over the other?
> >> >
> >> > I have to install SQL Server on an HP Proliant server with 6 drives
> >> > bays -
> >> > I
> >> > would prefer more, but that is what I have available. I plan to create
> >> > 3
> >> > RAID 1 arrays using the HP Array Configuration Manager to use as
> >> > follows:
> >> >
> >> > Array 1 (disks 0 & 1)
> >> > C:\ OS
> >> > S:\ SQL Server Installation
> >> > T:\ TempDB
> >> >
> >> > Array 2 (disks 2 & 3)
> >> > P:\ Page File
> >> > D:\ Data
> >> >
> >> > Array 3 (disks 4 & 5)
> >> > L:\ Transaction Logs
> >> > X:\ Backups
> >> >
> >> > All partitions will be formatted with NTFS. I plan a stripe and
> >> > cluster
> >> > size of 64KB for the data and TempDB partitions - all the others will
> >> > be
> >> > default sizes. DB backups will be made to a local drive and then
> >> > backed
> >> > up
> >> > to tape over the network.
> >> >
> >> > This configuration gives good separation for resillience. It should
> >> > reduce
> >> > file fragmentation and I hope will give reasonable performance.
> >> >
> >> > Thanks for any comments
> >>
>
OS level or into multiple logical drives at the hardware level? Does it make
any difference to track alignment? Will one give greater future flexibility
over the other?
I have to install SQL Server on an HP Proliant server with 6 drives bays - I
would prefer more, but that is what I have available. I plan to create 3
RAID 1 arrays using the HP Array Configuration Manager to use as follows:
Array 1 (disks 0 & 1)
C:\ OS
S:\ SQL Server Installation
T:\ TempDB
Array 2 (disks 2 & 3)
P:\ Page File
D:\ Data
Array 3 (disks 4 & 5)
L:\ Transaction Logs
X:\ Backups
All partitions will be formatted with NTFS. I plan a stripe and cluster
size of 64KB for the data and TempDB partitions - all the others will be
default sizes. DB backups will be made to a local drive and then backed up
to tape over the network.
This configuration gives good separation for resillience. It should reduce
file fragmentation and I hope will give reasonable performance.
Thanks for any commentsWhy not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
partitions to split stff up if you want.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||Partitioning at the OS level does nothing to enhance or degrade performance.
The underlying RAID container has exactly the same IO capacity no matter how
you partition it. RAID containers can and do segment IO loads and can have
significant impact on performance.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||thanks Geoff
by RAID container I take it you mean a logical drive created at hearware
level with the array.
"Geoff N. Hiten" wrote:
> Partitioning at the OS level does nothing to enhance or degrade performance.
> The underlying RAID container has exactly the same IO capacity no matter how
> you partition it. RAID containers can and do segment IO loads and can have
> significant impact on performance.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> > Is it better to divide an array into multiple partitions (or volumes) at
> > the
> > OS level or into multiple logical drives at the hardware level? Does it
> > make
> > any difference to track alignment? Will one give greater future
> > flexibility
> > over the other?
> >
> > I have to install SQL Server on an HP Proliant server with 6 drives bays -
> > I
> > would prefer more, but that is what I have available. I plan to create 3
> > RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> >
> > Array 1 (disks 0 & 1)
> > C:\ OS
> > S:\ SQL Server Installation
> > T:\ TempDB
> >
> > Array 2 (disks 2 & 3)
> > P:\ Page File
> > D:\ Data
> >
> > Array 3 (disks 4 & 5)
> > L:\ Transaction Logs
> > X:\ Backups
> >
> > All partitions will be formatted with NTFS. I plan a stripe and cluster
> > size of 64KB for the data and TempDB partitions - all the others will be
> > default sizes. DB backups will be made to a local drive and then backed
> > up
> > to tape over the network.
> >
> > This configuration gives good separation for resillience. It should
> > reduce
> > file fragmentation and I hope will give reasonable performance.
> >
> > Thanks for any comments
>|||Thanks Jason
I did consider this, but thought I would be better with the separation. Do
you think 1 RAID 10 array would perform better than 2 RAID 1?
"jason" wrote:
> Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still create
> partitions to split stff up if you want.
>
> --
> Jason Massie
> www: http://statisticsio.com
> rss: http://feeds.feedburner.com/statisticsio
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> > Is it better to divide an array into multiple partitions (or volumes) at
> > the
> > OS level or into multiple logical drives at the hardware level? Does it
> > make
> > any difference to track alignment? Will one give greater future
> > flexibility
> > over the other?
> >
> > I have to install SQL Server on an HP Proliant server with 6 drives bays -
> > I
> > would prefer more, but that is what I have available. I plan to create 3
> > RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> >
> > Array 1 (disks 0 & 1)
> > C:\ OS
> > S:\ SQL Server Installation
> > T:\ TempDB
> >
> > Array 2 (disks 2 & 3)
> > P:\ Page File
> > D:\ Data
> >
> > Array 3 (disks 4 & 5)
> > L:\ Transaction Logs
> > X:\ Backups
> >
> > All partitions will be formatted with NTFS. I plan a stripe and cluster
> > size of 64KB for the data and TempDB partitions - all the others will be
> > default sizes. DB backups will be made to a local drive and then backed
> > up
> > to tape over the network.
> >
> > This configuration gives good separation for resillience. It should
> > reduce
> > file fragmentation and I hope will give reasonable performance.
> >
> > Thanks for any comments
>|||Correct.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:D1EA04BC-EA2F-4F42-85B5-4057C9231E5C@.microsoft.com...
> thanks Geoff
> by RAID container I take it you mean a logical drive created at hearware
> level with the array.
> "Geoff N. Hiten" wrote:
>> Partitioning at the OS level does nothing to enhance or degrade
>> performance.
>> The underlying RAID container has exactly the same IO capacity no matter
>> how
>> you partition it. RAID containers can and do segment IO loads and can
>> have
>> significant impact on performance.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>>
>>
>> "andrew" <andrew@.discussions.microsoft.com> wrote in message
>> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>> > Is it better to divide an array into multiple partitions (or volumes)
>> > at
>> > the
>> > OS level or into multiple logical drives at the hardware level? Does
>> > it
>> > make
>> > any difference to track alignment? Will one give greater future
>> > flexibility
>> > over the other?
>> >
>> > I have to install SQL Server on an HP Proliant server with 6 drives
>> > bays -
>> > I
>> > would prefer more, but that is what I have available. I plan to create
>> > 3
>> > RAID 1 arrays using the HP Array Configuration Manager to use as
>> > follows:
>> >
>> > Array 1 (disks 0 & 1)
>> > C:\ OS
>> > S:\ SQL Server Installation
>> > T:\ TempDB
>> >
>> > Array 2 (disks 2 & 3)
>> > P:\ Page File
>> > D:\ Data
>> >
>> > Array 3 (disks 4 & 5)
>> > L:\ Transaction Logs
>> > X:\ Backups
>> >
>> > All partitions will be formatted with NTFS. I plan a stripe and
>> > cluster
>> > size of 64KB for the data and TempDB partitions - all the others will
>> > be
>> > default sizes. DB backups will be made to a local drive and then
>> > backed
>> > up
>> > to tape over the network.
>> >
>> > This configuration gives good separation for resillience. It should
>> > reduce
>> > file fragmentation and I hope will give reasonable performance.
>> >
>> > Thanks for any comments
>>|||You said "DB backups will be made to a local drive and then backed up to
tape over the network."
I don't know what way you'll use to copy your backups thru your network
however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
backup a database to a tape which is attached to a remote machine even if it
would be in the same network. The backup tape must be connected directly to
the SQL Server server to backup a database to tape.
Also, storing the OS and SQL Server binaries on the same drive would be OK
If tempdb is used in your environment frequently then you may consider
putting it on a dedicated disk which could be (if you don't need redundancy
or if you don't have enough resource for RAID1) RAID0.
However as Geoff mentioned, it would not make any difference to separate a
disk logically. You need physical disks to gain performance benefit for this
purpose.
You will want to give enough physical file space to your Transaction Log
file and Data files if you want less fragmentation.
--
Ekrem Ã?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> Is it better to divide an array into multiple partitions (or volumes) at
> the
> OS level or into multiple logical drives at the hardware level? Does it
> make
> any difference to track alignment? Will one give greater future
> flexibility
> over the other?
> I have to install SQL Server on an HP Proliant server with 6 drives bays -
> I
> would prefer more, but that is what I have available. I plan to create 3
> RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> Array 1 (disks 0 & 1)
> C:\ OS
> S:\ SQL Server Installation
> T:\ TempDB
> Array 2 (disks 2 & 3)
> P:\ Page File
> D:\ Data
> Array 3 (disks 4 & 5)
> L:\ Transaction Logs
> X:\ Backups
> All partitions will be formatted with NTFS. I plan a stripe and cluster
> size of 64KB for the data and TempDB partitions - all the others will be
> default sizes. DB backups will be made to a local drive and then backed
> up
> to tape over the network.
> This configuration gives good separation for resillience. It should
> reduce
> file fragmentation and I hope will give reasonable performance.
> Thanks for any comments|||Thanks Ekrem
Our backup plan is to run SQL backups to disk, then backup these files using
an enterprise backup solution along with system state etc. This eliminates
some of the additional complexities of backing up across the network (the
backup is less likely to fail if there are network problems or the other
server isn't available) and also means we have a local backup if we need it
for a partial restore or corrupt database.
I would love to put TempDB on a separate disk, but that is not possible in
this server as it only has 6 drive bays. I think separating logs and data
probably has a higher priority. I would hesitate to put TempDB on RAID 0 as
it still needs to be present, even if there is no data to preserve, or SQL
Server will fail.
We desperately need to replace some very old hardware right now and we
already have this server. Buying replacement hardware is a project for the
future - that box will have more drives!
"Ekrem Ã?nsoy" wrote:
> You said "DB backups will be made to a local drive and then backed up to
> tape over the network."
> I don't know what way you'll use to copy your backups thru your network
> however I just wanted to stress that "BACKUP ... to TAPE" can not be used to
> backup a database to a tape which is attached to a remote machine even if it
> would be in the same network. The backup tape must be connected directly to
> the SQL Server server to backup a database to tape.
> Also, storing the OS and SQL Server binaries on the same drive would be OK
> If tempdb is used in your environment frequently then you may consider
> putting it on a dedicated disk which could be (if you don't need redundancy
> or if you don't have enough resource for RAID1) RAID0.
> However as Geoff mentioned, it would not make any difference to separate a
> disk logically. You need physical disks to gain performance benefit for this
> purpose.
> You will want to give enough physical file space to your Transaction Log
> file and Data files if you want less fragmentation.
> --
> Ekrem Ã?nsoy
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> > Is it better to divide an array into multiple partitions (or volumes) at
> > the
> > OS level or into multiple logical drives at the hardware level? Does it
> > make
> > any difference to track alignment? Will one give greater future
> > flexibility
> > over the other?
> >
> > I have to install SQL Server on an HP Proliant server with 6 drives bays -
> > I
> > would prefer more, but that is what I have available. I plan to create 3
> > RAID 1 arrays using the HP Array Configuration Manager to use as follows:
> >
> > Array 1 (disks 0 & 1)
> > C:\ OS
> > S:\ SQL Server Installation
> > T:\ TempDB
> >
> > Array 2 (disks 2 & 3)
> > P:\ Page File
> > D:\ Data
> >
> > Array 3 (disks 4 & 5)
> > L:\ Transaction Logs
> > X:\ Backups
> >
> > All partitions will be formatted with NTFS. I plan a stripe and cluster
> > size of 64KB for the data and TempDB partitions - all the others will be
> > default sizes. DB backups will be made to a local drive and then backed
> > up
> > to tape over the network.
> >
> > This configuration gives good separation for resillience. It should
> > reduce
> > file fragmentation and I hope will give reasonable performance.
> >
> > Thanks for any comments
>|||Data and Log files have more priority for sure.
--
Ekrem Ã?nsoy
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...
> Thanks Ekrem
> Our backup plan is to run SQL backups to disk, then backup these files
> using
> an enterprise backup solution along with system state etc. This
> eliminates
> some of the additional complexities of backing up across the network (the
> backup is less likely to fail if there are network problems or the other
> server isn't available) and also means we have a local backup if we need
> it
> for a partial restore or corrupt database.
> I would love to put TempDB on a separate disk, but that is not possible in
> this server as it only has 6 drive bays. I think separating logs and data
> probably has a higher priority. I would hesitate to put TempDB on RAID 0
> as
> it still needs to be present, even if there is no data to preserve, or SQL
> Server will fail.
> We desperately need to replace some very old hardware right now and we
> already have this server. Buying replacement hardware is a project for
> the
> future - that box will have more drives!
>
> "Ekrem Ã?nsoy" wrote:
>> You said "DB backups will be made to a local drive and then backed up to
>> tape over the network."
>> I don't know what way you'll use to copy your backups thru your network
>> however I just wanted to stress that "BACKUP ... to TAPE" can not be used
>> to
>> backup a database to a tape which is attached to a remote machine even if
>> it
>> would be in the same network. The backup tape must be connected directly
>> to
>> the SQL Server server to backup a database to tape.
>> Also, storing the OS and SQL Server binaries on the same drive would be
>> OK
>> If tempdb is used in your environment frequently then you may consider
>> putting it on a dedicated disk which could be (if you don't need
>> redundancy
>> or if you don't have enough resource for RAID1) RAID0.
>> However as Geoff mentioned, it would not make any difference to separate
>> a
>> disk logically. You need physical disks to gain performance benefit for
>> this
>> purpose.
>> You will want to give enough physical file space to your Transaction Log
>> file and Data files if you want less fragmentation.
>> --
>> Ekrem Ã?nsoy
>>
>> "andrew" <andrew@.discussions.microsoft.com> wrote in message
>> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>> > Is it better to divide an array into multiple partitions (or volumes)
>> > at
>> > the
>> > OS level or into multiple logical drives at the hardware level? Does
>> > it
>> > make
>> > any difference to track alignment? Will one give greater future
>> > flexibility
>> > over the other?
>> >
>> > I have to install SQL Server on an HP Proliant server with 6 drives
>> > bays -
>> > I
>> > would prefer more, but that is what I have available. I plan to create
>> > 3
>> > RAID 1 arrays using the HP Array Configuration Manager to use as
>> > follows:
>> >
>> > Array 1 (disks 0 & 1)
>> > C:\ OS
>> > S:\ SQL Server Installation
>> > T:\ TempDB
>> >
>> > Array 2 (disks 2 & 3)
>> > P:\ Page File
>> > D:\ Data
>> >
>> > Array 3 (disks 4 & 5)
>> > L:\ Transaction Logs
>> > X:\ Backups
>> >
>> > All partitions will be formatted with NTFS. I plan a stripe and
>> > cluster
>> > size of 64KB for the data and TempDB partitions - all the others will
>> > be
>> > default sizes. DB backups will be made to a local drive and then
>> > backed
>> > up
>> > to tape over the network.
>> >
>> > This configuration gives good separation for resillience. It should
>> > reduce
>> > file fragmentation and I hope will give reasonable performance.
>> >
>> > Thanks for any comments|||Yes especially with the limited amount of drives. You are separating the
data and logs but you are also dividing the max throughput. BTW, in a small
hp config like this, I would set the stripe size in the ACU at 64k and the
cache to 0% read and 100% write(providing you have enough RAM). Sector align
with diskpart using a 128k offset as well.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:2BD76934-6814-46D7-BA51-EDBF09438BF0@.microsoft.com...
> Thanks Jason
> I did consider this, but thought I would be better with the separation.
> Do
> you think 1 RAID 10 array would perform better than 2 RAID 1?
> "jason" wrote:
>> Why not use disks 2-5 as RAID0+1 and move tempdb to it. You can still
>> create
>> partitions to split stff up if you want.
>>
>> --
>> Jason Massie
>> www: http://statisticsio.com
>> rss: http://feeds.feedburner.com/statisticsio
>>
>> "andrew" <andrew@.discussions.microsoft.com> wrote in message
>> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>> > Is it better to divide an array into multiple partitions (or volumes)
>> > at
>> > the
>> > OS level or into multiple logical drives at the hardware level? Does
>> > it
>> > make
>> > any difference to track alignment? Will one give greater future
>> > flexibility
>> > over the other?
>> >
>> > I have to install SQL Server on an HP Proliant server with 6 drives
>> > bays -
>> > I
>> > would prefer more, but that is what I have available. I plan to create
>> > 3
>> > RAID 1 arrays using the HP Array Configuration Manager to use as
>> > follows:
>> >
>> > Array 1 (disks 0 & 1)
>> > C:\ OS
>> > S:\ SQL Server Installation
>> > T:\ TempDB
>> >
>> > Array 2 (disks 2 & 3)
>> > P:\ Page File
>> > D:\ Data
>> >
>> > Array 3 (disks 4 & 5)
>> > L:\ Transaction Logs
>> > X:\ Backups
>> >
>> > All partitions will be formatted with NTFS. I plan a stripe and
>> > cluster
>> > size of 64KB for the data and TempDB partitions - all the others will
>> > be
>> > default sizes. DB backups will be made to a local drive and then
>> > backed
>> > up
>> > to tape over the network.
>> >
>> > This configuration gives good separation for resillience. It should
>> > reduce
>> > file fragmentation and I hope will give reasonable performance.
>> >
>> > Thanks for any comments|||Agreed on the relative priority of how to split data files.
I do disagree on backing up and restoring across the network. I do it all
the time, even with third-party compression tools, and have very little
trouble. Once you get the permissions correct, it is a snap. Here is a KB
article on how to do it with SQL 2000. The same permission rules apply to
SQL 2005. Getting the backup onto another system quickly to safeguard
against hardware failure is worth the extra hassle of setting it up.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/kb/555128
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"andrew" <andrew@.discussions.microsoft.com> wrote in message
news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...
> Thanks Ekrem
> Our backup plan is to run SQL backups to disk, then backup these files
> using
> an enterprise backup solution along with system state etc. This
> eliminates
> some of the additional complexities of backing up across the network (the
> backup is less likely to fail if there are network problems or the other
> server isn't available) and also means we have a local backup if we need
> it
> for a partial restore or corrupt database.
> I would love to put TempDB on a separate disk, but that is not possible in
> this server as it only has 6 drive bays. I think separating logs and data
> probably has a higher priority. I would hesitate to put TempDB on RAID 0
> as
> it still needs to be present, even if there is no data to preserve, or SQL
> Server will fail.
> We desperately need to replace some very old hardware right now and we
> already have this server. Buying replacement hardware is a project for
> the
> future - that box will have more drives!
>
> "Ekrem Ã?nsoy" wrote:
>> You said "DB backups will be made to a local drive and then backed up to
>> tape over the network."
>> I don't know what way you'll use to copy your backups thru your network
>> however I just wanted to stress that "BACKUP ... to TAPE" can not be used
>> to
>> backup a database to a tape which is attached to a remote machine even if
>> it
>> would be in the same network. The backup tape must be connected directly
>> to
>> the SQL Server server to backup a database to tape.
>> Also, storing the OS and SQL Server binaries on the same drive would be
>> OK
>> If tempdb is used in your environment frequently then you may consider
>> putting it on a dedicated disk which could be (if you don't need
>> redundancy
>> or if you don't have enough resource for RAID1) RAID0.
>> However as Geoff mentioned, it would not make any difference to separate
>> a
>> disk logically. You need physical disks to gain performance benefit for
>> this
>> purpose.
>> You will want to give enough physical file space to your Transaction Log
>> file and Data files if you want less fragmentation.
>> --
>> Ekrem Ã?nsoy
>>
>> "andrew" <andrew@.discussions.microsoft.com> wrote in message
>> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
>> > Is it better to divide an array into multiple partitions (or volumes)
>> > at
>> > the
>> > OS level or into multiple logical drives at the hardware level? Does
>> > it
>> > make
>> > any difference to track alignment? Will one give greater future
>> > flexibility
>> > over the other?
>> >
>> > I have to install SQL Server on an HP Proliant server with 6 drives
>> > bays -
>> > I
>> > would prefer more, but that is what I have available. I plan to create
>> > 3
>> > RAID 1 arrays using the HP Array Configuration Manager to use as
>> > follows:
>> >
>> > Array 1 (disks 0 & 1)
>> > C:\ OS
>> > S:\ SQL Server Installation
>> > T:\ TempDB
>> >
>> > Array 2 (disks 2 & 3)
>> > P:\ Page File
>> > D:\ Data
>> >
>> > Array 3 (disks 4 & 5)
>> > L:\ Transaction Logs
>> > X:\ Backups
>> >
>> > All partitions will be formatted with NTFS. I plan a stripe and
>> > cluster
>> > size of 64KB for the data and TempDB partitions - all the others will
>> > be
>> > default sizes. DB backups will be made to a local drive and then
>> > backed
>> > up
>> > to tape over the network.
>> >
>> > This configuration gives good separation for resillience. It should
>> > reduce
>> > file fragmentation and I hope will give reasonable performance.
>> >
>> > Thanks for any comments|||Thanks guys, this is all really helpful stuff.
The whole area of storage config for SQL Server seems to be one where there
are lots of differing views out there on the web. It would be nice to have
the time to study the I/O patterns in detail over several weeks and
experiment with different setups, but it's difficult to justify that amount
of time in a busy schedule.
"Geoff N. Hiten" wrote:
> Agreed on the relative priority of how to split data files.
> I do disagree on backing up and restoring across the network. I do it all
> the time, even with third-party compression tools, and have very little
> trouble. Once you get the permissions correct, it is a snap. Here is a KB
> article on how to do it with SQL 2000. The same permission rules apply to
> SQL 2005. Getting the backup onto another system quickly to safeguard
> against hardware failure is worth the extra hassle of setting it up.
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/kb/555128
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> news:13CD654F-8041-424B-903F-60389E725823@.microsoft.com...
> > Thanks Ekrem
> >
> > Our backup plan is to run SQL backups to disk, then backup these files
> > using
> > an enterprise backup solution along with system state etc. This
> > eliminates
> > some of the additional complexities of backing up across the network (the
> > backup is less likely to fail if there are network problems or the other
> > server isn't available) and also means we have a local backup if we need
> > it
> > for a partial restore or corrupt database.
> >
> > I would love to put TempDB on a separate disk, but that is not possible in
> > this server as it only has 6 drive bays. I think separating logs and data
> > probably has a higher priority. I would hesitate to put TempDB on RAID 0
> > as
> > it still needs to be present, even if there is no data to preserve, or SQL
> > Server will fail.
> >
> > We desperately need to replace some very old hardware right now and we
> > already have this server. Buying replacement hardware is a project for
> > the
> > future - that box will have more drives!
> >
> >
> > "Ekrem Ã?nsoy" wrote:
> >
> >> You said "DB backups will be made to a local drive and then backed up to
> >> tape over the network."
> >>
> >> I don't know what way you'll use to copy your backups thru your network
> >> however I just wanted to stress that "BACKUP ... to TAPE" can not be used
> >> to
> >> backup a database to a tape which is attached to a remote machine even if
> >> it
> >> would be in the same network. The backup tape must be connected directly
> >> to
> >> the SQL Server server to backup a database to tape.
> >>
> >> Also, storing the OS and SQL Server binaries on the same drive would be
> >> OK
> >> If tempdb is used in your environment frequently then you may consider
> >> putting it on a dedicated disk which could be (if you don't need
> >> redundancy
> >> or if you don't have enough resource for RAID1) RAID0.
> >>
> >> However as Geoff mentioned, it would not make any difference to separate
> >> a
> >> disk logically. You need physical disks to gain performance benefit for
> >> this
> >> purpose.
> >>
> >> You will want to give enough physical file space to your Transaction Log
> >> file and Data files if you want less fragmentation.
> >>
> >> --
> >> Ekrem Ã?nsoy
> >>
> >>
> >>
> >> "andrew" <andrew@.discussions.microsoft.com> wrote in message
> >> news:9498413C-FE6F-4DCA-B042-00B84890086B@.microsoft.com...
> >> > Is it better to divide an array into multiple partitions (or volumes)
> >> > at
> >> > the
> >> > OS level or into multiple logical drives at the hardware level? Does
> >> > it
> >> > make
> >> > any difference to track alignment? Will one give greater future
> >> > flexibility
> >> > over the other?
> >> >
> >> > I have to install SQL Server on an HP Proliant server with 6 drives
> >> > bays -
> >> > I
> >> > would prefer more, but that is what I have available. I plan to create
> >> > 3
> >> > RAID 1 arrays using the HP Array Configuration Manager to use as
> >> > follows:
> >> >
> >> > Array 1 (disks 0 & 1)
> >> > C:\ OS
> >> > S:\ SQL Server Installation
> >> > T:\ TempDB
> >> >
> >> > Array 2 (disks 2 & 3)
> >> > P:\ Page File
> >> > D:\ Data
> >> >
> >> > Array 3 (disks 4 & 5)
> >> > L:\ Transaction Logs
> >> > X:\ Backups
> >> >
> >> > All partitions will be formatted with NTFS. I plan a stripe and
> >> > cluster
> >> > size of 64KB for the data and TempDB partitions - all the others will
> >> > be
> >> > default sizes. DB backups will be made to a local drive and then
> >> > backed
> >> > up
> >> > to tape over the network.
> >> >
> >> > This configuration gives good separation for resillience. It should
> >> > reduce
> >> > file fragmentation and I hope will give reasonable performance.
> >> >
> >> > Thanks for any comments
> >>
>
Wednesday, March 7, 2012
Hard Drive Space and Disk Caching
Dear friends
I have what may be a basic question to most folks here. Does SQL manage disk caching across partitions or hard drives for greatest efficiency
Basically I have a small C drive partition reserved for basic operating systems stuff. All program and data files, including SQL and databases are on the much larger D and E drives. Would there ever be an issue of the C drive not being large enough and causing SQL to slow down or not perform as well
Someone told me that all disk caching happens on the C drive, which doesn't make sense to me. It seems to me that SQL would be smart enough to use other physical drive resources as needed. They are encouraging us to use Partition Magic to expand the size of our C drive (which currently has about 1 Gig available, to be expanded to between 5 and 10 Gig)
Does this seem like good advice?I do not understand what they are saying - by definition caching is =holding data locally for fast access, SQL Server holds data in memory =having read it from disc. What drive letter is utterly irrelevant to it =(and from a data management perspective the whole concept of drive =letters is horrid!)
SQL Server caching is fine across any drive letter - it cares little =about such things. I have one system where C is getting really tight =(due to successive service packs and fixes) but all the SQL server =stuff, pagefile etc is elsewhere and we have noticed no performance =problems.
Mike John
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message =news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> > I have what may be a basic question to most folks here. Does SQL =manage disk caching across partitions or hard drives for greatest =efficiency?
> > Basically I have a small C drive partition reserved for basic =operating systems stuff. All program and data files, including SQL and =databases are on the much larger D and E drives. Would there ever be an =issue of the C drive not being large enough and causing SQL to slow down =or not perform as well?
> > Someone told me that all disk caching happens on the C drive, which =doesn't make sense to me. It seems to me that SQL would be smart enough =to use other physical drive resources as needed. They are encouraging =us to use Partition Magic to expand the size of our C drive (which =currently has about 1 Gig available, to be expanded to between 5 and 10 =Gig).
> > Does this seem like good advice?|||Kevin,
First off sql servers cache is a memory based cache not a disk one. The
cache your thinking of is the OS cache or virtual memory. A properly tuned
Sql Server will not use this cache at all. And yes it is usually on the c:
drive. Second it sounds like your drives are all logical drives on the same
physical hard drive. If this is the case it doesn't matter since it is all
the same physical drive. Having multiple logical partitions is more of a
hindrance than a help in that case.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> I have what may be a basic question to most folks here. Does SQL manage
disk caching across partitions or hard drives for greatest efficiency?
> Basically I have a small C drive partition reserved for basic operating
systems stuff. All program and data files, including SQL and databases are
on the much larger D and E drives. Would there ever be an issue of the C
drive not being large enough and causing SQL to slow down or not perform as
well?
> Someone told me that all disk caching happens on the C drive, which
doesn't make sense to me. It seems to me that SQL would be smart enough to
use other physical drive resources as needed. They are encouraging us to
use Partition Magic to expand the size of our C drive (which currently has
about 1 Gig available, to be expanded to between 5 and 10 Gig).
> Does this seem like good advice?|||Dear Andriew and Mike,
Thank you both for your responses. It seems to me, from them, that we do not have an immediate need to re-partition our hard drive.
I did wonder what you had in mind Andrew, though, when you said:
"Having multiple logical partitions is more of a hindrance than a help in that case."
Pardon my naivete. Thanks!
In Peace,
K.|||Two things that I see often. One is that by having several logical drives
it tends to give the impression there are multiple physical drives. For
someone not familiar with the hardware they may make assumptions that are
wrong based on this. The other is that by having multiple logical
partitions you effectively limit how much space you have in each. Where as
if it was all one logical partition you don't have to worry about running
out of room until you hit the disk limit, not the partition limit. Hope
that helps.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:41D04118-B9C2-45BD-8D81-1179187976F6@.microsoft.com...
> Dear Andriew and Mike,
> Thank you both for your responses. It seems to me, from them, that we do
not have an immediate need to re-partition our hard drive.
> I did wonder what you had in mind Andrew, though, when you said:
> "Having multiple logical partitions is more of a hindrance than a help in
that case."
> Pardon my naivete. Thanks!
> In Peace,
> K.
>
I have what may be a basic question to most folks here. Does SQL manage disk caching across partitions or hard drives for greatest efficiency
Basically I have a small C drive partition reserved for basic operating systems stuff. All program and data files, including SQL and databases are on the much larger D and E drives. Would there ever be an issue of the C drive not being large enough and causing SQL to slow down or not perform as well
Someone told me that all disk caching happens on the C drive, which doesn't make sense to me. It seems to me that SQL would be smart enough to use other physical drive resources as needed. They are encouraging us to use Partition Magic to expand the size of our C drive (which currently has about 1 Gig available, to be expanded to between 5 and 10 Gig)
Does this seem like good advice?I do not understand what they are saying - by definition caching is =holding data locally for fast access, SQL Server holds data in memory =having read it from disc. What drive letter is utterly irrelevant to it =(and from a data management perspective the whole concept of drive =letters is horrid!)
SQL Server caching is fine across any drive letter - it cares little =about such things. I have one system where C is getting really tight =(due to successive service packs and fixes) but all the SQL server =stuff, pagefile etc is elsewhere and we have noticed no performance =problems.
Mike John
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message =news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> > I have what may be a basic question to most folks here. Does SQL =manage disk caching across partitions or hard drives for greatest =efficiency?
> > Basically I have a small C drive partition reserved for basic =operating systems stuff. All program and data files, including SQL and =databases are on the much larger D and E drives. Would there ever be an =issue of the C drive not being large enough and causing SQL to slow down =or not perform as well?
> > Someone told me that all disk caching happens on the C drive, which =doesn't make sense to me. It seems to me that SQL would be smart enough =to use other physical drive resources as needed. They are encouraging =us to use Partition Magic to expand the size of our C drive (which =currently has about 1 Gig available, to be expanded to between 5 and 10 =Gig).
> > Does this seem like good advice?|||Kevin,
First off sql servers cache is a memory based cache not a disk one. The
cache your thinking of is the OS cache or virtual memory. A properly tuned
Sql Server will not use this cache at all. And yes it is usually on the c:
drive. Second it sounds like your drives are all logical drives on the same
physical hard drive. If this is the case it doesn't matter since it is all
the same physical drive. Having multiple logical partitions is more of a
hindrance than a help in that case.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> I have what may be a basic question to most folks here. Does SQL manage
disk caching across partitions or hard drives for greatest efficiency?
> Basically I have a small C drive partition reserved for basic operating
systems stuff. All program and data files, including SQL and databases are
on the much larger D and E drives. Would there ever be an issue of the C
drive not being large enough and causing SQL to slow down or not perform as
well?
> Someone told me that all disk caching happens on the C drive, which
doesn't make sense to me. It seems to me that SQL would be smart enough to
use other physical drive resources as needed. They are encouraging us to
use Partition Magic to expand the size of our C drive (which currently has
about 1 Gig available, to be expanded to between 5 and 10 Gig).
> Does this seem like good advice?|||Dear Andriew and Mike,
Thank you both for your responses. It seems to me, from them, that we do not have an immediate need to re-partition our hard drive.
I did wonder what you had in mind Andrew, though, when you said:
"Having multiple logical partitions is more of a hindrance than a help in that case."
Pardon my naivete. Thanks!
In Peace,
K.|||Two things that I see often. One is that by having several logical drives
it tends to give the impression there are multiple physical drives. For
someone not familiar with the hardware they may make assumptions that are
wrong based on this. The other is that by having multiple logical
partitions you effectively limit how much space you have in each. Where as
if it was all one logical partition you don't have to worry about running
out of room until you hit the disk limit, not the partition limit. Hope
that helps.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:41D04118-B9C2-45BD-8D81-1179187976F6@.microsoft.com...
> Dear Andriew and Mike,
> Thank you both for your responses. It seems to me, from them, that we do
not have an immediate need to re-partition our hard drive.
> I did wonder what you had in mind Andrew, though, when you said:
> "Having multiple logical partitions is more of a hindrance than a help in
that case."
> Pardon my naivete. Thanks!
> In Peace,
> K.
>
Hard Drive Space and Disk Caching
Dear friends,
I have what may be a basic question to most folks here. Does SQL manage dis
k caching across partitions or hard drives for greatest efficiency?
Basically I have a small C drive partition reserved for basic operating syst
ems stuff. All program and data files, including SQL and databases are on t
he much larger D and E drives. Would there ever be an issue of the C drive
not being large enough and
causing SQL to slow down or not perform as well?
Someone told me that all disk caching happens on the C drive, which doesn't
make sense to me. It seems to me that SQL would be smart enough to use othe
r physical drive resources as needed. They are encouraging us to use Partit
ion Magic to expand the siz
e of our C drive (which currently has about 1 Gig available, to be expanded
to between 5 and 10 Gig).
Does this seem like good advice?I do not understand what they are saying - by definition caching is =
holding data locally for fast access, SQL Server holds data in memory =
having read it from disc. What drive letter is utterly irrelevant to it =
(and from a data management perspective the whole concept of drive =
letters is horrid!)=20
SQL Server caching is fine across any drive letter - it cares little =
about such things. I have one system where C is getting really tight =
(due to successive service packs and fixes) but all the SQL server =
stuff, pagefile etc is elsewhere and we have noticed no performance =
problems.
Mike John
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message =
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
>=20
> I have what may be a basic question to most folks here. Does SQL =
manage disk caching across partitions or hard drives for greatest =
efficiency?
>=20
> Basically I have a small C drive partition reserved for basic =
operating systems stuff. All program and data files, including SQL and =
databases are on the much larger D and E drives. Would there ever be an =
issue of the C drive not being large enough and causing SQL to slow down =
or not perform as well?
>=20
> Someone told me that all disk caching happens on the C drive, which =
doesn't make sense to me. It seems to me that SQL would be smart enough =
to use other physical drive resources as needed. They are encouraging =
us to use Partition Magic to expand the size of our C drive (which =
currently has about 1 Gig available, to be expanded to between 5 and 10 =
Gig).
>=20
> Does this seem like good advice?|||Kevin,
First off sql servers cache is a memory based cache not a disk one. The
cache your thinking of is the OS cache or virtual memory. A properly tuned
Sql Server will not use this cache at all. And yes it is usually on the c:
drive. Second it sounds like your drives are all logical drives on the same
physical hard drive. If this is the case it doesn't matter since it is all
the same physical drive. Having multiple logical partitions is more of a
hindrance than a help in that case.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> I have what may be a basic question to most folks here. Does SQL manage
disk caching across partitions or hard drives for greatest efficiency?
> Basically I have a small C drive partition reserved for basic operating
systems stuff. All program and data files, including SQL and databases are
on the much larger D and E drives. Would there ever be an issue of the C
drive not being large enough and causing SQL to slow down or not perform as
well?
> Someone told me that all disk caching happens on the C drive, which
doesn't make sense to me. It seems to me that SQL would be smart enough to
use other physical drive resources as needed. They are encouraging us to
use Partition Magic to expand the size of our C drive (which currently has
about 1 Gig available, to be expanded to between 5 and 10 Gig).
> Does this seem like good advice?|||Dear Andriew and Mike,
Thank you both for your responses. It seems to me, from them, that we do no
t have an immediate need to re-partition our hard drive.
I did wonder what you had in mind Andrew, though, when you said:
"Having multiple logical partitions is more of a hindrance than a help in th
at case."
Pardon my naivete. Thanks!
In Peace,
K.|||Two things that I see often. One is that by having several logical drives
it tends to give the impression there are multiple physical drives. For
someone not familiar with the hardware they may make assumptions that are
wrong based on this. The other is that by having multiple logical
partitions you effectively limit how much space you have in each. Where as
if it was all one logical partition you don't have to worry about running
out of room until you hit the disk limit, not the partition limit. Hope
that helps.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:41D04118-B9C2-45BD-8D81-1179187976F6@.microsoft.com...
> Dear Andriew and Mike,
> Thank you both for your responses. It seems to me, from them, that we do
not have an immediate need to re-partition our hard drive.
> I did wonder what you had in mind Andrew, though, when you said:
> "Having multiple logical partitions is more of a hindrance than a help in
that case."
> Pardon my naivete. Thanks!
> In Peace,
> K.
>
I have what may be a basic question to most folks here. Does SQL manage dis
k caching across partitions or hard drives for greatest efficiency?
Basically I have a small C drive partition reserved for basic operating syst
ems stuff. All program and data files, including SQL and databases are on t
he much larger D and E drives. Would there ever be an issue of the C drive
not being large enough and
causing SQL to slow down or not perform as well?
Someone told me that all disk caching happens on the C drive, which doesn't
make sense to me. It seems to me that SQL would be smart enough to use othe
r physical drive resources as needed. They are encouraging us to use Partit
ion Magic to expand the siz
e of our C drive (which currently has about 1 Gig available, to be expanded
to between 5 and 10 Gig).
Does this seem like good advice?I do not understand what they are saying - by definition caching is =
holding data locally for fast access, SQL Server holds data in memory =
having read it from disc. What drive letter is utterly irrelevant to it =
(and from a data management perspective the whole concept of drive =
letters is horrid!)=20
SQL Server caching is fine across any drive letter - it cares little =
about such things. I have one system where C is getting really tight =
(due to successive service packs and fixes) but all the SQL server =
stuff, pagefile etc is elsewhere and we have noticed no performance =
problems.
Mike John
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message =
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
>=20
> I have what may be a basic question to most folks here. Does SQL =
manage disk caching across partitions or hard drives for greatest =
efficiency?
>=20
> Basically I have a small C drive partition reserved for basic =
operating systems stuff. All program and data files, including SQL and =
databases are on the much larger D and E drives. Would there ever be an =
issue of the C drive not being large enough and causing SQL to slow down =
or not perform as well?
>=20
> Someone told me that all disk caching happens on the C drive, which =
doesn't make sense to me. It seems to me that SQL would be smart enough =
to use other physical drive resources as needed. They are encouraging =
us to use Partition Magic to expand the size of our C drive (which =
currently has about 1 Gig available, to be expanded to between 5 and 10 =
Gig).
>=20
> Does this seem like good advice?|||Kevin,
First off sql servers cache is a memory based cache not a disk one. The
cache your thinking of is the OS cache or virtual memory. A properly tuned
Sql Server will not use this cache at all. And yes it is usually on the c:
drive. Second it sounds like your drives are all logical drives on the same
physical hard drive. If this is the case it doesn't matter since it is all
the same physical drive. Having multiple logical partitions is more of a
hindrance than a help in that case.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:4376A82B-AC21-463C-BCDD-B124FE980C98@.microsoft.com...
> Dear friends,
> I have what may be a basic question to most folks here. Does SQL manage
disk caching across partitions or hard drives for greatest efficiency?
> Basically I have a small C drive partition reserved for basic operating
systems stuff. All program and data files, including SQL and databases are
on the much larger D and E drives. Would there ever be an issue of the C
drive not being large enough and causing SQL to slow down or not perform as
well?
> Someone told me that all disk caching happens on the C drive, which
doesn't make sense to me. It seems to me that SQL would be smart enough to
use other physical drive resources as needed. They are encouraging us to
use Partition Magic to expand the size of our C drive (which currently has
about 1 Gig available, to be expanded to between 5 and 10 Gig).
> Does this seem like good advice?|||Dear Andriew and Mike,
Thank you both for your responses. It seems to me, from them, that we do no
t have an immediate need to re-partition our hard drive.
I did wonder what you had in mind Andrew, though, when you said:
"Having multiple logical partitions is more of a hindrance than a help in th
at case."
Pardon my naivete. Thanks!
In Peace,
K.|||Two things that I see often. One is that by having several logical drives
it tends to give the impression there are multiple physical drives. For
someone not familiar with the hardware they may make assumptions that are
wrong based on this. The other is that by having multiple logical
partitions you effectively limit how much space you have in each. Where as
if it was all one logical partition you don't have to worry about running
out of room until you hit the disk limit, not the partition limit. Hope
that helps.
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:41D04118-B9C2-45BD-8D81-1179187976F6@.microsoft.com...
> Dear Andriew and Mike,
> Thank you both for your responses. It seems to me, from them, that we do
not have an immediate need to re-partition our hard drive.
> I did wonder what you had in mind Andrew, though, when you said:
> "Having multiple logical partitions is more of a hindrance than a help in
that case."
> Pardon my naivete. Thanks!
> In Peace,
> K.
>
Subscribe to:
Posts (Atom)