Indexes on Secondary Filegroup

[druer]

I haven't come across anything which would tell me for certain what would happen if I move some of my hardest hit (non-clustered) indexes to a Secondary Filegroup that is on a new (non-raided) hard drive that was added to the server. I am using SQL Server 2000 on a Windows 2000 box. Currently the database resides entirely on a large raided device.

In the even of a drive failure ...
Would the database continue to run without the indexes on that drive?
Would the database stop running because those indexes would be in accessible?

(The new drive was added for something specific that reliability isn't an issue for, but is not raided because its a stand alone drive. If I can take advantage of it without risking my database I'd like to because it has plenty of free space on it.)

 

[Walid]

You will gain performance if the new hard drive has its own IDE controller. If both hard drives are attached to the same controller, no gain will be realized.


Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[druer]

Thanks for the input. I'm 100% positive I'll get a performance gain. My fear is that the gain might come at the expense of the system's stability because I don't know how the system will respond if this "stand alone" drive should happen to fail since it has no redundancy. I wouldn't mind losing the indexes, becasue I could obviously have the system easily rebuild non-clustered indexes if that should happen, but I don't want the whole database to be marked as bad.