sqlserver 2005：raid-0上的索引？ [英] sqlserver 2005: indexes on raid-0?
问题描述
我正在计划磁盘布局并用于新版本的
数据库。当前版本具有默认的
文件组中的所有数据和索引，放在一个大的raid-5阵列（6个驱动器）以及
事务日志中。性能并不是最好的，正如您可能想象的那样......
下周我们将添加另外14个驱动器并组织它们以不同的方式组合它们。 10和raid-1，然后创建几个文件组和
将表和索引数据放在不同的物理驱动器上。
大部分数据库将被放置在一个raid-10数组，有些部分
（表/索引/ translog）将有自己的raid-1驱动器。
我去过在我们正在添加的一个或两个新磁盘阵列中使用raid-0代替
raid-1的相当错误的想法然后
place（这些驱动器上的索引。
理论上说，即使一个驱动器出现故障，数据库也会保持运行状态，并且它很容易重新创建更换磁盘时的索引。
（我们将有一个热备份）
有谁知道sqlserver 2005处理磁盘丢失的程度如何
con形象？
还有其他意见吗？ ;-)
boa
I''m currently planning disk layouts and use for a new version of our
database. The current version has all data and indexes in the default
filegroup, placed on one big raid-5 array(6 drives) along with the
transaction log. Performance is not the best, as you may imagine...
Next week we will add another 14 drives and organize them in different
combos of raid-10 and raid-1, and then create several filegroups and
place tables and index data on separate physical drives.
Most of the database will be placed on a raid-10 array, and some parts
(tables/indexes/translog) will have their own raid-1 drives.
I''ve been playing with the rather incorrect idea of using raid-0 instead
of raid-1 on one or two of the new disk arrays we''re adding and then
place (some) indexes on those drives.
The theory is that even if one drive fails, the db will stay up and it
will be easy to recreate the indexes when the disk has been replaced.
(We will have one hot spare available)
Does anyone know how well sqlserver 2005 handles disk loss in such a
configuration?
Any other comments? ;-)
boa
推荐答案
Boa：
我不能说话当索引由于
驱动器故障而消失时会发生什么。
然而，我因为写入的开销而远离raid-5。
更糟糕的是，当驱动器发生故障时，它必须进行N次读取才能重建缺失的数据。你拥有的驱动器越多，读取的内容就越多。 （我必须从每个驱动器读取数据条并读取校验和
以重建丢失驱动器中的数据）。
我说了所有这些，你应该看到更好的性能来自你raid
10或1个驱动器阵列。
另外，如果系统中有多个控制器来放置日志
与不同控制器上的数据相比。
我在一个控制器上有两个逻辑驱动器是什么阵列。
在这种情况下，我看不到拆分日志和数据变得更好
表现;他们使用相同的物理驱动器。
HTH
boa写道：
Boa:
I cant speak of what happens when an index disaapears because of a
drive failure.
However, I steer away from raid-5 as the have overhead for writes.
Worse, when a drive fails, it has to do N reads in order to reconstruct
the missing data. The more drives you have, the more reads it has to
do. (I has to read the data strip from each drive and read the checksum
to reconstruct the data from the missing drive).
I said all that to say you should see better performance from you raid
10 or 1 drive arrays.
Also, if you have more than one controller in the system to put logs
vs. data on the different controlers.
I''ve had two logical drives on a single controller what was an array.
In that situation I cant see splitting logs and data to get better
performance; they are using the same physical drives.
HTH
boa wrote:
我正在筹划磁盘布局和用于我们的
数据库的新版本。当前版本具有默认的
文件组中的所有数据和索引，放在一个大的raid-5阵列（6个驱动器）以及
事务日志中。性能并不是最好的，正如您可能想象的那样......
下周我们将添加另外14个驱动器并组织它们以不同的方式组合它们。 10和raid-1，然后创建几个文件组和
将表和索引数据放在不同的物理驱动器上。
大部分数据库将被放置在一个raid-10数组，有些部分
（表/索引/ translog）将有自己的raid-1驱动器。
我去过在我们正在添加的一个或两个新磁盘阵列中使用raid-0代替
raid-1的相当错误的想法然后
place（这些驱动器上的索引。
理论上说，即使一个驱动器出现故障，数据库也会保持运行状态，并且它很容易重新创建更换磁盘时的索引。
（我们将有一个热备份）
有谁知道sqlserver 2005处理磁盘丢失的程度如何
configur这个？
还有其他评论吗？ ;-)
boa
I''m currently planning disk layouts and use for a new version of our
database. The current version has all data and indexes in the default
filegroup, placed on one big raid-5 array(6 drives) along with the
transaction log. Performance is not the best, as you may imagine...
Next week we will add another 14 drives and organize them in different
combos of raid-10 and raid-1, and then create several filegroups and
place tables and index data on separate physical drives.
Most of the database will be placed on a raid-10 array, and some parts
(tables/indexes/translog) will have their own raid-1 drives.
I''ve been playing with the rather incorrect idea of using raid-0 instead
of raid-1 on one or two of the new disk arrays we''re adding and then
place (some) indexes on those drives.
The theory is that even if one drive fails, the db will stay up and it
will be easy to recreate the indexes when the disk has been replaced.
(We will have one hot spare available)
Does anyone know how well sqlserver 2005 handles disk loss in such a
configuration?
Any other comments? ;-)
boa
* rcamarda写道，On 20.08.2006 13:04：
* rcamarda wrote, On 20.08.2006 13:04:
Boa：
我不能说当索引因为
驱动器故障而消失时会发生什么。
但是，我避开了raid-5，因为它有写入的开销。
Boa:
I cant speak of what happens when an index disaapears because of a
drive failure.
However, I steer away from raid-5 as the have overhead for writes.
我想我在原帖中有点不清楚。当前版本的
数据库驻留在一个raid-5阵列上，我们的
数据库的新/下一个版本将放在raid-10和raid的组合上-1阵列。
所以raid-5出来了，永远:-)
Boa
[snip]
I guess I was a bit unclear in the original post. The current version of
the database resides on a raid-5 array, the new/next version of our
database will be placed on a combination of raid-10 and raid-1 arrays.
So raid-5 is out, forever :-)
Boa
[snip]
理论上说，即使一个驱动器出现故障，数据库也会保持运行，并且
The theory is that even if one drive fails, the db will stay up and it
$更换磁盘后，b $ b将很容易重新创建索引。 （我们
将有一个热备份）
有谁知道sqlserver 2005在这样的情况下如何处理磁盘丢失
组态？
will be easy to recreate the indexes when the disk has been replaced. (We
will have one hot spare available)
Does anyone know how well sqlserver 2005 handles disk loss in such a
configuration?
我见过一个带有可移动驱动器上文件的SQL 2005演示，可能会提供一些洞察力。在该演示中，只要事务日志可用，USB驱动器就会在物理上删除数据库时数据库保持在线状态。
当访问缓存数据时，命令继续成功运行
甚至更新成功。但是，需要物理I / O到缺少文件（非缓存数据）的命令失败。当SQL Server重新启动时，数据库被标记为可疑
，因为
缺少文件无法进行恢复。
丢失索引文件后无法轻松重新创建索引，因为文件丢失后需要完全恢复数据库。您需要从完全备份恢复
并应用事务日志备份。但是，至少
，数据库在降级期间将部分可用。
我建议你坚持使用RAID-10，如果高可用性在你的
环境。磁盘存储现在很便宜。
-
希望这会有所帮助。
Dan Guzman
SQL Server MVP
" boa" < bo ***** @ gmail.com写信息
新闻：MZ ******************** @ telenor.com。 ..
I''ve seen a SQL 2005 demo with files on removable drives that may provide
some insight. In that demo, the database remained online when USB drives
were physically removed as long as the transaction log was available.
Commands continued to run successfully when cached data were accessed and
even updates succeeded. However, commands that required physical I/O to
missing files (non-cached data) failed. The database was marked suspect
when SQL Server was restarted because recovery couldn''t take place with
missing files.
You cannot easily recreate the indexes after you lose index files because a
full database restore is required after files are lost. You''ll need to
restore from full backup and apply transaction log backups. However, at
least the database will be partially available during the degradation.
I suggest you stick with RAID-10 if high availability is important in your
environment. Disk storage is inexpensive nowadays.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"boa" <bo*****@gmail.comwrote in message
news:MZ********************@telenor.com...
我正在计划磁盘布局并用于新版本的
数据库。当前版本具有默认的
文件组中的所有数据和索引，放在一个大的raid-5阵列（6个驱动器）以及
事务日志中。性能并不是最好的，正如您可能想象的那样......
下周我们将添加另外14个驱动器并组织它们以不同的方式组合它们。 10和raid-1，然后创建几个文件组，并将
表和索引数据放在不同的物理驱动器上。
大部分数据库将被放置在一个raid-10数组，有些部分
（表/索引/ translog）将有自己的raid-1驱动器。
我去过在我们正在添加的一个或两个新磁盘阵列中使用raid-0而不是使用raid-1的
的相当错误的想法，然后放置
（这些驱动器上的索引。
理论上说，即使一个驱动器出现故障，数据库也会保持运行状态，并且它很容易重新创建磁盘更换后的索引。 （我们
将有一个热备份）
有谁知道sqlserver 2005在这样的情况下如何处理磁盘丢失
配置？
还有其他意见吗？ ;-)
boa
I''m currently planning disk layouts and use for a new version of our
database. The current version has all data and indexes in the default
filegroup, placed on one big raid-5 array(6 drives) along with the
transaction log. Performance is not the best, as you may imagine...
Next week we will add another 14 drives and organize them in different
combos of raid-10 and raid-1, and then create several filegroups and place
tables and index data on separate physical drives.
Most of the database will be placed on a raid-10 array, and some parts
(tables/indexes/translog) will have their own raid-1 drives.
I''ve been playing with the rather incorrect idea of using raid-0 instead
of raid-1 on one or two of the new disk arrays we''re adding and then place
(some) indexes on those drives.
The theory is that even if one drive fails, the db will stay up and it
will be easy to recreate the indexes when the disk has been replaced. (We
will have one hot spare available)
Does anyone know how well sqlserver 2005 handles disk loss in such a
configuration?
Any other comments? ;-)
boa
这篇关于sqlserver 2005：raid-0上的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
