Professional Web Applications Themes

Cheap clustering - Microsoft SQL / MS SQL Server

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 ...

  1. #1

    Default Cheap clustering

    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!





    Terence Guest

  2. #2

    Default Re: Cheap clustering

    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


    "Terence Siganakis" <net.au> wrote in message
    news:3f28d39b$adelaide.on.net... 
    insert 
    correctly 
    2GB 
    ie 
    conjunction 


    Andrew Guest

  3. #3

    Default Re: Cheap clustering

    On Fri, 01 Aug 2003 16:19:05 -0700, JXStern <net>
    wrote: 

    Note -- in case you're not familiar with BCP, it is a very, very fast
    way of loading bulk data into a database.

    J.

    JXStern Guest

  4. #4

    Default Re: Cheap clustering

    Thanks for that. I think i might implement that for when the queue gets
    really big. For example yesterday I was doing some stuff to the db that
    caused a lot of load, which slowed down the inserts. The VB Queue got to
    over 150,000. At that sort of length the over heads in using the queue
    become very significant, and the available memory to SQL Server drops (the
    VB program was using over 100 mb of memory - damn inefficient VB!). So if
    there is a fast way to drop everything from the queue when things get crazy,
    I'll have to look into it.

    Unfortunetly it wouldn't be possible to implement the BCP strategy all the
    time, becasue the system is supposed to be 'real time', ie the orders should
    be visible as soon as they occur, and often there will be little activity
    which might result in orders being 'cached' for extended periods of time.

    Thanks for your input


    "JXStern" <net> wrote in message
    news:com... [/ref]
    about [/ref]
    enough [/ref]
    VB 
    >
    >
    > How about you bundle up 1,000 or 10,000 or so, and BCP them into a
    > holding table, and then as a second step process them into their
    > destinations?
    >
    > (Although, as I speculated before, you might want to keep them in
    > blobs in bundles of 1,000 or 10,000 or more, in which case the process
    > would have to be a little different)
    >
    > Joshua Stern
    >[/ref]


    Terence Guest

Similar Threads

  1. FMS Clustering Questions
    By erwin_seah in forum Macromedia Flash Flashcom
    Replies: 3
    Last Post: March 15th, 02:59 PM
  2. CF 7 Clustering Problem
    By MattS2 in forum Coldfusion Server Administration
    Replies: 1
    Last Post: April 23rd, 08:40 PM
  3. Clustering MX7
    By Trygve in forum Coldfusion Server Administration
    Replies: 2
    Last Post: April 30th, 09:35 AM
  4. Clustering MX servers
    By Brad MacDonald in forum Macromedia Flash Flashcom
    Replies: 1
    Last Post: April 21st, 08:01 PM
  5. Clustering question
    By Cormac in forum Windows Server
    Replies: 3
    Last Post: August 3rd, 02:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139