Few questions about setting shared storage for SQL cluster

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

Fetch Question

I have a stored procedure that inserts one row into a table. From a
SELECT statement I would like to call the SP on each row in the
results. Is setting up a cursor and using fetch statements the best
way (or even the only way) to do this?Hi

It is probably the safest way to do it, but without knowing exactly what you
are trying to do it is hard to suggest alternatives.

John

"Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0310010701.261cbff2@.posting.google.c om...
> I have a stored procedure that inserts one row into a table. From a
> SELECT statement I would like to call the SP on each row in the
> results. Is setting up a cursor and using fetch statements the best
> way (or even the only way) to do this?

Feeling Stupid - Multi-Column report layout

Hi all,
New poster to the group. I scanned the group and googled this, but haven't
found a solution yet.
I am having problems setting up a multi-colum layout.
In designer, Click Report Menu / "Report properties".
Select "Layout" tab. Set the "Columns:" properties to 2.
The designer shows me a design space with two columns, but only allows me to
edit the contents in the left most column. Understood.
Problem. I drop a table or list in the designer, and when previewed (in
designer or browser after deploy) I don't get multiple columns. Just a
skinny, very tall report.
BOL has only basic references to multi-column reports.
I feel like I am missing something obvious. Any body have any suggestions?
Thanks,
Bret StatehamUnfortunately, multi-column layout is only supported in the 'page-oriented'
renderers (PDF, TIFF, Print).
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bret Stateham" <bret.no.spam@.netconnex.com> wrote in message
news:uPgnLgBSFHA.508@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> New poster to the group. I scanned the group and googled this, but
> haven't found a solution yet.
> I am having problems setting up a multi-colum layout.
> In designer, Click Report Menu / "Report properties".
> Select "Layout" tab. Set the "Columns:" properties to 2.
> The designer shows me a design space with two columns, but only allows me
> to edit the contents in the left most column. Understood.
> Problem. I drop a table or list in the designer, and when previewed (in
> designer or browser after deploy) I don't get multiple columns. Just a
> skinny, very tall report.
> BOL has only basic references to multi-column reports.
> I feel like I am missing something obvious. Any body have any
> suggestions?
> Thanks,
> Bret Stateham
>|||Brain,
Thanks much. I guess I shouldn't feel totally stupid because that wasn't
mentioned in BOL, or at least I didn't notice it, but I somehow still feel
like I should have thought of that.
Bret
"Brian Welcker" <bwelcker@.online.microsoft.com> wrote in message
news:uv%23oJvBSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Unfortunately, multi-column layout is only supported in the
> 'page-oriented' renderers (PDF, TIFF, Print).
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Bret Stateham" <bret.no.spam@.netconnex.com> wrote in message
> news:uPgnLgBSFHA.508@.TK2MSFTNGP12.phx.gbl...
>> Hi all,
>> New poster to the group. I scanned the group and googled this, but
>> haven't found a solution yet.
>> I am having problems setting up a multi-colum layout.
>> In designer, Click Report Menu / "Report properties".
>> Select "Layout" tab. Set the "Columns:" properties to 2.
>> The designer shows me a design space with two columns, but only allows me
>> to edit the contents in the left most column. Understood.
>> Problem. I drop a table or list in the designer, and when previewed (in
>> designer or browser after deploy) I don't get multiple columns. Just a
>> skinny, very tall report.
>> BOL has only basic references to multi-column reports.
>> I feel like I am missing something obvious. Any body have any
>> suggestions?
>> Thanks,
>> Bret Stateham
>|||The issue is that with HTML, there is no fixed page height so wrapping
multiple columns is sort of weird. We introduced "soft" page breaks late in
the cycle in the HTML renderer but didn't get around to adding the
multi-columns.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bret Stateham" <bret.no.spam@.netconnex.com> wrote in message
news:eiK5P%23CSFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Brain,
> Thanks much. I guess I shouldn't feel totally stupid because that wasn't
> mentioned in BOL, or at least I didn't notice it, but I somehow still feel
> like I should have thought of that.
> Bret
> "Brian Welcker" <bwelcker@.online.microsoft.com> wrote in message
> news:uv%23oJvBSFHA.2932@.TK2MSFTNGP09.phx.gbl...
>> Unfortunately, multi-column layout is only supported in the
>> 'page-oriented' renderers (PDF, TIFF, Print).
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Bret Stateham" <bret.no.spam@.netconnex.com> wrote in message
>> news:uPgnLgBSFHA.508@.TK2MSFTNGP12.phx.gbl...
>> Hi all,
>> New poster to the group. I scanned the group and googled this, but
>> haven't found a solution yet.
>> I am having problems setting up a multi-colum layout.
>> In designer, Click Report Menu / "Report properties".
>> Select "Layout" tab. Set the "Columns:" properties to 2.
>> The designer shows me a design space with two columns, but only allows
>> me to edit the contents in the left most column. Understood.
>> Problem. I drop a table or list in the designer, and when previewed (in
>> designer or browser after deploy) I don't get multiple columns. Just a
>> skinny, very tall report.
>> BOL has only basic references to multi-column reports.
>> I feel like I am missing something obvious. Any body have any
>> suggestions?
>> Thanks,
>> Bret Stateham
>>
>|||Is there anything you have to do to get multiple columns rendered in a PDF
file. I understand not being able to do it in html, but when you convert it
to PDF it still doesn't wrap. Anything special I need to do?
"Brian Welcker" wrote:
> The issue is that with HTML, there is no fixed page height so wrapping
> multiple columns is sort of weird. We introduced "soft" page breaks late in
> the cycle in the HTML renderer but didn't get around to adding the
> multi-columns.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Bret Stateham" <bret.no.spam@.netconnex.com> wrote in message
> news:eiK5P%23CSFHA.2356@.TK2MSFTNGP14.phx.gbl...
> > Brain,
> >
> > Thanks much. I guess I shouldn't feel totally stupid because that wasn't
> > mentioned in BOL, or at least I didn't notice it, but I somehow still feel
> > like I should have thought of that.
> >
> > Bret
> > "Brian Welcker" <bwelcker@.online.microsoft.com> wrote in message
> > news:uv%23oJvBSFHA.2932@.TK2MSFTNGP09.phx.gbl...
> >> Unfortunately, multi-column layout is only supported in the
> >> 'page-oriented' renderers (PDF, TIFF, Print).
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> Microsoft SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Bret Stateham" <bret.no.spam@.netconnex.com> wrote in message
> >> news:uPgnLgBSFHA.508@.TK2MSFTNGP12.phx.gbl...
> >> Hi all,
> >>
> >> New poster to the group. I scanned the group and googled this, but
> >> haven't found a solution yet.
> >>
> >> I am having problems setting up a multi-colum layout.
> >>
> >> In designer, Click Report Menu / "Report properties".
> >> Select "Layout" tab. Set the "Columns:" properties to 2.
> >>
> >> The designer shows me a design space with two columns, but only allows
> >> me to edit the contents in the left most column. Understood.
> >>
> >> Problem. I drop a table or list in the designer, and when previewed (in
> >> designer or browser after deploy) I don't get multiple columns. Just a
> >> skinny, very tall report.
> >>
> >> BOL has only basic references to multi-column reports.
> >>
> >> I feel like I am missing something obvious. Any body have any
> >> suggestions?
> >>
> >> Thanks,
> >>
> >> Bret Stateham
> >>
> >>
> >>
> >
> >
>
>

