Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007

    Unanswered: BIG and rapidly growing database, inexperienced tech guy... me!

    We're a very small company, I’m the “technical” director who has evolved enough skill in a wide variety of tasks (network setup, machine config, email systems, html, asp, database...) and then one day you notice that parts of the system are starting to get way more complex and troublesome than the layman knowledge you have can cope with... Well, I think I’ve got to that point and I need some outside help to get our system to the next level!

    OK, some rough details to start with. We run a small but fast-growing vehicle tracking system that sends back a LOT of data via GPRS to our SQL 2000 Enterprise server hosted on a dedicated server in London. The physical machine is a P4 3.2Ghz Dual-core Dell rackmount with 2GB RAM and 2 x 76GB SCSI disks in a RAID 1 array. This is partitioned into a 15GB C: partition and a 51GB D: partition. The system paging file is set to be 1536MB and is on the C: partition. The server is used for everything we do... it runs Smartermail email server (only about 5 or 6 domains and a few users, hardly used at all), SQL server as mentioned, web server & the proxy software that receives incoming data from our tracking devices.

    There are 9 or 10 active databases on the SQL server. 8 of them take up less than a gigabyte between them and are sparingly used. The main “active” database on the SQL server is the tracking system – and this is big... As our tracking devices send in data every 10 – 30 seconds, the database is hit with hundreds of thousands of events per day. On a weekday, some half a million rows of data are written to the main “events” table on the database. Over 7 days from 26th November to 2nd December, almost exactly 3 million rows of data were written to the events table. We undertake to hold 3 months or so of data “live” for our customers and I periodically archive data off. I’ve been too busy to archive recently and the database is holding data on the events table going back to July 1st. The physical .mdf file is just under 30GB on partition d: at present. The plan is to drop the active data stored to only 1 – 2 months, but this still leaves a 12GB .mdf file.

    The worrying thing with this is that this is only 700 or so devices writing to us at present... we aim to have thousands out there soon! We are looking into how we can hugely improve system performance and look to the future. Our hosting company is recommending VMWare virtual servers and SAN storage, but I’m not entirely sure that is the best way forward.

    Our non-tech MD thinks the way forward is to have one database per customer and can't understand when I tell him I think that's bad as it will create all the system tables and bits & pieces for EVERY customer if we do that, right? Also it would be a nightmare to add a new column to a table as I'd have to update every single version of the database too... I want to avoid this unless I'm missing something and this is actually the best way to go forward?

    I've had someone mention horizontal partitioning to me? not sure what implications this has to coding and table naming? Or is it all one big database spread among separate servers?

    Currently our server is drowning on disk access and it's only going to get worse... any suggestions or links to reading online that I can do would be great, thanks!


  2. #2
    Join Date
    Dec 2007
    Hmm. Was hoping I might have had an answer or two by now! Even if it was just an abusive one! Is there any way I can tailor or adapt the question to make it look slightly more appealing for an answer?

  3. #3
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    it's too long, there are few rights and wrongs and more "it depends", a number of questions to ask and it's monday.

    have you looked at DBCC SHRINFILE? there are important differences between it and dbcc shrinkdatabase.

    there is the fact that people will gripe about you not having a dedicated sql box.

    there are a number of pros & cons related to the single database vs multiple database thing. syncronizing code and structure, handling security etc...

    but if you are expecting a lot of growth, you need to either upscale your hardware or re-engineer the app so you are not storing so much data.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Dec 2007
    So... our ISP came back to us and said, "Argh! you're writing half a million rows of data a day?! And you're doing it to the same machine doing the Web serving, receiving incoming data PRIOR to the DB write and it's your mail server? Are you mad? You need a separate machine! What? And you have your transaction logs and DB on the same disk? No, no no!"

    They've recommended a bit of a beast of a machine to run DB only, leaving our existing server doing web & email... Their recommendation:

    IBM x3650
    2 x 73GB in RAID 1 for OS & files
    3 x 73GB in RAID 5 for SQL DB Files
    2 x 73GB in RAID 1 for transaction logs
    16GB Ram
    quad core 2.66Ghz CPU
    dual NIC
    dual PSU
    1MB bandwidth

    Any comments on that set up ? Sound like a plan?

  5. #5
    Join Date
    Jan 2003
    Nottinghamshire, UK
    Hi Carled

    Definately a new Box is the way forward - the spec you have is High End of a medium setup.

    If you wanna cut corners (but be preared to pay for it later) 8Gig Ram should suffice with room for expansion

    Dual NIC & Bandwidth is up to how much traffic u expect

    Raid 10 is best option for Both Data & Log files - but seperate sets
    Raid 5 2nd best for Data
    Raid 1 2nd best for Log

    Note with this spec I'd have 2 seperate disks for your Logs - one with your main DB on & one with the rest (Log writing is serial but if you have multiple log files it's not gonna be serial anyway)

    Don't forget your BACKUPS on a completely seperate Physical Drive.

    Get the Cheque Book out

    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Dec 2007
    Thanks for that info - looks like we're going to have to go for it. FD is sweating... !

Posting Permissions

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