什么是SQL Server 2000的最佳存储空间?

时间:2021-06-13 16:48:47

I want to access my sql server database files in a INTEL SS4000-E storage. It´s a NAS Storage. Could it be possible to work with it as a storage for sql server 2000? If not, what is the best solution?

我想访问INTEL SS4000-E存储中的sql server数据库文件。这是一个NAS存储。是否有可能将其作为sql server 2000的存储使用?如果没有,最好的解决方案是什么?

6 个解决方案

#1


1  

I strongly recommend against it.

我强烈建议不要这样做。

Put your data files locally on the server itself, with RAID mirrored drives. The reasons are twofold:

使用RAID镜像驱动器将数据文件本地放在服务器本身上。原因有两个:

  • SQL Server will run much faster for all but the smallest workloads
  • 除了最小的工作负载之外,SQL Server的运行速度要快得多

  • SQL Server will be much less prone to corruption in case the link to the NAS gets broken.
  • 如果到NAS的链接被破坏,SQL Server将更不容易出现损坏。

Use the NAS to store backups of your SQL Server, not to host your datafiles. I don't know what your database size will be, or what your usage pattern will be, so I can't tell you what you MUST have. At a minimum for a database that's going to take any significant load in a production environment, I would recommend two logical drives (one for data, one for your transaction log), each consisting of a RAID 1 array of the fastest drives you can stomach to buy. If that's overkill, put your database on just two physical drives, (one for the transaction log, and one for data). If even THAT is over budget, put your data on a single drive, back up often. But if you choose the single-drive or NAS solution, IMO you are putting your faith in the Power of Prayer (which may not be a bad thing, it just isn't that effective when designing databases).

使用NAS存储SQL Server的备份,而不是托管数据文件。我不知道你的数据库大小是什么,或者你的使用模式是什么,所以我不能告诉你你必须拥有什么。至少对于将在生产环境中承担任何重大负载的数据库,我建议使用两个逻辑驱动器(一个用于数据,一个用于事务日志),每个驱动器包含一个RAID 1阵列,可以运行最快的驱动器买。如果这太过分了,请将数据库放在两个物理驱动器上(一个用于事务日志,另一个用于数据)。如果即使这个超出预算,也可以将数据放在一个驱动器上,经常备份。但是,如果你选择单驱动器或NAS解决方案,IMO你就相信祷告的力量(这可能不是一件坏事,在设计数据库时它并没有那么有效)。

Note that a NAS is not the same thing as a SAN (on which people typically DO put database files). A NAS typically is much slower and has much less bandwidth than a SAN connection, which is designed for very high reliability, high speed, advanced management, and low latency. A NAS is geared more toward reducing your cost of network storage.

请注意,NAS与SAN不同(人们通常会将数据库文件置于其中)。与SAN连接相比,NAS通常要慢得多并且带宽要少得多,SAN连接专为高可靠性,高速度,高级管理和低延迟而设计。 NAS更倾向于降低网络存储成本。

#2


1  

My gut reaction - I think you're mad risking your data on a NAS. SQL's expectation is continuous low-latency uninterrupted access to your storage subsystem. The NAS is almost certainly none of those things - you local or SAN storage (in order of performance, simplicity and therefore preference) - leave the NAS for offline file storage/backups.

我的直觉反应 - 我认为你疯狂冒着NAS上的数据。 SQL的期望是对存储子系统的持续低延迟不间断访问。 NAS几乎肯定不是那些东西 - 你本地或SAN存储(按性能,简单性和优先顺序排序) - 让NAS离线文件存储/备份。

The following KB lists some of the constraints and issues you'd encounter trying to use a NAS with SQL - while the KB covers SQL 7 through 2005, a lot of the information still applies to SQL 2008 too.

以下KB列出了尝试将NAS与SQL一起使用时遇到的一些约束和问题 - 虽然KB涵盖了SQL 7到2005,但很多信息仍然适用于SQL 2008。

http://support.microsoft.com/kb/304261

#3


0  

local is almost always faster than networked storage.

本地几乎总是比网络存储更快。

Your performance for sql will depend on how your objects, files, and filegroups are defined, and how consumers use the data.

您对sql的性能取决于您的对象,文件和文件组的定义方式,以及消费者如何使用这些数据。

#4


0  

Well "best" means different things to different people, but I think "best" performance would be a TMS RAMSAN or a RAID of SSDs... etc

“最佳”对不同的人来说意味着不同的东西,但我认为“最佳”性能将是TMS RAMSAN或SSD的RAID ......等等

Best capacity would be achieved with a RAID of large HDDs...

使用大型硬盘驱动器RAID可实现最佳容量......

Best reliability/data saftey would be achieved with Mirroring across many drives, and regular backups (off site preferably)...

使用多个驱动器镜像和定期备份(最好在场外)可以实现最佳可靠性/数据安全性......

Best availability... I don't know... maybe a clone the system and have a hot backup ready to go at all times.

最好的可用性......我不知道......也许是克隆系统并随时准备好热备份。

Best security would require encryption, but mainly limiting physical access to the machine (and it's backups) is enough unless it's internet connected.

最好的安全性需要加密,但主要限制对机器的物理访问(并且它的备份)是足够的,除非它是互联网连接。

#5


0  

As the other answers point out, there will be a performance penalty here.

正如其他答案所指出的那样,这里会有性能损失。

It is also worth mentioning that these things sometimes implement a RAM cache to improve I/O performance, if that is the case and you do trial this config, the NAS should be on the same power protection / UPS as the server hardware, otherwise in case of power outtage the NAS may 'loose' the part of the file in cache. ouch!

还值得一提的是,这些东西有时会实现RAM缓存以提高I / O性能,如果是这种情况并且你试用了这个配置,NAS应该与服务器硬件在同一个电源保护/ UPS上,否则在在断电的情况下,NAS可能会“松开”缓存中文件的一部分。哎哟!

#6


-1  

It can work but a dedicated fiber attached SAN will be better.

它可以工作,但专用光纤连接SAN会更好。

Local will usually be faster but it has limited size and won't scale easily.

本地通常会更快,但尺寸有限,不易扩展。

I'm not familiar with the hardware but we initially deployed a warehouse on a shared NAS. Here's what we found.

我不熟悉硬件,但我们最初在共享NAS上部署了一个仓库。这是我们发现的。

We were regularly competing for resources on the head unit -- there was only so much bandwidth that it could handle. Massive warehouse queries and data loads were severely impacted.

我们经常在主机上争夺资源 - 它只能处理这么多的带宽。大规模仓库查询和数据加载受到严重影响。

We needed 1.5 TB for our warehouse (data/indexes/logs) we put each of these resources onto a separate set of LUNS (like you might do with attached storage). Data was spanning just 10 disks. We ran into all sorts of IO bottlenecks with this. the better solution was to create one big partition across lots of small disks and store data, index and logs all in the same place. This sped things up considerably.

我们的仓库(数据/索引/日志)需要1.5 TB我们将这些资源放在一组单独的LUN上(就像您可能使用附加存储一样)。数据仅包含10个磁盘。我们遇到了各种各样的IO瓶颈。更好的解决方案是在许多小磁盘上创建一个大分区,并将数据,索引和日志存储在同一个地方。这大大加快了速度。

If you're dealing with a moderately used OLTP system, you might be fine but a NAS can be troublesome.

如果您正在处理中等使用的OLTP系统,您可能会很好,但NAS可能会很麻烦。

#1


1  

I strongly recommend against it.

我强烈建议不要这样做。

Put your data files locally on the server itself, with RAID mirrored drives. The reasons are twofold:

使用RAID镜像驱动器将数据文件本地放在服务器本身上。原因有两个:

  • SQL Server will run much faster for all but the smallest workloads
  • 除了最小的工作负载之外,SQL Server的运行速度要快得多

  • SQL Server will be much less prone to corruption in case the link to the NAS gets broken.
  • 如果到NAS的链接被破坏,SQL Server将更不容易出现损坏。

Use the NAS to store backups of your SQL Server, not to host your datafiles. I don't know what your database size will be, or what your usage pattern will be, so I can't tell you what you MUST have. At a minimum for a database that's going to take any significant load in a production environment, I would recommend two logical drives (one for data, one for your transaction log), each consisting of a RAID 1 array of the fastest drives you can stomach to buy. If that's overkill, put your database on just two physical drives, (one for the transaction log, and one for data). If even THAT is over budget, put your data on a single drive, back up often. But if you choose the single-drive or NAS solution, IMO you are putting your faith in the Power of Prayer (which may not be a bad thing, it just isn't that effective when designing databases).

使用NAS存储SQL Server的备份,而不是托管数据文件。我不知道你的数据库大小是什么,或者你的使用模式是什么,所以我不能告诉你你必须拥有什么。至少对于将在生产环境中承担任何重大负载的数据库,我建议使用两个逻辑驱动器(一个用于数据,一个用于事务日志),每个驱动器包含一个RAID 1阵列,可以运行最快的驱动器买。如果这太过分了,请将数据库放在两个物理驱动器上(一个用于事务日志,另一个用于数据)。如果即使这个超出预算,也可以将数据放在一个驱动器上,经常备份。但是,如果你选择单驱动器或NAS解决方案,IMO你就相信祷告的力量(这可能不是一件坏事,在设计数据库时它并没有那么有效)。

Note that a NAS is not the same thing as a SAN (on which people typically DO put database files). A NAS typically is much slower and has much less bandwidth than a SAN connection, which is designed for very high reliability, high speed, advanced management, and low latency. A NAS is geared more toward reducing your cost of network storage.

请注意,NAS与SAN不同(人们通常会将数据库文件置于其中)。与SAN连接相比,NAS通常要慢得多并且带宽要少得多,SAN连接专为高可靠性,高速度,高级管理和低延迟而设计。 NAS更倾向于降低网络存储成本。

#2


1  

My gut reaction - I think you're mad risking your data on a NAS. SQL's expectation is continuous low-latency uninterrupted access to your storage subsystem. The NAS is almost certainly none of those things - you local or SAN storage (in order of performance, simplicity and therefore preference) - leave the NAS for offline file storage/backups.

我的直觉反应 - 我认为你疯狂冒着NAS上的数据。 SQL的期望是对存储子系统的持续低延迟不间断访问。 NAS几乎肯定不是那些东西 - 你本地或SAN存储(按性能,简单性和优先顺序排序) - 让NAS离线文件存储/备份。

The following KB lists some of the constraints and issues you'd encounter trying to use a NAS with SQL - while the KB covers SQL 7 through 2005, a lot of the information still applies to SQL 2008 too.

以下KB列出了尝试将NAS与SQL一起使用时遇到的一些约束和问题 - 虽然KB涵盖了SQL 7到2005,但很多信息仍然适用于SQL 2008。

http://support.microsoft.com/kb/304261

#3


0  

local is almost always faster than networked storage.

本地几乎总是比网络存储更快。

Your performance for sql will depend on how your objects, files, and filegroups are defined, and how consumers use the data.

您对sql的性能取决于您的对象,文件和文件组的定义方式,以及消费者如何使用这些数据。

#4


0  

Well "best" means different things to different people, but I think "best" performance would be a TMS RAMSAN or a RAID of SSDs... etc

“最佳”对不同的人来说意味着不同的东西,但我认为“最佳”性能将是TMS RAMSAN或SSD的RAID ......等等

Best capacity would be achieved with a RAID of large HDDs...

使用大型硬盘驱动器RAID可实现最佳容量......

Best reliability/data saftey would be achieved with Mirroring across many drives, and regular backups (off site preferably)...

使用多个驱动器镜像和定期备份(最好在场外)可以实现最佳可靠性/数据安全性......

Best availability... I don't know... maybe a clone the system and have a hot backup ready to go at all times.

最好的可用性......我不知道......也许是克隆系统并随时准备好热备份。

Best security would require encryption, but mainly limiting physical access to the machine (and it's backups) is enough unless it's internet connected.

最好的安全性需要加密,但主要限制对机器的物理访问(并且它的备份)是足够的,除非它是互联网连接。

#5


0  

As the other answers point out, there will be a performance penalty here.

正如其他答案所指出的那样,这里会有性能损失。

It is also worth mentioning that these things sometimes implement a RAM cache to improve I/O performance, if that is the case and you do trial this config, the NAS should be on the same power protection / UPS as the server hardware, otherwise in case of power outtage the NAS may 'loose' the part of the file in cache. ouch!

还值得一提的是,这些东西有时会实现RAM缓存以提高I / O性能,如果是这种情况并且你试用了这个配置,NAS应该与服务器硬件在同一个电源保护/ UPS上,否则在在断电的情况下,NAS可能会“松开”缓存中文件的一部分。哎哟!

#6


-1  

It can work but a dedicated fiber attached SAN will be better.

它可以工作,但专用光纤连接SAN会更好。

Local will usually be faster but it has limited size and won't scale easily.

本地通常会更快,但尺寸有限,不易扩展。

I'm not familiar with the hardware but we initially deployed a warehouse on a shared NAS. Here's what we found.

我不熟悉硬件,但我们最初在共享NAS上部署了一个仓库。这是我们发现的。

We were regularly competing for resources on the head unit -- there was only so much bandwidth that it could handle. Massive warehouse queries and data loads were severely impacted.

我们经常在主机上争夺资源 - 它只能处理这么多的带宽。大规模仓库查询和数据加载受到严重影响。

We needed 1.5 TB for our warehouse (data/indexes/logs) we put each of these resources onto a separate set of LUNS (like you might do with attached storage). Data was spanning just 10 disks. We ran into all sorts of IO bottlenecks with this. the better solution was to create one big partition across lots of small disks and store data, index and logs all in the same place. This sped things up considerably.

我们的仓库(数据/索引/日志)需要1.5 TB我们将这些资源放在一组单独的LUN上(就像您可能使用附加存储一样)。数据仅包含10个磁盘。我们遇到了各种各样的IO瓶颈。更好的解决方案是在许多小磁盘上创建一个大分区,并将数据,索引和日志存储在同一个地方。这大大加快了速度。

If you're dealing with a moderately used OLTP system, you might be fine but a NAS can be troublesome.

如果您正在处理中等使用的OLTP系统,您可能会很好,但NAS可能会很麻烦。