Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2007
    Posts
    86

    Unanswered:

    Apologies .. dbforum did not post the text .. it did hang quite a while tho' .. so here it is again ..

    Does anyone have experience with multi-terrabyte OLTP databases with 1500+ users, and data pumps running on a single Standard Edition SQL Server 2005 database (i.e. no partitioning). ?

    What s/b my expectation of performance (degradation) as we reach 1-7 terrabytes. Growth projections are 1 TB annually to start, and probably 3-4 TB annually when system is fully implemented.
    I'm concerned.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your expectations of performance are governed far more by your code quality and your hardware platform than by SQL Server.

    By the time the database gets to 5 Tb and for sure by the time it reaches 10 Tb you'll want to switch to a 64 bit server platform to get more RAM. If you upgrade to 64 bit, I'd recommend that you also switch to Server 2008 and SQL 2008 to take advantage of new features.

    When dealing with multi-terrabyte databases, I don't think it is wise to pinch pennies on the platform or server software. You can do it, and the entry level tools will work under that load, but I don't think that's a good idea... My time and teh server reliability is worth far more than that to me.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2007
    Posts
    86

    VLDB's on SS05

    We're on the 64-bit platform. Servers are blades with 16 cores. The hardware is not the issue. The powers that be, pruhased the standard edition server, agains my recommendations (enterprise is a given for VLDB's). I was wondering if anyone had expereinced, what I expect to be a train wreck within the years ..

    The application is a vendor app, so we don't own the code or the schema. Retention is regulatory to 9 years, and no DLM (data lifecycle maintenance) has been addressed.. so for now it's grow till it busts.

  4. #4
    Join Date
    Mar 2007
    Posts
    86

    VLDB's on SS05

    Oh Iforgot one more thing .. SS05 is the platform for a reason. The vendor cannot run on SS08 for some reason.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't have experience of VLDB OLTP (DSS yes, OLTP no) - sorry. However I noticed one thing.

    SQL Server Support Roadmap
    In less than 7 years SQL 2005 with be off Extended Support.

    Also, if the code\ schema are bad then it won't really matter what RDBMS this is running on.

    Are there no equivalent client installations the vendor will let you have access to?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, that's eye opening to me. Didn't expect 2k5 to be off mainstream so soon either. 2000 only went Extended 18 months ago.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2007
    Posts
    86

    VLDB's on SS05

    LOL .. this is the only large infra deployment for the vendor. They are rather small, and yet another reason for my concern. The code is not very good at space mgmt ..there are a lot of issues.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    About two years ago I was working with a client that had 28.3 Tb running on a 64 bit SQL 2005 Standard Edition. By now it should be approaching 40 Tb, and I'm pretty sure that they'd have called if they changed platform or had problems.

    Not my first choice either, I'd have drastically preferred using Enterprise or even Datacenter for that kind of load, but the 64 bit version of SQL 2005 Standard Edition seems to be handling it nicely.

    Analysis Server grinds heavily while processing the cubes from the relational data, but once it has processed a cube it then does a backup and restore into the corresponding "working" cube that their users access. The users get answers fairly quickly (many queries under a second, all the I ever tested under a minute) and have been quite happy with the result.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Pat Phelan
    About two years ago I was working with a client that had 28.3 Tb running on a 64 bit SQL 2005 Standard Edition. By now it should be approaching 40 Tb, and I'm pretty sure that they'd have called if they changed platform or had problems.

    Not my first choice either, I'd have drastically preferred using Enterprise or even Datacenter for that kind of load, but the 64 bit version of SQL 2005 Standard Edition seems to be handling it nicely.

    Analysis Server grinds heavily while processing the cubes from the relational data, but once it has processed a cube it then does a backup and restore into the corresponding "working" cube that their users access. The users get answers fairly quickly (many queries under a second, all the I ever tested under a minute) and have been quite happy with the result.

    -PatP
    No partitioned tables at that size ?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Their data has a natural partitioning due to the process, where some attributes become meaningless over time so the underlying schema changes as the data ages. There was no automated/SQL specific partitioning at all in this particular case.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by stuarta
    LOL .. this is the only large infra deployment for the vendor. They are rather small, and yet another reason for my concern. The code is not very good at space mgmt ..there are a lot of issues.
    In that case I would consider this a greater potential cause for concern than the platform per se. As Pat said, the greatest factors are the logical & physical database design coupled with the code accessing and manipulating this. These things can cripple performance issues WAY before your data is approaching multi TBs. I would be especially concerned that you are also committing to 9 years with the company (is that serious? Contractual or just as guestimate?).

    A few questions - I'm gonna guess you are a platinum client for them so you have some negotiation leverage:
    Have you been able to secure an evaluation copy of the application for stress testing? Have they any metrics they can supply to you to indicate they can handle your anticipated traffic? At a minimum can you run traces on a demo installation of their app to see what sort of physical processes are going on (this will give you some indication of how well designed everything is)?

    Also, you are projecting up to 4 TB growth pa with only 1500 users - that is interesting. I've not done the math but unless they are uploading large batches or BLOBS I don't see how so few people are generating so much data in an OLTP system.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Mar 2007
    Posts
    86

    VLDB's on SS05

    The large amount of data growth is from the realtime data feeds. These are from every insurance company in the country. Most of the data is transactional, real-time data pumps across TCP-IP, with EDI image data embedded. The images are EDI files, which are stored for regulatory reasons in the database. Regulatory compliance is 9 years retention. In addition there are 1500 users licensed doing OLTP type transactions. Probably 300-500 at any time. I would have done this differently, as most DBA's would, but the vendor doesn't have much DBA experience. They are a development shop, very small, and we are their first VLDB experience.

    Good to know that SS05 64-bit will manage the size. I'm more concerned with performance tuning, torn pages, and response times as the thing grows to massive proportions.

    Thanks for all the info contributed.

Posting Permissions

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