help! H/W spec for SQL Server 2000

Hi,

I have a SQL server 2000 database running in Windows Server 2000. The database only consists of a handful of tables and is taking multiple inserts from @.40 client PCs.

The server exists on two servers with half the clients talking to each. The databases use transactional replication to stay synchronised.

Each PC generates about 100 SQL transactions per busy hour, each being an insert.

The customer is expanding the number of client PCs from 40, through 300 to, ultimately, about 3000. They are asking me what hardware spec is required and want to be told it in a for each additional 50 client applications you need XYZ type of format to allow for gradual growth.

I have to say I am unsure how to go about scaling and configuring the servers!

So, is there such a thing as an automatic configurator which automatically produces a required spec for a given number of transactions and clients? Are there other criteria they/I need to take into account such as number of logins etc? For archiving I am guessing that a CD/RW drive is the way to go?

Also, in terms of design between the two servers they have. With clustering, mirroring, replication etc I am unsure as to which is most appropriate.

The thing I do know is that they are willing to spend serious $$$ for the right design and need 99.9% up time.

They also pull significant amount of reports which I think logically equates to a third server to keep this traffic away from the live database. So, is replication the best way to go? Say replication every 15 minutes to a reports server?

I am also of the opinion that they need to move from Windows 2000 Server to Advanced Server for added scalability but will pursue that elsewhere.

Any input MUCH appreciated.

Out of my depth! :confused:
PaulThe thing I do know is that they are willing to spend serious $$$ for the right design and need 99.9% up time.

Then they better be willing to spend some money. They'll need it.

I would try to get them to move to Advanced Server or wait for 2003 SP1 to come out. Enterprise Edition will be needed for many of the features you are talking about. It's impossible to answer your question without knowing the database. You need to figure out how many rows in the tables will be taken at different levels of users, what the average fill ratio will be on those tables, then how much size you'll need along the way. In addition, you need to figure out how many transactions, and how much those will cost you as the database grows do to increased size of the tables.

I would purchase the Microsoft Press book: Permformance Tuning Manual technical reference if I was you and have a look at the worksheets and formulas in the book.

HELP! - Problem with hints...

Hello everyone,
I'm having this problem, I'm trying to run a query that consists of six
tables and one view (all joined together)
When I run it without anything on the where (or just one criteria in the
where clause) works just fine, but if I run it with two criteria in the
where clause I get the following message:
<<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
larger than the page size into a hash table. Resubmit the query with the
ROBUST PLAN hint. >>
And when I do so -adding the ROBUST PLAN option-, I get the following
message:
<<< 'Query hints' cannot be used in this query type. >>
Any idea what is going on?!? please let me know!!!
TIA,
sb-rEveryone,
I forgot to put this...
I'm using SQL Server 2000 on Win2003 Server, SP 1
Thanks again...
"segis bata" <segisbata@.hotmail.com> wrote in message
news:exo1vo0qGHA.4192@.TK2MSFTNGP04.phx.gbl...
> Hello everyone,
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
> <<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
> <<< 'Query hints' cannot be used in this query type. >>
> Any idea what is going on?!? please let me know!!!
> TIA,
> sb-r
>|||segis bata (segisbata@.hotmail.com) writes:
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
><<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
><<< 'Query hints' cannot be used in this query type. >>
> Any idea what is going on?!? please let me know!!!
Sounds like you are in dire straits. But at least you could post the
query you are having problem with.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||What happen if you try the query without the view in it? with and without the
robust plan in the query.
"segis bata" wrote:
> Hello everyone,
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
> <<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
> <<< 'Query hints' cannot be used in this query type. >>
> Any idea what is going on?!? please let me know!!!
> TIA,
> sb-r
>
>

HELP! - Problem with hints...

Hello everyone,
I'm having this problem, I'm trying to run a query that consists of six
tables and one view (all joined together)
When I run it without anything on the where (or just one criteria in the
where clause) works just fine, but if I run it with two criteria in the
where clause I get the following message:
<<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not ins
ert a row
larger than the page size into a hash table. Resubmit the query with the
ROBUST PLAN hint. >>>
And when I do so -adding the ROBUST PLAN option-, I get the following
message:
<<< 'Query hints' cannot be used in this query type. >>>
Any idea what is going on?!? please let me know!!!
TIA,
sb-rEveryone,
I forgot to put this...
I'm using SQL Server 2000 on Win2003 Server, SP 1
Thanks again...
"segis bata" <segisbata@.hotmail.com> wrote in message
news:exo1vo0qGHA.4192@.TK2MSFTNGP04.phx.gbl...
> Hello everyone,
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
> <<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not i
nsert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
> <<< 'Query hints' cannot be used in this query type. >>>
> Any idea what is going on?!? please let me know!!!
> TIA,
> sb-r
>|||segis bata (segisbata@.hotmail.com) writes:
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
><<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not in
sert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
><<< 'Query hints' cannot be used in this query type. >>>
> Any idea what is going on?!? please let me know!!!
Sounds like you are in dire straits. But at least you could post the
query you are having problem with.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What happen if you try the query without the view in it? with and without th
e
robust plan in the query.
"segis bata" wrote:

> Hello everyone,
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
> <<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not i
nsert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
> <<< 'Query hints' cannot be used in this query type. >>>
> Any idea what is going on?!? please let me know!!!
> TIA,
> sb-r
>
>