Having Problems with a Simple Query

Hey guys,
I am trying to run a query that will return all rows where a column is
completely empty of data.
I try this query:
select * from patients_visitInsurers
where CompanyID is NULL
But it does not return anything for me. If I try the reverse
select * from patients_visitInsurers
where CompanyID is not NULL
It returns rows that contain CompanyId values and rows that are blank.
What am I missing here? Thanks!
Do you have those blank values for CompanyID being NULL or just blank
string? Assuming CompanyID is character format, you can check:
SELECT <columns>
FROM patients_visitInsurers
WHERE COALESCE(CompanyID, '') = ''
Or
SELECT <columns>
FROM patients_visitInsurers
WHERE CompanyID IS NULL
OR CompanyID = ''
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Is CompanyID a string? "Empty of data" and "blank" are two different
things, in my opinion. Empty of data means NULL (and you have the correct
syntax, of that were the case). Blank means an empty string, e.g. ''. In
which case,
WHERE CompanyID = ''
You may also be safer to trim the data first, since it could contain a
space...
WHERE RTRIM(CompanyID) = ''
However, you should not insert a blank string when you really meant NULL.
They are different in implementation, and they are different on a conceptual
level, as well.
<alvinstraight38@.hotmail.com> wrote in message
news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
> Hey guys,
> I am trying to run a query that will return all rows where a column is
> completely empty of data.
> I try this query:
> select * from patients_visitInsurers
> where CompanyID is NULL
> But it does not return anything for me. If I try the reverse
> select * from patients_visitInsurers
> where CompanyID is not NULL
> It returns rows that contain CompanyId values and rows that are blank.
> What am I missing here? Thanks!
|||Plamen Ratchev was thinking very hard :
> Do you have those blank values for CompanyID being NULL or just blank string?
> Assuming CompanyID is character format, you can check:
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE COALESCE(CompanyID, '') = ''
> Or
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE CompanyID IS NULL
> OR CompanyID = ''
>
and if that doesn't get it, then
OR LTRIM(RTRIM(CompanyID)) = ''
HTH,
Brad.
|||On Apr 4, 11:07Xam, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Is CompanyID a string? X"Empty of data" and "blank" are two different
> things, in my opinion. XEmpty of data means NULL (and you have the correct
> syntax, of that were the case). XBlank means an empty string, e.g. ''. XIn
> which case,
> WHERE CompanyID = ''
> You may also be safer to trim the data first, since it could contain a
> space...
> WHERE RTRIM(CompanyID) = ''
> However, you should not insert a blank string when you really meant NULL.
> They are different in implementation, and they are different on a conceptual
> level, as well.
> <alvinstraigh...@.hotmail.com> wrote in message
> news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows, but I want to exclude rows
that do contain values in this field.
Thanks!
|||>>
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows,[vbcol=seagreen]
Right. So you need to have two concepts very clear:
NULL is the absence of any data whatsoever.
'' is an empty string. It is data, even if it is zero-length.
There is little point in setting this column to NOT NULL if you can stick an
empty string in there. In this specific situation, it is apparent that the
two concepts are interchangeable...

Having Problems with a Simple Query

Hey guys,
I am trying to run a query that will return all rows where a column is
completely empty of data.
I try this query:
select * from patients_visitInsurers
where CompanyID is NULL
But it does not return anything for me. If I try the reverse
select * from patients_visitInsurers
where CompanyID is not NULL
It returns rows that contain CompanyId values and rows that are blank.
What am I missing here? Thanks!Do you have those blank values for CompanyID being NULL or just blank
string? Assuming CompanyID is character format, you can check:
SELECT <columns>
FROM patients_visitInsurers
WHERE COALESCE(CompanyID, '') = ''
Or
SELECT <columns>
FROM patients_visitInsurers
WHERE CompanyID IS NULL
OR CompanyID = ''
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Is CompanyID a string? "Empty of data" and "blank" are two different
things, in my opinion. Empty of data means NULL (and you have the correct
syntax, of that were the case). Blank means an empty string, e.g. ''. In
which case,
WHERE CompanyID = ''
You may also be safer to trim the data first, since it could contain a
space...
WHERE RTRIM(CompanyID) = ''
However, you should not insert a blank string when you really meant NULL.
They are different in implementation, and they are different on a conceptual
level, as well.
<alvinstraight38@.hotmail.com> wrote in message
news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
> Hey guys,
> I am trying to run a query that will return all rows where a column is
> completely empty of data.
> I try this query:
> select * from patients_visitInsurers
> where CompanyID is NULL
> But it does not return anything for me. If I try the reverse
> select * from patients_visitInsurers
> where CompanyID is not NULL
> It returns rows that contain CompanyId values and rows that are blank.
> What am I missing here? Thanks!|||Plamen Ratchev was thinking very hard :
> Do you have those blank values for CompanyID being NULL or just blank string?
> Assuming CompanyID is character format, you can check:
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE COALESCE(CompanyID, '') = ''
> Or
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE CompanyID IS NULL
> OR CompanyID = ''
>
and if that doesn't get it, then
OR LTRIM(RTRIM(CompanyID)) = ''
HTH,
Brad.|||On Apr 4, 11:07=A0am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Is CompanyID a string? =A0"Empty of data" and "blank" are two different
> things, in my opinion. =A0Empty of data means NULL (and you have the corre=ct
> syntax, of that were the case). =A0Blank means an empty string, e.g. ''. ==A0In
> which case,
> WHERE CompanyID =3D ''
> You may also be safer to trim the data first, since it could contain a
> space...
> WHERE RTRIM(CompanyID) =3D ''
> However, you should not insert a blank string when you really meant NULL.
> They are different in implementation, and they are different on a conceptu=al
> level, as well.
> <alvinstraigh...@.hotmail.com> wrote in message
> news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
>
> > Hey guys,
> > I am trying to run a query that will return all rows where a column is
> > completely empty of data.
> > I try this query:
> > select * from patients_visitInsurers
> > where CompanyID is =A0NULL
> > But it does not return anything for me. =A0If I try the reverse
> > select * from patients_visitInsurers
> > where CompanyID is =A0not NULL
> > It returns rows that contain CompanyId values and rows that are blank.
> > What am I missing here? =A0 Thanks!- Hide quoted text -
> - Show quoted text -
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows, but I want to exclude rows
that do contain values in this field.
Thanks!|||>>
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows,
Right. So you need to have two concepts very clear:
NULL is the absence of any data whatsoever.
'' is an empty string. It is data, even if it is zero-length.
There is little point in setting this column to NOT NULL if you can stick an
empty string in there. In this specific situation, it is apparent that the
two concepts are interchangeable...sql

Having problem with sql create

The following is a create that I was trying to run in SQL/2000 SQL
Analyser:

CREATE TABLE tblxyz as (Select * from tblPlayers);

tblPlayers has 2 rows in it.

I continue to get the error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

I have tried it with and without the () and it works on Oracle but not
SQL/2K. Don't know why.

This has all developed because of another problem.

First I have select with a complex join all records with a certain
employee ID.

Then I have to select the top 20 (by date) records from the first
select.

From the second select results I have to select the top 10 by a
numeric field.

The only way I thought of doing it was to create temporary tables (see
original question).SELECT * INTO tblxyz FROM tblPlayers

if you want an empty table with only the schema of tblPlayers use a WHERE
1=0

"Jim R" <JimReid@.comcast.net> wrote in message
news:7eb6df4b.0407150949.15a9468c@.posting.google.c om...
> The following is a create that I was trying to run in SQL/2000 SQL
> Analyser:
> CREATE TABLE tblxyz as (Select * from tblPlayers);
> tblPlayers has 2 rows in it.
> I continue to get the error
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'AS'.
> I have tried it with and without the () and it works on Oracle but not
> SQL/2K. Don't know why.
> This has all developed because of another problem.
> First I have select with a complex join all records with a certain
> employee ID.
> Then I have to select the top 20 (by date) records from the first
> select.
> From the second select results I have to select the top 10 by a
> numeric field.
> The only way I thought of doing it was to create temporary tables (see
> original question).|||> CREATE TABLE tblxyz as (Select * from tblPlayers);

The SQL Server equivalent is

SELECT * INTO tblxyz FROM tblPlayers ;

> The only way I thought of doing it was to create temporary tables (see
> original question).

Without more of a spec (DDL for the tables, some INSERT statements of sample
data) it's impossible for us to know if that's really the best way to
achieve what you want.

--
David Portas
SQL Server MVP
--|||Thank you. That works great. Now I create tbltemp and from tbltemp I
create a tbltempa and then drop tbltemp. All in a stored procedure.
Then after I'm done with my selection of the recordset I'll drop the
tbltempa.

Thanks again.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||SQL is a declarative language. To get the most out of SQL, start by writing
a query that will produce the end result you require rather than thinking of
procedural solutions like [Query 1] -> [Temp Table] -> [Query 2] -> etc.
From your description I doubt that temp tables are the most concise,
efficient or maintainable solution to your problem.

--
David Portas
SQL Server MVP
--|||David,

You are right. My problem was that I didn't know how to create a select
within a select. What I ended up with was just that and it is all in a
single stored procedure with no temporary tables. Works really well.