feature?

I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
AndreAndre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restor
ed
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok an
d
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I go
t
> at the end told me that DB1 had been restored...when in fact it was DB2 th
at
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

feature?

I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
Andre
Andre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok and
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I got
> at the end told me that DB1 had been restored...when in fact it was DB2 that
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

feature?

I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
AndreAndre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok and
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I got
> at the end told me that DB1 had been restored...when in fact it was DB2 that
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

FC or ATA for LOG FILES?

I'm setting up a new DB and separating the log files to storage separate
from the data. The data will be on FC storage. Will the log files perform
optimally on FC or ATA?
Thanks in advance.
SBGFC as in FC-ATA where you're basically using cheap hardware?
I question your decision to put data and log files on such equipment in the
first place. However, as to the log files themselves, I suspect you could
put log files on an MFM drive, or an old 20MB removable platter and be fine
99.99% of the time. There just isn't that much I/O to the logs.
"SBG" <sbg@.thomasville.org> wrote in message
news:O1j$TY%23HIHA.4196@.TK2MSFTNGP04.phx.gbl...
> I'm setting up a new DB and separating the log files to storage separate
> from the data. The data will be on FC storage. Will the log files
> perform
> optimally on FC or ATA?
> Thanks in advance.
> SBG
>|||Oh, and .01% of the time is about 1 hour a year.
"Jay" <nospan@.nospam.org> wrote in message
news:eA$a3i%23HIHA.5544@.TK2MSFTNGP02.phx.gbl...
> FC as in FC-ATA where you're basically using cheap hardware?
> I question your decision to put data and log files on such equipment in
> the first place. However, as to the log files themselves, I suspect you
> could put log files on an MFM drive, or an old 20MB removable platter and
> be fine 99.99% of the time. There just isn't that much I/O to the logs.
> "SBG" <sbg@.thomasville.org> wrote in message
> news:O1j$TY%23HIHA.4196@.TK2MSFTNGP04.phx.gbl...
>> I'm setting up a new DB and separating the log files to storage separate
>> from the data. The data will be on FC storage. Will the log files
>> perform
>> optimally on FC or ATA?
>> Thanks in advance.
>> SBG
>>
>

fault with configure the select stament

Goodday all

I,m having a problem with something that worked well just recently. maybe I changed a setting by mistake.

I have a Database grid view on my page. When I configure my data sourse and get to "configure the select statment " on the wizard and press advanced to open the wizard with two check boxes " generate INSERT, UPDATE etc and the other check box "Use optimistic concurrency " this input box of the wizard is greyed out (non responsive)

Does anyone know how to rectify this?

Regards

Rob

Insufficient information to be sure. Did you indicate to generate insert, update, or delete statements?

The optimistic concurrency does not mean anything unless you created at least one of those statements so it is likely disabled.

Failure setting security rights on user account SQLServer2005BrowserUser${computerName}

I'm trying to install SQL Server 2005 Express on a Windows 2000 server, but I'm getting the following error message:

"Failure setting security rights on user account SQLServer2005BrowserUser${computerName}"

Can anyone help me please?

Help!! Need to get the sql server installed so I can demo to my client!