SQL Question

[aftermid 0]

Server: 2.8 Xeon Processor
RAM: 1 gig
OS: Win2000 Server

Any DBA's out there? For MSSQL 2000 Server is there any:

-Recomended DB size limit?
-Recomended total # of DB on server.
-Recomended total # of connections

Thanks

[AlexCrowley 0]

This is gonna be another of those headache threads. I can feel it coming on now.

TV's got them images, TV's got them all, nothing's shocking.

[bazelos 0]

You should think how many users are likely to be connected at a given time.

He who makes a beast of himself gets rid of the pain of being a man.

[labrys 0]

Exactly. And also how many reads/writes there will be. These are more important considerations than size limits.

Are you (OP) asking about Standard or Enterprise?

Owned by Remi #?

[tdog 0]

I have a very similar server running SQL...

I have over a million rows in a payroll tax table - and in another table I store .jpg images for employee badges. (If I was going to do it again, I would store the images on disk and link to the images, but the report viewer I had at the time, an older version of Crystal, did not like that. So I went the easy route on development with the con of having a huge table full of images)

The database on disk is a few hundred megabytes. The amount of transactions is nothing like a credit card processor or mapping - but large for accounting/customer relationship management. Maybe 200 normalized tables... It has not slowed down at all as I push more data into it. I dig a lot into the raw data, and have learned returning a million rows to my desktop is not a good idea, so I make sure I limit the data I pull on the query/server instead of filtering it on the desktop (duh) - even when just pulling raw data to paste into excel or something for analysis/customer review.

I guess the answer is, "It depends"... I have a SQL Query that is written poorly that takes 10 minutes to process. I have one that is much more complex that takes 2 seconds to process. Nesting 10 layers of queries in one query will slow down any machine.

Also, how many transactions? One user hitting the server hard is different than 10,000 users hitting it lightly - but still, transaction loads effect a lot. If you had only a few rows in the database and it was hit hundreds of times a second - you would have a different "beast" than millions of rows touched by one user.

What is your goal? What are you planning on doing with it?

[AlexCrowley 0]

you're gonna need at least 1.1TB of raw storage.

TV's got them images, TV's got them all, nothing's shocking.

[aftermid 0]

Quote

You should think how many users are likely to be connected at a given time.

Thanks.

For the most part users access via dot net pages. The most POSSIBLE synchronous connections would be 120 via web server, but a realistic max number would probably be more like 20-30 at peak times. The largest active table has approx 35K rows. At any given time there might be 65% reads and 35% writes. Most of the user queries are pretty simple.

[AlexCrowley 0]

HOW BIG?


or little.

TV's got them images, TV's got them all, nothing's shocking.

[jerm 0]

Quote

Server: 2.8 Xeon Processor
RAM: 1 gig
OS: Win2000 Server

Any DBA's out there? For MSSQL 2000 Server is there any:

-Recomended DB size limit?
-Recomended total # of DB on server.
-Recomended total # of connections

Thanks

win2k is nearing, of not alreay at, EOL... you should consider 2k3

the details that you provided in the later post don't seem like nanything that server shoulnd't be able to handle so long as you structure your queries efficiently

also bear in mind that 120 users very very rarely means 120 simultaneous operations.

also bare in mind that most webservers re-use DB connections, so you may only end up with 1 or 2 open connections to the DB per server, not 1 per user.

for reference we host a sire on which 5000 simultaneous users are handled by 6 webservers and 1 SQL server.

Mind you, it has 4 processors,8 gigs of ram and a 14 drive raid10, but you're not doing anything anywhere near that level of activity.

from the setup you described i'd worry more about fast hard drives than anything else. you don't need 15k drives, but you should at least be running raid1 (even though writes aren't accelrated, any decent implementation will stripe reads), prefereably 5 or 10/0+1

Landing without injury is not necessarily evidence that you didn't fuck up... it just means you got away with it this time

[Plucky 0]

You may have already read this. If not, it's worth a read -
Scalability and Very Large Database (VLDB) Resources

- Citizen of the World -

[Guest 1010]

SQL Server 2000 specs


It may take a few moments to load ...

Database size: 1,048,516 TB
Databases per instance of SQL Server: 32,767
Instances SQL Server per computer: 16

You can have it good, fast, or cheap: pick two.