My biggest problem was all new things I was trying to do. I have always
done simple select statements before both with Views and Stored
Procedures.

Then I discovered that I needed to feed parameters to the first select
which knocked out Views. Then I needed only the top 20 by date and from
the top 20 the lowest 10(numerically) of those top 20.

It got very confusing real fast when I had a stored procedure that
select via param for the top 20. It would pick the top 20 first and
then the parms. Which wasn't the sequence I wanted.

Since I was only running on SQL/2k I ended up with the following stored
procedure:
CREATE PROCEDURE [SelectUSGA1]]
@.PlayerID integer
AS
SELECT TOP 10 WITH TIES dbo.tblScores.[Date], dbo.tblScores.Player,
dbo.tblScores.HandiDiff, dbo.tblScores.Tees, dbo.tblScores.H1,
dbo.tblScores.H2, dbo.tblScores.H3, dbo.tblScores.Course,
dbo.tblCourse.CourseID, dbo.tblCourse.CourseName, dbo.tblCourse.Hole1,
dbo.tblCourse.Hole2, dbo.tblCourse.Hole3, dbo.tblCourse.Hole4,
dbo.tblCourse.Hole5, dbo.tblCourse.Hole6, dbo.tblCourse.Hole7,
dbo.tblCourse.Hole8, dbo.tblCourse.Hole9, dbo.tblCourse.Hole10,
dbo.tblCourse.Hole11, dbo.tblCourse.Hole12, dbo.tblCourse.Hole13,
dbo.tblCourse.Hole14, dbo.tblCourse.Hole15, dbo.tblCourse.Hole16,
dbo.tblCourse.Hole17, dbo.tblCourse.Hole18, dbo.tblCourse.T,
dbo.tblCourse.Rating, dbo.tblCourse.Slope, dbo.tblPlayers.Player_ID,
dbo.tblPlayers.PlayerLastName, dbo.tblPlayers.PlayerFirstName,
dbo.tblPlayers.PlayerNickName, dbo.tblScores.H4, dbo.tblScores.H5,
dbo.tblScores.H6, dbo.tblScores.H7, dbo.tblScores.H8, dbo.tblScores.H9,
dbo.tblScores.H10, dbo.tblScores.H11, dbo.tblScores.H12,
dbo.tblScores.H13, dbo.tblScores.H14, dbo.tblScores.H15,
dbo.tblScores.H16, dbo.tblScores.H17, dbo.tblScores.H18,
dbo.tblScores.B9, dbo.tblScores.T18, dbo.tblScores.P1, dbo.tblScores.F9,
dbo.tblScores.P2, dbo.tblScores.P3, dbo.tblScores.P4, dbo.tblScores.P5,
dbo.tblScores.P6,
dbo.tblScores.P7, dbo.tblScores.P8, dbo.tblScores.P9, dbo.tblScores.P10,
dbo.tblScores.P11, dbo.tblScores.P12, dbo.tblScores.P13,
dbo.tblScores.P15, dbo.tblScores.P14,
dbo.tblScores.P16, dbo.tblScores.P17, dbo.tblScores.P18,
dbo.tblScores.FW1, dbo.tblScores.FW2, dbo.tblScores.FW3,
dbo.tblScores.FW4, dbo.tblScores.FW5, dbo.tblScores.FW6,
dbo.tblScores.FW7, dbo.tblScores.FW8, dbo.tblScores.FW9,
dbo.tblScores.FW10, dbo.tblScores.FW11, dbo.tblScores.FW12,
dbo.tblScores.FW13, dbo.tblScores.FW14, dbo.tblScores.FW15,
dbo.tblScores.FW16, dbo.tblScores.FW17,
dbo.tblScores.FW18, dbo.tblScores.FWT, dbo.tblScores.TotalPutts,
dbo.tblScores.ActualDiff, dbo.tblScores.TotPars, dbo.tblScores.TotBirds,
dbo.tblScores.TotEagles, dbo.tblScores.TotBogys,
dbo.tblScores.Tot2Bogys, dbo.tblScores.AvgPutts
FROM dbo.tblScores
LEFT OUTER JOIN
dbo.tblCourse ON dbo.tblScores.Course = dbo.tblCourse.CourseID
LEFT OUTER JOIN
dbo.tblPlayers ON dbo.tblScores.Player =
dbo.tblPlayers.Player_ID
WHERE tblPlayers.Player_ID=@.PlayerID
order by date, HandiDiff;
GO

Works really well now.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Looks like you just need date in DESCending order (the default is
ascending).

SELECT TOP 10 WITH TIES
...
ORDER BY date DESC, handidiff

Note that you may or may not want the WITH TIES option. Without it, TOP may
give non-deterministic results - if there are values where the sort columns
are equal (tied) then only some subset of the rows may be shown if the
result would otherwise give more than 10 rows.

--
David Portas
SQL Server MVP
--

> I posted another message because with your solution it gave me 10 rows by
> date which isn't exactly what I was looking for.

> I have to selection for a row of golf scores, the most current 20 rows by
> date for a particular player.

> Then I have to select the lowest 10 scores from that subset.

> Then I use those 10 to calculate the USGA Handicap Index.

> What I'm getting is the newest 10 by date which may not be the best scores|||Still have a problem with SQL. I have tried the following:

Select top 10 with ties, date, score
From tblScores
Where playerID = 3
Order by date desc, score;

The problem is that it selects the top 10 scores by date when in fact I
want the most current top 20 scores and from the most current top 20 I
want to pick the top 10 scores by lowest score.

So if I had 100 rows I would get the most current 20. From that group I
would select by score the lowest 10.

I'm not sure how TIES works but it's not doing what I wanted.

Any help would be appreciated.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Try this:

SELECT TOP 10 WITH TIES *
FROM
(SELECT TOP 20 WITH TIES
S.[date], S.player, S.handidiff, S.tees, S.h1, S.h2, S.h3, S.course,
C.courseid, C.coursename, C.hole1, C.hole2, C.hole3, C.hole4, C.hole5,
C.hole6, C.hole7, C.hole8, C.hole9, C.hole10, C.hole11, C.hole12,
C.hole13, C.hole14, C.hole15, C.hole16, C.hole17, C.hole18, C.t,
C.rating, C.slope, P.player_id, P.playerlastname, P.playerfirstname,
P.playernickname, S.h4, S.h5, S.h6, S.h7, S.h8, S.h9, S.h10, S.h11,
S.h12, S.h13, S.h14, S.h15, S.h16, S.h17, S.h18, S.b9, S.t18,
S.p1, S.f9, S.p2, S.p3, S.p4, S.p5, S.p6, S.p7, S.p8, S.p9, S.p10,
S.p11, S.p12, S.p13, S.p15, S.p14, S.p16, S.p17, S.p18, S.fw1, S.fw2,
S.fw3, S.fw4, S.fw5, S.fw6, S.fw7, S.fw8, S.fw9, S.fw10, S.fw11,
S.fw12, S.fw13, S.fw14, S.fw15, S.fw16, S.fw17, S.fw18, S.fwt,
S.totalputts, S.actualdiff, S.totpars, S.totbirds, S.toteagles,
S.totbogys, S.tot2bogys, S.avgputts
FROM dbo.tblScores AS S
LEFT OUTER JOIN
dbo.tblCourse AS C
ON S.course = C.courseid
LEFT OUTER JOIN
dbo.tblPlayers AS P
ON S.player = P.player_id
WHERE P.player_id = @.PlayerID
ORDER BY S.[date] DESC, S.handidiff) AS T
ORDER BY handidiff

--
David Portas
SQL Server MVP
--sql

Having Problem with Report Parameters

i am trying to generate a report based on 3 parameters

age, location, ethnciity

every thing works fine in data and layout tab,

when i run the preview tab, it give me the option to input paramaters and then when i hit veiw report, it shows processing report.... (indefinite) time.

i tried executing the query in data tab, it takes less than a sec.

any ideas?

am i doing somethign wrong in parameters?

Raja,

I'm going on the assumption you are using SQL as your datasource, If so, you can start a SQL profiler on the database you're using.

In the profiler, you can watch the query statement from SQL reporting service being executed, It will reveal exactly what is being passed in your parameter. You can copy this statement and execute to a new query to help you debug your issue.

I hope that this helps.

Ham

|||

its some weard problem.

its hitting the dbase and i double checked it in the profiler.

i dont knows what happening. i deleted the data cache files in the folder and tried rerun again..

nothing works.

data tab works 1000 times fine.

|||

What happens when you take the statement from the profiler and run it in a new query window. Does that execution of the statement still takes a long time?

Ham

|||

Hi,

I once had sort of the same problem. I didn't set the datatype of the parameters correctly which caused problems when viewing the report.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Yes,

I checked it with the QAnalyzer, Profiler, ReportingServices Data Tab.

everything takes less than a sec to execute the 15000 records.

This is one thing i noticed now after some weird research.

when i hit the preview tab and input parameters its not showing anything (i mean it shows report processinng..... )

but now when i deploy it to the server and try to view it. ITS WORKING

Where the h... is the problem. ?

|||any one have an email id of any of the webcast instructors who have instructed for RS before...|||any help?

Having problem to use the timestamp of the last successful job run

