Logical Drives

Any advantages/disadvantages in having two separate logical drives when
adding array discs in SQL server. This space will be mainly used for backups
and t-log backups.
ThanksNiles wrote:
> Any advantages/disadvantages in having two separate logical drives
> when adding array discs in SQL server. This space will be mainly
> used for backups and t-log backups.
> Thanks
Are you saying you want to partition you hard drives in the array? If
you're like most customers, you are probably using a RAID 5 array for
the data (the cheapest redundant solution). If so, RAID 5 has terrible
write performance. You would be better off using a mirrored set for the
OS and another mirrored set for the log files. Log files should never
really be on a RAID 5 array since they are written to sequentially. Same
with a backup file.
Let us know how you plan to configure the arrays for the server.
David Gugick
Imceda Software
www.imceda.com|||I'll assume you have your RAID configuration correctly set up. So, having
multiple devices? SQL Server will spawn multiple threads if you have
multiple devices. However, if you use sqlmaint, it will not create multiple
devices, so there would be no performance advantage except if you had
multiple maintenance schedules.
Sincerely,
Anthony Thomas
"Niles" wrote:
> Any advantages/disadvantages in having two separate logical drives when
> adding array discs in SQL server. This space will be mainly used for backups
> and t-log backups.
> Thanks

Logical Drives

Any advantages/disadvantages in having two separate logical drives when
adding array discs in SQL server. This space will be mainly used for backups
and t-log backups.
Thanks
Niles wrote:
> Any advantages/disadvantages in having two separate logical drives
> when adding array discs in SQL server. This space will be mainly
> used for backups and t-log backups.
> Thanks
Are you saying you want to partition you hard drives in the array? If
you're like most customers, you are probably using a RAID 5 array for
the data (the cheapest redundant solution). If so, RAID 5 has terrible
write performance. You would be better off using a mirrored set for the
OS and another mirrored set for the log files. Log files should never
really be on a RAID 5 array since they are written to sequentially. Same
with a backup file.
Let us know how you plan to configure the arrays for the server.
David Gugick
Imceda Software
www.imceda.com
|||I'll assume you have your RAID configuration correctly set up. So, having
multiple devices? SQL Server will spawn multiple threads if you have
multiple devices. However, if you use sqlmaint, it will not create multiple
devices, so there would be no performance advantage except if you had
multiple maintenance schedules.
Sincerely,
Anthony Thomas
"Niles" wrote:

> Any advantages/disadvantages in having two separate logical drives when
> adding array discs in SQL server. This space will be mainly used for backups
> and t-log backups.
> Thanks

Logical Drives

Any advantages/disadvantages in having two separate logical drives when
adding array discs in SQL server. This space will be mainly used for backup
s
and t-log backups.
ThanksNiles wrote:
> Any advantages/disadvantages in having two separate logical drives
> when adding array discs in SQL server. This space will be mainly
> used for backups and t-log backups.
> Thanks
Are you saying you want to partition you hard drives in the array? If
you're like most customers, you are probably using a RAID 5 array for
the data (the cheapest redundant solution). If so, RAID 5 has terrible
write performance. You would be better off using a mirrored set for the
OS and another mirrored set for the log files. Log files should never
really be on a RAID 5 array since they are written to sequentially. Same
with a backup file.
Let us know how you plan to configure the arrays for the server.
David Gugick
Imceda Software
www.imceda.com|||I'll assume you have your RAID configuration correctly set up. So, having
multiple devices? SQL Server will spawn multiple threads if you have
multiple devices. However, if you use sqlmaint, it will not create multiple
devices, so there would be no performance advantage except if you had
multiple maintenance schedules.
Sincerely,
Anthony Thomas
"Niles" wrote:

> Any advantages/disadvantages in having two separate logical drives when
> adding array discs in SQL server. This space will be mainly used for back
ups
> and t-log backups.
> Thanks

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

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"Thomas" <thomas-ggl-02@.data.iatn.net> wrote in message
news:a9081691.0406021521.54c0f399@.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|||"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<kStvc.34031$j24.13232@.twister.nyroc.rr.com>...
> "Thomas" <thomas-ggl-02@.data.iatn.net> wrote in message
> news:a9081691.0406021521.54c0f399@.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|||"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0406030702.4308cd27@.posting.google.c om...
> "Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in
message news:<kStvc.34031$j24.13232@.twister.nyroc.rr.com>...
> > "Thomas" <thomas-ggl-02@.data.iatn.net> wrote in message
> > news:a9081691.0406021521.54c0f399@.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