Hello,
I have 2 questions about setting HW for SQL cluster :
1. What is best practice to set up quorum and MSDTC disk resources? I'm
interested what group of disks they should use : one dedicated to SQL logs
or one dedicated to SQL data? How one or another design can affect
performance?
2. This question I think applies and to standalone SQL servers. It is stated
in some documents that stripe size in storage can affect performance of the
system. Is there any recomendations for this when using MS SQL?
I've been browsing through MS and HP (HP hardware is used)
supports/knowledge bases and found no precise answer to my questions so
maybe here someone can point me to some resources.
Br,
Darius K.
Quorum and MSDTC should reside on their own disks. No sharing with SQL Data
or Logs. MS states to separate Quorum and MSDTC, but that really only
applies if you are running a lot of distributed transactions.
Stripe size and stripe alignment should be covered by your hardware vendor.
If the 'expert' they provide does not understand SQL server's unique
requirements, make them send you an new one. A good hint is if he wants to
make everything a large RAID5 container and slice LUNS off of it as you need
them. That works OK for a file share, it stinks for SQL.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dakl" <dakl@.one.lt> wrote in message
news:%23DRA7GR2EHA.3092@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have 2 questions about setting HW for SQL cluster :
> 1. What is best practice to set up quorum and MSDTC disk resources? I'm
> interested what group of disks they should use : one dedicated to SQL logs
> or one dedicated to SQL data? How one or another design can affect
> performance?
> 2. This question I think applies and to standalone SQL servers. It is
stated
> in some documents that stripe size in storage can affect performance of
the
> system. Is there any recomendations for this when using MS SQL?
> I've been browsing through MS and HP (HP hardware is used)
> supports/knowledge bases and found no precise answer to my questions so
> maybe here someone can point me to some resources.
> Br,
> Darius K.
>
|||Hi
SQL Server does I/O in the database file in 8 extents of 8kb (=64kb). All
our volumes holding databases are formatted at 64kb at OS level. The EMC
hardware striping is out of our control. When I can control it, and we are
using RAID-5, I configure it to be 64kb blocks too.
Some I/O systems perform better at 32Kb blocks, others at 64kb. You need to
test to see what is best.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:#4NigkU2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Quorum and MSDTC should reside on their own disks. No sharing with SQL
Data
> or Logs. MS states to separate Quorum and MSDTC, but that really only
> applies if you are running a lot of distributed transactions.
> Stripe size and stripe alignment should be covered by your hardware
vendor.
> If the 'expert' they provide does not understand SQL server's unique
> requirements, make them send you an new one. A good hint is if he wants
to
> make everything a large RAID5 container and slice LUNS off of it as you
need[vbcol=seagreen]
> them. That works OK for a file share, it stinks for SQL.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Dakl" <dakl@.one.lt> wrote in message
> news:%23DRA7GR2EHA.3092@.TK2MSFTNGP10.phx.gbl...
logs
> stated
> the
>
|||Hi,
I know that Quorum and MSDTC should reside on their own disks. But it is not
affordable to use separate disks. So scenario is this : there are 2 disk
groups (arrays) - one with RAID1 for Logs and second one with RAID10 for
data. To what group is better to add LUNs for Quorum and MSDTC? Or maybe
there is no difference in fact?
Br,
Dakl
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%234NigkU2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Quorum and MSDTC should reside on their own disks. No sharing with SQL
Data
> or Logs. MS states to separate Quorum and MSDTC, but that really only
> applies if you are running a lot of distributed transactions.
> Stripe size and stripe alignment should be covered by your hardware
vendor.
> If the 'expert' they provide does not understand SQL server's unique
> requirements, make them send you an new one. A good hint is if he wants
to
> make everything a large RAID5 container and slice LUNS off of it as you
need[vbcol=seagreen]
> them. That works OK for a file share, it stinks for SQL.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Dakl" <dakl@.one.lt> wrote in message
> news:%23DRA7GR2EHA.3092@.TK2MSFTNGP10.phx.gbl...
logs
> stated
> the
>
|||If you can't do RAID 10 for everything, then I would put them on the RAID 1.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Dakl" <dakl@.one.lt> wrote in message
news:Ou3VNL52EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I know that Quorum and MSDTC should reside on their own disks. But it is
> not
> affordable to use separate disks. So scenario is this : there are 2 disk
> groups (arrays) - one with RAID1 for Logs and second one with RAID10 for
> data. To what group is better to add LUNs for Quorum and MSDTC? Or maybe
> there is no difference in fact?
> Br,
> Dakl
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:%234NigkU2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Data
> vendor.
> to
> need
> logs
>
|||I have a EMC CX500 with 1T striped Raid 10 for Data and separate 100G Raid 5
Luns for Logs, Qorum and MSDTC. This may be a little overkill but seems to
work fine under the load we put it. In testing changes to block size really
didn't appear to affect performance greatly, in production we went with 64K.
Sir Limey
"Rodney R. Fournier [MVP]" wrote:
> If you can't do RAID 10 for everything, then I would put them on the RAID 1.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Dakl" <dakl@.one.lt> wrote in message
> news:Ou3VNL52EHA.2192@.TK2MSFTNGP14.phx.gbl...
>
>
sql
Showing posts with label resources. Show all posts
Showing posts with label resources. Show all posts
Tuesday, March 27, 2012
Friday, March 23, 2012
Feedback on design issue sought
First, a brief backgrounder . . . I work for a small but quickly
growing company with a voracious appetite for data but limited
resources to provide the infrastructure I think they need to supply
this data.
The problem . . . Our primary operations application data, which
represents much of the data employees would like to access for various
reporting needs, is hosted in an off-site SQL Server 2005 database. I
would like to provide local access to this data so that A) people
aren't reporting off of a production operations database and B) we
don't have to worry about VPN's, network issues, etc. My understanding
is that I cannot mirror a 2005 database to a 2000 database. Is this
correct? If so, does anyone have suggestions for an alternative
approach to the problem? I could do some hokey thing with scripts and
scheduled tasks and move the nightly bak file around and restore it
using DTS or something like that, but I was thinking there must be a
more elegant way to address the issue.
Any thoughts? 24 hour latency of the data is acceptable.
Thanks,
RichYou can only mirror from SQL 2005 to SQL 2005 servers.
You might be able to use replication for this depending on what ports are
open on your respective firewalls, or you could also use log shipping and
send the logs and backup via ftp. DTS is another option.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Rich" <rsbaier@.gmail.com> wrote in message
news:1161110521.900413.111550@.k70g2000cwa.googlegroups.com...
> First, a brief backgrounder . . . I work for a small but quickly
> growing company with a voracious appetite for data but limited
> resources to provide the infrastructure I think they need to supply
> this data.
> The problem . . . Our primary operations application data, which
> represents much of the data employees would like to access for various
> reporting needs, is hosted in an off-site SQL Server 2005 database. I
> would like to provide local access to this data so that A) people
> aren't reporting off of a production operations database and B) we
> don't have to worry about VPN's, network issues, etc. My understanding
> is that I cannot mirror a 2005 database to a 2000 database. Is this
> correct? If so, does anyone have suggestions for an alternative
> approach to the problem? I could do some hokey thing with scripts and
> scheduled tasks and move the nightly bak file around and restore it
> using DTS or something like that, but I was thinking there must be a
> more elegant way to address the issue.
> Any thoughts? 24 hour latency of the data is acceptable.
> Thanks,
> Rich
>|||On 17 Oct 2006 11:42:01 -0700, "Rich" <rsbaier@.gmail.com> wrote:
>Any thoughts? 24 hour latency of the data is acceptable.
Go with the .bak file.
J.|||Log Shipping could be a good solution for your situation.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rich" <rsbaier@.gmail.com> wrote in message
news:1161110521.900413.111550@.k70g2000cwa.googlegroups.com...
> First, a brief backgrounder . . . I work for a small but quickly
> growing company with a voracious appetite for data but limited
> resources to provide the infrastructure I think they need to supply
> this data.
> The problem . . . Our primary operations application data, which
> represents much of the data employees would like to access for various
> reporting needs, is hosted in an off-site SQL Server 2005 database. I
> would like to provide local access to this data so that A) people
> aren't reporting off of a production operations database and B) we
> don't have to worry about VPN's, network issues, etc. My understanding
> is that I cannot mirror a 2005 database to a 2000 database. Is this
> correct? If so, does anyone have suggestions for an alternative
> approach to the problem? I could do some hokey thing with scripts and
> scheduled tasks and move the nightly bak file around and restore it
> using DTS or something like that, but I was thinking there must be a
> more elegant way to address the issue.
> Any thoughts? 24 hour latency of the data is acceptable.
> Thanks,
> Rich
>sql
growing company with a voracious appetite for data but limited
resources to provide the infrastructure I think they need to supply
this data.
The problem . . . Our primary operations application data, which
represents much of the data employees would like to access for various
reporting needs, is hosted in an off-site SQL Server 2005 database. I
would like to provide local access to this data so that A) people
aren't reporting off of a production operations database and B) we
don't have to worry about VPN's, network issues, etc. My understanding
is that I cannot mirror a 2005 database to a 2000 database. Is this
correct? If so, does anyone have suggestions for an alternative
approach to the problem? I could do some hokey thing with scripts and
scheduled tasks and move the nightly bak file around and restore it
using DTS or something like that, but I was thinking there must be a
more elegant way to address the issue.
Any thoughts? 24 hour latency of the data is acceptable.
Thanks,
RichYou can only mirror from SQL 2005 to SQL 2005 servers.
You might be able to use replication for this depending on what ports are
open on your respective firewalls, or you could also use log shipping and
send the logs and backup via ftp. DTS is another option.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Rich" <rsbaier@.gmail.com> wrote in message
news:1161110521.900413.111550@.k70g2000cwa.googlegroups.com...
> First, a brief backgrounder . . . I work for a small but quickly
> growing company with a voracious appetite for data but limited
> resources to provide the infrastructure I think they need to supply
> this data.
> The problem . . . Our primary operations application data, which
> represents much of the data employees would like to access for various
> reporting needs, is hosted in an off-site SQL Server 2005 database. I
> would like to provide local access to this data so that A) people
> aren't reporting off of a production operations database and B) we
> don't have to worry about VPN's, network issues, etc. My understanding
> is that I cannot mirror a 2005 database to a 2000 database. Is this
> correct? If so, does anyone have suggestions for an alternative
> approach to the problem? I could do some hokey thing with scripts and
> scheduled tasks and move the nightly bak file around and restore it
> using DTS or something like that, but I was thinking there must be a
> more elegant way to address the issue.
> Any thoughts? 24 hour latency of the data is acceptable.
> Thanks,
> Rich
>|||On 17 Oct 2006 11:42:01 -0700, "Rich" <rsbaier@.gmail.com> wrote:
>Any thoughts? 24 hour latency of the data is acceptable.
Go with the .bak file.
J.|||Log Shipping could be a good solution for your situation.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rich" <rsbaier@.gmail.com> wrote in message
news:1161110521.900413.111550@.k70g2000cwa.googlegroups.com...
> First, a brief backgrounder . . . I work for a small but quickly
> growing company with a voracious appetite for data but limited
> resources to provide the infrastructure I think they need to supply
> this data.
> The problem . . . Our primary operations application data, which
> represents much of the data employees would like to access for various
> reporting needs, is hosted in an off-site SQL Server 2005 database. I
> would like to provide local access to this data so that A) people
> aren't reporting off of a production operations database and B) we
> don't have to worry about VPN's, network issues, etc. My understanding
> is that I cannot mirror a 2005 database to a 2000 database. Is this
> correct? If so, does anyone have suggestions for an alternative
> approach to the problem? I could do some hokey thing with scripts and
> scheduled tasks and move the nightly bak file around and restore it
> using DTS or something like that, but I was thinking there must be a
> more elegant way to address the issue.
> Any thoughts? 24 hour latency of the data is acceptable.
> Thanks,
> Rich
>sql
Subscribe to:
Posts (Atom)