Hi,
I am using the MS sql DTS package to import some log data
from openenterprise server to the MS Sql server.
I am trying use the timestamps values of the MS SQL job
which ran successfully last time.
I was able to create an DTS pachage which imports the last
24 hours log data from a table of a database engine
name "polyhedra" which is used in a SCADA system to a MS
SQL sever table.
I made a schedule which imports the log data for the last
24 hours. My query statement look like as follows:
Select ID, name, timestamp, realvalue, delete
from realsamples
where timestamp > now()-hours(24)
But instead of hours(24), I need to use the timestamp when
my job last ran successfully. Because incase the any of
the server fails to run for more than 24 hours I will not
get the data older than last 24 hours.
Please help me to solve the problem.
Thank you in advance.You can get information about jobs (including the times when the job started
and if it completed succesfully) using the sp_help_jobhistory stored
procedure. See Books Online for the full syntax and details.
Jacco Schalkwijk
SQL Server MVP
"M Sikder" <anonymous@.discussions.microsoft.com> wrote in message
news:01a101c3bc3d$ed71f000$a301280a@.phx.gbl...
> Hi,
> I am using the MS sql DTS package to import some log data
> from openenterprise server to the MS Sql server.
> I am trying use the timestamps values of the MS SQL job
> which ran successfully last time.
> I was able to create an DTS pachage which imports the last
> 24 hours log data from a table of a database engine
> name "polyhedra" which is used in a SCADA system to a MS
> SQL sever table.
> I made a schedule which imports the log data for the last
> 24 hours. My query statement look like as follows:
> Select ID, name, timestamp, realvalue, delete
> from realsamples
> where timestamp > now()-hours(24)
> But instead of hours(24), I need to use the timestamp when
> my job last ran successfully. Because incase the any of
> the server fails to run for more than 24 hours I will not
> get the data older than last 24 hours.
> Please help me to solve the problem.
> Thank you in advance.

Having Clause wont run on Linked Server

I am trying to find all affiliates that have more commissions from this
week to the prior week. The problem is in the having part where
"aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like
trying to use "aff.affiliateid" to refer to the parent table.

The error I get is "Invalid column name 'Col1010'.", but ONLY on the
sql01 box; it runs correctly on the box where the affiliate database
is, sql02.

If I get rid of the alias of "aff" and use the full path, I get the
error, "The number name 'sql02.affiliates.dbo.affiliates_sum' contains
more than the maximum number of prefixes. The maximum is 3."

Is this just a SQL bug, or is there a fix?

declare @.date datetime ; set @.date = '2/3/05'

select affiliateid
from sql02.affiliates.dbo.affiliates_sum aff
where day >= @.date - 7 and day < @.date
group by affiliateid
having sum(lead_commissions) > (
select sum(lead_commissions + sales_commissions) as total
from sql02.affiliates.dbo.affiliates_sum aff2
where day >= @.date - 14 and day < @.date - 7
and aff2.affiliateid = aff.affiliateid
group by affiliateid
)<scottelkin@.gmail.com> wrote in message
news:1108711888.673744.271790@.c13g2000cwb.googlegr oups.com...
>I am trying to find all affiliates that have more commissions from this
> week to the prior week. The problem is in the having part where
> "aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like
> trying to use "aff.affiliateid" to refer to the parent table.
> The error I get is "Invalid column name 'Col1010'.", but ONLY on the
> sql01 box; it runs correctly on the box where the affiliate database
> is, sql02.
> If I get rid of the alias of "aff" and use the full path, I get the
> error, "The number name 'sql02.affiliates.dbo.affiliates_sum' contains
> more than the maximum number of prefixes. The maximum is 3."
> Is this just a SQL bug, or is there a fix?
>
> declare @.date datetime ; set @.date = '2/3/05'
> select affiliateid
> from sql02.affiliates.dbo.affiliates_sum aff
> where day >= @.date - 7 and day < @.date
> group by affiliateid
> having sum(lead_commissions) > (
> select sum(lead_commissions + sales_commissions) as total
> from sql02.affiliates.dbo.affiliates_sum aff2
> where day >= @.date - 14 and day < @.date - 7
> and aff2.affiliateid = aff.affiliateid
> group by affiliateid
> )

This KB article might be relevant:

http://support.microsoft.com/defaul...kb;en-us;825019

If that doesn't help, or if you need a workaround, you could try using
OPENQUERY() to pass the query through to sql02, although since you need to
include a parameter value, you would need dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

Simon|||(scottelkin@.gmail.com) writes:
> I am trying to find all affiliates that have more commissions from this
> week to the prior week. The problem is in the having part where
> "aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like
> trying to use "aff.affiliateid" to refer to the parent table.
> The error I get is "Invalid column name 'Col1010'.", but ONLY on the
> sql01 box; it runs correctly on the box where the affiliate database
> is, sql02.
> If I get rid of the alias of "aff" and use the full path, I get the
> error, "The number name 'sql02.affiliates.dbo.affiliates_sum' contains
> more than the maximum number of prefixes. The maximum is 3."
> Is this just a SQL bug, or is there a fix?

Judging from the error message, this is a bug in either SQL Server
or SQLOLEDB.

I created a table from the query and then ran the query on my box
with a loopback linked server, and the query completed succesfully.
I am running the beta of SP4, so it could be because the issue have
been fixed. However, the problem may be related to a specific query
plan, so it is difficult to tell for sure.

Info about SP4 Beta is here: http://support.microsoft.com/kb/290211.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

HAVING Clause has no effect

I have this stored procedure. I want to run a few simple SQL functions against my tables. In particular I want to take a subset of records (One or Two years worth) and calculate AVG, VAR and STDEV.

It does not work the way I thought it would. I end up with the whole input table in #tempor1 which is about 6 years worth of records.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF

GO
ALTER PROCEDURE [dbo].[findAve1YearDailyClose_MSFT]
AS
BEGIN
SET NOCOUNT ON;
SELECT adjClosed, volume INTO #tempor1 FROM dbo.dailyCl_MSFT
GROUP BY dateTimed, adjClosed, volume
HAVING (dateTimed > DATEADD (year, -1, MAX (dateTimed)))

SELECT AVG (adjClosed) AS "AVGAdjClose1Year",
VAR (adjClosed) AS "VARAdjClose1Year", AVG (volume) AS "AVGVolume1Year",
STDEV (volume) AS "STDEVVolume1Year", COUNT (*) AS "total"
FROM #tempor1
END

Thus if I change the number of years I subtract from the latest date from 1 to 2 I end up with the same result. What is the problem?

Thanks.

What about using:

SELECT adjClosed, volume INTO #tempor1
FROM dbo.dailyCl_MSFT
WHERE dateTimed > (SELECT DATEADD(year, -1, MAX (dateTimed)) FROM dbo.dailyCl)
GROUP BY dateTimed, adjClosed, volume

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

What about using:

SELECT adjClosed, volume INTO #tempor1
FROM dbo.dailyCl_MSFT
WHERE dateTimed > (SELECT DATEADD(year, -1, MAX (dateTimed)) FROM dbo.dailyCl)
GROUP BY dateTimed, adjClosed, volume

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

It sure worked! Many thanks for a lesson. Marked as answered!

Thanks.

sql

have sql file and want to run it on an ftp connection

i have a db set up that i access using an ftp connection, and an sql script
file that i wish to run, how do i go about doing so?

tiastrawberry (1@.2.com) writes:
> i have a db set up that i access using an ftp connection, and an sql
> script file that i wish to run, how do i go about doing so?

You have an SQL Server database that you access through FTP? Maybe I'm
dense, but I don't understand how you could do that?

Of course, if your FTP client permits you to run remote scripts, you
could upload your script, and then run it with OSQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

Why can you not get access to connect to the remote server, possibly through
a VPN?

John

"strawberry" <1@.2.com> wrote in message
news:exJ8f.19119$6i4.3749@.newsfe7-gui.ntli.net...
>i have a db set up that i access using an ftp connection, and an sql script
>file that i wish to run, how do i go about doing so?
> tia|||this is half the problem, I'm not sure how or where to execute the file I've
tried all sorts but suspect i don't have the correct privileges ( its a
university server)

thanks for your reply.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:436396ac$0$361$da0feed9@.news.zen.co.uk...
> Hi
> Why can you not get access to connect to the remote server, possibly
> through a VPN?
> John
> "strawberry" <1@.2.com> wrote in message
> news:exJ8f.19119$6i4.3749@.newsfe7-gui.ntli.net...
>>i have a db set up that i access using an ftp connection, and an sql
>>script file that i wish to run, how do i go about doing so?
>>
>> tia
>>|||its hosted on a linux server which has ftp access to it. what is osql?, and
where is the best place to upload it too ?

thanks for your reply

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96FE92704E4B5Yazorman@.127.0.0.1...
> strawberry (1@.2.com) writes:
>> i have a db set up that i access using an ftp connection, and an sql
>> script file that i wish to run, how do i go about doing so?
> You have an SQL Server database that you access through FTP? Maybe I'm
> dense, but I don't understand how you could do that?
> Of course, if your FTP client permits you to run remote scripts, you
> could upload your script, and then run it with OSQL.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||strawberry (1@.2.com) writes:
> its hosted on a linux server which has ftp access to it. what is osql?,
> and where is the best place to upload it too ?

