SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    Non-Member XX7509's Avatar
    Join Date
    Feb 2003
    Location
    Antarctica
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MSSQL Limitation?

    Is MSSQL bound by the 2gb database size, like Access is? If not, what is the max database size?

    Thanks.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Database size 1,048,516 TB ³
    ...

    ³ The size of a database cannot exceed 2 GB when using the SQL Server 2000 Desktop Engine or the Microsoft Data Engine (MSDE) 1.0.

    Sql Server Books Online

  3. #3
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another limitation...

    Databases per instance of SQL Server 32,767

  4. #4
    SitePoint Member
    Join Date
    Feb 2003
    Location
    in your mom
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What?

  5. #5
    SitePoint Guru
    Join Date
    Sep 1999
    Location
    Singapore
    Posts
    854
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But of course you can have up to 16 instances per computer...

  6. #6
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    16 * 32767 * 1048516 TB =

  7. #7
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by jofa
    16 * 32767 * 1048516 TB =
    So what am I going to do with the rest of my disk space?
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  8. #8
    Non-Member XX7509's Avatar
    Join Date
    Feb 2003
    Location
    Antarctica
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LOL! NUMbers.... hehe. On second thought, do they even make a 2tb hd yet?

    I was just looking at some info, on Access Limitations, and I seen it was limited to two gb, but I'm not sure if thats considered a lot... because I am building a forum script and... well any idea how much space a this forum would take up? With hundreds of people posting plus the size of the database, it just seems like the 2gb limit may be kind of small~? I mean unless the data is compressed in the database, the standard 1char=1byte would be overcome fairly quickly...

  9. #9
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    A 2GB database is fine for a forum for quite some time
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  10. #10
    Non-Member XX7509's Avatar
    Join Date
    Feb 2003
    Location
    Antarctica
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, that was my concern. I didn't think that these fourms got that big, most of them have an auto delete feature anyway, that removes old threads that havent been accessed in a while. I just wanted to make sure...

    If 2gb is fine a while I might stick with an Access backend for now. I got this info from Microsoft Support.

    ----
    The primary limitation with Access usually involved concurrent file access. Because Access is a file-oriented database, it locks records by telling the NTFS file system to locks range of bytes within the .mdb file. This can lead to performance problems or program exceptions when too many processes are going against the database at the same time. Some people say the limit is 15-20 SQL statements per second, but for planning purposes, try to keep within 2-3 SQL statements per second. If you anticipate more activity than that, you should probably consider SQL Server.
    ----

    The thing is, 2-3 SQL statements per second is about 10,800 database hits an hour, or about 180 per second. That's quite a few. I'm sure some of the biggest forums on the internet, don't get almost 200 hits per second.

  11. #11
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Access is totally unsuitable for use in a situation where it is being hit 180 times a second!!! Search these forums for previous discussions on this topic.

    In addition, while 2GB may be the limit, you will find that this does not mean that performance will be good at this size! Far from it!

    SQL Server all the way!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  12. #12
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by XX7509

    The thing is, 2-3 SQL statements per second is about 10,800 database hits an hour, or about 180 per second. That's quite a few. I'm sure some of the biggest forums on the internet, don't get almost 200 hits per second.
    I'm thinking that you meant that 2-3 statements per second meant ~180 per minute. And I can tell you this: some of the larger forums on the web get hit much more than that. I know that the somethingawful forums get about 345 queries per second; about 20,700 per minute. If you're planning for growth in your forums I would NOT recommend Access. MySQL may work, but SQL Server is your safest and most scalable bet (unless you're going for a MySQL cluster, which would scale, but IMO maintenance would be trickier).

  13. #13
    Non-Member XX7509's Avatar
    Join Date
    Feb 2003
    Location
    Antarctica
    Posts
    568
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by M@rco
    Access is totally unsuitable for use in a situation where it is being hit 180 times a second!!! Search these forums for previous discussions on this topic.

    In addition, while 2GB may be the limit, you will find that this does not mean that performance will be good at this size! Far from it!

    SQL Server all the way!
    Ok. first can someone please explaine to me the function of an Access database then? I have wanted to use it for many projects and on almost every single one of them, I was told Access is not suitable. Sooo... I'm guessing Access is just junk?

    Second you said that a database filled to or near the 2 gb limit will function poorly, sooo.. the SQL database has a limit of over a TB, does this also mean that a SQL database will be crap when it reaches that level, or close to it?


  14. #14
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Access is suitable for applications with a single user, or few users (== a lot of situations, but maybe not for a web application)
    You can create an Access db (.mdb file) without having to pay any license (== definitely cheaper than sql server)
    etc...

    More reading:
    http://www.microsoft.com/sql/techinf...70/msdejet.asp

  15. #15
    SitePoint Guru
    Join Date
    Sep 1999
    Location
    Singapore
    Posts
    854
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Access is great for small workgroups, like maybe a department in a company.

    And...a SQL Server database has a upper limit of about a million terabyte, not one terabyte. I can't speak from experience, but I think performance will degrade way before you get anywhere near the limit, but keep in mind SQL Server can make good use of additional CPUs and RAM, and you can do clusters.

  16. #16
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by XX7509
    Ok. first can someone please explaine to me the function of an Access database then? I have wanted to use it for many projects and on almost every single one of them, I was told Access is not suitable. Sooo... I'm guessing Access is just junk?
    It's a good "starter" database. Nice to learn about referential integrity, etc. with (which IMO MySQL lacks and you have to make up for in your application code). It also has its uses as a small-scale database. I'd use it as a personal finance manager, but I wouldn't use it for even a medium-traffic site.

    Second you said that a database filled to or near the 2 gb limit will function poorly, sooo.. the SQL database has a limit of over a TB, does this also mean that a SQL database will be crap when it reaches that level, or close to it?

    The upper bound of SQL Server is much more than 1TB, but do you really think you'll ever reach that? We've got some huge databases (MSSQL and Oracle) here at work and I don't think any of them are near 1TB.

  17. #17
    SitePoint Wizard davidjmedlock's Avatar
    Join Date
    Dec 2002
    Location
    Nashville, TN USA
    Posts
    1,688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For example:

    I used Access for my mother-in-laws photography website so she can add/remove pics and change service and contact page text herself.

    She might get 100-200 hits on her site per month, if she really puts her site out there. In this case, the site is very low volume, security was not a huge concern, and Access was much faster to put together a DB in.

    Ingold Photography

    Another case:

    American History Imprints wanted a searchable database that would eventually contain thousands of books, CD's, videos, and even DVD's. (Currently there are probably 4,000-5,000 products in inventory.) They also wanted to create an online library in the future. Access just wouldn't perform in this situation. It would be too slow and not nearly secure enough for the long term goals of the company.

    In this, I chose to go with SQL Server, due to the requirements of the project.

    So really, it's dependent upon the situation. For a forum of any sort, though, I would absolutely not recommend Access. In the long run, it won't suit your needs. Always plan for the future. Otherwise, you will find yourself doing an incredible amount of unnecessary work.

    Cheers!

  18. #18
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by XX7509
    ...does this also mean that a SQL database will be crap when it reaches that level, or close to it?
    How much data do you have?!
    Isn't 1048516 TB like 2 million years of around-the-clock mp3 audio?

  19. #19
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by jofa

    How much data do you have?!
    Isn't 1048516 TB like 2 million years of around-the-clock mp3 audio?
    Sign me up!

  20. #20
    SitePoint Member
    Join Date
    Dec 2002
    Location
    Clearwater, FL
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have an Access database with 3.5 million rows of about 200 characters/row. With forms, queries etc. it's about 1.1GB. It works fine for searching (This is a static db - no updates or inserts - I periodically re-populate it from SQL Server). It's easy for the non-IT people to use; they are familiar w/Access, not SQL Server and SQL.

    I also often use Access as the front-end for SQL Server using linked tables.

    I agree with prior posts that indicate if you expect a lot of hits, that Access is likely to have problems with concurrent usage and locks. However, in the short-term Access should be OK. You can always easily upgrade later if you need to.

    Bill Nye
    OCP
    (Yeah, it figures. I get certified in Oracle and find a job working with SQL Server).


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
  •