The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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.

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

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?

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

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.

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.

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.

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.

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