ASP with MS Access or MS SQL Server

which is better, fast, easy and reliable

[dhaliwal]

Which one is better choice as a database backend

Access or MS SQL server?

I am getting both but the trouble is that the MS SQL server is on some other server, So i may face some connectivity delay in case of each query that is made on server.

I am having around 800 articles in ms access database and the size is around 8 MB.

So what would people prefer.

For Speed and Reliability

Thanks in advance for advice.

Dhaliwal

[bateman_ap]

If you have both already deff SQL. We use it in a variety of different configurations and one website uses SQL on a different server. Can I ask if the SQL server is at the same ISP as the Web server, as if the info can go through the local network as opposed to the the 'net it will be much better.

Access is good for small websites where you read data only and don't really write to the database, thats where you get most problems with Access.

[dhaliwal]

thanks for advice

as you asked, the both servers are with same isp, only the servers are different.

and we are using the access to just read the data and not write into it.

and also there is one thing that i seek answer, you think 8 MB access is big or small, it has only 800 articles of around 1000 words each.

I also wish to know, how much database size, i mean how much size of access can run with no problems. and at which point we shoudl think of changing to SQL Server

[bateman_ap]

I would work out how many people are going to use your site. To be honest as you have access to SQL and it doesn't sound like there is any price difference it is a bit of a no brainer.

Just for the record I used to have a site with about 250,000 page views a month with a access database serving pretty much every page, there was no writing to the database and it didn't fall over once. But that was due to not at the time affording SQL, as soon as I could afford it I upgraded and one SQL server is now serving about 10m pages a month, both read and write.

[digitalv]

Aside from the obvious performance benefits to SQL Server over Access, one major reason to switch is MEMORY. If your database is 8MB then you are loading the entire DB into memory every time someone accesses your website. If you're not properly closing your connections, or the page load is interrupted before the ADO close command is issued, that database STAYS in memory until you restart IIS. Eventually this will bring your website to a crawl.

Not to mention other things like running the risk of corruption if power is lost when the file is open or simultaneous read/write failures, etc. Access was never designed to be used on the web ... just because you CAN doesn't mean you SHOULD :)

[dhaliwal]

no cost difference at all

i have permission to use both.

thanks for advice

But one question, does it effect badly that i have sql server at some other server while my hosted server is somewhere else, they both are with same isp.

I think it will also help me in making things fast if i use certain stored procedures in SQL server.

I must also tell you that my server is a kind of dedicated one with only 5 websites on a full P 4 server.

So there is no extra websites, only mine sites, but i am only confused about using SQL server on some other server.

[digitalv]

You connect to a remote SQL server the same way you connect to a local one - you have to specify the IP or hostname of the server either way (connecting to the IP is recommended over connecting to the name since you save DNS lookup time).

Having SQL on a different server is actually a much better way of doing it. SQL is a resource hog - and that's a good thing because the more resources SQL can take up the better it performs. So when you have a server that does nothing but SQL both your web server and SQL server will run more efficiently since they don't have to share a CPU, RAM and disk I/O.

In ASP you can connect easily via ADO using something like the following:

strCon = "DRIVER=SQL Server;SERVER=x.x.x.x;UID=sa;PWD=password;DATABASE=YourDBName"

Replace "x.x.x.x" with the IP address of the server running SQL Server. Then whenever you need to connect use your con.open strCon just like you would with MS Access. Don't bother with creating a DSN, it actually slows it down because its an extra step for the web server. You probably wouldn't notice it but every little fraction of a percentage of performance loss adds up.

[dhaliwal]

thanks DigitalAv

Your advice is quite concrete and it helps a lot in making a decision,

I hope that it would be better for me to change to SQL server

Thanks