The database is on a Linux server? OK, then you are in the wrong newsgroup.
This newsgroup is about MS SQL Server, which runs only on Windows. (And
OSQL is a command-line tool to access MS SQL Server. On Windows.)

I recommend that you find out which DB engine you are using, and then
find the a forum that engine. Or even better ask the staff at the
university where the DB engine is.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||ahh, oppsy! . hadn't thought of that!

many thanks.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96FF6A8A99F2Yazorman@.127.0.0.1...
> strawberry (1@.2.com) writes:
>> its hosted on a linux server which has ftp access to it. what is osql?,
>> and where is the best place to upload it too ?
> The database is on a Linux server? OK, then you are in the wrong
> newsgroup.
> This newsgroup is about MS SQL Server, which runs only on Windows. (And
> OSQL is a command-line tool to access MS SQL Server. On Windows.)
> I recommend that you find out which DB engine you are using, and then
> find the a forum that engine. Or even better ask the staff at the
> university where the DB engine is.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Have a query run at specific times

Please be patient I am a noobie
We have a Query created in Query Analyzer that we would like to run nightly.
How do we get this query to run on a schedule and update tables?
Hi
Wrong newsgroup. This is the group for data replication.
Look in BOL for "jobs".
If you want to use Enterprise Manager, you can create and schedule a job, it
is under SQL Server Agent.
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/
"Mueller" <Mueller@.discussions.microsoft.com> wrote in message
news:BA6E0722-D733-4708-B7D6-A972125F3AB9@.microsoft.com...
> Please be patient I am a noobie
> We have a Query created in Query Analyzer that we would like to run
nightly.
> How do we get this query to run on a schedule and update tables?
|||What you need to create is a 'Job'. This is available under Management, SQL
Server Agent, Jobs and there are plenty of details n Books-On-Line (BOL).
Basically right click the jobs node, select new job and the rest is pretty
intuitive as long as you ensure the SQL Server Agent is started and
scheduled to restart on bootup. Please post back after giving it a go.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Have a bet .... curious on input :)

So I have a person who is adamant in tell me that SQL Server does not run on windows XP.

Now, I have already done all the research on this (i.e. sql server 2000 product page / requirements) and know the answer, but they insist on asking the question, so here it is .....

'Will SQL Server run on Windows XP'

A simple YES or NO will suffice; however, if you want to explain the answer (if it requires one ;) ), please feel free.

YES and NO . Depends on what version of SQL Server you want to install. MSDE/SQL Server Express work. Also is the Developer Edition of SQL Server. But with the Enterprise Edition I think you're out of luck... it won't install.|||

Mike's right... it's a little trickier than Yes/No. :)

Here is the SQL Server 2005 Enterprise Edition page:

http://www.microsoft.com/sql/editions/enterprise/sysreqs.mspx

You can see that XP is not supported for the Enterprise SKU, but it is supported for the Standard SKU (as with Workgroup and Express SKU's). The standard SKU page is found here if you need proof for your bet. :)

http://www.microsoft.com/sql/editions/standard/sysreqs.mspx

Thanks,
Sam Lester (MSFT)

|||

i'm well aware of this as its covered in the product information page.

However, this person claims that 'SQL Server' automatically states you are talking about the Enterprise / Standard Editions wihch do not run on XP. My point is that 'SQL Server' without stating which edition is vague and the statement SQL Server WILL NOT run on XP is invalid because SQL Server is not a product but a product family. If one is talking about SQL Server without specifying, it would be talking about hte family of products.

If the question were ' Does SQL Server - Enterprise Edition run on XP', I would agree the answer is no. I would also agree 'Does SQL Server Developer run on XP, I would agree that answer is yes. However, if the ONLY question posed is 'Does SQL Server on XP', I would argue the answer is yes (with the above clarification attached to that. The statement 'SQL Server 2000 does NOT run on XP' would be false for the reason that SQL Server (without specificying which edition) is a general statement about the product family and that is incorrect as parts of the family DO run on XP.

I apologize for the splitting of hairs, but I was just curious on other people's take on this one.

Hardware Sizingtool

I'am saerching for an Server Hardware Sizingtool. I'de like configure a
Server to run an MS SQL Server for up to 250 Users
Hello Zip,
Try http://h21007.www2.hp.com/dspp/tech/...,6014,00.html. You can also search on 'sizer' at www.hp.com.
-Kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)

> I'am saerching for an Server Hardware Sizingtool. I'de like configure
> a Server to run an MS SQL Server for up to 250 Users
>
|||Thx Kevin,
Greetings from Germany
"Kevin Kline [MVP]" schrieb:

> Hello Zip,
> Try http://h21007.www2.hp.com/dspp/tech/...,6014,00.html. You can also search on 'sizer' at www.hp.com.
> -Kev
> ~~~
> -Kevin Kline
> Quest Software (www.quest.com)
> SQL Server MVP
> I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
>
>
|||Thanks for your hint,
greetings from Germany
"Kevin Kline [MVP]" schrieb:

> Hello Zip,
> Try http://h21007.www2.hp.com/dspp/tech/...,6014,00.html. You can also search on 'sizer' at www.hp.com.
> -Kev
> ~~~
> -Kevin Kline
> Quest Software (www.quest.com)
> SQL Server MVP
> I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
>
>

Hardware Recommendation

Hello,
I plan to run Microsoft CRM, Navision SQL, and Project Server off the
same SQL box. What would you recommend hardware wise?
Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
Thanks in advance
First, put as much memory as you can afford in the box. Be aware that
anything over 4GB will require Enterprise Edition to take advantage of the
extra RAM. Dual Procs are a minimum. Go with procs with more cache rather
than faster procs, but only by 300-500 MHz. Then we geet to the disk
system. Definitely separate your SQL logs and data onto different spindles.
RAID 1 or 1+0 for logs will improve performance significantly. RAID 1+0 for
data if you can afford it, RAID5 otherwise, but it will impact performance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"CB" <chadb@.dentistryonline.com> wrote in message
news:esoMsGvMEHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I plan to run Microsoft CRM, Navision SQL, and Project Server off the
> same SQL box. What would you recommend hardware wise?
> Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
> Thanks in advance
>

Hardware Recommendation

Hello,
I plan to run Microsoft CRM, Navision SQL, and Project Server off the
same SQL box. What would you recommend hardware wise?
Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
Thanks in advanceFirst, put as much memory as you can afford in the box. Be aware that
anything over 4GB will require Enterprise Edition to take advantage of the
extra RAM. Dual Procs are a minimum. Go with procs with more cache rather
than faster procs, but only by 300-500 MHz. Then we geet to the disk
system. Definitely separate your SQL logs and data onto different spindles.
RAID 1 or 1+0 for logs will improve performance significantly. RAID 1+0 for
data if you can afford it, RAID5 otherwise, but it will impact performance.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"CB" <chadb@.dentistryonline.com> wrote in message
news:esoMsGvMEHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I plan to run Microsoft CRM, Navision SQL, and Project Server off the
> same SQL box. What would you recommend hardware wise?
> Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
> Thanks in advance
>

Hardware Recommendation

Hello,
I plan to run Microsoft CRM, Navision SQL, and Project Server off the
same SQL box. What would you recommend hardware wise?
Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
Thanks in advanceFirst, put as much memory as you can afford in the box. Be aware that
anything over 4GB will require Enterprise Edition to take advantage of the
extra RAM. Dual Procs are a minimum. Go with procs with more cache rather
than faster procs, but only by 300-500 MHz. Then we geet to the disk
system. Definitely separate your SQL logs and data onto different spindles.
RAID 1 or 1+0 for logs will improve performance significantly. RAID 1+0 for
data if you can afford it, RAID5 otherwise, but it will impact performance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"CB" <chadb@.dentistryonline.com> wrote in message
news:esoMsGvMEHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I plan to run Microsoft CRM, Navision SQL, and Project Server off the
> same SQL box. What would you recommend hardware wise?
> Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
> Thanks in advance
>

Hard disk configuration question

