Terence,
I think your going about this the wrong way completely. You would have to
do a lot of magic to make these machines work the way you describe it.
There is no easy way to split the computing power of a SQL Database that
shares common data except with distributed partitioned views. And even that
is not that simple. 1.6M inserts a day is not a lot these days if you have
the right HW and schema configurations. What makes you think your current
system can't handle the load or another single relatively low cost one?
My guess is that your seeing massive disk queues. Since you only state 3
drives for the server I suspect you are running off of a similar
configuration now or even worse, maybe a single disk drive. With that many
inserts it is imperative you have a separate drive (preferably a raid 1) for
the log files. A modern dual processor system with proper disk
configuration should easily handle a situation such as yours.
--
Andrew J. Kelly
SQL Server MVP
[quoted text, click to view] "Terence Siganakis" <blah@bigpond.net.au> wrote in message
news:3f28d39b$1@duster.adelaide.on.net...
> Hi, I am currently running a large database (1.6 million inserts a day, a
> query on that once a second with heaps of aggregate functions) on a single
> relatively powerful server, although it is not powerful enough!
>
> As cost is a very large factor I am thinking about clustering. Clustering
> to save money?!? hehehe I am contemplating using a singly license of SQL
> Server 2000 Standard on the 'cluster controller' and then having several
> other PC's connected to it running MSDE.
>
> The concept is that applications talk to the cluser controler. On an
insert
> the CC sends off the data to the cluster member who has the least work to
> do. Each MSDE machine has each table, but only a small proportion of the
> data (ie with 2 MSDE machines each will take half of the inserts). When
> queries are run, the CC requests the data, makes sure it is sorted
correctly
> and fires it back off to the client. Every night a batch job will run to
> ensure that the servers are nicely balanced. All this is done with custom
> written stored procs.
>
> The idea is that the MSDE machines will be basic P4 2.4Ghz machines with
2GB
> and a cheap HD. ie around AUD$1,200 (US$600).
>
> The obvious worry is the 5 concurrent query limit on MSDE. However, by
> minimising the length and type of queries, this should not be a problem.
ie
> if queries are limited to basic inserts / range selects with all major
> processing occuring on the CC which has a lot more processing power.
> Basically I am comparing the option of upgrading a single machine, vs my
> little clustering experiment.... The jump to Enterprise from Standard is
> extremely expensive, as im sure you're all aware.
>
> What do you guys think?
>
> -------------------------------------------------
> Compare the following 2 options...
>
> Both assume a 'starting' configuration of a single server:
> Dual Xeon 2.4,
> 1 GB ECC Ram,
> 1 18 GB U320 SCSI Drive
>
> And no SQL Licenses. I am using Dell prices for the servers componentry
> because I'm lazy at the moment. All prices are AUD converted to USD...
>
> Option 1 (No clustering):
> __________________________________
> SQL Server Enterprise (10 CAL's)
> $6,382 + 10 * $146 = $7,842
>
> Memory Upgrade to 4GB ECC
> $1,600
>
> 3 * 18GB Ultra 320 (15k RPM) SCSI Drives
> 3 * $240 = $720
>
> Total = $10,162
>
> __________________________________
> Option 2 (Clustering)
>
> SQL Server Standard Per Processor
> (my understanding of the MSDE license is that it can be used in
conjunction
> with SQL Server so long as you have a per processor license for the server
> MSDE is being used with)
>
> $4,999
>
> Memory Upgrade to 2GB ECC
> $800
>
> 1 * 18GB Ultra 320
> $240
>
> 6 * MSDE PC (2.4Ghz, 2GB, IDE HD)
> $600
>
> 6 * Windows 2000 Professional
> $220
>
> Networking gear (10 / 100 switch and misc)
> $300
>
> Total = $11,259
>
>
> I'm thinking I'd get better performance from the second option.... Am i
> making a tragic mistake with my licensing assumptions? I bet so!
>
> Anyway, i hope you have found this interesting...
>
> Cheers!
>
>
>
>
>
