Hello.
I have large database with very high workload (100 trans/sec avg.)
When database files were located on local disks on the server I tried to
part data files, index files, files with binary objects and log files from
each other and place them on separate physical discs to improve performance.
Now we're planning to use cluster server with external storage (HP MSA 1000).
A technician, who was configuring the hardware, told me that in this
configuration storage library segments presented to MSSQL server as discs are
already spread on many physical discs and there is no reason now to put
database files on different drives.
The question is: is this true? Will it be good if I'll put all files on
one logical disc and let storage library to manage data distridution across
physical discs?
Thanks.
Serge Shakhov
Hi
Ideally, the log and data portion of the database should be on different
LUNs ("storage library segments") and the Log should be on RAID-1 or RAID-10.
RAID-5 is not optimal for the Log due to it's high latency. Actually, with
the price of storage today, RAID-5 has no place near a IO intensive server.
Further to that, if you get presented 5 LUNs, and then present them to SQL
Server as only one disk, generally, you do not use up all the storage, so the
first 2 or 3 of the LUNS will have all the traffic, and there is no data on
the other LUNs, so they get no traffic. You don't want this. You rather have
5 database files, one on each LUN so that all the drives are busy.
Without knowing exactly how the LUNs are mapped to the physical drives and
how you want to map to LUNs to OS drives, I can't pass more comment.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Serge Shakhov" wrote:
> Hello.
> I have large database with very high workload (100 trans/sec avg.)
> When database files were located on local disks on the server I tried to
> part data files, index files, files with binary objects and log files from
> each other and place them on separate physical discs to improve performance.
> Now we're planning to use cluster server with external storage (HP MSA 1000).
> A technician, who was configuring the hardware, told me that in this
> configuration storage library segments presented to MSSQL server as discs are
> already spread on many physical discs and there is no reason now to put
> database files on different drives.
> The question is: is this true? Will it be good if I'll put all files on
> one logical disc and let storage library to manage data distridution across
> physical discs?
> Thanks.
> Serge Shakhov
|||Mike,
Is there a net guide somewhere on setting up log files and data optimally?
Thanks,
Mica
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F43766EE-6358-4515-B6A3-7705B516DA18@.microsoft.com...[vbcol=seagreen]
> Hi
> Ideally, the log and data portion of the database should be on different
> LUNs ("storage library segments") and the Log should be on RAID-1 or
> RAID-10.
> RAID-5 is not optimal for the Log due to it's high latency. Actually, with
> the price of storage today, RAID-5 has no place near a IO intensive
> server.
> Further to that, if you get presented 5 LUNs, and then present them to SQL
> Server as only one disk, generally, you do not use up all the storage, so
> the
> first 2 or 3 of the LUNS will have all the traffic, and there is no data
> on
> the other LUNs, so they get no traffic. You don't want this. You rather
> have
> 5 database files, one on each LUN so that all the drives are busy.
> Without knowing exactly how the LUNs are mapped to the physical drives and
> how you want to map to LUNs to OS drives, I can't pass more comment.
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Serge Shakhov" wrote:
Showing posts with label location. Show all posts
Showing posts with label location. Show all posts
Thursday, March 29, 2012
Data files location.
Hello.
I have large database with very high workload (100 trans/sec avg.)
When database files were located on local disks on the server I tried to
part data files, index files, files with binary objects and log files from
each other and place them on separate physical discs to improve performance.
Now we're planning to use cluster server with external storage (HP MSA 1000)
.
A technician, who was configuring the hardware, told me that in this
configuration storage library segments presented to MSSQL server as discs ar
e
already spread on many physical discs and there is no reason now to put
database files on different drives.
The question is: is this true? Will it be good if I'll put all files on
one logical disc and let storage library to manage data distridution across
physical discs?
Thanks.
Serge ShakhovHi
Ideally, the log and data portion of the database should be on different
LUNs ("storage library segments") and the Log should be on RAID-1 or RAID-10
.
RAID-5 is not optimal for the Log due to it's high latency. Actually, with
the price of storage today, RAID-5 has no place near a IO intensive server.
Further to that, if you get presented 5 LUNs, and then present them to SQL
Server as only one disk, generally, you do not use up all the storage, so th
e
first 2 or 3 of the LUNS will have all the traffic, and there is no data on
the other LUNs, so they get no traffic. You don't want this. You rather have
5 database files, one on each LUN so that all the drives are busy.
Without knowing exactly how the LUNs are mapped to the physical drives and
how you want to map to LUNs to OS drives, I can't pass more comment.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Serge Shakhov" wrote:
> Hello.
> I have large database with very high workload (100 trans/sec avg.)
> When database files were located on local disks on the server I tried to
> part data files, index files, files with binary objects and log files from
> each other and place them on separate physical discs to improve performanc
e.
> Now we're planning to use cluster server with external storage (HP MSA 100
0).
> A technician, who was configuring the hardware, told me that in this
> configuration storage library segments presented to MSSQL server as discs
are
> already spread on many physical discs and there is no reason now to put
> database files on different drives.
> The question is: is this true? Will it be good if I'll put all files on
> one logical disc and let storage library to manage data distridution acros
s
> physical discs?
> Thanks.
> Serge Shakhov|||Mike,
Is there a net guide somewhere on setting up log files and data optimally?
Thanks,
Mica
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F43766EE-6358-4515-B6A3-7705B516DA18@.microsoft.com...[vbcol=seagreen]
> Hi
> Ideally, the log and data portion of the database should be on different
> LUNs ("storage library segments") and the Log should be on RAID-1 or
> RAID-10.
> RAID-5 is not optimal for the Log due to it's high latency. Actually, with
> the price of storage today, RAID-5 has no place near a IO intensive
> server.
> Further to that, if you get presented 5 LUNs, and then present them to SQL
> Server as only one disk, generally, you do not use up all the storage, so
> the
> first 2 or 3 of the LUNS will have all the traffic, and there is no data
> on
> the other LUNs, so they get no traffic. You don't want this. You rather
> have
> 5 database files, one on each LUN so that all the drives are busy.
> Without knowing exactly how the LUNs are mapped to the physical drives and
> how you want to map to LUNs to OS drives, I can't pass more comment.
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Serge Shakhov" wrote:
>
I have large database with very high workload (100 trans/sec avg.)
When database files were located on local disks on the server I tried to
part data files, index files, files with binary objects and log files from
each other and place them on separate physical discs to improve performance.
Now we're planning to use cluster server with external storage (HP MSA 1000)
.
A technician, who was configuring the hardware, told me that in this
configuration storage library segments presented to MSSQL server as discs ar
e
already spread on many physical discs and there is no reason now to put
database files on different drives.
The question is: is this true? Will it be good if I'll put all files on
one logical disc and let storage library to manage data distridution across
physical discs?
Thanks.
Serge ShakhovHi
Ideally, the log and data portion of the database should be on different
LUNs ("storage library segments") and the Log should be on RAID-1 or RAID-10
.
RAID-5 is not optimal for the Log due to it's high latency. Actually, with
the price of storage today, RAID-5 has no place near a IO intensive server.
Further to that, if you get presented 5 LUNs, and then present them to SQL
Server as only one disk, generally, you do not use up all the storage, so th
e
first 2 or 3 of the LUNS will have all the traffic, and there is no data on
the other LUNs, so they get no traffic. You don't want this. You rather have
5 database files, one on each LUN so that all the drives are busy.
Without knowing exactly how the LUNs are mapped to the physical drives and
how you want to map to LUNs to OS drives, I can't pass more comment.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Serge Shakhov" wrote:
> Hello.
> I have large database with very high workload (100 trans/sec avg.)
> When database files were located on local disks on the server I tried to
> part data files, index files, files with binary objects and log files from
> each other and place them on separate physical discs to improve performanc
e.
> Now we're planning to use cluster server with external storage (HP MSA 100
0).
> A technician, who was configuring the hardware, told me that in this
> configuration storage library segments presented to MSSQL server as discs
are
> already spread on many physical discs and there is no reason now to put
> database files on different drives.
> The question is: is this true? Will it be good if I'll put all files on
> one logical disc and let storage library to manage data distridution acros
s
> physical discs?
> Thanks.
> Serge Shakhov|||Mike,
Is there a net guide somewhere on setting up log files and data optimally?
Thanks,
Mica
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:F43766EE-6358-4515-B6A3-7705B516DA18@.microsoft.com...[vbcol=seagreen]
> Hi
> Ideally, the log and data portion of the database should be on different
> LUNs ("storage library segments") and the Log should be on RAID-1 or
> RAID-10.
> RAID-5 is not optimal for the Log due to it's high latency. Actually, with
> the price of storage today, RAID-5 has no place near a IO intensive
> server.
> Further to that, if you get presented 5 LUNs, and then present them to SQL
> Server as only one disk, generally, you do not use up all the storage, so
> the
> first 2 or 3 of the LUNS will have all the traffic, and there is no data
> on
> the other LUNs, so they get no traffic. You don't want this. You rather
> have
> 5 database files, one on each LUN so that all the drives are busy.
> Without knowing exactly how the LUNs are mapped to the physical drives and
> how you want to map to LUNs to OS drives, I can't pass more comment.
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Serge Shakhov" wrote:
>
Wednesday, March 21, 2012
Data Directory In Network location?
Is it possible to set DATADIR=\\<company_network_somedirectory>\? When I
try it, set-up seems to fail.
Also, if I were to create a new database on a default instance of MSDE, I am
not able to pick mapped network directories.
Appreciate any insight.
Thanks,
Sha.
hi Sha,
"Sha S." <shajihans@.ttnus.com> ha scritto nel messaggio
news:OajGz8D7EHA.3836@.tk2msftngp13.phx.gbl
> Is it possible to set DATADIR=\\<company_network_somedirectory>\?
> When I try it, set-up seems to fail.
>
it is possible, using a trace flag, but strongly not suggested... SQL Server
requires a strong, reliant and trusted, verified path (and enough permission
must be granted to the Windows account running it's services) in order
perform (possibly as fast as possible) disk IO operations...
as Network share usually are not that secure and robust, nor fast, it is
strongly advised only to use "local" storag subsystem and not remote...
as regard the setup.exe parameter, I actually do not know if it's possible
to specify remote paths...
please keep you data on your local disk(s) :D
> Also, if I were to create a new database on a default instance of
> MSDE, I am not able to pick mapped network directories.
>
again, enough permission(s) [on the network shares] must be granted to the
Windows account running MSDE SQL Server and SQL Server Agent services..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you, Andrea, for your reply. I shall follow it.
Andrea Montanari wrote:
> hi Sha,
> "Sha S." <shajihans@.ttnus.com> ha scritto nel messaggio
> news:OajGz8D7EHA.3836@.tk2msftngp13.phx.gbl
>
> it is possible, using a trace flag, but strongly not suggested... SQL Server
> requires a strong, reliant and trusted, verified path (and enough permission
> must be granted to the Windows account running it's services) in order
> perform (possibly as fast as possible) disk IO operations...
> as Network share usually are not that secure and robust, nor fast, it is
> strongly advised only to use "local" storag subsystem and not remote...
> as regard the setup.exe parameter, I actually do not know if it's possible
> to specify remote paths...
> please keep you data on your local disk(s) :D
>
>
> again, enough permission(s) [on the network shares] must be granted to the
> Windows account running MSDE SQL Server and SQL Server Agent services..
>
|||hi Sha,
"Sha S." <pilgrim216@.gmail.com> ha scritto nel messaggio
news:e8MrtIP7EHA.3616@.TK2MSFTNGP11.phx.gbl
> Thank you, Andrea, for your reply. I shall follow it.
>
:D
just a consideratio I had after posting... for sure it's not possible to
specify remote folders for <DATA_DIR> parameter as you can not install MSDE
specifying the trace flag required for that kind of feature
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
try it, set-up seems to fail.
Also, if I were to create a new database on a default instance of MSDE, I am
not able to pick mapped network directories.
Appreciate any insight.
Thanks,
Sha.
hi Sha,
"Sha S." <shajihans@.ttnus.com> ha scritto nel messaggio
news:OajGz8D7EHA.3836@.tk2msftngp13.phx.gbl
> Is it possible to set DATADIR=\\<company_network_somedirectory>\?
> When I try it, set-up seems to fail.
>
it is possible, using a trace flag, but strongly not suggested... SQL Server
requires a strong, reliant and trusted, verified path (and enough permission
must be granted to the Windows account running it's services) in order
perform (possibly as fast as possible) disk IO operations...
as Network share usually are not that secure and robust, nor fast, it is
strongly advised only to use "local" storag subsystem and not remote...
as regard the setup.exe parameter, I actually do not know if it's possible
to specify remote paths...
please keep you data on your local disk(s) :D
> Also, if I were to create a new database on a default instance of
> MSDE, I am not able to pick mapped network directories.
>
again, enough permission(s) [on the network shares] must be granted to the
Windows account running MSDE SQL Server and SQL Server Agent services..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you, Andrea, for your reply. I shall follow it.
Andrea Montanari wrote:
> hi Sha,
> "Sha S." <shajihans@.ttnus.com> ha scritto nel messaggio
> news:OajGz8D7EHA.3836@.tk2msftngp13.phx.gbl
>
> it is possible, using a trace flag, but strongly not suggested... SQL Server
> requires a strong, reliant and trusted, verified path (and enough permission
> must be granted to the Windows account running it's services) in order
> perform (possibly as fast as possible) disk IO operations...
> as Network share usually are not that secure and robust, nor fast, it is
> strongly advised only to use "local" storag subsystem and not remote...
> as regard the setup.exe parameter, I actually do not know if it's possible
> to specify remote paths...
> please keep you data on your local disk(s) :D
>
>
> again, enough permission(s) [on the network shares] must be granted to the
> Windows account running MSDE SQL Server and SQL Server Agent services..
>
|||hi Sha,
"Sha S." <pilgrim216@.gmail.com> ha scritto nel messaggio
news:e8MrtIP7EHA.3616@.TK2MSFTNGP11.phx.gbl
> Thank you, Andrea, for your reply. I shall follow it.
>
:D
just a consideratio I had after posting... for sure it's not possible to
specify remote folders for <DATA_DIR> parameter as you can not install MSDE
specifying the trace flag required for that kind of feature
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Wednesday, March 7, 2012
data and tran log location
I am new to SQl server and would apreciate help in
understanding where to place data and tran log for
optimal performance and fault tolerance.
I found info in BOL confusing.
We have Windows 2003 Enterpise edition with 1 physical
drive and 2 logical drives.(mirrored)
Should I put everything ( data + tran log on one logical
drive considering that if logical drive fails evrything
will be written to a second drive or tran log should go
on one logical drive and data on an other?
Thanks very muchI don't follow you. You say that you have one physical drive, so how can any
thing be mirrored?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"miro" <anonymous@.discussions.microsoft.com> wrote in message news:1353a01c41289$79c4d9e0$a
501280a@.phx.gbl...
> I am new to SQl server and would apreciate help in
> understanding where to place data and tran log for
> optimal performance and fault tolerance.
> I found info in BOL confusing.
> We have Windows 2003 Enterpise edition with 1 physical
> drive and 2 logical drives.(mirrored)
> Should I put everything ( data + tran log on one logical
> drive considering that if logical drive fails evrything
> will be written to a second drive or tran log should go
> on one logical drive and data on an other?
> Thanks very much
>|||"miro" <anonymous@.discussions.microsoft.com> wrote in message
news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
> I am new to SQl server and would apreciate help in
> understanding where to place data and tran log for
> optimal performance and fault tolerance.
> I found info in BOL confusing.
> We have Windows 2003 Enterpise edition with 1 physical
> drive and 2 logical drives.(mirrored)
> Should I put everything ( data + tran log on one logical
> drive considering that if logical drive fails evrything
> will be written to a second drive or tran log should go
> on one logical drive and data on an other?
> Thanks very much
>
If you have only one physical disc you have _no_ fault tolerance, regardless
of your file locations.
If you only have one disc and you put your transaction logs on a different
logical drive you will actually slow your performance down, as the heads of
the drive will have further to travel.
The only acceptable minimum configuration if you want any fault tolerance at
all is to have at least 2 physical drives, data on one, log on the other, so
that if you loose the data disk you can restore from your backup and roll
your logs forward to the point of failure. If this is a mission critical
server then you need hardware RAID and as much redundancy as you can afford.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||Hi Tibor,
you are right the number of physical drives is two. They
are mirrored. I am wondering now if two logical drives
would be suficient and appropriate for 1.- OS, SQL
Server and 2 - database files. What is the best practice ?
Would creating additional logical drives be of any
benefit ?
Thanks
>--Original Message--
>I don't follow you. You say that you have one physical
drive, so how can anything be mirrored?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"miro" <anonymous@.discussions.microsoft.com> wrote in
message news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
logical
>
>.
>|||Whether you create logical drives or not does not in any way affect performa
nce as all logical drives are on
the same mirrored physical drive.
Assuming that you only have two drives to work with, and you estimate that i
t will give sufficient
performance:
Having the OS on it's own partition can be a good thing so autogrow (if you
use that) of the database files
cannot starve the OS from disk. Apart from that, it doesn't matter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"miro" <anonymous@.discussions.microsoft.com> wrote in message news:10b1b01c412a5$55098e90$a
601280a@.phx.gbl...
> Hi Tibor,
> you are right the number of physical drives is two. They
> are mirrored. I am wondering now if two logical drives
> would be suficient and appropriate for 1.- OS, SQL
> Server and 2 - database files. What is the best practice ?
> Would creating additional logical drives be of any
> benefit ?
> Thanks
> drive, so how can anything be mirrored?
> message news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
> logical
understanding where to place data and tran log for
optimal performance and fault tolerance.
I found info in BOL confusing.
We have Windows 2003 Enterpise edition with 1 physical
drive and 2 logical drives.(mirrored)
Should I put everything ( data + tran log on one logical
drive considering that if logical drive fails evrything
will be written to a second drive or tran log should go
on one logical drive and data on an other?
Thanks very muchI don't follow you. You say that you have one physical drive, so how can any
thing be mirrored?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"miro" <anonymous@.discussions.microsoft.com> wrote in message news:1353a01c41289$79c4d9e0$a
501280a@.phx.gbl...
> I am new to SQl server and would apreciate help in
> understanding where to place data and tran log for
> optimal performance and fault tolerance.
> I found info in BOL confusing.
> We have Windows 2003 Enterpise edition with 1 physical
> drive and 2 logical drives.(mirrored)
> Should I put everything ( data + tran log on one logical
> drive considering that if logical drive fails evrything
> will be written to a second drive or tran log should go
> on one logical drive and data on an other?
> Thanks very much
>|||"miro" <anonymous@.discussions.microsoft.com> wrote in message
news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
> I am new to SQl server and would apreciate help in
> understanding where to place data and tran log for
> optimal performance and fault tolerance.
> I found info in BOL confusing.
> We have Windows 2003 Enterpise edition with 1 physical
> drive and 2 logical drives.(mirrored)
> Should I put everything ( data + tran log on one logical
> drive considering that if logical drive fails evrything
> will be written to a second drive or tran log should go
> on one logical drive and data on an other?
> Thanks very much
>
If you have only one physical disc you have _no_ fault tolerance, regardless
of your file locations.
If you only have one disc and you put your transaction logs on a different
logical drive you will actually slow your performance down, as the heads of
the drive will have further to travel.
The only acceptable minimum configuration if you want any fault tolerance at
all is to have at least 2 physical drives, data on one, log on the other, so
that if you loose the data disk you can restore from your backup and roll
your logs forward to the point of failure. If this is a mission critical
server then you need hardware RAID and as much redundancy as you can afford.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||Hi Tibor,
you are right the number of physical drives is two. They
are mirrored. I am wondering now if two logical drives
would be suficient and appropriate for 1.- OS, SQL
Server and 2 - database files. What is the best practice ?
Would creating additional logical drives be of any
benefit ?
Thanks
>--Original Message--
>I don't follow you. You say that you have one physical
drive, so how can anything be mirrored?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"miro" <anonymous@.discussions.microsoft.com> wrote in
message news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
logical
>
>.
>|||Whether you create logical drives or not does not in any way affect performa
nce as all logical drives are on
the same mirrored physical drive.
Assuming that you only have two drives to work with, and you estimate that i
t will give sufficient
performance:
Having the OS on it's own partition can be a good thing so autogrow (if you
use that) of the database files
cannot starve the OS from disk. Apart from that, it doesn't matter.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"miro" <anonymous@.discussions.microsoft.com> wrote in message news:10b1b01c412a5$55098e90$a
601280a@.phx.gbl...
> Hi Tibor,
> you are right the number of physical drives is two. They
> are mirrored. I am wondering now if two logical drives
> would be suficient and appropriate for 1.- OS, SQL
> Server and 2 - database files. What is the best practice ?
> Would creating additional logical drives be of any
> benefit ?
> Thanks
> drive, so how can anything be mirrored?
> message news:1353a01c41289$79c4d9e0$a501280a@.phx
.gbl...
> logical
data and tran log location
I am new to SQl server and would apreciate help in
understanding where to place data and tran log for
optimal performance and fault tolerance.
I found info in BOL confusing.
We have Windows 2003 Enterpise edition with 1 physical
drive and 2 logical drives.(mirrored)
Should I put everything ( data + tran log on one logical
drive considering that if logical drive fails evrything
will be written to a second drive or tran log should go
on one logical drive and data on an other?
Thanks very muchIf you have only one logical drive you cannot separate log and data aiming b
oost performance. For this kind of tuning to work, you must have a bunch of
logical drives, with different RAID levels.
Mirror volumes are better for writting on disk than reading compared to RAID
5, so would be good for tran lob, that is writing intensive. But read perfor
mance for RAID5(when all disks are working fine) is better, so, storing rea
d-only data here would be n
ice. RAID 5 overall performance is better then mirrored volume, but it need
s at least 3 physical disks to be built.
So, if you have only 2 disks, separating log, data and index is worthless.
understanding where to place data and tran log for
optimal performance and fault tolerance.
I found info in BOL confusing.
We have Windows 2003 Enterpise edition with 1 physical
drive and 2 logical drives.(mirrored)
Should I put everything ( data + tran log on one logical
drive considering that if logical drive fails evrything
will be written to a second drive or tran log should go
on one logical drive and data on an other?
Thanks very muchIf you have only one logical drive you cannot separate log and data aiming b
oost performance. For this kind of tuning to work, you must have a bunch of
logical drives, with different RAID levels.
Mirror volumes are better for writting on disk than reading compared to RAID
5, so would be good for tran lob, that is writing intensive. But read perfor
mance for RAID5(when all disks are working fine) is better, so, storing rea
d-only data here would be n
ice. RAID 5 overall performance is better then mirrored volume, but it need
s at least 3 physical disks to be built.
So, if you have only 2 disks, separating log, data and index is worthless.
Friday, February 24, 2012
Data & Log File Location on RAID 5
Hi! I searched for an answer on this, but did not find exactly the info I am looking for . . .
With SQL 2005 I assume it is still appropriate to put data and logs on separate physical drives for best performance. However, I have only one RAID 5 for data and logs. Does it help to put these on separate partitions on the same array for data and logs or should I not even bother since it won't be separate physical disks? Thanks!
Matt, I'm moving your thread to the Database Engine forum where I think you'll get a faster answer.
-Jeffrey
Subscribe to:
Posts (Atom)