I just acquire a new server with 8 hard drives and 2 RAID controllers. The
objective is to load SQL SERVER 2005 on this server to run CRM and one other
database centric application. In addition rerporting services and analysis
services will also be installed. I have configured two of the RAID hard
disks as RAID1 and created the System partition on it as Drive C (30GB) and
the remaining as Drive D.
I configured another 2 as RAID1 (67GB) which I intend to use for the
Databases log files.
I now have 4 hard disks left. I can either configure this drives as 1 RAID5
Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an issue
here, I am trying to achieve the best possible performance. Which RAID
configuration is better for the data files? Any suggestions or
recommendations?
Thank you,
ODMake the 4 hard drives RAID 10 (mirror then striping).
Don't even touch RAID 5 with a barge poll now-a-days.
Put tempdb on the D: on its own RAID 1 array away from the second and third
arrays, tempdb in SQL 2005 is more heavily used but even in 2000 I'm
recommending it has its own uncontended array and even use a RAM disk if a)
the RAM disk fully supports SQL Server IO requirements and b) you have spare
memory in the box and c) if your application pattern after testing benefits
that approach (which stuff with heaving sorting, hashing, temp table usage
will) - see my blog entry: Put TEMPDB on a RAM DRIVE; Short lived # tables -
do they get written to disk?
(http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx)
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"OD" <oludan@.hotmail.com> wrote in message
news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>I just acquire a new server with 8 hard drives and 2 RAID controllers. The
>objective is to load SQL SERVER 2005 on this server to run CRM and one
>other database centric application. In addition rerporting services and
>analysis services will also be installed. I have configured two of the RAID
>hard disks as RAID1 and created the System partition on it as Drive C
>(30GB) and the remaining as Drive D.
> I configured another 2 as RAID1 (67GB) which I intend to use for the
> Databases log files.
> I now have 4 hard disks left. I can either configure this drives as 1
> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an
> issue here, I am trying to achieve the best possible performance. Which
> RAID configuration is better for the data files? Any suggestions or
> recommendations?
> Thank you,
> OD
>|||With 8 drives, my suggestion for best performance is:
Two Drives -RAID 1 (log files)
Three Drives RAID 0
Three Drives RAID 0
Mirror the above two RAID 0 sets, making a RAID 10.
Use the RAID 10 for OS and datafiles.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"OD" <oludan@.hotmail.com> wrote in message
news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>I just acquire a new server with 8 hard drives and 2 RAID controllers. The
>objective is to load SQL SERVER 2005 on this server to run CRM and one
>other database centric application. In addition rerporting services and
>analysis services will also be installed. I have configured two of the RAID
>hard disks as RAID1 and created the System partition on it as Drive C
>(30GB) and the remaining as Drive D.
> I configured another 2 as RAID1 (67GB) which I intend to use for the
> Databases log files.
> I now have 4 hard disks left. I can either configure this drives as 1
> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an
> issue here, I am trying to achieve the best possible performance. Which
> RAID configuration is better for the data files? Any suggestions or
> recommendations?
> Thank you,
> OD
>|||Thats RAID 01 which isn't as fault tolerant as RAID 10.
01 is striping and then mirroring the stripe; if a disk in the stripe fails
then your whole array is exposed and a single drive failure on the second
mirrored striped can cause the set to be unavailable.
10 is mirror the drives and then stripe across, if a disk in the stripe
fails then so long as a disk in the mirror stripe isn't the mirror for the
one that failed then you are ok.
Basically, you can lose more drives RAID 10 then with RAID 01.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:u3zHWl3yGHA.4596@.TK2MSFTNGP02.phx.gbl...
> With 8 drives, my suggestion for best performance is:
> Two Drives -RAID 1 (log files)
> Three Drives RAID 0
> Three Drives RAID 0
> Mirror the above two RAID 0 sets, making a RAID 10.
> Use the RAID 10 for OS and datafiles.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers. The
>>objective is to load SQL SERVER 2005 on this server to run CRM and one
>>other database centric application. In addition rerporting services and
>>analysis services will also be installed. I have configured two of the
>>RAID hard disks as RAID1 and created the System partition on it as Drive C
>>(30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an
>> issue here, I am trying to achieve the best possible performance. Which
>> RAID configuration is better for the data files? Any suggestions or
>> recommendations?
>> Thank you,
>> OD
>|||Thanks for the recomendation. Drive C and D are on the same RAID 1 set but
they are 2 different OS partition on the RAID Vitual Disk. Is it ok to
locate the Data files for Tempdb on the RAID 10 set with other user create
data files?
here is what I will have:
-RAID 1: partitioned to DRIVE C (for System) and D (May be backup and other
media files).
-RAID 1: Partioned to E (for logs) and F: for backup
-RAID 10: Partioned to G and H for different data files
what do you think?
OD
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
> Make the 4 hard drives RAID 10 (mirror then striping).
> Don't even touch RAID 5 with a barge poll now-a-days.
> Put tempdb on the D: on its own RAID 1 array away from the second and
> third arrays, tempdb in SQL 2005 is more heavily used but even in 2000 I'm
> recommending it has its own uncontended array and even use a RAM disk if
> a) the RAM disk fully supports SQL Server IO requirements and b) you have
> spare memory in the box and c) if your application pattern after testing
> benefits that approach (which stuff with heaving sorting, hashing, temp
> table usage will) - see my blog entry: Put TEMPDB on a RAM DRIVE; Short
> lived # tables - do they get written to disk?
> (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx)
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers. The
>>objective is to load SQL SERVER 2005 on this server to run CRM and one
>>other database centric application. In addition rerporting services and
>>analysis services will also be installed. I have configured two of the
>>RAID hard disks as RAID1 and created the System partition on it as Drive C
>>(30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an
>> issue here, I am trying to achieve the best possible performance. Which
>> RAID configuration is better for the data files? Any suggestions or
>> recommendations?
>> Thank you,
>> OD
>|||Hi OD,
> Is it ok to locate the Data files for Tempdb on the RAID 10 set with
> other user create data files?
Why? Your system should rarely ever page so disk performance to the RAID 1
array containing pagefile.sys should not be a problem - better to utilise
it, if your box is paging (thereby causing IO to that RAID 1 array) then you
have other memory problems to solve first anyway, by using D: you aren't
touching the logical system drive so you will have enough space to put the
pagefile.sys (fix this rather than allow OS to grow it).
Think of tempdb in the same way you think of a database transaction log.
Why have RAID 10 partitioned as G and H? Just make it one logical drive -
drive G:
Again, why partition RAID 1 as E: and F: - just make it one logical drive -
drive E:
When you format the partitions in NT, format them NTFS but give it an
allocation size that matches your RAID stripe size, its usually 64K.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"OD" <oludan@.hotmail.com> wrote in message
news:eWT2923yGHA.4232@.TK2MSFTNGP05.phx.gbl...
> Thanks for the recomendation. Drive C and D are on the same RAID 1 set but
> they are 2 different OS partition on the RAID Vitual Disk. Is it ok to
> locate the Data files for Tempdb on the RAID 10 set with other user create
> data files?
> here is what I will have:
> -RAID 1: partitioned to DRIVE C (for System) and D (May be backup and
> other media files).
> -RAID 1: Partioned to E (for logs) and F: for backup
> -RAID 10: Partioned to G and H for different data files
> what do you think?
> OD
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Make the 4 hard drives RAID 10 (mirror then striping).
>> Don't even touch RAID 5 with a barge poll now-a-days.
>> Put tempdb on the D: on its own RAID 1 array away from the second and
>> third arrays, tempdb in SQL 2005 is more heavily used but even in 2000
>> I'm recommending it has its own uncontended array and even use a RAM disk
>> if a) the RAM disk fully supports SQL Server IO requirements and b) you
>> have spare memory in the box and c) if your application pattern after
>> testing benefits that approach (which stuff with heaving sorting,
>> hashing, temp table usage will) - see my blog entry: Put TEMPDB on a RAM
>> DRIVE; Short lived # tables - do they get written to disk?
>> (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx)
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
>> SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting services
>>and analysis services will also be installed. I have configured two of
>>the RAID hard disks as RAID1 and created the System partition on it as
>>Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of
>> an issue here, I am trying to achieve the best possible performance.
>> Which RAID configuration is better for the data files? Any suggestions
>> or recommendations?
>> Thank you,
>> OD
>>
>|||Thanks Tony, for the additional information.
However, as I was apt to do in my many Econ classes, I love to challenge the
assumptions. Anytime there is an 'IF', as in:
"if a disk in the stripe
fails then so long as a disk in the mirror stripe isn't the mirror for
the
one that failed then you are ok"
It seems mirrored stripes AND stripped mirrors are both vulnerable to
multiple drive failures. However, as you indicated, the stripped mirrors
seem to provide a little bit more of a element of chance to which mirrored
drive fails, whereas with mirrored stripes, failure in the mirror is certain
'death'.
One could posit that all of society survives on the element of chance.
Another day, another useful bit of information gleaned.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uoxYHr3yGHA.3656@.TK2MSFTNGP04.phx.gbl...
> Thats RAID 01 which isn't as fault tolerant as RAID 10.
> 01 is striping and then mirroring the stripe; if a disk in the stripe
> fails then your whole array is exposed and a single drive failure on the
> second mirrored striped can cause the set to be unavailable.
> 10 is mirror the drives and then stripe across, if a disk in the stripe
> fails then so long as a disk in the mirror stripe isn't the mirror for the
> one that failed then you are ok.
> Basically, you can lose more drives RAID 10 then with RAID 01.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:u3zHWl3yGHA.4596@.TK2MSFTNGP02.phx.gbl...
>> With 8 drives, my suggestion for best performance is:
>> Two Drives -RAID 1 (log files)
>> Three Drives RAID 0
>> Three Drives RAID 0
>> Mirror the above two RAID 0 sets, making a RAID 10.
>> Use the RAID 10 for OS and datafiles.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting services
>>and analysis services will also be installed. I have configured two of
>>the RAID hard disks as RAID1 and created the System partition on it as
>>Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of
>> an issue here, I am trying to achieve the best possible performance.
>> Which RAID configuration is better for the data files? Any suggestions
>> or recommendations?
>> Thank you,
>> OD
>>
>|||Thank you all for the assistance. In that case, I will stick with your
earlier advice as so:
First RAID 1
--C: ->System and Binary
--D: -> TempDB
Second Raid 1
--E: -> LOGS + Backup
RAID 10
--F: Data files
Now that the DB spaces have been figured out will it be ok (performance
wise) for one of Drives D, E or F to also hold other data such as Word and
Excel files that are generated by one of the appliations?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uzfHy73yGHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hi OD,
>> Is it ok to locate the Data files for Tempdb on the RAID 10 set with
>> other user create data files?
> Why? Your system should rarely ever page so disk performance to the RAID 1
> array containing pagefile.sys should not be a problem - better to utilise
> it, if your box is paging (thereby causing IO to that RAID 1 array) then
> you have other memory problems to solve first anyway, by using D: you
> aren't touching the logical system drive so you will have enough space to
> put the pagefile.sys (fix this rather than allow OS to grow it).
> Think of tempdb in the same way you think of a database transaction log.
> Why have RAID 10 partitioned as G and H? Just make it one logical drive -
> drive G:
> Again, why partition RAID 1 as E: and F: - just make it one logical
> drive - drive E:
> When you format the partitions in NT, format them NTFS but give it an
> allocation size that matches your RAID stripe size, its usually 64K.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:eWT2923yGHA.4232@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the recomendation. Drive C and D are on the same RAID 1 set
>> but they are 2 different OS partition on the RAID Vitual Disk. Is it ok
>> to locate the Data files for Tempdb on the RAID 10 set with other user
>> create data files?
>> here is what I will have:
>> -RAID 1: partitioned to DRIVE C (for System) and D (May be backup and
>> other media files).
>> -RAID 1: Partioned to E (for logs) and F: for backup
>> -RAID 10: Partioned to G and H for different data files
>> what do you think?
>> OD
>>
>> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
>> news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Make the 4 hard drives RAID 10 (mirror then striping).
>> Don't even touch RAID 5 with a barge poll now-a-days.
>> Put tempdb on the D: on its own RAID 1 array away from the second and
>> third arrays, tempdb in SQL 2005 is more heavily used but even in 2000
>> I'm recommending it has its own uncontended array and even use a RAM
>> disk if a) the RAM disk fully supports SQL Server IO requirements and b)
>> you have spare memory in the box and c) if your application pattern
>> after testing benefits that approach (which stuff with heaving sorting,
>> hashing, temp table usage will) - see my blog entry: Put TEMPDB on a RAM
>> DRIVE; Short lived # tables - do they get written to disk?
>> (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx)
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
>> SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting services
>>and analysis services will also be installed. I have configured two of
>>the RAID hard disks as RAID1 and created the System partition on it as
>>Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of
>> an issue here, I am trying to achieve the best possible performance.
>> Which RAID configuration is better for the data files? Any suggestions
>> or recommendations?
>> Thank you,
>> OD
>>
>>
>|||You raise a good point.
Something else I've seen is that all the onboard SATA RAID controllers I've
seen so far implement RAID 01, so if you want RAID 10 you must RAID 1 the
drives first and then use Windows to stripe across the drives.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:e9%23BGC4yGHA.4968@.TK2MSFTNGP05.phx.gbl...
> Thanks Tony, for the additional information.
> However, as I was apt to do in my many Econ classes, I love to challenge
> the assumptions. Anytime there is an 'IF', as in:
> "if a disk in the stripe
> fails then so long as a disk in the mirror stripe isn't the mirror for
> the
> one that failed then you are ok"
> It seems mirrored stripes AND stripped mirrors are both vulnerable to
> multiple drive failures. However, as you indicated, the stripped mirrors
> seem to provide a little bit more of a element of chance to which mirrored
> drive fails, whereas with mirrored stripes, failure in the mirror is
> certain 'death'.
> One could posit that all of society survives on the element of chance.
> Another day, another useful bit of information gleaned.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uoxYHr3yGHA.3656@.TK2MSFTNGP04.phx.gbl...
>> Thats RAID 01 which isn't as fault tolerant as RAID 10.
>> 01 is striping and then mirroring the stripe; if a disk in the stripe
>> fails then your whole array is exposed and a single drive failure on the
>> second mirrored striped can cause the set to be unavailable.
>> 10 is mirror the drives and then stripe across, if a disk in the stripe
>> fails then so long as a disk in the mirror stripe isn't the mirror for
>> the one that failed then you are ok.
>> Basically, you can lose more drives RAID 10 then with RAID 01.
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
>> SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:u3zHWl3yGHA.4596@.TK2MSFTNGP02.phx.gbl...
>> With 8 drives, my suggestion for best performance is:
>> Two Drives -RAID 1 (log files)
>> Three Drives RAID 0
>> Three Drives RAID 0
>> Mirror the above two RAID 0 sets, making a RAID 10.
>> Use the RAID 10 for OS and datafiles.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting services
>>and analysis services will also be installed. I have configured two of
>>the RAID hard disks as RAID1 and created the System partition on it as
>>Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of
>> an issue here, I am trying to achieve the best possible performance.
>> Which RAID configuration is better for the data files? Any suggestions
>> or recommendations?
>> Thank you,
>> OD
>>
>>
>|||I am running a DELL PERC 5 RAID controller and it explains this same thing.
However, it only allows configuration for RAIDs 0, 1, 5, and 10. So I have
configured the last 4 drives as RAID 10 since it's the only option supported
on the controller between RAID 01 and RAID 10.
thank you for your assistance.
OD
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:e9%23BGC4yGHA.4968@.TK2MSFTNGP05.phx.gbl...
> Thanks Tony, for the additional information.
> However, as I was apt to do in my many Econ classes, I love to challenge
> the assumptions. Anytime there is an 'IF', as in:
> "if a disk in the stripe
> fails then so long as a disk in the mirror stripe isn't the mirror for
> the
> one that failed then you are ok"
> It seems mirrored stripes AND stripped mirrors are both vulnerable to
> multiple drive failures. However, as you indicated, the stripped mirrors
> seem to provide a little bit more of a element of chance to which mirrored
> drive fails, whereas with mirrored stripes, failure in the mirror is
> certain 'death'.
> One could posit that all of society survives on the element of chance.
> Another day, another useful bit of information gleaned.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uoxYHr3yGHA.3656@.TK2MSFTNGP04.phx.gbl...
>> Thats RAID 01 which isn't as fault tolerant as RAID 10.
>> 01 is striping and then mirroring the stripe; if a disk in the stripe
>> fails then your whole array is exposed and a single drive failure on the
>> second mirrored striped can cause the set to be unavailable.
>> 10 is mirror the drives and then stripe across, if a disk in the stripe
>> fails then so long as a disk in the mirror stripe isn't the mirror for
>> the one that failed then you are ok.
>> Basically, you can lose more drives RAID 10 then with RAID 01.
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
>> SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:u3zHWl3yGHA.4596@.TK2MSFTNGP02.phx.gbl...
>> With 8 drives, my suggestion for best performance is:
>> Two Drives -RAID 1 (log files)
>> Three Drives RAID 0
>> Three Drives RAID 0
>> Mirror the above two RAID 0 sets, making a RAID 10.
>> Use the RAID 10 for OS and datafiles.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting services
>>and analysis services will also be installed. I have configured two of
>>the RAID hard disks as RAID1 and created the System partition on it as
>>Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of
>> an issue here, I am trying to achieve the best possible performance.
>> Which RAID configuration is better for the data files? Any suggestions
>> or recommendations?
>> Thank you,
>> OD
>>
>>
>|||> Now that the DB spaces have been figured out will it be ok (performance
> wise) for one of Drives D, E or F to also hold other data such as Word and
> Excel files that are generated by one of the appliations?
Depends on the volume, if you have an application that is continuously
knocking out files then I'd advise something else, but if its just a file
server then you need to ask yourself how often are files read or written
to/from and that will answer your question, I'd probably put the files on
the RAID 10 array because you really need the IO bandwidth for the
checkpoint process or when dirty pages are being written out.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"OD" <oludan@.hotmail.com> wrote in message
news:u%23XUZD4yGHA.3552@.TK2MSFTNGP02.phx.gbl...
> Thank you all for the assistance. In that case, I will stick with your
> earlier advice as so:
> First RAID 1
> --C: ->System and Binary
> --D: -> TempDB
> Second Raid 1
> --E: -> LOGS + Backup
> RAID 10
> --F: Data files
> Now that the DB spaces have been figured out will it be ok (performance
> wise) for one of Drives D, E or F to also hold other data such as Word and
> Excel files that are generated by one of the appliations?
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uzfHy73yGHA.5048@.TK2MSFTNGP05.phx.gbl...
>> Hi OD,
>> Is it ok to locate the Data files for Tempdb on the RAID 10 set with
>> other user create data files?
>> Why? Your system should rarely ever page so disk performance to the RAID
>> 1 array containing pagefile.sys should not be a problem - better to
>> utilise it, if your box is paging (thereby causing IO to that RAID 1
>> array) then you have other memory problems to solve first anyway, by
>> using D: you aren't touching the logical system drive so you will have
>> enough space to put the pagefile.sys (fix this rather than allow OS to
>> grow it).
>> Think of tempdb in the same way you think of a database transaction log.
>> Why have RAID 10 partitioned as G and H? Just make it one logical drive -
>> drive G:
>> Again, why partition RAID 1 as E: and F: - just make it one logical
>> drive - drive E:
>> When you format the partitions in NT, format them NTFS but give it an
>> allocation size that matches your RAID stripe size, its usually 64K.
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
>> SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:eWT2923yGHA.4232@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the recomendation. Drive C and D are on the same RAID 1 set
>> but they are 2 different OS partition on the RAID Vitual Disk. Is it ok
>> to locate the Data files for Tempdb on the RAID 10 set with other user
>> create data files?
>> here is what I will have:
>> -RAID 1: partitioned to DRIVE C (for System) and D (May be backup and
>> other media files).
>> -RAID 1: Partioned to E (for logs) and F: for backup
>> -RAID 10: Partioned to G and H for different data files
>> what do you think?
>> OD
>>
>> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
>> news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Make the 4 hard drives RAID 10 (mirror then striping).
>> Don't even touch RAID 5 with a barge poll now-a-days.
>> Put tempdb on the D: on its own RAID 1 array away from the second and
>> third arrays, tempdb in SQL 2005 is more heavily used but even in 2000
>> I'm recommending it has its own uncontended array and even use a RAM
>> disk if a) the RAM disk fully supports SQL Server IO requirements and
>> b) you have spare memory in the box and c) if your application pattern
>> after testing benefits that approach (which stuff with heaving sorting,
>> hashing, temp table usage will) - see my blog entry: Put TEMPDB on a
>> RAM DRIVE; Short lived # tables - do they get written to disk?
>> (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx)
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from
>> a SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting services
>>and analysis services will also be installed. I have configured two of
>>the RAID hard disks as RAID1 and created the System partition on it as
>>Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of
>> an issue here, I am trying to achieve the best possible performance.
>> Which RAID configuration is better for the data files? Any suggestions
>> or recommendations?
>> Thank you,
>> OD
>>
>>
>>
>|||Thank you Annie and Tony. You have helped a lot and saved me sometime. I
will go ahead and drop the Excel and Word files on the RAID 10 temporarily
and if there is a performance issue I will add more hard drives and move
them.
Thanks again!.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:O4VKpL4yGHA.1256@.TK2MSFTNGP04.phx.gbl...
>> Now that the DB spaces have been figured out will it be ok (performance
>> wise) for one of Drives D, E or F to also hold other data such as Word
>> and Excel files that are generated by one of the appliations?
> Depends on the volume, if you have an application that is continuously
> knocking out files then I'd advise something else, but if its just a file
> server then you need to ask yourself how often are files read or written
> to/from and that will answer your question, I'd probably put the files on
> the RAID 10 array because you really need the IO bandwidth for the
> checkpoint process or when dirty pages are being written out.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:u%23XUZD4yGHA.3552@.TK2MSFTNGP02.phx.gbl...
>> Thank you all for the assistance. In that case, I will stick with your
>> earlier advice as so:
>> First RAID 1
>> --C: ->System and Binary
>> --D: -> TempDB
>> Second Raid 1
>> --E: -> LOGS + Backup
>> RAID 10
>> --F: Data files
>> Now that the DB spaces have been figured out will it be ok (performance
>> wise) for one of Drives D, E or F to also hold other data such as Word
>> and Excel files that are generated by one of the appliations?
>>
>> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
>> news:uzfHy73yGHA.5048@.TK2MSFTNGP05.phx.gbl...
>> Hi OD,
>> Is it ok to locate the Data files for Tempdb on the RAID 10 set with
>> other user create data files?
>> Why? Your system should rarely ever page so disk performance to the RAID
>> 1 array containing pagefile.sys should not be a problem - better to
>> utilise it, if your box is paging (thereby causing IO to that RAID 1
>> array) then you have other memory problems to solve first anyway, by
>> using D: you aren't touching the logical system drive so you will have
>> enough space to put the pagefile.sys (fix this rather than allow OS to
>> grow it).
>> Think of tempdb in the same way you think of a database transaction log.
>> Why have RAID 10 partitioned as G and H? Just make it one logical
>> drive - drive G:
>> Again, why partition RAID 1 as E: and F: - just make it one logical
>> drive - drive E:
>> When you format the partitions in NT, format them NTFS but give it an
>> allocation size that matches your RAID stripe size, its usually 64K.
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
>> SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:eWT2923yGHA.4232@.TK2MSFTNGP05.phx.gbl...
>> Thanks for the recomendation. Drive C and D are on the same RAID 1 set
>> but they are 2 different OS partition on the RAID Vitual Disk. Is it ok
>> to locate the Data files for Tempdb on the RAID 10 set with other user
>> create data files?
>> here is what I will have:
>> -RAID 1: partitioned to DRIVE C (for System) and D (May be backup and
>> other media files).
>> -RAID 1: Partioned to E (for logs) and F: for backup
>> -RAID 10: Partioned to G and H for different data files
>> what do you think?
>> OD
>>
>> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
>> news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
>> Make the 4 hard drives RAID 10 (mirror then striping).
>> Don't even touch RAID 5 with a barge poll now-a-days.
>> Put tempdb on the D: on its own RAID 1 array away from the second and
>> third arrays, tempdb in SQL 2005 is more heavily used but even in 2000
>> I'm recommending it has its own uncontended array and even use a RAM
>> disk if a) the RAM disk fully supports SQL Server IO requirements and
>> b) you have spare memory in the box and c) if your application pattern
>> after testing benefits that approach (which stuff with heaving
>> sorting, hashing, temp table usage will) - see my blog entry: Put
>> TEMPDB on a RAM DRIVE; Short lived # tables - do they get written to
>> disk?
>> (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx)
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from
>> a SQL Server Consultant
>> http://sqlserverfaq.com - free video tutorials
>>
>> "OD" <oludan@.hotmail.com> wrote in message
>> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>>I just acquire a new server with 8 hard drives and 2 RAID controllers.
>>The objective is to load SQL SERVER 2005 on this server to run CRM and
>>one other database centric application. In addition rerporting
>>services and analysis services will also be installed. I have
>>configured two of the RAID hard disks as RAID1 and created the System
>>partition on it as Drive C (30GB) and the remaining as Drive D.
>> I configured another 2 as RAID1 (67GB) which I intend to use for the
>> Databases log files.
>> I now have 4 hard disks left. I can either configure this drives as 1
>> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much
>> of an issue here, I am trying to achieve the best possible
>> performance. Which RAID configuration is better for the data files?
>> Any suggestions or recommendations?
>> Thank you,
>> OD
>>
>>
>>
>>
>

