Logging to second disk -- what happens when it crashes?

[Thomas]

Currently we have SQL Server running on a single RAID5 array.
The data and the logs are all written to this array. We don't
have huge volume of activity, but it is growing. Most activity
is from our website that interacts a lot with the database.

We have read that it is a good idea to separate the data files
from the log files, having each on separate disks (or in the
case of RAID, separate arrays). So, we are considering adding
a separate SCSI hard drive, or maybe a RAID 1 system, and have
sql server log to that instead.

My question is, what happens if that secondary drive or array
completely fails. Let's say it melts. Assuming the OS doesn't
crash (Windows 2003 Server), what will SQL Server 2000 do when
the drive stops working and it is trying to write its data
logging to it? Will it continue to function and write alert
events? Will it shutdown? If it shuts down, how do we alter its
configuration to tell it to use another drive for the logs?

Also, what kind of throughput would we need to be doing to see
any kind of benefit to making this change? It is easier to
justify the expense of another disk or RAID setup if it is
actually going to matter. ;-)

Thanks all!

Thomas

[Greg D. Moore \(Strider\)]

"Thomas" <th***********@data.iatn.net> wrote in message
news:a9**************************@posting.google.c om...

Currently we have SQL Server running on a single RAID5 array.
The data and the logs are all written to this array. We don't
have huge volume of activity, but it is growing. Most activity
is from our website that interacts a lot with the database.

We have read that it is a good idea to separate the data files
from the log files, having each on separate disks (or in the
case of RAID, separate arrays). So, we are considering adding
a separate SCSI hard drive, or maybe a RAID 1 system, and have
sql server log to that instead.
RAID 1 at the least. Don't use a single drive in this case.

My question is, what happens if that secondary drive or array
completely fails. Let's say it melts. Assuming the OS doesn't
crash (Windows 2003 Server), what will SQL Server 2000 do when
the drive stops working and it is trying to write its data
logging to it? Will it continue to function and write alert
events? Will it shutdown? If it shuts down, how do we alter its
configuration to tell it to use another drive for the logs?
It will stop running. It can't write anything. Just the same as if you
lost your data drive.

At that point you pull out your disaster recovery plan and go from here.

We've had a few cases where either the log or data disks went off-line and
it was just a matter of bringing them back online through the RAID
controller interface. SQL 2000 picked up where it left off.


Also, what kind of throughput would we need to be doing to see
any kind of benefit to making this change? It is easier to
justify the expense of another disk or RAID setup if it is
actually going to matter. ;-)

Thanks all!

Thomas

[Thomas]

"Greg D. Moore \(Strider\)" <mo****************@greenms.com> wrote in message news:<kS*******************@twister.nyroc.rr.com>. ..

"Thomas" <th***********@data.iatn.net> wrote in message
news:a9**************************@posting.google.c om...

My question is, what happens if that secondary drive or array
completely fails. Let's say it melts. Assuming the OS doesn't
crash (Windows 2003 Server), what will SQL Server 2000 do when
the drive stops working and it is trying to write its data
logging to it? Will it continue to function and write alert
events? Will it shutdown? If it shuts down, how do we alter its
configuration to tell it to use another drive for the logs?

It will stop running. It can't write anything. Just the same as if you
lost your data drive.

At that point you pull out your disaster recovery plan and go from here.

We've had a few cases where either the log or data disks went off-line and
it was just a matter of bringing them back online through the RAID
controller interface. SQL 2000 picked up where it left off.

Let's imagine that this new RAID 1 for the logging goes down hard,
controller malfunction or what have you, and it will take a few days
to be repaired. In the meantime our data drive is working fine, and
we want to move the logging back to that array. You mentioned that
SQL Server would "stop running." If it isn't running, how would we
be able to update its configuration to change the location of the
logfiles?

Thanks for your feedback and advice!

Regards,
Thomas

[Greg D. Moore \(Strider\)]

"Thomas" <th***********@data.iatn.net> wrote in message
news:4f**************************@posting.google.c om...

"Greg D. Moore \(Strider\)" <mo****************@greenms.com> wrote in message news:<kS*******************@twister.nyroc.rr.com>. ..

"Thomas" <th***********@data.iatn.net> wrote in message
news:a9**************************@posting.google.c om...

My question is, what happens if that secondary drive or array
completely fails. Let's say it melts. Assuming the OS doesn't
crash (Windows 2003 Server), what will SQL Server 2000 do when
the drive stops working and it is trying to write its data
logging to it? Will it continue to function and write alert
events? Will it shutdown? If it shuts down, how do we alter its
configuration to tell it to use another drive for the logs?

It will stop running. It can't write anything. Just the same as if you
lost your data drive.

At that point you pull out your disaster recovery plan and go from here.

We've had a few cases where either the log or data disks went off-line and it was just a matter of bringing them back online through the RAID
controller interface. SQL 2000 picked up where it left off.

Let's imagine that this new RAID 1 for the logging goes down hard,
controller malfunction or what have you, and it will take a few days
to be repaired. In the meantime our data drive is working fine, and
we want to move the logging back to that array. You mentioned that
SQL Server would "stop running." If it isn't running, how would we
be able to update its configuration to change the location of the
logfiles?

Well, first of all, if you lose your log device, you have to do a restore
from backup in any case. At that point simply restore the backup to a
different device using the MOVE option.

Now, if your master DB and other system DBs are on the failed device, you
need to startup SQL server manually and specify on the command line where
they are.

MS has multiple KB articles on this.

Turn the question around... what happens if the RAID device with your DATA
fails? (ironically in some ways this can be EASIER to recover from assuming
you have good backups.)


Thanks for your feedback and advice!

Regards,
Thomas