I am moving from an Oracle Db server to a SQL server running on a new w2k3
member server. Prior to purchase I would like to select the appropriate RAID
level for the server which gives me the best performance and recovery options.
I heard something about RAID 10?
Appreciate it!
RPM
Here are the improvements in order of importance. Go as far down the list
as you have budget for.
1) Tlogs and Data on separate physical devices. If necessary, OS and TLogs
can share the same physical disks without too much of an impact IF it is a
dedicated and properly tuned SQL server.
2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
critical to transactional performance. Slow writes to the TLOGS are death
to a SQL server. Data can survive on RAID5.
3) Data and TLogs on different controllers. Much better for recovery in
case of hardware failure.
4) Data on RAID 1+0. Much faster than RAID5. About a five times faster for
transactional updates, depending on the number of drives in the physical
array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds up.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
RAID
> level for the server which gives me the best performance and recovery
options.
> I heard something about RAID 10?
> Appreciate it!
> RPM
|||In addition to Geoff's excellent advice, separate filegroups for data and
nonclustered indexes can help performance if they're on separate
drives/controllers. All this depends on how many drives/controllers you
have. I would place this tip between 3 and 4 on Geoff's list below.
Thanks,
Michael C#, MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eiQ3kZyCFHA.3812@.TK2MSFTNGP15.phx.gbl...
> Here are the improvements in order of importance. Go as far down the list
> as you have budget for.
> 1) Tlogs and Data on separate physical devices. If necessary, OS and
> TLogs
> can share the same physical disks without too much of an impact IF it is a
> dedicated and properly tuned SQL server.
> 2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
> critical to transactional performance. Slow writes to the TLOGS are death
> to a SQL server. Data can survive on RAID5.
> 3) Data and TLogs on different controllers. Much better for recovery in
> case of hardware failure.
> 4) Data on RAID 1+0. Much faster than RAID5. About a five times faster
> for
> transactional updates, depending on the number of drives in the physical
> array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds
> up.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Ron P" <RonP@.discussions.microsoft.com> wrote in message
> news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> RAID
> options.
>
|||First, you should evaluate your needs in term of capacity, performance and
reliability before making the choice for the RAID. Simply saying that you
want the *best* worths nothing in an evaluation.
S. L.
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
> RAID
> level for the server which gives me the best performance and recovery
> options.
> I heard something about RAID 10?
> Appreciate it!
> RPM
|||Thank you all for the input.
To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup, would I
be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
the TLogs on the OS or on the data partition?
TX again
Ron P
"Ron P" wrote:
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate RAID
> level for the server which gives me the best performance and recovery options.
> I heard something about RAID 10?
> Appreciate it!
> RPM
|||"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
I'd probably put the OS and data on the same partition and the Tlogs on a
separete physical partition.
But my first question would be, "do you really need to?". Is Disk I/O your
biggest bottleneck here?
[vbcol=seagreen]
> TX again
> Ron P
> "Ron P" wrote:
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]
|||Ron,
I think you need to study up a bit more.
RAID 1+0 is definately the highest performance RAID leve, but it is also the
most expensive.
a huge percentage of production systems use RAID 5. RAID 5 is not the best
for performance, but it provides fault tolerance and is relatively
inexpensive.
It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data and
Indexes on RAID 5 might be the ticket.
Another important issue with RAID is, the MORE physical disks you have the
better. In other words, if you need 140GB of storage, you are better off
purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
RAID Controller is also important. You need a Controller with Battery Backed
Cache for performance and for Fault Tolerance.
There are TONS of articles on RAID on the net. Just "Google" it up.
if you have more questions, you can email me directly
Greg Jackson
Portland, Oregon
|||My standard build recommendation on a 2850 is Mirrored drives (RAID 1) on
one channel for OS and TLOGs. Definitely 15KRPM, most likely 73GB. The
other four drive slots can be used for RAID 1+ 0 data on the second channel.
Depending on your actual data space requirements, you can use either 73GB
15KRPM drives or 146GB 10KRPM drives. Remember to order the split backplane
module to take advantage of the extra controller channel.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I[vbcol=seagreen]
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
> TX again
> Ron P
> "Ron P" wrote:
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]
|||The choice between RAID 1 and RAID 5 is highly dependent on the ratio of
reads vs. writes. If you perform a large proportion of writes, RAID 5 will
cause a performance hit. As you mentioned, RAID 5 is not good for
Transaction Logs, regardless of how you decide to store data and indexes.
Also, the OP mentions separate partitions - how many physical drives do you
have Ron? As Greg pointed out, Physical Drives and separate controllers are
important performance factors. Separate partitions on the same hard drive
probably won't help, and might hinder, performance.
Thanks,
Michael C#, MCDBA
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVe0OSHDFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ron,
> I think you need to study up a bit more.
> RAID 1+0 is definately the highest performance RAID leve, but it is also
> the most expensive.
> a huge percentage of production systems use RAID 5. RAID 5 is not the best
> for performance, but it provides fault tolerance and is relatively
> inexpensive.
> It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data
> and Indexes on RAID 5 might be the ticket.
> Another important issue with RAID is, the MORE physical disks you have the
> better. In other words, if you need 140GB of storage, you are better off
> purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
> RAID Controller is also important. You need a Controller with Battery
> Backed Cache for performance and for Fault Tolerance.
> There are TONS of articles on RAID on the net. Just "Google" it up.
> if you have more questions, you can email me directly
>
> Greg Jackson
> Portland, Oregon
>
|||If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
place for SQL backup files?
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Friday, March 30, 2012
RAID-Best Practices ?
I am moving from an Oracle Db server to a SQL server running on a new w2k3
member server. Prior to purchase I would like to select the appropriate RAID
level for the server which gives me the best performance and recovery option
s.
I heard something about RAID 10?
Appreciate it!
RPMHere are the improvements in order of importance. Go as far down the list
as you have budget for.
1) Tlogs and Data on separate physical devices. If necessary, OS and TLogs
can share the same physical disks without too much of an impact IF it is a
dedicated and properly tuned SQL server.
2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
critical to transactional performance. Slow writes to the TLOGS are death
to a SQL server. Data can survive on RAID5.
3) Data and TLogs on different controllers. Much better for recovery in
case of hardware failure.
4) Data on RAID 1+0. Much faster than RAID5. About a five times faster for
transactional updates, depending on the number of drives in the physical
array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds up.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
RAID
> level for the server which gives me the best performance and recovery
options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||In addition to Geoff's excellent advice, separate filegroups for data and
nonclustered indexes can help performance if they're on separate
drives/controllers. All this depends on how many drives/controllers you
have. I would place this tip between 3 and 4 on Geoff's list below.
Thanks,
Michael C#, MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eiQ3kZyCFHA.3812@.TK2MSFTNGP15.phx.gbl...
> Here are the improvements in order of importance. Go as far down the list
> as you have budget for.
> 1) Tlogs and Data on separate physical devices. If necessary, OS and
> TLogs
> can share the same physical disks without too much of an impact IF it is a
> dedicated and properly tuned SQL server.
> 2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
> critical to transactional performance. Slow writes to the TLOGS are death
> to a SQL server. Data can survive on RAID5.
> 3) Data and TLogs on different controllers. Much better for recovery in
> case of hardware failure.
> 4) Data on RAID 1+0. Much faster than RAID5. About a five times faster
> for
> transactional updates, depending on the number of drives in the physical
> array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds
> up.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Ron P" <RonP@.discussions.microsoft.com> wrote in message
> news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> RAID
> options.
>|||First, you should evaluate your needs in term of capacity, performance and
reliability before making the choice for the RAID. Simply saying that you
want the *best* worths nothing in an evaluation.
S. L.
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
> RAID
> level for the server which gives me the best performance and recovery
> options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||Thank you all for the input.
To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup, would
I
be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
the TLogs on the OS or on the data partition?
TX again
Ron P
"Ron P" wrote:
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate RA
ID
> level for the server which gives me the best performance and recovery opti
ons.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
I'd probably put the OS and data on the same partition and the Tlogs on a
separete physical partition.
But my first question would be, "do you really need to?". Is Disk I/O your
biggest bottleneck here?
[vbcol=seagreen]
> TX again
> Ron P
> "Ron P" wrote:
>
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]|||Ron,
I think you need to study up a bit more.
RAID 1+0 is definately the highest performance RAID leve, but it is also the
most expensive.
a huge percentage of production systems use RAID 5. RAID 5 is not the best
for performance, but it provides fault tolerance and is relatively
inexpensive.
It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data and
Indexes on RAID 5 might be the ticket.
Another important issue with RAID is, the MORE physical disks you have the
better. In other words, if you need 140GB of storage, you are better off
purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
RAID Controller is also important. You need a Controller with Battery Backed
Cache for performance and for Fault Tolerance.
There are TONS of articles on RAID on the net. Just "Google" it up.
if you have more questions, you can email me directly
Greg Jackson
Portland, Oregon|||My standard build recommendation on a 2850 is Mirrored drives (RAID 1) on
one channel for OS and TLOGs. Definitely 15KRPM, most likely 73GB. The
other four drive slots can be used for RAID 1+ 0 data on the second channel.
Depending on your actual data space requirements, you can use either 73GB
15KRPM drives or 146GB 10KRPM drives. Remember to order the split backplane
module to take advantage of the extra controller channel.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I[vbcol=seagreen]
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
> TX again
> Ron P
> "Ron P" wrote:
>
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]|||The choice between RAID 1 and RAID 5 is highly dependent on the ratio of
reads vs. writes. If you perform a large proportion of writes, RAID 5 will
cause a performance hit. As you mentioned, RAID 5 is not good for
Transaction Logs, regardless of how you decide to store data and indexes.
Also, the OP mentions separate partitions - how many physical drives do you
have Ron? As Greg pointed out, Physical Drives and separate controllers are
important performance factors. Separate partitions on the same hard drive
probably won't help, and might hinder, performance.
Thanks,
Michael C#, MCDBA
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVe0OSHDFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ron,
> I think you need to study up a bit more.
> RAID 1+0 is definately the highest performance RAID leve, but it is also
> the most expensive.
> a huge percentage of production systems use RAID 5. RAID 5 is not the best
> for performance, but it provides fault tolerance and is relatively
> inexpensive.
> It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data
> and Indexes on RAID 5 might be the ticket.
> Another important issue with RAID is, the MORE physical disks you have the
> better. In other words, if you need 140GB of storage, you are better off
> purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
> RAID Controller is also important. You need a Controller with Battery
> Backed Cache for performance and for Fault Tolerance.
> There are TONS of articles on RAID on the net. Just "Google" it up.
> if you have more questions, you can email me directly
>
> Greg Jackson
> Portland, Oregon
>
member server. Prior to purchase I would like to select the appropriate RAID
level for the server which gives me the best performance and recovery option
s.
I heard something about RAID 10?
Appreciate it!
RPMHere are the improvements in order of importance. Go as far down the list
as you have budget for.
1) Tlogs and Data on separate physical devices. If necessary, OS and TLogs
can share the same physical disks without too much of an impact IF it is a
dedicated and properly tuned SQL server.
2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
critical to transactional performance. Slow writes to the TLOGS are death
to a SQL server. Data can survive on RAID5.
3) Data and TLogs on different controllers. Much better for recovery in
case of hardware failure.
4) Data on RAID 1+0. Much faster than RAID5. About a five times faster for
transactional updates, depending on the number of drives in the physical
array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds up.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
RAID
> level for the server which gives me the best performance and recovery
options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||In addition to Geoff's excellent advice, separate filegroups for data and
nonclustered indexes can help performance if they're on separate
drives/controllers. All this depends on how many drives/controllers you
have. I would place this tip between 3 and 4 on Geoff's list below.
Thanks,
Michael C#, MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eiQ3kZyCFHA.3812@.TK2MSFTNGP15.phx.gbl...
> Here are the improvements in order of importance. Go as far down the list
> as you have budget for.
> 1) Tlogs and Data on separate physical devices. If necessary, OS and
> TLogs
> can share the same physical disks without too much of an impact IF it is a
> dedicated and properly tuned SQL server.
> 2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
> critical to transactional performance. Slow writes to the TLOGS are death
> to a SQL server. Data can survive on RAID5.
> 3) Data and TLogs on different controllers. Much better for recovery in
> case of hardware failure.
> 4) Data on RAID 1+0. Much faster than RAID5. About a five times faster
> for
> transactional updates, depending on the number of drives in the physical
> array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds
> up.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Ron P" <RonP@.discussions.microsoft.com> wrote in message
> news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> RAID
> options.
>|||First, you should evaluate your needs in term of capacity, performance and
reliability before making the choice for the RAID. Simply saying that you
want the *best* worths nothing in an evaluation.
S. L.
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
> RAID
> level for the server which gives me the best performance and recovery
> options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||Thank you all for the input.
To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup, would
I
be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
the TLogs on the OS or on the data partition?
TX again
Ron P
"Ron P" wrote:
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate RA
ID
> level for the server which gives me the best performance and recovery opti
ons.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
I'd probably put the OS and data on the same partition and the Tlogs on a
separete physical partition.
But my first question would be, "do you really need to?". Is Disk I/O your
biggest bottleneck here?
[vbcol=seagreen]
> TX again
> Ron P
> "Ron P" wrote:
>
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]|||Ron,
I think you need to study up a bit more.
RAID 1+0 is definately the highest performance RAID leve, but it is also the
most expensive.
a huge percentage of production systems use RAID 5. RAID 5 is not the best
for performance, but it provides fault tolerance and is relatively
inexpensive.
It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data and
Indexes on RAID 5 might be the ticket.
Another important issue with RAID is, the MORE physical disks you have the
better. In other words, if you need 140GB of storage, you are better off
purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
RAID Controller is also important. You need a Controller with Battery Backed
Cache for performance and for Fault Tolerance.
There are TONS of articles on RAID on the net. Just "Google" it up.
if you have more questions, you can email me directly
Greg Jackson
Portland, Oregon|||My standard build recommendation on a 2850 is Mirrored drives (RAID 1) on
one channel for OS and TLOGs. Definitely 15KRPM, most likely 73GB. The
other four drive slots can be used for RAID 1+ 0 data on the second channel.
Depending on your actual data space requirements, you can use either 73GB
15KRPM drives or 146GB 10KRPM drives. Remember to order the split backplane
module to take advantage of the extra controller channel.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I[vbcol=seagreen]
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
> TX again
> Ron P
> "Ron P" wrote:
>
w2k3[vbcol=seagreen]
RAID[vbcol=seagreen]
options.[vbcol=seagreen]|||The choice between RAID 1 and RAID 5 is highly dependent on the ratio of
reads vs. writes. If you perform a large proportion of writes, RAID 5 will
cause a performance hit. As you mentioned, RAID 5 is not good for
Transaction Logs, regardless of how you decide to store data and indexes.
Also, the OP mentions separate partitions - how many physical drives do you
have Ron? As Greg pointed out, Physical Drives and separate controllers are
important performance factors. Separate partitions on the same hard drive
probably won't help, and might hinder, performance.
Thanks,
Michael C#, MCDBA
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVe0OSHDFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ron,
> I think you need to study up a bit more.
> RAID 1+0 is definately the highest performance RAID leve, but it is also
> the most expensive.
> a huge percentage of production systems use RAID 5. RAID 5 is not the best
> for performance, but it provides fault tolerance and is relatively
> inexpensive.
> It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data
> and Indexes on RAID 5 might be the ticket.
> Another important issue with RAID is, the MORE physical disks you have the
> better. In other words, if you need 140GB of storage, you are better off
> purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
> RAID Controller is also important. You need a Controller with Battery
> Backed Cache for performance and for Fault Tolerance.
> There are TONS of articles on RAID on the net. Just "Google" it up.
> if you have more questions, you can email me directly
>
> Greg Jackson
> Portland, Oregon
>
RAID-Best Practices ?
I am moving from an Oracle Db server to a SQL server running on a new w2k3
member server. Prior to purchase I would like to select the appropriate RAID
level for the server which gives me the best performance and recovery options.
I heard something about RAID 10?
Appreciate it!
RPMHere are the improvements in order of importance. Go as far down the list
as you have budget for.
1) Tlogs and Data on separate physical devices. If necessary, OS and TLogs
can share the same physical disks without too much of an impact IF it is a
dedicated and properly tuned SQL server.
2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
critical to transactional performance. Slow writes to the TLOGS are death
to a SQL server. Data can survive on RAID5.
3) Data and TLogs on different controllers. Much better for recovery in
case of hardware failure.
4) Data on RAID 1+0. Much faster than RAID5. About a five times faster for
transactional updates, depending on the number of drives in the physical
array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds up.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
RAID
> level for the server which gives me the best performance and recovery
options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||In addition to Geoff's excellent advice, separate filegroups for data and
nonclustered indexes can help performance if they're on separate
drives/controllers. All this depends on how many drives/controllers you
have. I would place this tip between 3 and 4 on Geoff's list below.
Thanks,
Michael C#, MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eiQ3kZyCFHA.3812@.TK2MSFTNGP15.phx.gbl...
> Here are the improvements in order of importance. Go as far down the list
> as you have budget for.
> 1) Tlogs and Data on separate physical devices. If necessary, OS and
> TLogs
> can share the same physical disks without too much of an impact IF it is a
> dedicated and properly tuned SQL server.
> 2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
> critical to transactional performance. Slow writes to the TLOGS are death
> to a SQL server. Data can survive on RAID5.
> 3) Data and TLogs on different controllers. Much better for recovery in
> case of hardware failure.
> 4) Data on RAID 1+0. Much faster than RAID5. About a five times faster
> for
> transactional updates, depending on the number of drives in the physical
> array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds
> up.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Ron P" <RonP@.discussions.microsoft.com> wrote in message
> news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>> I am moving from an Oracle Db server to a SQL server running on a new
>> w2k3
>> member server. Prior to purchase I would like to select the appropriate
> RAID
>> level for the server which gives me the best performance and recovery
> options.
>> I heard something about RAID 10?
>> Appreciate it!
>> RPM
>|||First, you should evaluate your needs in term of capacity, performance and
reliability before making the choice for the RAID. Simply saying that you
want the *best* worths nothing in an evaluation.
S. L.
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
> RAID
> level for the server which gives me the best performance and recovery
> options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||Thank you all for the input.
To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup, would I
be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
the TLogs on the OS or on the data partition?
TX again
Ron P
"Ron P" wrote:
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate RAID
> level for the server which gives me the best performance and recovery options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
I'd probably put the OS and data on the same partition and the Tlogs on a
separete physical partition.
But my first question would be, "do you really need to?". Is Disk I/O your
biggest bottleneck here?
> TX again
> Ron P
> "Ron P" wrote:
> > I am moving from an Oracle Db server to a SQL server running on a new
w2k3
> > member server. Prior to purchase I would like to select the appropriate
RAID
> > level for the server which gives me the best performance and recovery
options.
> >
> > I heard something about RAID 10?
> > Appreciate it!
> >
> > RPM|||Ron,
I think you need to study up a bit more.
RAID 1+0 is definately the highest performance RAID leve, but it is also the
most expensive.
a huge percentage of production systems use RAID 5. RAID 5 is not the best
for performance, but it provides fault tolerance and is relatively
inexpensive.
It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data and
Indexes on RAID 5 might be the ticket.
Another important issue with RAID is, the MORE physical disks you have the
better. In other words, if you need 140GB of storage, you are better off
purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
RAID Controller is also important. You need a Controller with Battery Backed
Cache for performance and for Fault Tolerance.
There are TONS of articles on RAID on the net. Just "Google" it up.
if you have more questions, you can email me directly
Greg Jackson
Portland, Oregon|||My standard build recommendation on a 2850 is Mirrored drives (RAID 1) on
one channel for OS and TLOGs. Definitely 15KRPM, most likely 73GB. The
other four drive slots can be used for RAID 1+ 0 data on the second channel.
Depending on your actual data space requirements, you can use either 73GB
15KRPM drives or 146GB 10KRPM drives. Remember to order the split backplane
module to take advantage of the extra controller channel.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
> TX again
> Ron P
> "Ron P" wrote:
> > I am moving from an Oracle Db server to a SQL server running on a new
w2k3
> > member server. Prior to purchase I would like to select the appropriate
RAID
> > level for the server which gives me the best performance and recovery
options.
> >
> > I heard something about RAID 10?
> > Appreciate it!
> >
> > RPM|||The choice between RAID 1 and RAID 5 is highly dependent on the ratio of
reads vs. writes. If you perform a large proportion of writes, RAID 5 will
cause a performance hit. As you mentioned, RAID 5 is not good for
Transaction Logs, regardless of how you decide to store data and indexes.
Also, the OP mentions separate partitions - how many physical drives do you
have Ron? As Greg pointed out, Physical Drives and separate controllers are
important performance factors. Separate partitions on the same hard drive
probably won't help, and might hinder, performance.
Thanks,
Michael C#, MCDBA
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVe0OSHDFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ron,
> I think you need to study up a bit more.
> RAID 1+0 is definately the highest performance RAID leve, but it is also
> the most expensive.
> a huge percentage of production systems use RAID 5. RAID 5 is not the best
> for performance, but it provides fault tolerance and is relatively
> inexpensive.
> It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data
> and Indexes on RAID 5 might be the ticket.
> Another important issue with RAID is, the MORE physical disks you have the
> better. In other words, if you need 140GB of storage, you are better off
> purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
> RAID Controller is also important. You need a Controller with Battery
> Backed Cache for performance and for Fault Tolerance.
> There are TONS of articles on RAID on the net. Just "Google" it up.
> if you have more questions, you can email me directly
>
> Greg Jackson
> Portland, Oregon
>|||If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
place for SQL backup files?|||Another server.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F8350DA6-12A5-4930-B131-9CD2C217BBC4@.microsoft.com...
> If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
> place for SQL backup files?|||I agree that would be my first choice. However in the senario where there is
no option but to have the SQL backups on the same server, where is the best
place for an successful recovery.|||USB drive so you can attach it to a recovery server.
Local backups aren't.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F87D91FF-1B21-4646-83DB-4DE7BD2C3725@.microsoft.com...
>I agree that would be my first choice. However in the senario where there
>is
> no option but to have the SQL backups on the same server, where is the
> best
> place for an successful recovery.
>|||As Geoff suggests, you need to get the data off the server. However, if
your .mdf data files are on the RAID 5, and you need to first backup to the
local disks, backup to the other physical disks on your server. It sounds
like your only option is the RAID 1 partition. If two of your RAID 5 disks
die, you still have your latest backup on the RAID 1 partition.
Mark
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F8350DA6-12A5-4930-B131-9CD2C217BBC4@.microsoft.com...
> If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
> place for SQL backup files?
member server. Prior to purchase I would like to select the appropriate RAID
level for the server which gives me the best performance and recovery options.
I heard something about RAID 10?
Appreciate it!
RPMHere are the improvements in order of importance. Go as far down the list
as you have budget for.
1) Tlogs and Data on separate physical devices. If necessary, OS and TLogs
can share the same physical disks without too much of an impact IF it is a
dedicated and properly tuned SQL server.
2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
critical to transactional performance. Slow writes to the TLOGS are death
to a SQL server. Data can survive on RAID5.
3) Data and TLogs on different controllers. Much better for recovery in
case of hardware failure.
4) Data on RAID 1+0. Much faster than RAID5. About a five times faster for
transactional updates, depending on the number of drives in the physical
array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds up.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
RAID
> level for the server which gives me the best performance and recovery
options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||In addition to Geoff's excellent advice, separate filegroups for data and
nonclustered indexes can help performance if they're on separate
drives/controllers. All this depends on how many drives/controllers you
have. I would place this tip between 3 and 4 on Geoff's list below.
Thanks,
Michael C#, MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eiQ3kZyCFHA.3812@.TK2MSFTNGP15.phx.gbl...
> Here are the improvements in order of importance. Go as far down the list
> as you have budget for.
> 1) Tlogs and Data on separate physical devices. If necessary, OS and
> TLogs
> can share the same physical disks without too much of an impact IF it is a
> dedicated and properly tuned SQL server.
> 2) Tlogs on RAID 1 or 1+0. These are sequentially written files and are
> critical to transactional performance. Slow writes to the TLOGS are death
> to a SQL server. Data can survive on RAID5.
> 3) Data and TLogs on different controllers. Much better for recovery in
> case of hardware failure.
> 4) Data on RAID 1+0. Much faster than RAID5. About a five times faster
> for
> transactional updates, depending on the number of drives in the physical
> array. RAID 5 slows down on writes with more spindles. RAID 1+0 speeds
> up.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Ron P" <RonP@.discussions.microsoft.com> wrote in message
> news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>> I am moving from an Oracle Db server to a SQL server running on a new
>> w2k3
>> member server. Prior to purchase I would like to select the appropriate
> RAID
>> level for the server which gives me the best performance and recovery
> options.
>> I heard something about RAID 10?
>> Appreciate it!
>> RPM
>|||First, you should evaluate your needs in term of capacity, performance and
reliability before making the choice for the RAID. Simply saying that you
want the *best* worths nothing in an evaluation.
S. L.
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:3ED1EBCB-0A73-4BAF-9AC4-5AD52D59DC62@.microsoft.com...
>I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate
> RAID
> level for the server which gives me the best performance and recovery
> options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||Thank you all for the input.
To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup, would I
be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
the TLogs on the OS or on the data partition?
TX again
Ron P
"Ron P" wrote:
> I am moving from an Oracle Db server to a SQL server running on a new w2k3
> member server. Prior to purchase I would like to select the appropriate RAID
> level for the server which gives me the best performance and recovery options.
> I heard something about RAID 10?
> Appreciate it!
> RPM|||"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
I'd probably put the OS and data on the same partition and the Tlogs on a
separete physical partition.
But my first question would be, "do you really need to?". Is Disk I/O your
biggest bottleneck here?
> TX again
> Ron P
> "Ron P" wrote:
> > I am moving from an Oracle Db server to a SQL server running on a new
w2k3
> > member server. Prior to purchase I would like to select the appropriate
RAID
> > level for the server which gives me the best performance and recovery
options.
> >
> > I heard something about RAID 10?
> > Appreciate it!
> >
> > RPM|||Ron,
I think you need to study up a bit more.
RAID 1+0 is definately the highest performance RAID leve, but it is also the
most expensive.
a huge percentage of production systems use RAID 5. RAID 5 is not the best
for performance, but it provides fault tolerance and is relatively
inexpensive.
It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data and
Indexes on RAID 5 might be the ticket.
Another important issue with RAID is, the MORE physical disks you have the
better. In other words, if you need 140GB of storage, you are better off
purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
RAID Controller is also important. You need a Controller with Battery Backed
Cache for performance and for Fault Tolerance.
There are TONS of articles on RAID on the net. Just "Google" it up.
if you have more questions, you can email me directly
Greg Jackson
Portland, Oregon|||My standard build recommendation on a 2850 is Mirrored drives (RAID 1) on
one channel for OS and TLOGs. Definitely 15KRPM, most likely 73GB. The
other four drive slots can be used for RAID 1+ 0 data on the second channel.
Depending on your actual data space requirements, you can use either 73GB
15KRPM drives or 146GB 10KRPM drives. Remember to order the split backplane
module to take advantage of the extra controller channel.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron P" <RonP@.discussions.microsoft.com> wrote in message
news:F8A7A612-C982-4912-B04A-BB0B63E5160E@.microsoft.com...
> Thank you all for the input.
> To clarify, If I have 20-25 users hitting this dedicated SQL server (DELL
> 2850 2.0 GB RAM, Dual Processors) which has the OS on a Raid 1 setup,
would I
> be better off with RAID 1+0 or Raid 1 for the data partition. Should I put
> the TLogs on the OS or on the data partition?
> TX again
> Ron P
> "Ron P" wrote:
> > I am moving from an Oracle Db server to a SQL server running on a new
w2k3
> > member server. Prior to purchase I would like to select the appropriate
RAID
> > level for the server which gives me the best performance and recovery
options.
> >
> > I heard something about RAID 10?
> > Appreciate it!
> >
> > RPM|||The choice between RAID 1 and RAID 5 is highly dependent on the ratio of
reads vs. writes. If you perform a large proportion of writes, RAID 5 will
cause a performance hit. As you mentioned, RAID 5 is not good for
Transaction Logs, regardless of how you decide to store data and indexes.
Also, the OP mentions separate partitions - how many physical drives do you
have Ron? As Greg pointed out, Physical Drives and separate controllers are
important performance factors. Separate partitions on the same hard drive
probably won't help, and might hinder, performance.
Thanks,
Michael C#, MCDBA
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVe0OSHDFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ron,
> I think you need to study up a bit more.
> RAID 1+0 is definately the highest performance RAID leve, but it is also
> the most expensive.
> a huge percentage of production systems use RAID 5. RAID 5 is not the best
> for performance, but it provides fault tolerance and is relatively
> inexpensive.
> It sounds to me like placing OS and Logs on a RAID 1 Volume and the Data
> and Indexes on RAID 5 might be the ticket.
> Another important issue with RAID is, the MORE physical disks you have the
> better. In other words, if you need 140GB of storage, you are better off
> purchasing 4 36GB Drives as opposed to using 2 72GB Drives.
> RAID Controller is also important. You need a Controller with Battery
> Backed Cache for performance and for Fault Tolerance.
> There are TONS of articles on RAID on the net. Just "Google" it up.
> if you have more questions, you can email me directly
>
> Greg Jackson
> Portland, Oregon
>|||If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
place for SQL backup files?|||Another server.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F8350DA6-12A5-4930-B131-9CD2C217BBC4@.microsoft.com...
> If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
> place for SQL backup files?|||I agree that would be my first choice. However in the senario where there is
no option but to have the SQL backups on the same server, where is the best
place for an successful recovery.|||USB drive so you can attach it to a recovery server.
Local backups aren't.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F87D91FF-1B21-4646-83DB-4DE7BD2C3725@.microsoft.com...
>I agree that would be my first choice. However in the senario where there
>is
> no option but to have the SQL backups on the same server, where is the
> best
> place for an successful recovery.
>|||As Geoff suggests, you need to get the data off the server. However, if
your .mdf data files are on the RAID 5, and you need to first backup to the
local disks, backup to the other physical disks on your server. It sounds
like your only option is the RAID 1 partition. If two of your RAID 5 disks
die, you still have your latest backup on the RAID 1 partition.
Mark
"John Oberlin" <JohnOberlin@.discussions.microsoft.com> wrote in message
news:F8350DA6-12A5-4930-B131-9CD2C217BBC4@.microsoft.com...
> If Windows and TLogs are on RAID1 and DB's are on RAID5, where is the best
> place for SQL backup files?
Monday, March 26, 2012
Raid 10 and SQL
Are there any significant performance issues between running raid 5 verses
raid 10
with equal hardware configurations?. Thanks for being there to help, you
people have been life-savers.
Hi
Check out SQL Server 2000 Performance Tuning Technical Reference ISBN
0-7356-1270-6
There is a performance gain from Raid 10, this has to be taken into the
context of the system as a whole and what loads it will be under.
John
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
|||Yes for sure..
Only think i can say that cost is high with RAID 10 but for performance it's
great..
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
|||in most cases, yes.
I think I have read articles that suggest anything over 10% Writes you
should get off of RAID 5
Greg Jackson
PDX, Oregon
|||We found that RAID10 was over twice as fast when used for our database dump
volume as compared to RAID5. Not to mention that RAID10 is significantly
more tolerant of disk failures.
Mike Kruchten
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
|||Check out what this site has to say about Raid 5:
http://www.baarf.com/
Andrew J. Kelly SQL MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
raid 10
with equal hardware configurations?. Thanks for being there to help, you
people have been life-savers.
Hi
Check out SQL Server 2000 Performance Tuning Technical Reference ISBN
0-7356-1270-6
There is a performance gain from Raid 10, this has to be taken into the
context of the system as a whole and what loads it will be under.
John
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
|||Yes for sure..
Only think i can say that cost is high with RAID 10 but for performance it's
great..
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
|||in most cases, yes.
I think I have read articles that suggest anything over 10% Writes you
should get off of RAID 5
Greg Jackson
PDX, Oregon
|||We found that RAID10 was over twice as fast when used for our database dump
volume as compared to RAID5. Not to mention that RAID10 is significantly
more tolerant of disk failures.
Mike Kruchten
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
|||Check out what this site has to say about Raid 5:
http://www.baarf.com/
Andrew J. Kelly SQL MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
Raid 10 and SQL
Are there any significant performance issues between running raid 5 verses
raid 10
with equal hardware configurations?. Thanks for being there to help, you
people have been life-savers.Yes for sure..
Only think i can say that cost is high with RAID 10 but for performance it's
great..
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||Hi
Check out SQL Server 2000 Performance Tuning Technical Reference ISBN
0-7356-1270-6
There is a performance gain from Raid 10, this has to be taken into the
context of the system as a whole and what loads it will be under.
John
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||in most cases, yes.
I think I have read articles that suggest anything over 10% Writes you
should get off of RAID 5
Greg Jackson
PDX, Oregon|||We found that RAID10 was over twice as fast when used for our database dump
volume as compared to RAID5. Not to mention that RAID10 is significantly
more tolerant of disk failures.
Mike Kruchten
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||Check out what this site has to say about Raid 5:
http://www.baarf.com/
--
Andrew J. Kelly SQL MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
raid 10
with equal hardware configurations?. Thanks for being there to help, you
people have been life-savers.Yes for sure..
Only think i can say that cost is high with RAID 10 but for performance it's
great..
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||Hi
Check out SQL Server 2000 Performance Tuning Technical Reference ISBN
0-7356-1270-6
There is a performance gain from Raid 10, this has to be taken into the
context of the system as a whole and what loads it will be under.
John
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||in most cases, yes.
I think I have read articles that suggest anything over 10% Writes you
should get off of RAID 5
Greg Jackson
PDX, Oregon|||We found that RAID10 was over twice as fast when used for our database dump
volume as compared to RAID5. Not to mention that RAID10 is significantly
more tolerant of disk failures.
Mike Kruchten
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||Check out what this site has to say about Raid 5:
http://www.baarf.com/
--
Andrew J. Kelly SQL MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
Raid 10 and SQL
Are there any significant performance issues between running raid 5 verses
raid 10
with equal hardware configurations?. Thanks for being there to help, you
people have been life-savers.Hi
Check out SQL Server 2000 Performance Tuning Technical Reference ISBN
0-7356-1270-6
There is a performance gain from Raid 10, this has to be taken into the
context of the system as a whole and what loads it will be under.
John
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||Yes for sure..
Only think i can say that cost is high with RAID 10 but for performance it's
great..
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||in most cases, yes.
I think I have read articles that suggest anything over 10% Writes you
should get off of RAID 5
Greg Jackson
PDX, Oregon|||We found that RAID10 was over twice as fast when used for our database dump
volume as compared to RAID5. Not to mention that RAID10 is significantly
more tolerant of disk failures.
Mike Kruchten
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||Check out what this site has to say about Raid 5:
http://www.baarf.com/
Andrew J. Kelly SQL MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
raid 10
with equal hardware configurations?. Thanks for being there to help, you
people have been life-savers.Hi
Check out SQL Server 2000 Performance Tuning Technical Reference ISBN
0-7356-1270-6
There is a performance gain from Raid 10, this has to be taken into the
context of the system as a whole and what loads it will be under.
John
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||Yes for sure..
Only think i can say that cost is high with RAID 10 but for performance it's
great..
"coenzyme" wrote:
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||in most cases, yes.
I think I have read articles that suggest anything over 10% Writes you
should get off of RAID 5
Greg Jackson
PDX, Oregon|||We found that RAID10 was over twice as fast when used for our database dump
volume as compared to RAID5. Not to mention that RAID10 is significantly
more tolerant of disk failures.
Mike Kruchten
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>|||Check out what this site has to say about Raid 5:
http://www.baarf.com/
Andrew J. Kelly SQL MVP
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:74B28604-FE4A-4553-B134-269A3E6FBD1F@.microsoft.com...
> Are there any significant performance issues between running raid 5 verses
> raid 10
> with equal hardware configurations?. Thanks for being there to help, you
> people have been life-savers.
>
RAID - Failure -- SQL Server Won't Start
Running a Dell 2400 dual CPU - 4 drive in a RAID-5
configuration. System was telling me I lost 2 drives
of the four - Forced restore (twice) after changing
different pices of hardware and everything seems to be
fine except SQL Server WON'T start --
reloaded - on top - SQL Developer Edition and SP 3a --
still no help --
1. If I remove SQL - what happens to my DBs?
2. Any other ideas would be great?
jed@.experiencedtechnology.comYour databses are unaffected by uninstalling and relaoding SQL Server - =the files will still be there but you will need to re-attach them.
More intersting is why will SQl Server not start - can you post the =contents of the error log file. There should be no need to re-install - =but any messages in the SQL Server error log will give us a clue.
Mike John
"Jim D" <anonymous@.discussions.microsoft.com> wrote in message =news:06ec01c3d78f$8e85bb90$a501280a@.phx.gbl...
> Running a Dell 2400 dual CPU - 4 drive in a RAID-5 > configuration. System was telling me I lost 2 drives > of the four - Forced restore (twice) after changing > different pices of hardware and everything seems to be > fine except SQL Server WON'T start --
> > reloaded - on top - SQL Developer Edition and SP 3a -- > still no help -- > > 1. If I remove SQL - what happens to my DBs?
> 2. Any other ideas would be great?
> > jed@.experiencedtechnology.com
configuration. System was telling me I lost 2 drives
of the four - Forced restore (twice) after changing
different pices of hardware and everything seems to be
fine except SQL Server WON'T start --
reloaded - on top - SQL Developer Edition and SP 3a --
still no help --
1. If I remove SQL - what happens to my DBs?
2. Any other ideas would be great?
jed@.experiencedtechnology.comYour databses are unaffected by uninstalling and relaoding SQL Server - =the files will still be there but you will need to re-attach them.
More intersting is why will SQl Server not start - can you post the =contents of the error log file. There should be no need to re-install - =but any messages in the SQL Server error log will give us a clue.
Mike John
"Jim D" <anonymous@.discussions.microsoft.com> wrote in message =news:06ec01c3d78f$8e85bb90$a501280a@.phx.gbl...
> Running a Dell 2400 dual CPU - 4 drive in a RAID-5 > configuration. System was telling me I lost 2 drives > of the four - Forced restore (twice) after changing > different pices of hardware and everything seems to be > fine except SQL Server WON'T start --
> > reloaded - on top - SQL Developer Edition and SP 3a -- > still no help -- > > 1. If I remove SQL - what happens to my DBs?
> 2. Any other ideas would be great?
> > jed@.experiencedtechnology.com
RAID - Failure -- SQL Server Won't Start
Running a Dell 2400 dual CPU - 4 drive in a RAID-5
configuration. System was telling me I lost 2 drives
of the four - Forced restore (twice) after changing
different pices of hardware and everything seems to be
fine except SQL Server WON'T start --
reloaded - on top - SQL Developer Edition and SP 3a --
still no help --
1. If I remove SQL - what happens to my DBs?
2. Any other ideas would be great?
jed@.experiencedtechnology.comYour databses are unaffected by uninstalling and relaoding SQL Server - =
the files will still be there but you will need to re-attach them.=20
More intersting is why will SQl Server not start - can you post the =
contents of the error log file. There should be no need to re-install - =
but any messages in the SQL Server error log will give us a clue.
Mike John
"Jim D" <anonymous@.discussions.microsoft.com> wrote in message =
news:06ec01c3d78f$8e85bb90$a501280a@.phx.gbl...
configuration. System was telling me I lost 2 drives
of the four - Forced restore (twice) after changing
different pices of hardware and everything seems to be
fine except SQL Server WON'T start --
reloaded - on top - SQL Developer Edition and SP 3a --
still no help --
1. If I remove SQL - what happens to my DBs?
2. Any other ideas would be great?
jed@.experiencedtechnology.comYour databses are unaffected by uninstalling and relaoding SQL Server - =
the files will still be there but you will need to re-attach them.=20
More intersting is why will SQl Server not start - can you post the =
contents of the error log file. There should be no need to re-install - =
but any messages in the SQL Server error log will give us a clue.
Mike John
"Jim D" <anonymous@.discussions.microsoft.com> wrote in message =
news:06ec01c3d78f$8e85bb90$a501280a@.phx.gbl...
quote:
> Running a Dell 2400 dual CPU - 4 drive in a RAID-5=20
> configuration. System was telling me I lost 2 drives=20
> of the four - Forced restore (twice) after changing=20
> different pices of hardware and everything seems to be=20
> fine except SQL Server WON'T start --
>=20
> reloaded - on top - SQL Developer Edition and SP 3a -- =20
> still no help --=20
>=20
> 1. If I remove SQL - what happens to my DBs?
> 2. Any other ideas would be great?
>=20
> jed@.experiencedtechnology.com
Friday, March 23, 2012
R
I am having some issues with my replication server. The sqlservr.exe is running 75-100%. Sometimes it pins up above 95 for more than two minutes. It's eating a ton of memory as well. The only thing the server is used for is to replicate our production server. The distribution database runs on it and the log reader, but thats it!
Dell Poweredge 2950
2003 x64
Xeon 5160 3.00
8.0 gbs of ram
Currently replication is running OK with the processor that high. But it only has about 900 mb of memory available. Does anyone have any ideas why sqlserver.exe needs that much horsepower? I just can't image it is right.
Thanks,ok ok i figured this out. i am kind of a newbie. It was an in house app running huge select statements that was eating up all the CPU. I ran a trace and it was pretty obvious. The replication db also serves as the reporting db. Not a replication issue at all, the cpu is running very low now.
thanks
Wednesday, March 21, 2012
'QUOTED_IDENTIFIER' when updating
Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.
The setting is a sticky option, so setting it before execution or inside the proc doesn't change
anything. You have to set this in the connection where you *create* the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:13732376-28A5-4352-93CB-619B20137D3D@.microsoft.com...
> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>
>
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.
The setting is a sticky option, so setting it before execution or inside the proc doesn't change
anything. You have to set this in the connection where you *create* the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:13732376-28A5-4352-93CB-619B20137D3D@.microsoft.com...
> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>
>
'QUOTED_IDENTIFIER' when updating
Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.The setting is a sticky option, so setting it before execution or inside the
proc doesn't change
anything. You have to set this in the connection where you *create* the proc
edure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:13732376-28A5-4352-93CB-619B20137D3D@.microsoft.com...
> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Lin
e
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>
>
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.The setting is a sticky option, so setting it before execution or inside the
proc doesn't change
anything. You have to set this in the connection where you *create* the proc
edure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:13732376-28A5-4352-93CB-619B20137D3D@.microsoft.com...
> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Lin
e
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>
>
'QUOTED_IDENTIFIER' when updating
Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.The setting is a sticky option, so setting it before execution or inside the proc doesn't change
anything. You have to set this in the connection where you *create* the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:13732376-28A5-4352-93CB-619B20137D3D@.microsoft.com...
> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>
>
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.The setting is a sticky option, so setting it before execution or inside the proc doesn't change
anything. You have to set this in the connection where you *create* the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:13732376-28A5-4352-93CB-619B20137D3D@.microsoft.com...
> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>
>
Monday, March 12, 2012
Quick SQL Server 2000 Locking question
We are running a 3rd party ETL tool to populate a denormailized version of a production database for reports. Everything works fine 95% of the time. However there is a semi-rare occurence of the ETL tool hanging up. The norm is for the tool to take about 5 times longer than usual, but it still works. Over the weekend however it through an error saying:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
The reports are run through Crystal using stored procs and are all basically select statements
So my question(s) are the following:
1. What kind of lock would a report put on a table (select statement)
2. Would it make sense to change stored procs to use WITH NOLOCK?
3. Or is something else going on?
Your thoughts would be greatly appreciated.1) Select statements put shared locks on tables. These allow other readers, but block writers to the locked portions of the tables. Shared locks are blocked by existing Exclusive locks.
2) If you like partial results, using (nolock) is perfectly fine. I wouldn't suggest it to an accountant, though.
3) Could be, but you would need to monitor the process as it is running to be certain.|||Here is response I received from 3rd party ETL tool:
Tables/rows seem to be locked by another application or process. We have seen users do this with DBArtisan or Query Analyzer. They lock the table and can't figure out why DT/Engine appears to hang for hours. When they close whichever application that is locking the tables, DT/Engine finishes up what it was doing and everything goes back to normal. You may have a DBA tool like Query Analyzer open looking at either the source or target data as it moves which would result in a hang as the engine waits for the release.|||The ETL tool does update some Flag fields just to keep track of what's being moved around. My concern is that the reports run lighting fast with none taking more than 1 second.
The slow down (when it occurs) happens on the same task over and over. You could call it a main enitity table, but its not the largest table. However, most of the reports use it. But we are talking about a 3 hour hang-up on the ETL side. There's no way it wouldn't be able to obtain a lock in that time, right?
Just having query analyzer open is not causing this.
Next steps??
1. Keep a trace going with duration set to more than 10 minutes?
2. Try to track down who's doing what on this server (located across the globe)?|||First step is to prove who is blocking and whether there is blocking at all. A three hour block sounds suspiciously like a maintenance job. A three hour slowdown could be SQL Server got starved for memory by some currently unknown process.
Personally, I would spend a night with this thing, to see if I can spot the reason for the slowdown/stoppage. OK, first thing I would do is write scripts to capture the interesting information (the sysprocesses table is a nice place to start. Pay attention to the blocked, waittype, waitresource, and open_tran columns). But then, I am lazy like that.|||Never used sysprocesses table before. Looking at it for first time I notice a few suspect entries. Is this just a current snapshot or does it keep archive info. It only had 3 pertinent rows out of 22.
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
The reports are run through Crystal using stored procs and are all basically select statements
So my question(s) are the following:
1. What kind of lock would a report put on a table (select statement)
2. Would it make sense to change stored procs to use WITH NOLOCK?
3. Or is something else going on?
Your thoughts would be greatly appreciated.1) Select statements put shared locks on tables. These allow other readers, but block writers to the locked portions of the tables. Shared locks are blocked by existing Exclusive locks.
2) If you like partial results, using (nolock) is perfectly fine. I wouldn't suggest it to an accountant, though.
3) Could be, but you would need to monitor the process as it is running to be certain.|||Here is response I received from 3rd party ETL tool:
Tables/rows seem to be locked by another application or process. We have seen users do this with DBArtisan or Query Analyzer. They lock the table and can't figure out why DT/Engine appears to hang for hours. When they close whichever application that is locking the tables, DT/Engine finishes up what it was doing and everything goes back to normal. You may have a DBA tool like Query Analyzer open looking at either the source or target data as it moves which would result in a hang as the engine waits for the release.|||The ETL tool does update some Flag fields just to keep track of what's being moved around. My concern is that the reports run lighting fast with none taking more than 1 second.
The slow down (when it occurs) happens on the same task over and over. You could call it a main enitity table, but its not the largest table. However, most of the reports use it. But we are talking about a 3 hour hang-up on the ETL side. There's no way it wouldn't be able to obtain a lock in that time, right?
Just having query analyzer open is not causing this.
Next steps??
1. Keep a trace going with duration set to more than 10 minutes?
2. Try to track down who's doing what on this server (located across the globe)?|||First step is to prove who is blocking and whether there is blocking at all. A three hour block sounds suspiciously like a maintenance job. A three hour slowdown could be SQL Server got starved for memory by some currently unknown process.
Personally, I would spend a night with this thing, to see if I can spot the reason for the slowdown/stoppage. OK, first thing I would do is write scripts to capture the interesting information (the sysprocesses table is a nice place to start. Pay attention to the blocked, waittype, waitresource, and open_tran columns). But then, I am lazy like that.|||Never used sysprocesses table before. Looking at it for first time I notice a few suspect entries. Is this just a current snapshot or does it keep archive info. It only had 3 pertinent rows out of 22.
Friday, March 9, 2012
Quick Question regarding number of instances...
I am developing an application for a client who already has a liscensed SQL server running. They have a software package that requires an instance using "Windows Only" for the authentication mode. I need an instance to use "SQL Server and Windows" for my application to be able to speak with the server from other computers on their network (or at least I believe I do...).
My question is, "is there any restrictions on the 'number' of instances an institution can run on a computer that is liscensed to run the software"?
Alternately, if there is a limit, I need to figure out if it is possible to set up the application (VB6, btw) to connect to the server from outside the host computer if it is using "Windows Only" Authentication. The application will be installed on several computers in the client's network and I'm not familiar with how to dynamicly poll the 'current Windows Login' information.
It would be much simpler for the application to create a new instance with the desired authentication method, but is there a security issue in doing this? Would the data be any more at risk to outside corruption using the SQL server authentication mode?Number of named instances if default instance is already present is 15. Total number of instances including default instance cannot exceed 16.
Windows authentication is safer simply because there is no need to provide user ID and password in clear text. If you use OLE DB Provider then "Intgrated Security=SSPI'and for DSN-less connections, - "Trusted_Connection=Yes"
My question is, "is there any restrictions on the 'number' of instances an institution can run on a computer that is liscensed to run the software"?
Alternately, if there is a limit, I need to figure out if it is possible to set up the application (VB6, btw) to connect to the server from outside the host computer if it is using "Windows Only" Authentication. The application will be installed on several computers in the client's network and I'm not familiar with how to dynamicly poll the 'current Windows Login' information.
It would be much simpler for the application to create a new instance with the desired authentication method, but is there a security issue in doing this? Would the data be any more at risk to outside corruption using the SQL server authentication mode?Number of named instances if default instance is already present is 15. Total number of instances including default instance cannot exceed 16.
Windows authentication is safer simply because there is no need to provide user ID and password in clear text. If you use OLE DB Provider then "Intgrated Security=SSPI'and for DSN-less connections, - "Trusted_Connection=Yes"
Quick question about security and MS SQL 2000
OK, we have a couple of MS SQL 2000 servers running in a Win2k AD domain.
Both machines are using mixed mode security, the issue appears to be this.
If I go into AD and change a groups name that has been granted access to
the SQL server, it doesn't seem to pick up on the name change.
I also can't add the new name to the SQL server, because of a conflict of a
SID.
So how do I get the MS SQL server to refresh the names of the native NT
groups that have been granted access, then have the names changed ??I think you need to drop the old name and add the new one. Why are you doing this? Experimenting?|||Its not uncommon to have a native AD Security group renamed to match
a changed "department" name lets say.
SQL 2000 doesn't seem to see the name change in AD, where is this info stored in
the SQL server ?? is there a SP to refresh the cache or this table ?|||I'd say changing a department name should result in a change of an OU, not a group. But that's just me.
Both machines are using mixed mode security, the issue appears to be this.
If I go into AD and change a groups name that has been granted access to
the SQL server, it doesn't seem to pick up on the name change.
I also can't add the new name to the SQL server, because of a conflict of a
SID.
So how do I get the MS SQL server to refresh the names of the native NT
groups that have been granted access, then have the names changed ??I think you need to drop the old name and add the new one. Why are you doing this? Experimenting?|||Its not uncommon to have a native AD Security group renamed to match
a changed "department" name lets say.
SQL 2000 doesn't seem to see the name change in AD, where is this info stored in
the SQL server ?? is there a SP to refresh the cache or this table ?|||I'd say changing a department name should result in a change of an OU, not a group. But that's just me.
Wednesday, March 7, 2012
quick question
I have SQL 2000 server running on my SBS box.
Is it normal for there to be 5 instances of sqlservr.exe to be running at
teh same time?>I have SQL 2000 server running on my SBS box.
> Is it normal for there to be 5 instances of sqlservr.exe to be running at
> teh same time?
If you have 5 separate SQL Server instances and all are running, then this
is normal. I know next to nothing about SBS but it may be that multiple
applications are installed (Sharepoint, etc.), each with a separate SQL
Server instance.
Crossposted to microsoft.public.windows.server.sbs.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Johnfli" <john@.ivhs.us> wrote in message
news:u3NPUDG$HHA.2004@.TK2MSFTNGP06.phx.gbl...
>I have SQL 2000 server running on my SBS box.
> Is it normal for there to be 5 instances of sqlservr.exe to be running at
> teh same time?
>
Is it normal for there to be 5 instances of sqlservr.exe to be running at
teh same time?>I have SQL 2000 server running on my SBS box.
> Is it normal for there to be 5 instances of sqlservr.exe to be running at
> teh same time?
If you have 5 separate SQL Server instances and all are running, then this
is normal. I know next to nothing about SBS but it may be that multiple
applications are installed (Sharepoint, etc.), each with a separate SQL
Server instance.
Crossposted to microsoft.public.windows.server.sbs.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Johnfli" <john@.ivhs.us> wrote in message
news:u3NPUDG$HHA.2004@.TK2MSFTNGP06.phx.gbl...
>I have SQL 2000 server running on my SBS box.
> Is it normal for there to be 5 instances of sqlservr.exe to be running at
> teh same time?
>
Quick Dumb Question
Quick Dumb Question
I was wondering if there was a way to revel what format a query returns
per column.
I am running into the situation where I am pulling data from a huge
list, and occasionally I get the error "Implicit conversion from data
type sql_variant to varchar is not allowed. Use the CONVERT function to
run this query." When inserting it into a table. When I run the
command without the insert it return all the information without the
error. So now I need to go hunt what column has the incorrect format
and what format to change it to or need to run CONVERT on that
particular item in the query
Hope that made sense.SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Your Table'
Should give you a listing of the columns in a table, and their datatypes.
"Matthew" wrote:
> Quick Dumb Question
> I was wondering if there was a way to revel what format a query returns
> per column.
> I am running into the situation where I am pulling data from a huge
> list, and occasionally I get the error "Implicit conversion from data
> type sql_variant to varchar is not allowed. Use the CONVERT function to
> run this query." When inserting it into a table. When I run the
> command without the insert it return all the information without the
> error. So now I need to go hunt what column has the incorrect format
> and what format to change it to or need to run CONVERT on that
> particular item in the query
> Hope that made sense.
>
I was wondering if there was a way to revel what format a query returns
per column.
I am running into the situation where I am pulling data from a huge
list, and occasionally I get the error "Implicit conversion from data
type sql_variant to varchar is not allowed. Use the CONVERT function to
run this query." When inserting it into a table. When I run the
command without the insert it return all the information without the
error. So now I need to go hunt what column has the incorrect format
and what format to change it to or need to run CONVERT on that
particular item in the query
Hope that made sense.SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Your Table'
Should give you a listing of the columns in a table, and their datatypes.
"Matthew" wrote:
> Quick Dumb Question
> I was wondering if there was a way to revel what format a query returns
> per column.
> I am running into the situation where I am pulling data from a huge
> list, and occasionally I get the error "Implicit conversion from data
> type sql_variant to varchar is not allowed. Use the CONVERT function to
> run this query." When inserting it into a table. When I run the
> command without the insert it return all the information without the
> error. So now I need to go hunt what column has the incorrect format
> and what format to change it to or need to run CONVERT on that
> particular item in the query
> Hope that made sense.
>
Saturday, February 25, 2012
Queue Reader fails
I'm running a clustered distributor on SQL 2000 sp3a.
When I stop the publisher that runs in the same cluster
(diffrent SQL server) the Queue reader first logs
"ODBC Error:SQL Server does not exist or access denied."
(It's dead) then it says "Unable to connect to publisher"
and finaly it says "Queue Reader aborting step failed"
and then the queue reader fails.
Any ideas why the queue reader fails?
Is your Queue Reader on the Publisher or Subscriber?
You might want to do some logging to see what the error message is. Follow
the steps in the below link:
http://support.microsoft.com/default...b;EN-US;312292
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:29b3201c4658d$2f264d90$a401280a@.phx.gbl...
> I'm running a clustered distributor on SQL 2000 sp3a.
> When I stop the publisher that runs in the same cluster
> (diffrent SQL server) the Queue reader first logs
> "ODBC Error:SQL Server does not exist or access denied."
> (It's dead) then it says "Unable to connect to publisher"
> and finaly it says "Queue Reader aborting step failed"
> and then the queue reader fails.
> Any ideas why the queue reader fails?
|||When I enabled logging for the agent I saw something that
might have been the problem.
In the "Run Agent" step there is a "Step
name", "Type", "Database" and "Command" option.
The Database option read AMS which is one of our user
databases. I changed that to distribution (name of the
distribution database) and now it seems to work. Is this
a SQL bug?
BTW the queue reader runs on the Distributor.
>--Original Message--
>Is your Queue Reader on the Publisher or Subscriber?
>You might want to do some logging to see what the error
message is. Follow
>the steps in the below link:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;312292
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote
in message[vbcol=seagreen]
>news:29b3201c4658d$2f264d90$a401280a@.phx.gbl...
denied."[vbcol=seagreen]
publisher"
>
>.
>
|||Hi,
I had a similar issue. The Queue Reader Agent would fail, and whenever I
tried to restrart I would get the "Queue Reader aborting step failed" error.
after logging the error messages as Hillary suggested, I saw that it was
failing on startup with the following SQL error
[6/22/2004 12:54:22 PM]MAST.Bill: select top 1 id, name from
MSqreader_agents
Server: MAST, Database Bill : ODBC Error:Invalid object name
'MSqreader_agents'.
"Bill" is a user database; the table MSqreader_agents is in the distribution
database. When I looked at the job step that runs the queue reader agent, I
saw that the database dropdown was et to "Bill". This job was created by the
replication setup wizard and never updated. I changed the step to run with
the distribution database, and I was able to restart the agent. However, the
change doesn't "stick"; if I go back and look at the job step, it's set to
run under the Bill" database.
I first thought something on my server had gotten corrupted, but it sounds
like you have the same problem.
If anyone from Microsift reads this newsgroup, please chime in!
Ed
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:299fd01c465ad$498c1000$a501280a@.phx.gbl...[vbcol=seagreen]
> When I enabled logging for the agent I saw something that
> might have been the problem.
> In the "Run Agent" step there is a "Step
> name", "Type", "Database" and "Command" option.
> The Database option read AMS which is one of our user
> databases. I changed that to distribution (name of the
> distribution database) and now it seems to work. Is this
> a SQL bug?
> BTW the queue reader runs on the Distributor.
> message is. Follow
> US;312292
> in message
> denied."
> publisher"
|||can either of you script out your tables, publications, subscriptions so I
or MS folks can repro this?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<ed.d@.osgbilling.com> wrote in message
news:erbmp3eZEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I had a similar issue. The Queue Reader Agent would fail, and whenever I
> tried to restrart I would get the "Queue Reader aborting step failed"
error.
> after logging the error messages as Hillary suggested, I saw that it was
> failing on startup with the following SQL error
> [6/22/2004 12:54:22 PM]MAST.Bill: select top 1 id, name from
> MSqreader_agents
> Server: MAST, Database Bill : ODBC Error:Invalid object name
> 'MSqreader_agents'.
> "Bill" is a user database; the table MSqreader_agents is in the
distribution
> database. When I looked at the job step that runs the queue reader agent,
I
> saw that the database dropdown was et to "Bill". This job was created by
the
> replication setup wizard and never updated. I changed the step to run with
> the distribution database, and I was able to restart the agent. However,
the
> change doesn't "stick"; if I go back and look at the job step, it's set to
> run under the Bill" database.
> I first thought something on my server had gotten corrupted, but it sounds
> like you have the same problem.
> If anyone from Microsift reads this newsgroup, please chime in!
> Ed
>
> "Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
> news:299fd01c465ad$498c1000$a501280a@.phx.gbl...
>
|||I don't think it has anything to do with our publications
or subscriptions my guess is that both Ed and I have user
database starting in my case with A (AMS) and in Ed's
case B (Bill) and they both are sorted before D
(distribution). So my guess is that if you add a database
at the distributor with a name that sorts
before "distribution" you will see the problem.
/Peter
>--Original Message--
>can either of you script out your tables, publications,
subscriptions so I[vbcol=seagreen]
>or MS folks can repro this?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
><ed.d@.osgbilling.com> wrote in message
>news:erbmp3eZEHA.3508@.TK2MSFTNGP09.phx.gbl...
fail, and whenever I[vbcol=seagreen]
aborting step failed"[vbcol=seagreen]
>error.
I saw that it was[vbcol=seagreen]
name from[vbcol=seagreen]
object name[vbcol=seagreen]
is in the[vbcol=seagreen]
>distribution
queue reader agent,[vbcol=seagreen]
>I
job was created by[vbcol=seagreen]
>the
the step to run with[vbcol=seagreen]
the agent. However,[vbcol=seagreen]
>the
job step, it's set to[vbcol=seagreen]
corrupted, but it sounds[vbcol=seagreen]
chime in![vbcol=seagreen]
in message[vbcol=seagreen]
that[vbcol=seagreen]
the[vbcol=seagreen]
this[vbcol=seagreen]
error[vbcol=seagreen]
scid=kb;EN-[vbcol=seagreen]
wrote[vbcol=seagreen]
sp3a.[vbcol=seagreen]
cluster[vbcol=seagreen]
failed"
>
>.
>
|||Hi,
I have the same problem but I achieved that the DB that appears in log would
be the correct one and continues wrong.
I have proved what you say and it continues without working.
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> escribi en el mensaje
news:2ac8f01c467ed$c0c284a0$a501280a@.phx.gbl...[vbcol=seagreen]
> I don't think it has anything to do with our publications
> or subscriptions my guess is that both Ed and I have user
> database starting in my case with A (AMS) and in Ed's
> case B (Bill) and they both are sorted before D
> (distribution). So my guess is that if you add a database
> at the distributor with a name that sorts
> before "distribution" you will see the problem.
> /Peter
> subscriptions so I
> fail, and whenever I
> aborting step failed"
> I saw that it was
> name from
> object name
> is in the
> queue reader agent,
> job was created by
> the step to run with
> the agent. However,
> job step, it's set to
> corrupted, but it sounds
> chime in!
> in message
> that
> the
> this
> error
> scid=kb;EN-
> wrote
> sp3a.
> cluster
> failed"
When I stop the publisher that runs in the same cluster
(diffrent SQL server) the Queue reader first logs
"ODBC Error:SQL Server does not exist or access denied."
(It's dead) then it says "Unable to connect to publisher"
and finaly it says "Queue Reader aborting step failed"
and then the queue reader fails.
Any ideas why the queue reader fails?
Is your Queue Reader on the Publisher or Subscriber?
You might want to do some logging to see what the error message is. Follow
the steps in the below link:
http://support.microsoft.com/default...b;EN-US;312292
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:29b3201c4658d$2f264d90$a401280a@.phx.gbl...
> I'm running a clustered distributor on SQL 2000 sp3a.
> When I stop the publisher that runs in the same cluster
> (diffrent SQL server) the Queue reader first logs
> "ODBC Error:SQL Server does not exist or access denied."
> (It's dead) then it says "Unable to connect to publisher"
> and finaly it says "Queue Reader aborting step failed"
> and then the queue reader fails.
> Any ideas why the queue reader fails?
|||When I enabled logging for the agent I saw something that
might have been the problem.
In the "Run Agent" step there is a "Step
name", "Type", "Database" and "Command" option.
The Database option read AMS which is one of our user
databases. I changed that to distribution (name of the
distribution database) and now it seems to work. Is this
a SQL bug?
BTW the queue reader runs on the Distributor.
>--Original Message--
>Is your Queue Reader on the Publisher or Subscriber?
>You might want to do some logging to see what the error
message is. Follow
>the steps in the below link:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;312292
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote
in message[vbcol=seagreen]
>news:29b3201c4658d$2f264d90$a401280a@.phx.gbl...
denied."[vbcol=seagreen]
publisher"
>
>.
>
|||Hi,
I had a similar issue. The Queue Reader Agent would fail, and whenever I
tried to restrart I would get the "Queue Reader aborting step failed" error.
after logging the error messages as Hillary suggested, I saw that it was
failing on startup with the following SQL error
[6/22/2004 12:54:22 PM]MAST.Bill: select top 1 id, name from
MSqreader_agents
Server: MAST, Database Bill : ODBC Error:Invalid object name
'MSqreader_agents'.
"Bill" is a user database; the table MSqreader_agents is in the distribution
database. When I looked at the job step that runs the queue reader agent, I
saw that the database dropdown was et to "Bill". This job was created by the
replication setup wizard and never updated. I changed the step to run with
the distribution database, and I was able to restart the agent. However, the
change doesn't "stick"; if I go back and look at the job step, it's set to
run under the Bill" database.
I first thought something on my server had gotten corrupted, but it sounds
like you have the same problem.
If anyone from Microsift reads this newsgroup, please chime in!
Ed
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
news:299fd01c465ad$498c1000$a501280a@.phx.gbl...[vbcol=seagreen]
> When I enabled logging for the agent I saw something that
> might have been the problem.
> In the "Run Agent" step there is a "Step
> name", "Type", "Database" and "Command" option.
> The Database option read AMS which is one of our user
> databases. I changed that to distribution (name of the
> distribution database) and now it seems to work. Is this
> a SQL bug?
> BTW the queue reader runs on the Distributor.
> message is. Follow
> US;312292
> in message
> denied."
> publisher"
|||can either of you script out your tables, publications, subscriptions so I
or MS folks can repro this?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<ed.d@.osgbilling.com> wrote in message
news:erbmp3eZEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I had a similar issue. The Queue Reader Agent would fail, and whenever I
> tried to restrart I would get the "Queue Reader aborting step failed"
error.
> after logging the error messages as Hillary suggested, I saw that it was
> failing on startup with the following SQL error
> [6/22/2004 12:54:22 PM]MAST.Bill: select top 1 id, name from
> MSqreader_agents
> Server: MAST, Database Bill : ODBC Error:Invalid object name
> 'MSqreader_agents'.
> "Bill" is a user database; the table MSqreader_agents is in the
distribution
> database. When I looked at the job step that runs the queue reader agent,
I
> saw that the database dropdown was et to "Bill". This job was created by
the
> replication setup wizard and never updated. I changed the step to run with
> the distribution database, and I was able to restart the agent. However,
the
> change doesn't "stick"; if I go back and look at the job step, it's set to
> run under the Bill" database.
> I first thought something on my server had gotten corrupted, but it sounds
> like you have the same problem.
> If anyone from Microsift reads this newsgroup, please chime in!
> Ed
>
> "Peter Ericsson" <peter.l.ericsson@.ericsson.com> wrote in message
> news:299fd01c465ad$498c1000$a501280a@.phx.gbl...
>
|||I don't think it has anything to do with our publications
or subscriptions my guess is that both Ed and I have user
database starting in my case with A (AMS) and in Ed's
case B (Bill) and they both are sorted before D
(distribution). So my guess is that if you add a database
at the distributor with a name that sorts
before "distribution" you will see the problem.
/Peter
>--Original Message--
>can either of you script out your tables, publications,
subscriptions so I[vbcol=seagreen]
>or MS folks can repro this?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
><ed.d@.osgbilling.com> wrote in message
>news:erbmp3eZEHA.3508@.TK2MSFTNGP09.phx.gbl...
fail, and whenever I[vbcol=seagreen]
aborting step failed"[vbcol=seagreen]
>error.
I saw that it was[vbcol=seagreen]
name from[vbcol=seagreen]
object name[vbcol=seagreen]
is in the[vbcol=seagreen]
>distribution
queue reader agent,[vbcol=seagreen]
>I
job was created by[vbcol=seagreen]
>the
the step to run with[vbcol=seagreen]
the agent. However,[vbcol=seagreen]
>the
job step, it's set to[vbcol=seagreen]
corrupted, but it sounds[vbcol=seagreen]
chime in![vbcol=seagreen]
in message[vbcol=seagreen]
that[vbcol=seagreen]
the[vbcol=seagreen]
this[vbcol=seagreen]
error[vbcol=seagreen]
scid=kb;EN-[vbcol=seagreen]
wrote[vbcol=seagreen]
sp3a.[vbcol=seagreen]
cluster[vbcol=seagreen]
failed"
>
>.
>
|||Hi,
I have the same problem but I achieved that the DB that appears in log would
be the correct one and continues wrong.
I have proved what you say and it continues without working.
"Peter Ericsson" <peter.l.ericsson@.ericsson.com> escribi en el mensaje
news:2ac8f01c467ed$c0c284a0$a501280a@.phx.gbl...[vbcol=seagreen]
> I don't think it has anything to do with our publications
> or subscriptions my guess is that both Ed and I have user
> database starting in my case with A (AMS) and in Ed's
> case B (Bill) and they both are sorted before D
> (distribution). So my guess is that if you add a database
> at the distributor with a name that sorts
> before "distribution" you will see the problem.
> /Peter
> subscriptions so I
> fail, and whenever I
> aborting step failed"
> I saw that it was
> name from
> object name
> is in the
> queue reader agent,
> job was created by
> the step to run with
> the agent. However,
> job step, it's set to
> corrupted, but it sounds
> chime in!
> in message
> that
> the
> this
> error
> scid=kb;EN-
> wrote
> sp3a.
> cluster
> failed"
Queue Reader failes occasionally
I am using transactional replication with queued updating between two
servers running SQL Server 2000 SP3. Publisher and distributor run
both on the same server (a cluster machine). There is one subscriber.
The Queue Reader Agent starts successfully, and transactions
originating on either side are properly replicated. However, the Queue
Reader occasionally fails. I enabled logging, but the output file
looks normal to me; several queries for queued data, but then it seems
to timeout. It just sits there for 3 minutes, then fails and retries.
No error message in the output file. Finally (after specified count of
retries) the agent shuts down with the message "Remote procedure call
failed". When I try to restart Queue Reader Agent, same behavior. I
have to examine the queue (table MSreplication_queue) and delete the
first respectively first few rows. Before deleting I execute the
corresponding queries (sp calls of type sp_MSsync_upd_MyTable) in
Query Analyzer, what succeeds. After deleting the rows from
MSreplication_queue I can restart Queue Reader Agent and now all works
properly up to the next fail. :-(
Does anyone have any advice? Thanks in advance
Dorrit
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict237945.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=827170
How many subscribers do you have? Queued Replication works best with under
10 subscribers and when most of the transactions originate on your
publisher.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Riemi" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_827170_8970ece2d43554af53766b469d92d042@.dbf orumz.com...
> I am using transactional replication with queued updating between two
> servers running SQL Server 2000 SP3. Publisher and distributor run
> both on the same server (a cluster machine). There is one subscriber.
> The Queue Reader Agent starts successfully, and transactions
> originating on either side are properly replicated. However, the Queue
> Reader occasionally fails. I enabled logging, but the output file
> looks normal to me; several queries for queued data, but then it seems
> to timeout. It just sits there for 3 minutes, then fails and retries.
> No error message in the output file. Finally (after specified count of
> retries) the agent shuts down with the message "Remote procedure call
> failed". When I try to restart Queue Reader Agent, same behavior. I
> have to examine the queue (table MSreplication_queue) and delete the
> first respectively first few rows. Before deleting I execute the
> corresponding queries (sp calls of type sp_MSsync_upd_MyTable) in
> Query Analyzer, what succeeds. After deleting the rows from
> MSreplication_queue I can restart Queue Reader Agent and now all works
> properly up to the next fail. :-(
> Does anyone have any advice? Thanks in advance
> Dorrit
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
http://www.dbforumz.com/Replication-...ict237945.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
http://www.dbforumz.com/eform.php?p=827170
|||"" wrote:
> How many subscribers do you have? Queued Replication works
> best with under
> 10 subscribers and when most of the transactions originate on
> your
> publisher.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Riemi" <UseLinkToEmail@.dbForumz.com> wrote in message
> news:4_827170_8970ece2d43554af53766b469d92d042@.dbf orumz.com...
> between two
> distributor run
> subscriber.
> the Queue
> file
> then it seems
> retries.
> specified count of
> procedure call
> behavior. I
> delete the
> the
> sp_MSsync_upd_MyTable) in
> all works
> standards
> http://www.dbforumz.com/Replication-...ict237945.html
> abuse:
> http://www.dbforumz.com/eform.php?p=827170
There is one subscriber.
servers running SQL Server 2000 SP3. Publisher and distributor run
both on the same server (a cluster machine). There is one subscriber.
The Queue Reader Agent starts successfully, and transactions
originating on either side are properly replicated. However, the Queue
Reader occasionally fails. I enabled logging, but the output file
looks normal to me; several queries for queued data, but then it seems
to timeout. It just sits there for 3 minutes, then fails and retries.
No error message in the output file. Finally (after specified count of
retries) the agent shuts down with the message "Remote procedure call
failed". When I try to restart Queue Reader Agent, same behavior. I
have to examine the queue (table MSreplication_queue) and delete the
first respectively first few rows. Before deleting I execute the
corresponding queries (sp calls of type sp_MSsync_upd_MyTable) in
Query Analyzer, what succeeds. After deleting the rows from
MSreplication_queue I can restart Queue Reader Agent and now all works
properly up to the next fail. :-(
Does anyone have any advice? Thanks in advance
Dorrit
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict237945.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=827170
How many subscribers do you have? Queued Replication works best with under
10 subscribers and when most of the transactions originate on your
publisher.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Riemi" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_827170_8970ece2d43554af53766b469d92d042@.dbf orumz.com...
> I am using transactional replication with queued updating between two
> servers running SQL Server 2000 SP3. Publisher and distributor run
> both on the same server (a cluster machine). There is one subscriber.
> The Queue Reader Agent starts successfully, and transactions
> originating on either side are properly replicated. However, the Queue
> Reader occasionally fails. I enabled logging, but the output file
> looks normal to me; several queries for queued data, but then it seems
> to timeout. It just sits there for 3 minutes, then fails and retries.
> No error message in the output file. Finally (after specified count of
> retries) the agent shuts down with the message "Remote procedure call
> failed". When I try to restart Queue Reader Agent, same behavior. I
> have to examine the queue (table MSreplication_queue) and delete the
> first respectively first few rows. Before deleting I execute the
> corresponding queries (sp calls of type sp_MSsync_upd_MyTable) in
> Query Analyzer, what succeeds. After deleting the rows from
> MSreplication_queue I can restart Queue Reader Agent and now all works
> properly up to the next fail. :-(
> Does anyone have any advice? Thanks in advance
> Dorrit
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
http://www.dbforumz.com/Replication-...ict237945.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
http://www.dbforumz.com/eform.php?p=827170
|||"" wrote:
> How many subscribers do you have? Queued Replication works
> best with under
> 10 subscribers and when most of the transactions originate on
> your
> publisher.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Riemi" <UseLinkToEmail@.dbForumz.com> wrote in message
> news:4_827170_8970ece2d43554af53766b469d92d042@.dbf orumz.com...
> between two
> distributor run
> subscriber.
> the Queue
> file
> then it seems
> retries.
> specified count of
> procedure call
> behavior. I
> delete the
> the
> sp_MSsync_upd_MyTable) in
> all works
> standards
> http://www.dbforumz.com/Replication-...ict237945.html
> abuse:
> http://www.dbforumz.com/eform.php?p=827170
There is one subscriber.
Monday, February 20, 2012
Queue reader agent aborting
Hi,
I'm running transactional replication (SQL Server 2000) with queued updating
between two locations (New Jersey & Arizona). Generally it runs fine, but
about every other week the Queue Reader agent fails with an error message
"Queue reader aborting. The step failed". I can't find any information
regarding the source or nature of the error, and if I try to restart the
agent if fails immediately with the same error. The only way I've been able
to get around the error is to drop the subscription and redo the snapshot.
Does anyone have any suggestions how to determine the source of this error?
Thanks for any help...
Ed
Can you do logging for your queue reader and post the error message you get back here?
Follow the instructions in this kb for more information on how to enable it.
http://support.microsoft.com/default...b;EN-US;312292
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:
> Hi,
> I'm running transactional replication (SQL Server 2000) with queued updating
> between two locations (New Jersey & Arizona). Generally it runs fine, but
> about every other week the Queue Reader agent fails with an error message
> "Queue reader aborting. The step failed". I can't find any information
> regarding the source or nature of the error, and if I try to restart the
> agent if fails immediately with the same error. The only way I've been able
> to get around the error is to drop the subscription and redo the snapshot.
> Does anyone have any suggestions how to determine the source of this error?
> Thanks for any help...
> Ed
>
>
|||Thanks, I'll try adding that logging parameter. What's the best way to stop
the queue reader agent? In the past, I stopped it from enterprise manager by
right clicking and selecting "Stop Agent", but once I do that it often won't
start again...
Ed
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:2AE57792-B5BE-461C-9C5B-347F261FF985@.microsoft.com...
> Can you do logging for your queue reader and post the error message you
get back here?
> Follow the instructions in this kb for more information on how to enable
it.[vbcol=seagreen]
> http://support.microsoft.com/default...b;EN-US;312292
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
updating[vbcol=seagreen]
but[vbcol=seagreen]
message[vbcol=seagreen]
able[vbcol=seagreen]
snapshot.[vbcol=seagreen]
error?[vbcol=seagreen]
|||For me the only reliable way to stop the queue reader and log reader agents is to stop the SQL Server agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:
> Thanks, I'll try adding that logging parameter. What's the best way to stop
> the queue reader agent? In the past, I stopped it from enterprise manager by
> right clicking and selecting "Stop Agent", but once I do that it often won't
> start again...
> Ed
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:2AE57792-B5BE-461C-9C5B-347F261FF985@.microsoft.com...
> get back here?
> it.
> updating
> but
> message
> able
> snapshot.
> error?
>
>
|||Thanks again. Now I'm just waiting for it to fail...
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:40F318EA-ACF6-4B3A-865D-7B33B00F305E@.microsoft.com...
> For me the only reliable way to stop the queue reader and log reader
agents is to stop the SQL Server agent.[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
stop[vbcol=seagreen]
manager by[vbcol=seagreen]
won't[vbcol=seagreen]
you[vbcol=seagreen]
enable[vbcol=seagreen]
fine,[vbcol=seagreen]
information[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
|||Hi,
The queue reader failed. Below is the output from the log. Any insight you
have would be greatly appreciated.
Ed
Microsoft SQL Server Replication Queue Reader Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: [SLIM].12
Connecting to QueueReader 'SLIM.Billing'
Server: SLIM
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[6/22/2004 12:57:11 PM]SLIM.Billing: select count(*) from
master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (Billing)'
[6/22/2004 12:57:11 PM]SLIM.Billing: select top 1 id, name from
MSqreader_agents
Server: SLIM, Database Billing : ODBC Error:Invalid object name
'MSqreader_agents'.
Unable to connect to Local Distributor
Queue Reader aborting
|||can you check to see if this table exists in your local distribution database?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:
> Hi,
> The queue reader failed. Below is the output from the log. Any insight you
> have would be greatly appreciated.
> Ed
> Microsoft SQL Server Replication Queue Reader Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: [SLIM].12
> Connecting to QueueReader 'SLIM.Billing'
> Server: SLIM
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [6/22/2004 12:57:11 PM]SLIM.Billing: select count(*) from
> master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (Billing)'
> [6/22/2004 12:57:11 PM]SLIM.Billing: select top 1 id, name from
> MSqreader_agents
> Server: SLIM, Database Billing : ODBC Error:Invalid object name
> 'MSqreader_agents'.
> Unable to connect to Local Distributor
> Queue Reader aborting
>
>
|||The table does exist - but I thought it was odd that the statements
contained references to the "Billing" database. That's a database on the
server, but it's not part of replication. Digging into the job under SQL
Server Agent, I saw that the queue reader step was set to run in the
"Billing" database. I'm guessing someone changed that setting
unintentionally. I changed it to run under the "distribution" database, and
the step restarted fine.
Thanks for the tip on logging the output - it would have been impossible to
figure this out without it
Ed
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:810FBB09-2770-4D9C-A07B-ED917F4B9E32@.microsoft.com...
> can you check to see if this table exists in your local distribution
database?[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
you[vbcol=seagreen]
(Billing)'[vbcol=seagreen]
I'm running transactional replication (SQL Server 2000) with queued updating
between two locations (New Jersey & Arizona). Generally it runs fine, but
about every other week the Queue Reader agent fails with an error message
"Queue reader aborting. The step failed". I can't find any information
regarding the source or nature of the error, and if I try to restart the
agent if fails immediately with the same error. The only way I've been able
to get around the error is to drop the subscription and redo the snapshot.
Does anyone have any suggestions how to determine the source of this error?
Thanks for any help...
Ed
Can you do logging for your queue reader and post the error message you get back here?
Follow the instructions in this kb for more information on how to enable it.
http://support.microsoft.com/default...b;EN-US;312292
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:
> Hi,
> I'm running transactional replication (SQL Server 2000) with queued updating
> between two locations (New Jersey & Arizona). Generally it runs fine, but
> about every other week the Queue Reader agent fails with an error message
> "Queue reader aborting. The step failed". I can't find any information
> regarding the source or nature of the error, and if I try to restart the
> agent if fails immediately with the same error. The only way I've been able
> to get around the error is to drop the subscription and redo the snapshot.
> Does anyone have any suggestions how to determine the source of this error?
> Thanks for any help...
> Ed
>
>
|||Thanks, I'll try adding that logging parameter. What's the best way to stop
the queue reader agent? In the past, I stopped it from enterprise manager by
right clicking and selecting "Stop Agent", but once I do that it often won't
start again...
Ed
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:2AE57792-B5BE-461C-9C5B-347F261FF985@.microsoft.com...
> Can you do logging for your queue reader and post the error message you
get back here?
> Follow the instructions in this kb for more information on how to enable
it.[vbcol=seagreen]
> http://support.microsoft.com/default...b;EN-US;312292
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
updating[vbcol=seagreen]
but[vbcol=seagreen]
message[vbcol=seagreen]
able[vbcol=seagreen]
snapshot.[vbcol=seagreen]
error?[vbcol=seagreen]
|||For me the only reliable way to stop the queue reader and log reader agents is to stop the SQL Server agent.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:
> Thanks, I'll try adding that logging parameter. What's the best way to stop
> the queue reader agent? In the past, I stopped it from enterprise manager by
> right clicking and selecting "Stop Agent", but once I do that it often won't
> start again...
> Ed
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:2AE57792-B5BE-461C-9C5B-347F261FF985@.microsoft.com...
> get back here?
> it.
> updating
> but
> message
> able
> snapshot.
> error?
>
>
|||Thanks again. Now I'm just waiting for it to fail...
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:40F318EA-ACF6-4B3A-865D-7B33B00F305E@.microsoft.com...
> For me the only reliable way to stop the queue reader and log reader
agents is to stop the SQL Server agent.[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
stop[vbcol=seagreen]
manager by[vbcol=seagreen]
won't[vbcol=seagreen]
you[vbcol=seagreen]
enable[vbcol=seagreen]
fine,[vbcol=seagreen]
information[vbcol=seagreen]
the[vbcol=seagreen]
been[vbcol=seagreen]
|||Hi,
The queue reader failed. Below is the output from the log. Any insight you
have would be greatly appreciated.
Ed
Microsoft SQL Server Replication Queue Reader Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: [SLIM].12
Connecting to QueueReader 'SLIM.Billing'
Server: SLIM
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[6/22/2004 12:57:11 PM]SLIM.Billing: select count(*) from
master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (Billing)'
[6/22/2004 12:57:11 PM]SLIM.Billing: select top 1 id, name from
MSqreader_agents
Server: SLIM, Database Billing : ODBC Error:Invalid object name
'MSqreader_agents'.
Unable to connect to Local Distributor
Queue Reader aborting
|||can you check to see if this table exists in your local distribution database?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ed.d@.osgbilling.com" wrote:
> Hi,
> The queue reader failed. Below is the output from the log. Any insight you
> have would be greatly appreciated.
> Ed
> Microsoft SQL Server Replication Queue Reader Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: [SLIM].12
> Connecting to QueueReader 'SLIM.Billing'
> Server: SLIM
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [6/22/2004 12:57:11 PM]SLIM.Billing: select count(*) from
> master.dbo.sysprocesses where [program_name] = 'Queue Reader Main (Billing)'
> [6/22/2004 12:57:11 PM]SLIM.Billing: select top 1 id, name from
> MSqreader_agents
> Server: SLIM, Database Billing : ODBC Error:Invalid object name
> 'MSqreader_agents'.
> Unable to connect to Local Distributor
> Queue Reader aborting
>
>
|||The table does exist - but I thought it was odd that the statements
contained references to the "Billing" database. That's a database on the
server, but it's not part of replication. Digging into the job under SQL
Server Agent, I saw that the queue reader step was set to run in the
"Billing" database. I'm guessing someone changed that setting
unintentionally. I changed it to run under the "distribution" database, and
the step restarted fine.
Thanks for the tip on logging the output - it would have been impossible to
figure this out without it
Ed
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:810FBB09-2770-4D9C-A07B-ED917F4B9E32@.microsoft.com...
> can you check to see if this table exists in your local distribution
database?[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "ed.d@.osgbilling.com" wrote:
you[vbcol=seagreen]
(Billing)'[vbcol=seagreen]
Subscribe to:
Posts (Atom)