Hard disk configuration question

I just acquire a new server with 8 hard drives and 2 RAID controllers. The
objective is to load SQL SERVER 2005 on this server to run CRM and one other
database centric application. In addition rerporting services and analysis
services will also be installed. I have configured two of the RAID hard
disks as RAID1 and created the System partition on it as Drive C (30GB) and
the remaining as Drive D.
I configured another 2 as RAID1 (67GB) which I intend to use for the
Databases log files.
I now have 4 hard disks left. I can either configure this drives as 1 RAID5
Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an issue
here, I am trying to achieve the best possible performance. Which RAID
configuration is better for the data files? Any suggestions or
recommendations?
Thank you,
ODMake the 4 hard drives RAID 10 (mirror then striping).
Don't even touch RAID 5 with a barge poll now-a-days.
Put tempdb on the D: on its own RAID 1 array away from the second and third
arrays, tempdb in SQL 2005 is more heavily used but even in 2000 I'm
recommending it has its own uncontended array and even use a RAM disk if a)
the RAM disk fully supports SQL Server IO requirements and b) you have spare
memory in the box and c) if your application pattern after testing benefits
that approach (which stuff with heaving sorting, hashing, temp table usage
will) - see my blog entry: Put TEMPDB on a RAM DRIVE; Short lived # tables -
do they get written to disk?
(http://sqlblogcasts.com/blogs/tonyr.../08/24/958.aspx)
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"OD" <oludan@.hotmail.com> wrote in message
news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>I just acquire a new server with 8 hard drives and 2 RAID controllers. The
>objective is to load SQL SERVER 2005 on this server to run CRM and one
>other database centric application. In addition rerporting services and
>analysis services will also be installed. I have configured two of the RAID
>hard disks as RAID1 and created the System partition on it as Drive C
>(30GB) and the remaining as Drive D.
> I configured another 2 as RAID1 (67GB) which I intend to use for the
> Databases log files.
> I now have 4 hard disks left. I can either configure this drives as 1
> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an
> issue here, I am trying to achieve the best possible performance. Which
> RAID configuration is better for the data files? Any suggestions or
> recommendations?
> Thank you,
> OD
>|||With 8 drives, my suggestion for best performance is:
Two Drives -RAID 1 (log files)
Three Drives RAID 0
Three Drives RAID 0
Mirror the above two RAID 0 sets, making a RAID 10.
Use the RAID 10 for OS and datafiles.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"OD" <oludan@.hotmail.com> wrote in message
news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>I just acquire a new server with 8 hard drives and 2 RAID controllers. The
>objective is to load SQL SERVER 2005 on this server to run CRM and one
>other database centric application. In addition rerporting services and
>analysis services will also be installed. I have configured two of the RAID
>hard disks as RAID1 and created the System partition on it as Drive C
>(30GB) and the remaining as Drive D.
> I configured another 2 as RAID1 (67GB) which I intend to use for the
> Databases log files.
> I now have 4 hard disks left. I can either configure this drives as 1
> RAID5 Vitual Disk or 2 RAID1 Vitual Disk.Since space is not so much of an
> issue here, I am trying to achieve the best possible performance. Which
> RAID configuration is better for the data files? Any suggestions or
> recommendations?
> Thank you,
> OD
>|||Thats RAID 01 which isn't as fault tolerant as RAID 10.
01 is striping and then mirroring the stripe; if a disk in the stripe fails
then your whole array is exposed and a single drive failure on the second
mirrored striped can cause the set to be unavailable.
10 is mirror the drives and then stripe across, if a disk in the stripe
fails then so long as a disk in the mirror stripe isn't the mirror for the
one that failed then you are ok.
Basically, you can lose more drives RAID 10 then with RAID 01.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:u3zHWl3yGHA.4596@.TK2MSFTNGP02.phx.gbl...
> With 8 drives, my suggestion for best performance is:
> Two Drives -RAID 1 (log files)
> Three Drives RAID 0
> Three Drives RAID 0
> Mirror the above two RAID 0 sets, making a RAID 10.
> Use the RAID 10 for OS and datafiles.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>|||Thanks for the recomendation. Drive C and D are on the same RAID 1 set but
they are 2 different OS partition on the RAID Vitual Disk. Is it ok to
locate the Data files for Tempdb on the RAID 10 set with other user create
data files?
here is what I will have:
-RAID 1: partitioned to DRIVE C (for System) and D (May be backup and other
media files).
-RAID 1: Partioned to E (for logs) and F: for backup
-RAID 10: Partioned to G and H for different data files
what do you think?
OD
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
> Make the 4 hard drives RAID 10 (mirror then striping).
> Don't even touch RAID 5 with a barge poll now-a-days.
> Put tempdb on the D: on its own RAID 1 array away from the second and
> third arrays, tempdb in SQL 2005 is more heavily used but even in 2000 I'm
> recommending it has its own uncontended array and even use a RAM disk if
> a) the RAM disk fully supports SQL Server IO requirements and b) you have
> spare memory in the box and c) if your application pattern after testing
> benefits that approach (which stuff with heaving sorting, hashing, temp
> table usage will) - see my blog entry: Put TEMPDB on a RAM DRIVE; Short
> lived # tables - do they get written to disk?
> (http://sqlblogcasts.com/blogs/tonyr.../08/24/958.aspx)
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:ePVRL72yGHA.4844@.TK2MSFTNGP04.phx.gbl...
>|||Hi OD,

> Is it ok to locate the Data files for Tempdb on the RAID 10 set with
> other user create data files?
Why? Your system should rarely ever page so disk performance to the RAID 1
array containing pagefile.sys should not be a problem - better to utilise
it, if your box is paging (thereby causing IO to that RAID 1 array) then you
have other memory problems to solve first anyway, by using D: you aren't
touching the logical system drive so you will have enough space to put the
pagefile.sys (fix this rather than allow OS to grow it).
Think of tempdb in the same way you think of a database transaction log.
Why have RAID 10 partitioned as G and H? Just make it one logical drive -
drive G:
Again, why partition RAID 1 as E: and F: - just make it one logical drive -
drive E:
When you format the partitions in NT, format them NTFS but give it an
allocation size that matches your RAID stripe size, its usually 64K.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"OD" <oludan@.hotmail.com> wrote in message
news:eWT2923yGHA.4232@.TK2MSFTNGP05.phx.gbl...
> Thanks for the recomendation. Drive C and D are on the same RAID 1 set but
> they are 2 different OS partition on the RAID Vitual Disk. Is it ok to
> locate the Data files for Tempdb on the RAID 10 set with other user create
> data files?
> here is what I will have:
> -RAID 1: partitioned to DRIVE C (for System) and D (May be backup and
> other media files).
> -RAID 1: Partioned to E (for logs) and F: for backup
> -RAID 10: Partioned to G and H for different data files
> what do you think?
> OD
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eQ7X2i3yGHA.2640@.TK2MSFTNGP06.phx.gbl...
>|||Thanks Tony, for the additional information.
However, as I was apt to do in my many Econ classes, I love to challenge the
assumptions. Anytime there is an 'IF', as in:
"if a disk in the stripe
fails then so long as a disk in the mirror stripe isn't the mirror for
the
one that failed then you are ok"
It seems mirrored stripes AND stripped mirrors are both vulnerable to
multiple drive failures. However, as you indicated, the stripped mirrors
seem to provide a little bit more of a element of chance to which mirrored
drive fails, whereas with mirrored stripes, failure in the mirror is certain
'death'.
One could posit that all of society survives on the element of chance.
Another day, another useful bit of information gleaned.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uoxYHr3yGHA.3656@.TK2MSFTNGP04.phx.gbl...
> Thats RAID 01 which isn't as fault tolerant as RAID 10.
> 01 is striping and then mirroring the stripe; if a disk in the stripe
> fails then your whole array is exposed and a single drive failure on the
> second mirrored striped can cause the set to be unavailable.
> 10 is mirror the drives and then stripe across, if a disk in the stripe
> fails then so long as a disk in the mirror stripe isn't the mirror for the
> one that failed then you are ok.
> Basically, you can lose more drives RAID 10 then with RAID 01.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:u3zHWl3yGHA.4596@.TK2MSFTNGP02.phx.gbl...
>|||Thank you all for the assistance. In that case, I will stick with your
earlier advice as so:
First RAID 1
--C: ->System and Binary
--D: -> TempDB
Second Raid 1
--E: -> LOGS + Backup
RAID 10
--F: Data files
Now that the DB spaces have been figured out will it be ok (performance
wise) for one of Drives D, E or F to also hold other data such as Word and
Excel files that are generated by one of the appliations?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uzfHy73yGHA.5048@.TK2MSFTNGP05.phx.gbl...
> Hi OD,
>
> Why? Your system should rarely ever page so disk performance to the RAID 1
> array containing pagefile.sys should not be a problem - better to utilise
> it, if your box is paging (thereby causing IO to that RAID 1 array) then
> you have other memory problems to solve first anyway, by using D: you
> aren't touching the logical system drive so you will have enough space to
> put the pagefile.sys (fix this rather than allow OS to grow it).
> Think of tempdb in the same way you think of a database transaction log.
> Why have RAID 10 partitioned as G and H? Just make it one logical drive -
> drive G:
> Again, why partition RAID 1 as E: and F: - just make it one logical
> drive - drive E:
> When you format the partitions in NT, format them NTFS but give it an
> allocation size that matches your RAID stripe size, its usually 64K.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "OD" <oludan@.hotmail.com> wrote in message
> news:eWT2923yGHA.4232@.TK2MSFTNGP05.phx.gbl...
>|||You raise a good point.
Something else I've seen is that all the onboard SATA RAID controllers I've
seen so far implement RAID 01, so if you want RAID 10 you must RAID 1 the
drives first and then use Windows to stripe across the drives.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:e9%23BGC4yGHA.4968@.TK2MSFTNGP05.phx.gbl...
> Thanks Tony, for the additional information.
> However, as I was apt to do in my many Econ classes, I love to challenge
> the assumptions. Anytime there is an 'IF', as in:
> "if a disk in the stripe
> fails then so long as a disk in the mirror stripe isn't the mirror for
> the
> one that failed then you are ok"
> It seems mirrored stripes AND stripped mirrors are both vulnerable to
> multiple drive failures. However, as you indicated, the stripped mirrors
> seem to provide a little bit more of a element of chance to which mirrored
> drive fails, whereas with mirrored stripes, failure in the mirror is
> certain 'death'.
> One could posit that all of society survives on the element of chance.
> Another day, another useful bit of information gleaned.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uoxYHr3yGHA.3656@.TK2MSFTNGP04.phx.gbl...
>|||I am running a DELL PERC 5 RAID controller and it explains this same thing.
However, it only allows configuration for RAIDs 0, 1, 5, and 10. So I have
configured the last 4 drives as RAID 10 since it's the only option supported
on the controller between RAID 01 and RAID 10.
thank you for your assistance.
OD
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:e9%23BGC4yGHA.4968@.TK2MSFTNGP05.phx.gbl...
> Thanks Tony, for the additional information.
> However, as I was apt to do in my many Econ classes, I love to challenge
> the assumptions. Anytime there is an 'IF', as in:
> "if a disk in the stripe
> fails then so long as a disk in the mirror stripe isn't the mirror for
> the
> one that failed then you are ok"
> It seems mirrored stripes AND stripped mirrors are both vulnerable to
> multiple drive failures. However, as you indicated, the stripped mirrors
> seem to provide a little bit more of a element of chance to which mirrored
> drive fails, whereas with mirrored stripes, failure in the mirror is
> certain 'death'.
> One could posit that all of society survives on the element of chance.
> Another day, another useful bit of information gleaned.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uoxYHr3yGHA.3656@.TK2MSFTNGP04.phx.gbl...
>