Hi,
I have the availability of a database of 100mb. I need to store large
amounts of forum messages, the more the better.Currently every table
row has 4 small nvarchar fields, and a ntext for the body of the forum
message.
This way every row is about 4 kbyte in size. That means I am able to
store "only" 25,600 forum messages.
Is there a more optimal solution so I can put more messages in this
database?
WardWard
Do you say that a database cannot grow more than 100MB?
Is it by defenition?
As an alterrnative you store the messages at filesystem and read them when
you need
"Ward Bekker" <w.bekker@.gmail.com> wrote in message
news:1142579469.310833.81370@.p10g2000cwp.googlegroups.com...
> Hi,
> I have the availability of a database of 100mb. I need to store large
> amounts of forum messages, the more the better.Currently every table
> row has 4 small nvarchar fields, and a ntext for the body of the forum
> message.
> This way every row is about 4 kbyte in size. That means I am able to
> store "only" 25,600 forum messages.
> Is there a more optimal solution so I can put more messages in this
> database?
> Ward
>|||Yes, it cannot grow larger. I do have around 500 mb storage space. The
downside of storing it on the filesystem is that I won't be able to use
full-text search. Or doesn't have that to be a problem?|||Ward
> downside of storing it on the filesystem is that I won't be able to use
> full-text search. Or doesn't have that to be a problem?
>
If does not relate . In fact you need a storage SAN probably to deal with
your issue as well as to allow growing your database
"Ward Bekker" <w.bekker@.gmail.com> wrote in message
news:1142581209.270659.116510@.u72g2000cwu.googlegroups.com...
> Yes, it cannot grow larger. I do have around 500 mb storage space. The
> downside of storing it on the filesystem is that I won't be able to use
> full-text search. Or doesn't have that to be a problem?
>|||Sorry, should be IT does not relate
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OaGQ5bZSGHA.1728@.TK2MSFTNGP11.phx.gbl...
> Ward
> If does not relate . In fact you need a storage SAN probably to deal with
> your issue as well as to allow growing your database
>
> "Ward Bekker" <w.bekker@.gmail.com> wrote in message
> news:1142581209.270659.116510@.u72g2000cwu.googlegroups.com...
>|||What is your expected database size? Perhaps you simply need more space.
ML
http://milambda.blogspot.com/|||So, you say it's possible to have full-text search on real files from
Sql Server? If so, could you eleborate?|||> So, you say it's possible to have full-text search on real files from
> Sql Server? If so, could you eleborate?
>
If the file is located in SQL Server's table
Well, you will be better of to ask the question in full-text news group, I
have played with full-text very little
Also , there is pretty good article in the BOL how to create/work with
full-text in SQL Server
"Ward Bekker" <w.bekker@.gmail.com> wrote in message
news:1142583077.165690.319440@.p10g2000cwp.googlegroups.com...
> So, you say it's possible to have full-text search on real files from
> Sql Server? If so, could you eleborate?
>|||The nvarchar and ntext datatypes support the unicode character set, but
store 2 bytes per character instead of 1 byte per character for varchar and
text. However, switching to varchar and text would only double the capacity
to 50,000 messages; which would not be any real order of magnitude.
You can store the text of the messages in seperate text files with the
naming convention based on the primary key of the message. For example,
message id #120455 would have a related file called 00120455.txt. How you
relate the text files with the messages is an application programming issue.
From what you describe, it sounds like a 3rd party hosted database. Do you
have the option of just paying for the additional storage?
"Ward Bekker" <w.bekker@.gmail.com> wrote in message
news:1142579469.310833.81370@.p10g2000cwp.googlegroups.com...
> Hi,
> I have the availability of a database of 100mb. I need to store large
> amounts of forum messages, the more the better.Currently every table
> row has 4 small nvarchar fields, and a ntext for the body of the forum
> message.
> This way every row is about 4 kbyte in size. That means I am able to
> store "only" 25,600 forum messages.
> Is there a more optimal solution so I can put more messages in this
> database?
> Ward
>|||JT,
Yes, it's a hosted database. I can pay for additional storage, but I
want to first make sure that I have a optimal SQL Server config so i
get more bang per buck ;-)
Tnx,
Ward
Showing posts with label optimal. Show all posts
Showing posts with label optimal. Show all posts
Monday, March 12, 2012
Optimal SQL
Anybody know why Query B is so much more expensive than Query A? Would you agree that Query C is the best bet?
Goal:
Return 0 results
Given:
CustomerID will never be '99999'
CustomerID is the primary key
A - SELECT * FROM Customers WHERE CustomerID=99999
B - SELECT * FROM Customers WHERE CustomerID<>CustomerID?
C - SELECT TOP 0 * FROM Customersin A you are scanning a select range of rows from a clustered index. SQL server is doing a binary search and thus able to eliminate most of the data within a couple of evaluations.
in B you are scanning all rows of a clustered index. Since you are asking SQL server to compare two attributes of a record, each record must be evaluated.
in C you are scanning an internal table.
I personnaly prefer select * from <table name> where 1 = 2.|||HI,
Agree with Paul .
select * from <table name> where 1 = 0
Cheers
Gola
Originally posted by Paul Young
in A you are scanning a select range of rows from a clustered index. SQL server is doing a binary search and thus able to eliminate most of the data within a couple of evaluations.
in B you are scanning all rows of a clustered index. Since you are asking SQL server to compare two attributes of a record, each record must be evaluated.
in C you are scanning an internal table.
I personnaly prefer select * from <table name> where 1 = 2.|||Thanks Paul. What you said makes perfect sense. I knew how SQL impelemented indexing, but failed to see the obvious need to skip the indexing and go straight to a table scan and scan each record individually when comparing one field to another.
Gola's example would seem to be more efficient, as would option C or as a friend pointed out, even...
SELECT * FROM Customers WHERE CustomerID IS NULL.
...would work since we are looking at the primary key.
Thanks guys.
Moki|||I don't agree with your friend. You will still be scanning part of the index. If the table doesn't have an index you will be scanning the table. My suggestion will always scan an internal table regardless.
the only diffrence between my suggestion and Gola's is the numbers used to generate a false condition.|||I'll need to think about that last one...maybe I don't understand MSSQL's implementation like I thought. I would have thought the primary key was always indexed and so checking for NULL would be the same as running thru the B-Tree looking for a value.
In either case, how about option C, "SELECT TOP 0...."?|||Yes, in SQL server the primary key will have some type of index. My point was that your friends suggestion will cause a partial index scan when a table has a usable index OR a table scan if no index is present or no existing indexes are present to match your where clause.
Bottom line, C is the best choice of the three.|||Ok, thanks. I thought about it (Option C) and the suggestion from you (and Gola), and think I like y'alls better. Only because I'm questioning the use of "...TOP 0..." and its conformance (or possible lack thereof) to the ANSI-92 standard...just in case that becomes a customer's constraint in the future.
Thanks again.
Goal:
Return 0 results
Given:
CustomerID will never be '99999'
CustomerID is the primary key
A - SELECT * FROM Customers WHERE CustomerID=99999
B - SELECT * FROM Customers WHERE CustomerID<>CustomerID?
C - SELECT TOP 0 * FROM Customersin A you are scanning a select range of rows from a clustered index. SQL server is doing a binary search and thus able to eliminate most of the data within a couple of evaluations.
in B you are scanning all rows of a clustered index. Since you are asking SQL server to compare two attributes of a record, each record must be evaluated.
in C you are scanning an internal table.
I personnaly prefer select * from <table name> where 1 = 2.|||HI,
Agree with Paul .
select * from <table name> where 1 = 0
Cheers
Gola
Originally posted by Paul Young
in A you are scanning a select range of rows from a clustered index. SQL server is doing a binary search and thus able to eliminate most of the data within a couple of evaluations.
in B you are scanning all rows of a clustered index. Since you are asking SQL server to compare two attributes of a record, each record must be evaluated.
in C you are scanning an internal table.
I personnaly prefer select * from <table name> where 1 = 2.|||Thanks Paul. What you said makes perfect sense. I knew how SQL impelemented indexing, but failed to see the obvious need to skip the indexing and go straight to a table scan and scan each record individually when comparing one field to another.
Gola's example would seem to be more efficient, as would option C or as a friend pointed out, even...
SELECT * FROM Customers WHERE CustomerID IS NULL.
...would work since we are looking at the primary key.
Thanks guys.
Moki|||I don't agree with your friend. You will still be scanning part of the index. If the table doesn't have an index you will be scanning the table. My suggestion will always scan an internal table regardless.
the only diffrence between my suggestion and Gola's is the numbers used to generate a false condition.|||I'll need to think about that last one...maybe I don't understand MSSQL's implementation like I thought. I would have thought the primary key was always indexed and so checking for NULL would be the same as running thru the B-Tree looking for a value.
In either case, how about option C, "SELECT TOP 0...."?|||Yes, in SQL server the primary key will have some type of index. My point was that your friends suggestion will cause a partial index scan when a table has a usable index OR a table scan if no index is present or no existing indexes are present to match your where clause.
Bottom line, C is the best choice of the three.|||Ok, thanks. I thought about it (Option C) and the suggestion from you (and Gola), and think I like y'alls better. Only because I'm questioning the use of "...TOP 0..." and its conformance (or possible lack thereof) to the ANSI-92 standard...just in case that becomes a customer's constraint in the future.
Thanks again.
Optimal SQL
Please excuse what is probably a no-brainer, but here goes.
Is there any difference, in terms of performance or any other pertinent
factor, between:
SELECT * FROM tblCustomers INNER JOIN tblCustomerOrders ON
tblCustomers.fldCustomerID = tblCustomerOrders.fldCustomerID
and
SELECT * FROM tblCustomers, tblCustomerOrders WHERE
tblCustomers.fldCustomerID = tblCustomerOrders.fldCustomerID
I note that if I type the latter into the SQL pane in a Data window,
SQL Server replaces it with the former.
TIA
Edward
--
The reading group's reading group:
http://www.bookgroup.org.ukShould give the same query plan.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||If you examine the execution plans in Query Analyzer you will find the two
statements are identical. For INNER JOINs either syntax is legal and which
you use really comes down to readability and personal preference.
> I note that if I type the latter into the SQL pane in a Data window,
> SQL Server replaces it with the former.
One reason to avoid GUI query tools is that they tend to mess with the
formatting and syntax of your code. My advice is to use a proper query
editor, like Query Analyzer. In the long run you'll find you can write
better code much quicker that way.
--
David Portas
SQL Server MVP
--|||David Portas wrote:
> If you examine the execution plans in Query Analyzer you will find
the two
> statements are identical. For INNER JOINs either syntax is legal and
which
> you use really comes down to readability and personal preference.
> > I note that if I type the latter into the SQL pane in a Data
window,
> > SQL Server replaces it with the former.
> One reason to avoid GUI query tools is that they tend to mess with
the
> formatting and syntax of your code. My advice is to use a proper
query
> editor, like Query Analyzer. In the long run you'll find you can
write
> better code much quicker that way.
Thanks to you and Nigel. I agree with your distress at the way the GUI
tool in SQL Server messes with code - I like to have things laid out
just so, and it's no business of SQL Server to do it's nanny thing.
But that's Microsoft for you, the company that brought us Office
Assistant.
Unfortunately, for very involved outer joins with multiple tables, I'm
just not expert enough to craft the thing in QA, or rather I like to
see a graphical representation of the table relations. But I suppose I
could write it in QA, then paste it into a Data pane and see what the
GUI made of it.
Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk
--
We are what we repeatedly do. Excellence, then, is not an act, but a
habit - Aristotle
Those heights by great men reached and kept
Were not obtained by sudden flight,
But they, while their companions slept
Were toiling upward in the night
- Longfellow
Optimal solution for getting timestamp of last entry
Hi
I'm trying to find the optimal way of getting the timestamp of the last updated entry in an mssql database. A database is updated only about 5 times a minute, how ever a request for the time of the last entry could be around 1 per second. For this reason i was thinking of having a separate table which has a single row which is updated everytime a new entry is updated in the main table. I would then only need a simple SELECT statement and need very little processing power.
Is this the best method, or can you think of any others i could use?
many thanksWhat's the scope? Database wide or table specific?|||single table, single row? a potential bottleneck, but only 5/minute should be no problem, so yeah, that would be the simplest solution
but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easily|||but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easilyHow does SQL Server handle a monotonically decreasing index? Presumably with regular page splits? Like you say low activity will mitigate this somewhat.|||oh dear, i'm in over my head here
what is monotonically? no music? and how is monotonically different from sequentially? from consecutively?
and what's a page split?
sheesh, i should stick to stuff i know|||SELECT MAX(upd_dt) FROM (
SELECT MAX(upd_dt) AS upd_dt FROM tbl1 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl2 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl3 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl4 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl5 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl6) AS XXX
???????????????
You gotta give us more to go on|||Hi, its a pretty simple database, there are only two tables, one with all the data, and the other which will hold the single row with the latest time stamp.
I realise i can SELECT the top1 descending record from the main table, but won't that be more work than simply selecting a single value?
...or is selecting top1 more viable than loosing out on 'bottle necking' when selecting a single row..!|||more work? for you or for the server?
i'll bet if you timed the cpu cycles, it'd be pretty close|||more work? for you or for the server?
i'll bet if you timed the cpu cycles, it'd be pretty close
lol - i don't mind doing the work! - i just want this to be as optimal as possible...|||How many rows in the table?|||oh dear, i'm in over my head here
...
sheesh, i should stick to stuff i knowI don't think so - I think you just sometimes pretend you know less than you do so no one asks you anything remotely resembling a dba question :)|||In the main table, there will be approximately 1000 new rows per day.
I suppose this leads onto another question, are there any limits other than disk space, eg on the cpu that i should be aware of?|||Sorry I meant in total|||well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?|||well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?You won't get close to the sort of volume where you need to start getting canny at 1000 inserts a day.
Agreed with Rudy - just put the column in your table and then select MAX() of that column to get the last update. Index the column. I think I would index ASC but there you go. Triger to update it.
BTW - one table - is it normalised?|||ok - sounds good to me - can i just write this out how i see it working from what you've said above:
1) Index time/date column Ascending
2) When getting the last update, use something similiar to:
SELECT MAX(timeDateStampColumn) as "LastEntry"
FROM table;
BTW - one table - is it normalised?
My application is based around sms text messages, each new row in my table is basically a new text message, there are only a few columns (id, message, mobile number, dateTimeStamp) so no need to normalise. I'm at the designing stages of the project so will certainly be more tables added later on.|||My application is based around sms text messages, each new row in my table is basically a new text message, there are only a few columns (id, message, mobile number, dateTimeStamp) so no need to normalise. That table structure sounds fine although I would disagree and say that the very early stages are absolutely the right time to normalise. I would also strongly recommend you design your complete database before worrying about requirements like "how do I find the time of the last SMS?".|||point taken ;)
thanks everyone for your excellent help (as usual) :D
I'm trying to find the optimal way of getting the timestamp of the last updated entry in an mssql database. A database is updated only about 5 times a minute, how ever a request for the time of the last entry could be around 1 per second. For this reason i was thinking of having a separate table which has a single row which is updated everytime a new entry is updated in the main table. I would then only need a simple SELECT statement and need very little processing power.
Is this the best method, or can you think of any others i could use?
many thanksWhat's the scope? Database wide or table specific?|||single table, single row? a potential bottleneck, but only 5/minute should be no problem, so yeah, that would be the simplest solution
but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easily|||but if this is just for your main table, consider an index on the datetime column descending, so that TOP 1 gets your last update easilyHow does SQL Server handle a monotonically decreasing index? Presumably with regular page splits? Like you say low activity will mitigate this somewhat.|||oh dear, i'm in over my head here
what is monotonically? no music? and how is monotonically different from sequentially? from consecutively?
and what's a page split?
sheesh, i should stick to stuff i know|||SELECT MAX(upd_dt) FROM (
SELECT MAX(upd_dt) AS upd_dt FROM tbl1 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl2 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl3 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl4 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl5 UNION ALL
SELECT MAX(upd_dt) AS upd_dt FROM tbl6) AS XXX
???????????????
You gotta give us more to go on|||Hi, its a pretty simple database, there are only two tables, one with all the data, and the other which will hold the single row with the latest time stamp.
I realise i can SELECT the top1 descending record from the main table, but won't that be more work than simply selecting a single value?
...or is selecting top1 more viable than loosing out on 'bottle necking' when selecting a single row..!|||more work? for you or for the server?
i'll bet if you timed the cpu cycles, it'd be pretty close|||more work? for you or for the server?
i'll bet if you timed the cpu cycles, it'd be pretty close
lol - i don't mind doing the work! - i just want this to be as optimal as possible...|||How many rows in the table?|||oh dear, i'm in over my head here
...
sheesh, i should stick to stuff i knowI don't think so - I think you just sometimes pretend you know less than you do so no one asks you anything remotely resembling a dba question :)|||In the main table, there will be approximately 1000 new rows per day.
I suppose this leads onto another question, are there any limits other than disk space, eg on the cpu that i should be aware of?|||Sorry I meant in total|||well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?|||well theres zero rows at the moment because im in the middle of building the application and setting up the database. But i am expecting approx 1000 new rows a day - not sure what limits are advisable as to how many rows before i need to do something, eg create a new table or whatever?You won't get close to the sort of volume where you need to start getting canny at 1000 inserts a day.
Agreed with Rudy - just put the column in your table and then select MAX() of that column to get the last update. Index the column. I think I would index ASC but there you go. Triger to update it.
BTW - one table - is it normalised?|||ok - sounds good to me - can i just write this out how i see it working from what you've said above:
1) Index time/date column Ascending
2) When getting the last update, use something similiar to:
SELECT MAX(timeDateStampColumn) as "LastEntry"
FROM table;
BTW - one table - is it normalised?
My application is based around sms text messages, each new row in my table is basically a new text message, there are only a few columns (id, message, mobile number, dateTimeStamp) so no need to normalise. I'm at the designing stages of the project so will certainly be more tables added later on.|||My application is based around sms text messages, each new row in my table is basically a new text message, there are only a few columns (id, message, mobile number, dateTimeStamp) so no need to normalise. That table structure sounds fine although I would disagree and say that the very early stages are absolutely the right time to normalise. I would also strongly recommend you design your complete database before worrying about requirements like "how do I find the time of the last SMS?".|||point taken ;)
thanks everyone for your excellent help (as usual) :D
Optimal restore of database
How can I restore Database_1 on MSSQL2K server with minimal effect on working users of Database_2 ?
When I use EM (restore with create), users are getting "General network error" for 5 minutes.Hopefully you're not pulling the backup from across the network. Remember that, restoring does take some taxing on the server resources. However, this should not drop your users.
Take a look at perfmon/netmon to see what's going on.
When I use EM (restore with create), users are getting "General network error" for 5 minutes.Hopefully you're not pulling the backup from across the network. Remember that, restoring does take some taxing on the server resources. However, this should not drop your users.
Take a look at perfmon/netmon to see what's going on.
Optimal Placement of Data/Log Files
What is the best way to setup a server for hosting SQL Sever 2005? If I could get input of type of RAID to use and where to place the files that would be very appreciated.
Thanks,
Todd Sparks
Hey Todd. Well, ideally we'd always recommend RAID 10 if possible for both data and log file LUNs for performance and availability reasons. If that's not possible due to cost, I'd recommend a RAID 1 configuration for your log file and a RAID 5 for data files...ideally, if cost is not prohibitive again RAID 1 for data files would probaby be better than 5.
For a more in depth look at the internals of SQL Server IO see the following paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
In addition, check out the following paper on disk subsystem performance:
http://www.microsoft.com/whdc/device/storage/subsys_perf.mspx
HTH
Optimal Physical Layer config SQL Server on IBM xSeries 345 server
Hi all, I am building a new IBM xSeries 345 server and we plan consolidate
most of the SQL Servers databases on it (~40). I am trying to figure out the
best configuration for the disk subsystem for both reasonable performance and
disk cost savings and reduced down time. The server may have upto 6 X 73.4
GB SCSI disks. Can anyone suggest any ideas?
DISK 1 and
DISK 2 as RAID 1 â' System & Tlog
DISK 3 and
DISK 4 as RAID 1 - DATA
DISK 5 and
DISK 6 as RAID 1 â' DATA
---
DISK 1 and
DISK 2 as RAID 1 System & Tempdb
DISK 3 and
DISK 4 as RAID 1 â' Tlogs
DISK 5 and
DISK 6 as AID 1 â' DATA
---
Thanks."Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:493C572F-C196-4301-B2D7-4AD665B2A48C@.microsoft.com...
> Hi all, I am building a new IBM xSeries 345 server and we plan consolidate
> most of the SQL Servers databases on it (~40). I am trying to figure out
> the
> best configuration for the disk subsystem for both reasonable performance
> and
> disk cost savings and reduced down time. The server may have upto 6 X
> 73.4
> GB SCSI disks. Can anyone suggest any ideas?
> DISK 1 and
> DISK 2 as RAID 1 - System & Tlog
> DISK 3 and
> DISK 4 as RAID 1 - DATA
> DISK 5 and
> DISK 6 as RAID 1 - DATA
> ---
> DISK 1 and
> DISK 2 as RAID 1 System & Tempdb
> DISK 3 and
> DISK 4 as RAID 1 - Tlogs
> DISK 5 and
> DISK 6 as AID 1 - DATA
> ---
>
Physical optimization like this mostly involves aranging the spindles to
maximize the number of spindes used and minimize the contention for
spindles. However with 40 databases and 40 different workloads on the
server you will probably not be able to optimize the physical layout much.
BTW, I wouldn't consolodate onto the x345 if I could help it because it's a
32-bit machine. The newer x346 can run 64bit Windows and SQL Server 2005.
This will let you use much more memory, and minimize how much your workload
utilizes the disks.
David|||I second that emotion.
I'd also like to add that many writes wind up being cached and first written
into the transaction log... the point being that you could probably do all
your data on one RAID-5 array and have more actual space as a result or keep
one remaining disk as a spare.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O4b2YHzJGHA.1676@.TK2MSFTNGP09.phx.gbl...
>
> Physical optimization like this mostly involves aranging the spindles to
> maximize the number of spindes used and minimize the contention for
> spindles. However with 40 databases and 40 different workloads on the
> server you will probably not be able to optimize the physical layout much.
>
most of the SQL Servers databases on it (~40). I am trying to figure out the
best configuration for the disk subsystem for both reasonable performance and
disk cost savings and reduced down time. The server may have upto 6 X 73.4
GB SCSI disks. Can anyone suggest any ideas?
DISK 1 and
DISK 2 as RAID 1 â' System & Tlog
DISK 3 and
DISK 4 as RAID 1 - DATA
DISK 5 and
DISK 6 as RAID 1 â' DATA
---
DISK 1 and
DISK 2 as RAID 1 System & Tempdb
DISK 3 and
DISK 4 as RAID 1 â' Tlogs
DISK 5 and
DISK 6 as AID 1 â' DATA
---
Thanks."Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:493C572F-C196-4301-B2D7-4AD665B2A48C@.microsoft.com...
> Hi all, I am building a new IBM xSeries 345 server and we plan consolidate
> most of the SQL Servers databases on it (~40). I am trying to figure out
> the
> best configuration for the disk subsystem for both reasonable performance
> and
> disk cost savings and reduced down time. The server may have upto 6 X
> 73.4
> GB SCSI disks. Can anyone suggest any ideas?
> DISK 1 and
> DISK 2 as RAID 1 - System & Tlog
> DISK 3 and
> DISK 4 as RAID 1 - DATA
> DISK 5 and
> DISK 6 as RAID 1 - DATA
> ---
> DISK 1 and
> DISK 2 as RAID 1 System & Tempdb
> DISK 3 and
> DISK 4 as RAID 1 - Tlogs
> DISK 5 and
> DISK 6 as AID 1 - DATA
> ---
>
Physical optimization like this mostly involves aranging the spindles to
maximize the number of spindes used and minimize the contention for
spindles. However with 40 databases and 40 different workloads on the
server you will probably not be able to optimize the physical layout much.
BTW, I wouldn't consolodate onto the x345 if I could help it because it's a
32-bit machine. The newer x346 can run 64bit Windows and SQL Server 2005.
This will let you use much more memory, and minimize how much your workload
utilizes the disks.
David|||I second that emotion.
I'd also like to add that many writes wind up being cached and first written
into the transaction log... the point being that you could probably do all
your data on one RAID-5 array and have more actual space as a result or keep
one remaining disk as a spare.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O4b2YHzJGHA.1676@.TK2MSFTNGP09.phx.gbl...
>
> Physical optimization like this mostly involves aranging the spindles to
> maximize the number of spindes used and minimize the contention for
> spindles. However with 40 databases and 40 different workloads on the
> server you will probably not be able to optimize the physical layout much.
>
Optimal location for SQL USR and PWD
We have created an application in VB6 where mulitple clients access a SQL
Server 2000 database. The application uses the 3tier model. We currently hav
e
20 users, but this will probably increase exponentialy. All users uses the
same SQL username and password and we handle the security in the application
.
I need advice on where and how to store the sql login username and password.
Thank you.If you are using three tiers, the safest place for the USR and PWD is in the
middle tier.
Assuming a COM+ object, you get the administrator to assign the account and
password to the middle tier. Assuming that no one has direct access to that
machine, then the password is pretty safe.
If you manage it all from the client tier, then you must store the passwords
somewhere, encrypt it somehow, and protect the knowledge of how to decrypt
it. There are several routines available out there (try GOOGLE) that you
can use for the encrypt / decrypt.
Russell Fields
"Hugo" <Hugo@.discussions.microsoft.com> wrote in message
news:7137E0FF-D18F-4FBB-B167-EB63C93FEC44@.microsoft.com...
> We have created an application in VB6 where mulitple clients access a SQL
> Server 2000 database. The application uses the 3tier model. We currently
have
> 20 users, but this will probably increase exponentialy. All users uses the
> same SQL username and password and we handle the security in the
application.
> I need advice on where and how to store the sql login username and
password.
> Thank you.
>|||We have not moved to COM+ yet. I wanted to know if there is any standard
practice for client apps.
I will now the registry with ecryption.
Thank you.
Server 2000 database. The application uses the 3tier model. We currently hav
e
20 users, but this will probably increase exponentialy. All users uses the
same SQL username and password and we handle the security in the application
.
I need advice on where and how to store the sql login username and password.
Thank you.If you are using three tiers, the safest place for the USR and PWD is in the
middle tier.
Assuming a COM+ object, you get the administrator to assign the account and
password to the middle tier. Assuming that no one has direct access to that
machine, then the password is pretty safe.
If you manage it all from the client tier, then you must store the passwords
somewhere, encrypt it somehow, and protect the knowledge of how to decrypt
it. There are several routines available out there (try GOOGLE) that you
can use for the encrypt / decrypt.
Russell Fields
"Hugo" <Hugo@.discussions.microsoft.com> wrote in message
news:7137E0FF-D18F-4FBB-B167-EB63C93FEC44@.microsoft.com...
> We have created an application in VB6 where mulitple clients access a SQL
> Server 2000 database. The application uses the 3tier model. We currently
have
> 20 users, but this will probably increase exponentialy. All users uses the
> same SQL username and password and we handle the security in the
application.
> I need advice on where and how to store the sql login username and
password.
> Thank you.
>|||We have not moved to COM+ yet. I wanted to know if there is any standard
practice for client apps.
I will now the registry with ecryption.
Thank you.
optimal location for database files on SAN?
Hi, we have 2 sql servers that will have all their system databases/user
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, john
i will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>
|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>
|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, john
i will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>
|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>
|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
optimal location for database files on SAN?
Hi, we have 2 sql servers that will have all their system databases/user
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, johni will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Hi, we have 2 sql servers that will have all their system databases/user
> > databases/log files located to a san. what is the best configuration?
> >
> > note: sql1 performs transactional replication to sql2 (which is used for
> > reporting):
> >
> > we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > if I could get access to another RAID 1 how would this sound:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 1: tempdb (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > Would both servers share the same tempdb? or would there be two instances?
> >
> > since sql1 replicates to sql2, having all the logs on the same RAID 1
> > would increase replication speed?
> >
> > Any help most appreciated!
> > thanks, john
> >
>
>|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Hi, we have 2 sql servers that will have all their system databases/user
> > databases/log files located to a san. what is the best configuration?
> >
> > note: sql1 performs transactional replication to sql2 (which is used for
> > reporting):
> >
> > we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > if I could get access to another RAID 1 how would this sound:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 1: tempdb (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > Would both servers share the same tempdb? or would there be two instances?
> >
> > since sql1 replicates to sql2, having all the logs on the same RAID 1
> > would increase replication speed?
> >
> > Any help most appreciated!
> > thanks, john
> >
>
>|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, johni will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Hi, we have 2 sql servers that will have all their system databases/user
> > databases/log files located to a san. what is the best configuration?
> >
> > note: sql1 performs transactional replication to sql2 (which is used for
> > reporting):
> >
> > we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > if I could get access to another RAID 1 how would this sound:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 1: tempdb (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > Would both servers share the same tempdb? or would there be two instances?
> >
> > since sql1 replicates to sql2, having all the logs on the same RAID 1
> > would increase replication speed?
> >
> > Any help most appreciated!
> > thanks, john
> >
>
>|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Hi, we have 2 sql servers that will have all their system databases/user
> > databases/log files located to a san. what is the best configuration?
> >
> > note: sql1 performs transactional replication to sql2 (which is used for
> > reporting):
> >
> > we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > if I could get access to another RAID 1 how would this sound:
> >
> > RAID 1: sql2 user + system data files
> > RAID 1: Logs (from both sql1 and sql2)
> > RAID 1: tempdb (from both sql1 and sql2)
> > RAID 5: sql1 user + system data files
> >
> > Would both servers share the same tempdb? or would there be two instances?
> >
> > since sql1 replicates to sql2, having all the logs on the same RAID 1
> > would increase replication speed?
> >
> > Any help most appreciated!
> > thanks, john
> >
>
>|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
optimal location for database files on SAN?
Hi, we have 2 sql servers that will have all their system databases/user
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, johni will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
databases/log files located to a san. what is the best configuration?
note: sql1 performs transactional replication to sql2 (which is used for
reporting):
we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 5: sql1 user + system data files
if I could get access to another RAID 1 how would this sound:
RAID 1: sql2 user + system data files
RAID 1: Logs (from both sql1 and sql2)
RAID 1: tempdb (from both sql1 and sql2)
RAID 5: sql1 user + system data files
Would both servers share the same tempdb? or would there be two instances?
since sql1 replicates to sql2, having all the logs on the same RAID 1 would
increase replication speed?
Any help most appreciated!
thanks, johni will have access to initially 9 hdd's to build my config, but i might
possibly get a hold of more. any help most appreciated! ciao john
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>|||I am not sure if I hit enter or lost the page. I tried an earlier response
Anyway, I suggest Mirroring your OS drive and a Binaries Drive witht the SAN
used for all the data files.
I like mirroring, but that is a bias others will contest.
Without knowing your SAN, I can't say much more. Some SANs don't give you
enough control to worry about RAID levels.
The real question is how many LUNs to the SAN?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>|||The primary question for me is how many LUNs do you have.
I like Raid 0+1 (mirroring) rather than Raid-5 and all. Personal bias.
I would look to configure as follows:
Logical C: Mirror, OS files
Logical D: Mirror, Application (including SS) binaries
Logical E; SAN-data and log files.
This presumes 1 LUN, probably fiber, to the SAN.
As I said, I like mirroring. Without knowing which SAN you are using,
though, it is difficult to give advice (foot in mouth?) there. Some SANS do
not give you
--
Joseph R.P. Maloney, CSP,CCP,CDP
"john r" wrote:
> i will have access to initially 9 hdd's to build my config, but i might
> possibly get a hold of more. any help most appreciated! ciao john
>
> "john r" <johnr@.trailer.com> wrote in message
> news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
>|||I believe it is going to depend on the size of your databases and what they
are used for. Are they creating a lot of temp tables in the temp database?
if so, then temp needs two RAID1 volumes, 1 for data, 1 for logs.
Also, whatever config you end up with, separate the log files from the data
files.
Another thing to consider if you have it available is to use RAID1 or RAID10
on your databases. RAID5 is too expensive on the write operation for your
heavily used databases.
I hope this helps.
"john r" <johnr@.trailer.com> wrote in message
news:uSqTyAS6FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Hi, we have 2 sql servers that will have all their system databases/user
> databases/log files located to a san. what is the best configuration?
> note: sql1 performs transactional replication to sql2 (which is used for
> reporting):
> we have 2 x RAID 1 and 1 x RAID 5. I was thinking:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> if I could get access to another RAID 1 how would this sound:
> RAID 1: sql2 user + system data files
> RAID 1: Logs (from both sql1 and sql2)
> RAID 1: tempdb (from both sql1 and sql2)
> RAID 5: sql1 user + system data files
> Would both servers share the same tempdb? or would there be two instances?
> since sql1 replicates to sql2, having all the logs on the same RAID 1
> would increase replication speed?
> Any help most appreciated!
> thanks, john
>
Optimal installation on Win 2003
Hi
I'm fairly new to this, so bare with me...
I have to make a new installation of an MS SQL 2000 EE on a Windows 2003 Std. Edt.
HW:
-------
Dual Xeon 2,4 + 1 GB Ecc
1 x 32 MB Adaptec 2100S RAID Controller
2 x 18 GB 10K HD
4 x 18 GB 15K HD
-------
So far I have made following configuration...
-------
2 x 18 GB 10K HD / RAID 1
- C:\OS
- D:\MSSQL program files + System DB's (Master, pubs ect.)
4 x 18 GB 15K HD / RAID 5
- E:\TempDB
- F:\Data + Logs
-------
But I'm not sure that this is the optimal configuration, and I'm willing to start all over :)
So my q's are......
-------
Which RAID configuration would you suggest?
Which partitions on the raids would you suggest?
Which usage would you assign the various partitions?
How do I move the system and temp db's?
-------
Thanx!
Regards,
Taras Bredel dkHi Taras,
I just had our systems guys create a SQL 2000 box on Win 2k3. I have a raid 5 (with 2 HD) on a dual p3 with 1 gig of ram.
Let me just say that I can't remember having so many problems with a SQL box. Granted anyone using Win 2k3 in a production environment should have expected some early adopter problems, I didn't think my problem would be so frustrating.
My problem is that I can't get the SQL box to listen on any ports. By default, SQL server should listen on port 1433. I can plainly see that Network Utility has TCP/IP enabled and the default port is 1433.
I'm this close to rebuilding that box to a win 2k server.
The only thing really stopping me now is debating what is less work - moving the data - or trying to fix the SQL server.|||Hi Lee
Well it could install it on a W2K, but benchmarks shows that the performance gain on W2K3 should be considerably large.
So my advice to you is trouble shoot the W2K3 installation.
//Taras|||Taras,
Yes. That was the main reason that I wanted it on a 2k3 machine. The data is to be served internally on a quasi realtime basis. This is not a real-time system - but I would like as much performance as possible.
Once you get the machine up and running tell me if you had any problems.
Thanks.
I'm fairly new to this, so bare with me...
I have to make a new installation of an MS SQL 2000 EE on a Windows 2003 Std. Edt.
HW:
-------
Dual Xeon 2,4 + 1 GB Ecc
1 x 32 MB Adaptec 2100S RAID Controller
2 x 18 GB 10K HD
4 x 18 GB 15K HD
-------
So far I have made following configuration...
-------
2 x 18 GB 10K HD / RAID 1
- C:\OS
- D:\MSSQL program files + System DB's (Master, pubs ect.)
4 x 18 GB 15K HD / RAID 5
- E:\TempDB
- F:\Data + Logs
-------
But I'm not sure that this is the optimal configuration, and I'm willing to start all over :)
So my q's are......
-------
Which RAID configuration would you suggest?
Which partitions on the raids would you suggest?
Which usage would you assign the various partitions?
How do I move the system and temp db's?
-------
Thanx!
Regards,
Taras Bredel dkHi Taras,
I just had our systems guys create a SQL 2000 box on Win 2k3. I have a raid 5 (with 2 HD) on a dual p3 with 1 gig of ram.
Let me just say that I can't remember having so many problems with a SQL box. Granted anyone using Win 2k3 in a production environment should have expected some early adopter problems, I didn't think my problem would be so frustrating.
My problem is that I can't get the SQL box to listen on any ports. By default, SQL server should listen on port 1433. I can plainly see that Network Utility has TCP/IP enabled and the default port is 1433.
I'm this close to rebuilding that box to a win 2k server.
The only thing really stopping me now is debating what is less work - moving the data - or trying to fix the SQL server.|||Hi Lee
Well it could install it on a W2K, but benchmarks shows that the performance gain on W2K3 should be considerably large.
So my advice to you is trouble shoot the W2K3 installation.
//Taras|||Taras,
Yes. That was the main reason that I wanted it on a 2k3 machine. The data is to be served internally on a quasi realtime basis. This is not a real-time system - but I would like as much performance as possible.
Once you get the machine up and running tell me if you had any problems.
Thanks.
Optimal Drive Performance
I am getting ready to setup our first SQL server in house. I have been
looking into hardware requirements for drive performance and memory.
The specs that were sent to me by a vendor sugested that I use 768MB of
memory in the SQL server. I had another vendor laugh and say he would
not install less than 2GB but recommended 4GB. Where is a good place to
be as far as memory was is concearned.
For Drives. One vendor recommended installing
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
The configuration they gave only allows for one RAID controller to
access the cage. Because of this I don't see a benifit and would all six
drives in a RAID 5 be better performance?
Another Vendor suggested:
2 x 36GB RAID 1 for OS
2 x 36GB RAID 1 for Data
2 x 36GB RAID 1 for Logging
3 x RAID controller channels for performance
They suggested that this gives the best performance for logging because
logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
be better performance for writing. RAID 0 wouldn't give me redundancy
but would give very fast write performance.
Since the first Vendor recommended a Prolient ML350, It is not possible
for me to do the second suggestion since the drive cage can not be
segregated for the 3 channel RAID adapter.
I have a choice to either go with the first suggestion, return the
server and upgrade to a DL380 to allow for drive segregation or my
hybrid of a configuration:
Add a 2 drive Drive Cage in the open 5.25 slots.
Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
Run the OS + Logging in the new Drive Cage using RAID 1.
Any comments or suggestions. This is my first SQL implementation.
Thank You,
John JakusMemory is too cheap these days to not have enough. How large do you expect
your DB to get and of that how much of the data would be read or written to
each day? How many transactions per second do you expect to do? Will you
read large amounts of data at a time or small amounts?
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:eTpadQ56DHA.3704@.tk2msftngp13.phx.gbl...
I have no idea. This is for an implementation of Axapta. They never gave
me estimates on how many transactions will be performed. I just know it
will slowly ramp up. I know it's better to over build a system like this
because it's easier then upgrading later. I just want to know which
configuration will perform better with an SQL server. Since I am new to
SQL Server.
Sorry for being so vague but this is all I have right now and they are
in a hurry to implement. I just want to make it the most robust that I can.
Thanks,
John Jakus|||John
Take a look at this link you'll find lots of useful info and tips.
http://www.sql-server-performance.com
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
can.
suite your needs without that kind of information. So with this in mind I
would opt for the first configuration:
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
This will separate the log files from he data which is important under heavy
write situations. A RAID 0+1 is good, the only thing is it only has 4
disks. This makes for only 36GB of usable space and as always with database
the more disks the better, not the size. Hope that is going to be enough.
If not and you are stuck with that drive configuration maybe you can use
36GB drives instead of 18GB for the Raid 0+1. since they didn't provide
specs it is most likely not too intensive of an application and this should
be fine. Definitely go with more memory than 768 though. Again how much
depends on the size and use of the db but 2GB should do it.
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
can.
Memory - as suggested, memory is relatively inexpensive these days and datab
ase servers are generally pretty memory and disk intensive. It really depend
s on the usage profile and DB size but 4GB is not really *that* expensive.
Disk config: Disk config is very important as it can make a major difference
to the performance of the database.
Here are some general recommendations but again this depends on the app and
how it will be used.
RAID 5: Good fault tolerance, good read performance, BAD write performance (
parity has to be written every time the disk is written to). Use this if the
DB is read intensive with few writes. RAID 5 offers good fault tolerance at
a relatively low cost.
RAID 1: Good fault tolerance, and good performance for sequential writes (su
ch as transaction logs). Expensive because you only have effective use of ha
lf the disk.
RAID 0: RAID 0 stripes data across multiple disks. This offers excellent per
formance, but NO fault tolerance.
RAID 1+0 and RAID 0+1: These two must not be confused - people use the terms
interchangeably but they are very different.
RAID 1+0 is the striping of data across multiple RAID 1 mirrors. For example
if you have 8 disks, it would stripe data across 4 mirrors. This offers exc
ellent performance, excellent fault tolerance but a not-so-excellent bank ba
lance!
RAID 0+1 is the mirroring of two stripe sets. In our scenario of 8 disks, yo
u would have 2 striped sets (RAID 0) of 4 disks each, that are in turn mirro
red. This also offers good performance and fault tolerance (as RAID 1+0) but
this will be degraded in t
he event of disk failures (much more than RAID 1+0).
So if faced with the choice between RAID 1+0 and RAID 0+1 I would always cho
ose RAID 1+0.
An example of a high-end disk spec would be:
OS: 2 x 18.2GB RAID1
Logs: 2 x 36.4GB RAID1 - or 4 x 36.4 RAID 1+0
Data: 8 x 36.4GB RAID1+0 (this could be any amount of disks in multiples of
2, the total number constrained by the storage device)
This may or may not be an overkill depending on the actual app.
In terms of controllers, separate controllers sounds like a good idea - agai
n its down to the cost/benefit of doing this.
I hope this helps as a very general guideline.
Regards,
Rob
looking into hardware requirements for drive performance and memory.
The specs that were sent to me by a vendor sugested that I use 768MB of
memory in the SQL server. I had another vendor laugh and say he would
not install less than 2GB but recommended 4GB. Where is a good place to
be as far as memory was is concearned.
For Drives. One vendor recommended installing
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
The configuration they gave only allows for one RAID controller to
access the cage. Because of this I don't see a benifit and would all six
drives in a RAID 5 be better performance?
Another Vendor suggested:
2 x 36GB RAID 1 for OS
2 x 36GB RAID 1 for Data
2 x 36GB RAID 1 for Logging
3 x RAID controller channels for performance
They suggested that this gives the best performance for logging because
logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
be better performance for writing. RAID 0 wouldn't give me redundancy
but would give very fast write performance.
Since the first Vendor recommended a Prolient ML350, It is not possible
for me to do the second suggestion since the drive cage can not be
segregated for the 3 channel RAID adapter.
I have a choice to either go with the first suggestion, return the
server and upgrade to a DL380 to allow for drive segregation or my
hybrid of a configuration:
Add a 2 drive Drive Cage in the open 5.25 slots.
Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
Run the OS + Logging in the new Drive Cage using RAID 1.
Any comments or suggestions. This is my first SQL implementation.
Thank You,
John JakusMemory is too cheap these days to not have enough. How large do you expect
your DB to get and of that how much of the data would be read or written to
each day? How many transactions per second do you expect to do? Will you
read large amounts of data at a time or small amounts?
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:eTpadQ56DHA.3704@.tk2msftngp13.phx.gbl...
quote:|||Andrew J. Kelly wrote:
> I am getting ready to setup our first SQL server in house. I have been
> looking into hardware requirements for drive performance and memory.
> The specs that were sent to me by a vendor sugested that I use 768MB of
> memory in the SQL server. I had another vendor laugh and say he would
> not install less than 2GB but recommended 4GB. Where is a good place to
> be as far as memory was is concearned.
> For Drives. One vendor recommended installing
> 2 x 18GB RAID 1 for OS and SQL logging.
> 4 x 18GB RAID 0+1 for Data
> The configuration they gave only allows for one RAID controller to
> access the cage. Because of this I don't see a benifit and would all six
> drives in a RAID 5 be better performance?
> Another Vendor suggested:
> 2 x 36GB RAID 1 for OS
> 2 x 36GB RAID 1 for Data
> 2 x 36GB RAID 1 for Logging
> 3 x RAID controller channels for performance
> They suggested that this gives the best performance for logging because
> logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
> be better performance for writing. RAID 0 wouldn't give me redundancy
> but would give very fast write performance.
> Since the first Vendor recommended a Prolient ML350, It is not possible
> for me to do the second suggestion since the drive cage can not be
> segregated for the 3 channel RAID adapter.
> I have a choice to either go with the first suggestion, return the
> server and upgrade to a DL380 to allow for drive segregation or my
> hybrid of a configuration:
> Add a 2 drive Drive Cage in the open 5.25 slots.
> Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
> Run the OS + Logging in the new Drive Cage using RAID 1.
> Any comments or suggestions. This is my first SQL implementation.
> Thank You,
> John Jakus
quote:
> Memory is too cheap these days to not have enough. How large do you expec
t
> your DB to get and of that how much of the data would be read or written t
o
> each day? How many transactions per second do you expect to do? Will you
> read large amounts of data at a time or small amounts?
>
I have no idea. This is for an implementation of Axapta. They never gave
me estimates on how many transactions will be performed. I just know it
will slowly ramp up. I know it's better to over build a system like this
because it's easier then upgrading later. I just want to know which
configuration will perform better with an SQL server. Since I am new to
SQL Server.
Sorry for being so vague but this is all I have right now and they are
in a hurry to implement. I just want to make it the most robust that I can.
Thanks,
John Jakus|||John
Take a look at this link you'll find lots of useful info and tips.
http://www.sql-server-performance.com
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
quote:
> Andrew J. Kelly wrote:
expect[QUOTE]
to[QUOTE]
you[QUOTE]
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
quote:|||Well it's imposable to tell if any of those configurations will ultimately
> Thanks,
> John Jakus
suite your needs without that kind of information. So with this in mind I
would opt for the first configuration:
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
This will separate the log files from he data which is important under heavy
write situations. A RAID 0+1 is good, the only thing is it only has 4
disks. This makes for only 36GB of usable space and as always with database
the more disks the better, not the size. Hope that is going to be enough.
If not and you are stuck with that drive configuration maybe you can use
36GB drives instead of 18GB for the Raid 0+1. since they didn't provide
specs it is most likely not too intensive of an application and this should
be fine. Definitely go with more memory than 768 though. Again how much
depends on the size and use of the db but 2GB should do it.
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
quote:
> Andrew J. Kelly wrote:
expect[QUOTE]
to[QUOTE]
you[QUOTE]
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
quote:|||To try and cover your issues separately:
> Thanks,
> John Jakus
Memory - as suggested, memory is relatively inexpensive these days and datab
ase servers are generally pretty memory and disk intensive. It really depend
s on the usage profile and DB size but 4GB is not really *that* expensive.
Disk config: Disk config is very important as it can make a major difference
to the performance of the database.
Here are some general recommendations but again this depends on the app and
how it will be used.
RAID 5: Good fault tolerance, good read performance, BAD write performance (
parity has to be written every time the disk is written to). Use this if the
DB is read intensive with few writes. RAID 5 offers good fault tolerance at
a relatively low cost.
RAID 1: Good fault tolerance, and good performance for sequential writes (su
ch as transaction logs). Expensive because you only have effective use of ha
lf the disk.
RAID 0: RAID 0 stripes data across multiple disks. This offers excellent per
formance, but NO fault tolerance.
RAID 1+0 and RAID 0+1: These two must not be confused - people use the terms
interchangeably but they are very different.
RAID 1+0 is the striping of data across multiple RAID 1 mirrors. For example
if you have 8 disks, it would stripe data across 4 mirrors. This offers exc
ellent performance, excellent fault tolerance but a not-so-excellent bank ba
lance!
RAID 0+1 is the mirroring of two stripe sets. In our scenario of 8 disks, yo
u would have 2 striped sets (RAID 0) of 4 disks each, that are in turn mirro
red. This also offers good performance and fault tolerance (as RAID 1+0) but
this will be degraded in t
he event of disk failures (much more than RAID 1+0).
So if faced with the choice between RAID 1+0 and RAID 0+1 I would always cho
ose RAID 1+0.
An example of a high-end disk spec would be:
OS: 2 x 18.2GB RAID1
Logs: 2 x 36.4GB RAID1 - or 4 x 36.4 RAID 1+0
Data: 8 x 36.4GB RAID1+0 (this could be any amount of disks in multiples of
2, the total number constrained by the storage device)
This may or may not be an overkill depending on the actual app.
In terms of controllers, separate controllers sounds like a good idea - agai
n its down to the cost/benefit of doing this.
I hope this helps as a very general guideline.
Regards,
Rob
Optimal Drive Performance
I am getting ready to setup our first SQL server in house. I have been
looking into hardware requirements for drive performance and memory.
The specs that were sent to me by a vendor sugested that I use 768MB of
memory in the SQL server. I had another vendor laugh and say he would
not install less than 2GB but recommended 4GB. Where is a good place to
be as far as memory was is concearned.
For Drives. One vendor recommended installing
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
The configuration they gave only allows for one RAID controller to
access the cage. Because of this I don't see a benifit and would all six
drives in a RAID 5 be better performance?
Another Vendor suggested:
2 x 36GB RAID 1 for OS
2 x 36GB RAID 1 for Data
2 x 36GB RAID 1 for Logging
3 x RAID controller channels for performance
They suggested that this gives the best performance for logging because
logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
be better performance for writing. RAID 0 wouldn't give me redundancy
but would give very fast write performance.
Since the first Vendor recommended a Prolient ML350, It is not possible
for me to do the second suggestion since the drive cage can not be
segregated for the 3 channel RAID adapter.
I have a choice to either go with the first suggestion, return the
server and upgrade to a DL380 to allow for drive segregation or my
hybrid of a configuration:
Add a 2 drive Drive Cage in the open 5.25 slots.
Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
Run the OS + Logging in the new Drive Cage using RAID 1.
Any comments or suggestions. This is my first SQL implementation.
Thank You,
John JakusMemory is too cheap these days to not have enough. How large do you expect
your DB to get and of that how much of the data would be read or written to
each day? How many transactions per second do you expect to do? Will you
read large amounts of data at a time or small amounts?
--
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:eTpadQ56DHA.3704@.tk2msftngp13.phx.gbl...
> I am getting ready to setup our first SQL server in house. I have been
> looking into hardware requirements for drive performance and memory.
> The specs that were sent to me by a vendor sugested that I use 768MB of
> memory in the SQL server. I had another vendor laugh and say he would
> not install less than 2GB but recommended 4GB. Where is a good place to
> be as far as memory was is concearned.
> For Drives. One vendor recommended installing
> 2 x 18GB RAID 1 for OS and SQL logging.
> 4 x 18GB RAID 0+1 for Data
> The configuration they gave only allows for one RAID controller to
> access the cage. Because of this I don't see a benifit and would all six
> drives in a RAID 5 be better performance?
> Another Vendor suggested:
> 2 x 36GB RAID 1 for OS
> 2 x 36GB RAID 1 for Data
> 2 x 36GB RAID 1 for Logging
> 3 x RAID controller channels for performance
> They suggested that this gives the best performance for logging because
> logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
> be better performance for writing. RAID 0 wouldn't give me redundancy
> but would give very fast write performance.
> Since the first Vendor recommended a Prolient ML350, It is not possible
> for me to do the second suggestion since the drive cage can not be
> segregated for the 3 channel RAID adapter.
> I have a choice to either go with the first suggestion, return the
> server and upgrade to a DL380 to allow for drive segregation or my
> hybrid of a configuration:
> Add a 2 drive Drive Cage in the open 5.25 slots.
> Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
> Run the OS + Logging in the new Drive Cage using RAID 1.
> Any comments or suggestions. This is my first SQL implementation.
> Thank You,
> John Jakus|||Andrew J. Kelly wrote:
> Memory is too cheap these days to not have enough. How large do you expect
> your DB to get and of that how much of the data would be read or written to
> each day? How many transactions per second do you expect to do? Will you
> read large amounts of data at a time or small amounts?
>
I have no idea. This is for an implementation of Axapta. They never gave
me estimates on how many transactions will be performed. I just know it
will slowly ramp up. I know it's better to over build a system like this
because it's easier then upgrading later. I just want to know which
configuration will perform better with an SQL server. Since I am new to
SQL Server.
Sorry for being so vague but this is all I have right now and they are
in a hurry to implement. I just want to make it the most robust that I can.
Thanks,
John Jakus|||John
Take a look at this link you'll find lots of useful info and tips.
http://www.sql-server-performance.com
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> > Memory is too cheap these days to not have enough. How large do you
expect
> > your DB to get and of that how much of the data would be read or written
to
> > each day? How many transactions per second do you expect to do? Will
you
> > read large amounts of data at a time or small amounts?
> >
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
> Thanks,
> John Jakus|||Well it's imposable to tell if any of those configurations will ultimately
suite your needs without that kind of information. So with this in mind I
would opt for the first configuration:
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
This will separate the log files from he data which is important under heavy
write situations. A RAID 0+1 is good, the only thing is it only has 4
disks. This makes for only 36GB of usable space and as always with database
the more disks the better, not the size. Hope that is going to be enough.
If not and you are stuck with that drive configuration maybe you can use
36GB drives instead of 18GB for the Raid 0+1. since they didn't provide
specs it is most likely not too intensive of an application and this should
be fine. Definitely go with more memory than 768 though. Again how much
depends on the size and use of the db but 2GB should do it.
--
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> > Memory is too cheap these days to not have enough. How large do you
expect
> > your DB to get and of that how much of the data would be read or written
to
> > each day? How many transactions per second do you expect to do? Will
you
> > read large amounts of data at a time or small amounts?
> >
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
> Thanks,
> John Jakus|||To try and cover your issues separately
Memory - as suggested, memory is relatively inexpensive these days and database servers are generally pretty memory and disk intensive. It really depends on the usage profile and DB size but 4GB is not really *that* expensive
Disk config: Disk config is very important as it can make a major difference to the performance of the database
Here are some general recommendations but again this depends on the app and how it will be used
RAID 5: Good fault tolerance, good read performance, BAD write performance (parity has to be written every time the disk is written to). Use this if the DB is read intensive with few writes. RAID 5 offers good fault tolerance at a relatively low cost
RAID 1: Good fault tolerance, and good performance for sequential writes (such as transaction logs). Expensive because you only have effective use of half the disk
RAID 0: RAID 0 stripes data across multiple disks. This offers excellent performance, but NO fault tolerance
RAID 1+0 and RAID 0+1: These two must not be confused - people use the terms interchangeably but they are very different.
RAID 1+0 is the striping of data across multiple RAID 1 mirrors. For example if you have 8 disks, it would stripe data across 4 mirrors. This offers excellent performance, excellent fault tolerance but a not-so-excellent bank balance
RAID 0+1 is the mirroring of two stripe sets. In our scenario of 8 disks, you would have 2 striped sets (RAID 0) of 4 disks each, that are in turn mirrored. This also offers good performance and fault tolerance (as RAID 1+0) but this will be degraded in the event of disk failures (much more than RAID 1+0)
So if faced with the choice between RAID 1+0 and RAID 0+1 I would always choose RAID 1+0
An example of a high-end disk spec would be
OS: 2 x 18.2GB RAID
Logs: 2 x 36.4GB RAID1 - or 4 x 36.4 RAID 1+
Data: 8 x 36.4GB RAID1+0 (this could be any amount of disks in multiples of 2, the total number constrained by the storage device
This may or may not be an overkill depending on the actual app
In terms of controllers, separate controllers sounds like a good idea - again its down to the cost/benefit of doing this
I hope this helps as a very general guideline
Regards
Ro
looking into hardware requirements for drive performance and memory.
The specs that were sent to me by a vendor sugested that I use 768MB of
memory in the SQL server. I had another vendor laugh and say he would
not install less than 2GB but recommended 4GB. Where is a good place to
be as far as memory was is concearned.
For Drives. One vendor recommended installing
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
The configuration they gave only allows for one RAID controller to
access the cage. Because of this I don't see a benifit and would all six
drives in a RAID 5 be better performance?
Another Vendor suggested:
2 x 36GB RAID 1 for OS
2 x 36GB RAID 1 for Data
2 x 36GB RAID 1 for Logging
3 x RAID controller channels for performance
They suggested that this gives the best performance for logging because
logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
be better performance for writing. RAID 0 wouldn't give me redundancy
but would give very fast write performance.
Since the first Vendor recommended a Prolient ML350, It is not possible
for me to do the second suggestion since the drive cage can not be
segregated for the 3 channel RAID adapter.
I have a choice to either go with the first suggestion, return the
server and upgrade to a DL380 to allow for drive segregation or my
hybrid of a configuration:
Add a 2 drive Drive Cage in the open 5.25 slots.
Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
Run the OS + Logging in the new Drive Cage using RAID 1.
Any comments or suggestions. This is my first SQL implementation.
Thank You,
John JakusMemory is too cheap these days to not have enough. How large do you expect
your DB to get and of that how much of the data would be read or written to
each day? How many transactions per second do you expect to do? Will you
read large amounts of data at a time or small amounts?
--
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:eTpadQ56DHA.3704@.tk2msftngp13.phx.gbl...
> I am getting ready to setup our first SQL server in house. I have been
> looking into hardware requirements for drive performance and memory.
> The specs that were sent to me by a vendor sugested that I use 768MB of
> memory in the SQL server. I had another vendor laugh and say he would
> not install less than 2GB but recommended 4GB. Where is a good place to
> be as far as memory was is concearned.
> For Drives. One vendor recommended installing
> 2 x 18GB RAID 1 for OS and SQL logging.
> 4 x 18GB RAID 0+1 for Data
> The configuration they gave only allows for one RAID controller to
> access the cage. Because of this I don't see a benifit and would all six
> drives in a RAID 5 be better performance?
> Another Vendor suggested:
> 2 x 36GB RAID 1 for OS
> 2 x 36GB RAID 1 for Data
> 2 x 36GB RAID 1 for Logging
> 3 x RAID controller channels for performance
> They suggested that this gives the best performance for logging because
> logging takes the most IO. If that is the case wouldn't RAID 5 or RAID 0
> be better performance for writing. RAID 0 wouldn't give me redundancy
> but would give very fast write performance.
> Since the first Vendor recommended a Prolient ML350, It is not possible
> for me to do the second suggestion since the drive cage can not be
> segregated for the 3 channel RAID adapter.
> I have a choice to either go with the first suggestion, return the
> server and upgrade to a DL380 to allow for drive segregation or my
> hybrid of a configuration:
> Add a 2 drive Drive Cage in the open 5.25 slots.
> Run the Data drive in a RAID 0 + 1 from the primary DRIVE Cage.
> Run the OS + Logging in the new Drive Cage using RAID 1.
> Any comments or suggestions. This is my first SQL implementation.
> Thank You,
> John Jakus|||Andrew J. Kelly wrote:
> Memory is too cheap these days to not have enough. How large do you expect
> your DB to get and of that how much of the data would be read or written to
> each day? How many transactions per second do you expect to do? Will you
> read large amounts of data at a time or small amounts?
>
I have no idea. This is for an implementation of Axapta. They never gave
me estimates on how many transactions will be performed. I just know it
will slowly ramp up. I know it's better to over build a system like this
because it's easier then upgrading later. I just want to know which
configuration will perform better with an SQL server. Since I am new to
SQL Server.
Sorry for being so vague but this is all I have right now and they are
in a hurry to implement. I just want to make it the most robust that I can.
Thanks,
John Jakus|||John
Take a look at this link you'll find lots of useful info and tips.
http://www.sql-server-performance.com
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> > Memory is too cheap these days to not have enough. How large do you
expect
> > your DB to get and of that how much of the data would be read or written
to
> > each day? How many transactions per second do you expect to do? Will
you
> > read large amounts of data at a time or small amounts?
> >
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
> Thanks,
> John Jakus|||Well it's imposable to tell if any of those configurations will ultimately
suite your needs without that kind of information. So with this in mind I
would opt for the first configuration:
2 x 18GB RAID 1 for OS and SQL logging.
4 x 18GB RAID 0+1 for Data
This will separate the log files from he data which is important under heavy
write situations. A RAID 0+1 is good, the only thing is it only has 4
disks. This makes for only 36GB of usable space and as always with database
the more disks the better, not the size. Hope that is going to be enough.
If not and you are stuck with that drive configuration maybe you can use
36GB drives instead of 18GB for the Raid 0+1. since they didn't provide
specs it is most likely not too intensive of an application and this should
be fine. Definitely go with more memory than 768 though. Again how much
depends on the size and use of the db but 2GB should do it.
--
Andrew J. Kelly
SQL Server MVP
"John Jakus" <John.Jakus.DieSpammerDie@.Valence.com> wrote in message
news:ubcuJq66DHA.2404@.TK2MSFTNGP11.phx.gbl...
> Andrew J. Kelly wrote:
> > Memory is too cheap these days to not have enough. How large do you
expect
> > your DB to get and of that how much of the data would be read or written
to
> > each day? How many transactions per second do you expect to do? Will
you
> > read large amounts of data at a time or small amounts?
> >
> I have no idea. This is for an implementation of Axapta. They never gave
> me estimates on how many transactions will be performed. I just know it
> will slowly ramp up. I know it's better to over build a system like this
> because it's easier then upgrading later. I just want to know which
> configuration will perform better with an SQL server. Since I am new to
> SQL Server.
> Sorry for being so vague but this is all I have right now and they are
> in a hurry to implement. I just want to make it the most robust that I
can.
> Thanks,
> John Jakus|||To try and cover your issues separately
Memory - as suggested, memory is relatively inexpensive these days and database servers are generally pretty memory and disk intensive. It really depends on the usage profile and DB size but 4GB is not really *that* expensive
Disk config: Disk config is very important as it can make a major difference to the performance of the database
Here are some general recommendations but again this depends on the app and how it will be used
RAID 5: Good fault tolerance, good read performance, BAD write performance (parity has to be written every time the disk is written to). Use this if the DB is read intensive with few writes. RAID 5 offers good fault tolerance at a relatively low cost
RAID 1: Good fault tolerance, and good performance for sequential writes (such as transaction logs). Expensive because you only have effective use of half the disk
RAID 0: RAID 0 stripes data across multiple disks. This offers excellent performance, but NO fault tolerance
RAID 1+0 and RAID 0+1: These two must not be confused - people use the terms interchangeably but they are very different.
RAID 1+0 is the striping of data across multiple RAID 1 mirrors. For example if you have 8 disks, it would stripe data across 4 mirrors. This offers excellent performance, excellent fault tolerance but a not-so-excellent bank balance
RAID 0+1 is the mirroring of two stripe sets. In our scenario of 8 disks, you would have 2 striped sets (RAID 0) of 4 disks each, that are in turn mirrored. This also offers good performance and fault tolerance (as RAID 1+0) but this will be degraded in the event of disk failures (much more than RAID 1+0)
So if faced with the choice between RAID 1+0 and RAID 0+1 I would always choose RAID 1+0
An example of a high-end disk spec would be
OS: 2 x 18.2GB RAID
Logs: 2 x 36.4GB RAID1 - or 4 x 36.4 RAID 1+
Data: 8 x 36.4GB RAID1+0 (this could be any amount of disks in multiples of 2, the total number constrained by the storage device
This may or may not be an overkill depending on the actual app
In terms of controllers, separate controllers sounds like a good idea - again its down to the cost/benefit of doing this
I hope this helps as a very general guideline
Regards
Ro
Optimal disk configuration for SQL
I think that RAID5 is still sort of the default for the main database
for average conditions, because it is more efficient in the use of
disk, when you get up to four or more drives, and it may be better for
reads, and average tables in average databases do 99% reads.
But, most apps may have a few more actively written tables, which
might be best on a filegroup and/or database on a RAID10 drive
instead.
I'm having my conscious raised on a number of hardware and
configurations issues these days myself.
Josh
On Sat, 03 Mar 2007 09:05:52 +0100, sp <kofa@.noemail.noemail> wrote:
>sp napisa?(a):
>
>what do you think about this configuration?
Hello KoFa,
The default Stripe Element Size for your hardware configuration is
recommanded. For example, in the Dell EMC white paper, it recommanded to
use the default size 128 blocks or 64 KB
Here are some article for you to refer:
http://www.dell.com/downloads/global/solutions/dell_emc_sap_bestpractice.pdf
http://forums.dantz.com/ubbthreads/showflat.php?Number=93175&page=0
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
for average conditions, because it is more efficient in the use of
disk, when you get up to four or more drives, and it may be better for
reads, and average tables in average databases do 99% reads.
But, most apps may have a few more actively written tables, which
might be best on a filegroup and/or database on a RAID10 drive
instead.
I'm having my conscious raised on a number of hardware and
configurations issues these days myself.
Josh
On Sat, 03 Mar 2007 09:05:52 +0100, sp <kofa@.noemail.noemail> wrote:
>sp napisa?(a):
>
>what do you think about this configuration?
Hello KoFa,
The default Stripe Element Size for your hardware configuration is
recommanded. For example, in the Dell EMC white paper, it recommanded to
use the default size 128 blocks or 64 KB
Here are some article for you to refer:
http://www.dell.com/downloads/global/solutions/dell_emc_sap_bestpractice.pdf
http://forums.dantz.com/ubbthreads/showflat.php?Number=93175&page=0
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Optimal disk configuration for SQL
Optimal disk configuration for SQL
I think it is:
For datafiles (RAID10)
Stripe Element Size 8KB â' because page has 8 KB
Read Policy: No read Ahead (there is a choose beetwen read ahead i
adaptive read ahead)
Write Policy: Write Back (there is a choose beetwen Write Through i
Force Write Back)
For log files as above but RAID1sp napisaÅ?(a):
> Optimal disk configuration for SQL
> I think it is:
> For datafiles (RAID10)
> Stripe Element Size 8KB â' because page has 8 KB
> Read Policy: No read Ahead (there is a choose beetwen read ahead i
> adaptive read ahead)
> Write Policy: Write Back (there is a choose beetwen Write Through i
> Force Write Back)
>
> For log files as above but RAID1
what do you think about this configuration?|||I think that RAID5 is still sort of the default for the main database
for average conditions, because it is more efficient in the use of
disk, when you get up to four or more drives, and it may be better for
reads, and average tables in average databases do 99% reads.
But, most apps may have a few more actively written tables, which
might be best on a filegroup and/or database on a RAID10 drive
instead.
I'm having my conscious raised on a number of hardware and
configurations issues these days myself.
Josh
On Sat, 03 Mar 2007 09:05:52 +0100, sp <kofa@.noemail.noemail> wrote:
>sp napisa?(a):
>> Optimal disk configuration for SQL
>> I think it is:
>> For datafiles (RAID10)
>> Stripe Element Size 8KB ? because page has 8 KB
>> Read Policy: No read Ahead (there is a choose beetwen read ahead i
>> adaptive read ahead)
>> Write Policy: Write Back (there is a choose beetwen Write Through i
>> Force Write Back)
>>
>> For log files as above but RAID1
>
>what do you think about this configuration?|||What is about Stripe Element Size in RAID 10 or 5 '
JXStern napisaÅ?(a):
> I think that RAID5 is still sort of the default for the main database
> for average conditions, because it is more efficient in the use of
> disk, when you get up to four or more drives, and it may be better for
> reads, and average tables in average databases do 99% reads.
> But, most apps may have a few more actively written tables, which
> might be best on a filegroup and/or database on a RAID10 drive
> instead.
> I'm having my conscious raised on a number of hardware and
> configurations issues these days myself.
> Josh
>
> On Sat, 03 Mar 2007 09:05:52 +0100, sp <kofa@.noemail.noemail> wrote:
>> sp napisa?(a):
>> Optimal disk configuration for SQL
>> I think it is:
>> For datafiles (RAID10)
>> Stripe Element Size 8KB â' because page has 8 KB
>> Read Policy: No read Ahead (there is a choose beetwen read ahead i
>> adaptive read ahead)
>> Write Policy: Write Back (there is a choose beetwen Write Through i
>> Force Write Back)
>>
>> For log files as above but RAID1
>> what do you think about this configuration?
>|||Hello KoFa,
The default Stripe Element Size for your hardware configuration is
recommanded. For example, in the Dell EMC white paper, it recommanded to
use the default size 128 blocks or 64 KB
Here are some article for you to refer:
http://www.dell.com/downloads/global/solutions/dell_emc_sap_bestpractice.pdf
http://forums.dantz.com/ubbthreads/showflat.php?Number=93175&page=0
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
I think it is:
For datafiles (RAID10)
Stripe Element Size 8KB â' because page has 8 KB
Read Policy: No read Ahead (there is a choose beetwen read ahead i
adaptive read ahead)
Write Policy: Write Back (there is a choose beetwen Write Through i
Force Write Back)
For log files as above but RAID1sp napisaÅ?(a):
> Optimal disk configuration for SQL
> I think it is:
> For datafiles (RAID10)
> Stripe Element Size 8KB â' because page has 8 KB
> Read Policy: No read Ahead (there is a choose beetwen read ahead i
> adaptive read ahead)
> Write Policy: Write Back (there is a choose beetwen Write Through i
> Force Write Back)
>
> For log files as above but RAID1
what do you think about this configuration?|||I think that RAID5 is still sort of the default for the main database
for average conditions, because it is more efficient in the use of
disk, when you get up to four or more drives, and it may be better for
reads, and average tables in average databases do 99% reads.
But, most apps may have a few more actively written tables, which
might be best on a filegroup and/or database on a RAID10 drive
instead.
I'm having my conscious raised on a number of hardware and
configurations issues these days myself.
Josh
On Sat, 03 Mar 2007 09:05:52 +0100, sp <kofa@.noemail.noemail> wrote:
>sp napisa?(a):
>> Optimal disk configuration for SQL
>> I think it is:
>> For datafiles (RAID10)
>> Stripe Element Size 8KB ? because page has 8 KB
>> Read Policy: No read Ahead (there is a choose beetwen read ahead i
>> adaptive read ahead)
>> Write Policy: Write Back (there is a choose beetwen Write Through i
>> Force Write Back)
>>
>> For log files as above but RAID1
>
>what do you think about this configuration?|||What is about Stripe Element Size in RAID 10 or 5 '
JXStern napisaÅ?(a):
> I think that RAID5 is still sort of the default for the main database
> for average conditions, because it is more efficient in the use of
> disk, when you get up to four or more drives, and it may be better for
> reads, and average tables in average databases do 99% reads.
> But, most apps may have a few more actively written tables, which
> might be best on a filegroup and/or database on a RAID10 drive
> instead.
> I'm having my conscious raised on a number of hardware and
> configurations issues these days myself.
> Josh
>
> On Sat, 03 Mar 2007 09:05:52 +0100, sp <kofa@.noemail.noemail> wrote:
>> sp napisa?(a):
>> Optimal disk configuration for SQL
>> I think it is:
>> For datafiles (RAID10)
>> Stripe Element Size 8KB â' because page has 8 KB
>> Read Policy: No read Ahead (there is a choose beetwen read ahead i
>> adaptive read ahead)
>> Write Policy: Write Back (there is a choose beetwen Write Through i
>> Force Write Back)
>>
>> For log files as above but RAID1
>> what do you think about this configuration?
>|||Hello KoFa,
The default Stripe Element Size for your hardware configuration is
recommanded. For example, in the Dell EMC white paper, it recommanded to
use the default size 128 blocks or 64 KB
Here are some article for you to refer:
http://www.dell.com/downloads/global/solutions/dell_emc_sap_bestpractice.pdf
http://forums.dantz.com/ubbthreads/showflat.php?Number=93175&page=0
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Optimal disk configuration for SQL
Optimal disk configuration for SQL
I think it is:
For datafiles (RAID10)
Stripe Element Size 8KB – because page has 8 KB
Read Policy: No read Ahead (there is a choose beetwen read ahead i
adaptive read ahead)
Write Policy: Write Back (there is a choose beetwen Write Through i
Force Write Back)
For log files as above but RAID1sp napisa?(a):
> Optimal disk configuration for SQL
> I think it is:
> For datafiles (RAID10)
> Stripe Element Size 8KB – because page has 8 KB
> Read Policy: No read Ahead (there is a choose beetwen read ahead i
> adaptive read ahead)
> Write Policy: Write Back (there is a choose beetwen Write Through i
> Force Write Back)
>
> For log files as above but RAID1
what do you think about this configuration?|||I think that RAID5 is still sort of the default for the main database
for average conditions, because it is more efficient in the use of
disk, when you get up to four or more drives, and it may be better for
reads, and average tables in average databases do 99% reads.
But, most apps may have a few more actively written tables, which
might be best on a filegroup and/or database on a RAID10 drive
instead.
I'm having my conscious raised on a number of hardware and
configurations issues these days myself.
Josh
On Sat, 03 Mar 2007 09:05:52 +0100, sp <kofa@.noemail.noemail> wrote:
>sp napisa?(a):
>
>what do you think about this configuration?|||What is about Stripe Element Size in RAID 10 or 5 '
JXStern napisa?(a):
> I think that RAID5 is still sort of the default for the main database
> for average conditions, because it is more efficient in the use of
> disk, when you get up to four or more drives, and it may be better for
> reads, and average tables in average databases do 99% reads.
> But, most apps may have a few more actively written tables, which
> might be best on a filegroup and/or database on a RAID10 drive
> instead.
> I'm having my conscious raised on a number of hardware and
> configurations issues these days myself.
> Josh
>
> On Sat, 03 Mar 2007 09:05:52 +0100, sp <kofa@.noemail.noemail> wrote:
>
>|||Hello KoFa,
The default Stripe Element Size for your hardware configuration is
recommanded. For example, in the Dell EMC white paper, it recommanded to
use the default size 128 blocks or 64 KB
Here are some article for you to refer:
http://www.dell.com/downloads/globa...estpractice.pdf
http://forums.dantz.com/ubbthreads/...er=93175&page=0
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
I think it is:
For datafiles (RAID10)
Stripe Element Size 8KB – because page has 8 KB
Read Policy: No read Ahead (there is a choose beetwen read ahead i
adaptive read ahead)
Write Policy: Write Back (there is a choose beetwen Write Through i
Force Write Back)
For log files as above but RAID1sp napisa?(a):
> Optimal disk configuration for SQL
> I think it is:
> For datafiles (RAID10)
> Stripe Element Size 8KB – because page has 8 KB
> Read Policy: No read Ahead (there is a choose beetwen read ahead i
> adaptive read ahead)
> Write Policy: Write Back (there is a choose beetwen Write Through i
> Force Write Back)
>
> For log files as above but RAID1
what do you think about this configuration?|||I think that RAID5 is still sort of the default for the main database
for average conditions, because it is more efficient in the use of
disk, when you get up to four or more drives, and it may be better for
reads, and average tables in average databases do 99% reads.
But, most apps may have a few more actively written tables, which
might be best on a filegroup and/or database on a RAID10 drive
instead.
I'm having my conscious raised on a number of hardware and
configurations issues these days myself.
Josh
On Sat, 03 Mar 2007 09:05:52 +0100, sp <kofa@.noemail.noemail> wrote:
>sp napisa?(a):
>
>what do you think about this configuration?|||What is about Stripe Element Size in RAID 10 or 5 '
JXStern napisa?(a):
> I think that RAID5 is still sort of the default for the main database
> for average conditions, because it is more efficient in the use of
> disk, when you get up to four or more drives, and it may be better for
> reads, and average tables in average databases do 99% reads.
> But, most apps may have a few more actively written tables, which
> might be best on a filegroup and/or database on a RAID10 drive
> instead.
> I'm having my conscious raised on a number of hardware and
> configurations issues these days myself.
> Josh
>
> On Sat, 03 Mar 2007 09:05:52 +0100, sp <kofa@.noemail.noemail> wrote:
>
>|||Hello KoFa,
The default Stripe Element Size for your hardware configuration is
recommanded. For example, in the Dell EMC white paper, it recommanded to
use the default size 128 blocks or 64 KB
Here are some article for you to refer:
http://www.dell.com/downloads/globa...estpractice.pdf
http://forums.dantz.com/ubbthreads/...er=93175&page=0
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
optimal database design
What is more efficient for a database design - a lot of tables with only a
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
Thx
Database design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>
|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>
|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
David Portas
SQL Server MVP
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
Thx
Database design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>
|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>
|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
David Portas
SQL Server MVP
optimal database design
What is more efficient for a database design - a lot of tables with only a
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
ThxDatabase design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
David Portas
SQL Server MVP
--
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
ThxDatabase design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
David Portas
SQL Server MVP
--
optimal database design
What is more efficient for a database design - a lot of tables with only a
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
ThxDatabase design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
--
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
--
David Portas
SQL Server MVP
--
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
ThxDatabase design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
--
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
--
David Portas
SQL Server MVP
--
Subscribe to:
Posts (Atom)