Access v. MSSQL

L

LegalAlien

Perch
I use a lot of Access databases for ecommerce and other dynamic websites, but am slowing moving towards the idea of using MSSQL. Was wondering if anyone could give me some opinions on the following three points:

1. I've read that MS Access doesn't perform well under a high user load (depending on who you believe, some say it can slow down your website when more than 10 users are online at once.) I sometimes have well in excess of several times this amount of users in some of my stores and have never noticed the website slowing down because of it.. any comments anyone?

2. I like using Access because of the convenience of downloading and using the information offline, and its easy to back up my files. How simple is it to download backups of single AND multiple MSSQL databases?

3. Refering to the last point, I've been pretty paranoid about keeping regular backups of my databases since the total loss on Win4 earlier this year. I'm sure that is very unlikely to ever happen again (touch wood), but I seem to remember that such a hard-disk loss does not effect MSSQL. Is that true, and if so, why? Does this mean I'd have less to worry about when using MSSQL and therefore no need to backup my databases quite as regularly?

Thanks guys!
 
I'd strongly recommend you go for MSSQL. MS Access is not designed or meant for ecommerce sites that get good traffic.

And yes, MSSQL is on a separate server so it will not be affected by what happens on the web server. MSSQL backups are taken daily and are moved to our backup servers
 
Thanks Yash - but to be clear, how easy is it to take backups of MSSQL databases, and if necessary replace an existing db with a backup? Or, for example, if I wanted to upload a new table to bulk update product listings...
 
If yiu have enterprise manager, all this becomes pretty easy. You can backup and do alot of sql server operations easily

If you don't, you'd have to use our online MSSQL manager. Although it does have a backup utility, it basically creates a SQL dump for you to download. You could use that SQL dump to also restore. It's not as easy as using FTP
 
OK - help me out - where would I buy MSSQL enterprise manager please? I can find other database utilities (like at http://www.teratrax.com/ for example), but I can't seem to pin down enterprise manager... is it part of the SQL server or something?
 
LegalAlien said:
Thanks Yash - but to be clear, how easy is it to take backups of MSSQL databases, and if necessary replace an existing db with a backup? Or, for example, if I wanted to upload a new table to bulk update product listings...
Click to expand...
Don't be fooled here...

Using SQL Enterprise Manager will indeed allow you to see and play with your database, but doing a backup is not as easy as it would seem. You have two options (IMHO): Export, and Backup.

You can use the Export feature to export to a local SQL server or Access database. Exporting to a local SQL server is ideal, but most don't have a local SQL server. However this will provide you with an exact copy of your databse locally. You can also export to a local MS Access database. This will backup your data fine, but some of the fields will change types which may cause problems if you then want to use the MS Access database on it's own. Specifially, the SQL Identity field does not convert to an MS Access AutoNumber field.

If you use the backup feature of SQL Enterprise Manager, the backup file is actually stored on the JH host server and not locally. I have not tried this, but I would imagine that you then need to ask JH to ZIP up the file and place in your site so you can download it via FTP. This will increase your monthly traffic, as will any traffic through FTP and HTTP.

This being said, SQL is better for larger and busy databases. However I have run with MS Access on several sites for years without any hint of corruption which some say happens. As for load, I would not say that my site is as busy as microsoft.com, but some sites are busy. However, I am not relying on my sites for income, and indeed I am not conducting any sort of e-commerce site.

Hope this helps.
 
Using MS Access for busy sites might not make any difference to you Logan, but to us as a hosting company it does. It is CPU intensive and can cause unexpected problems, especially with the Access ODBC driver which is sooo unstable.
 
I use MDSE on my desktop and that is a totally free version of MSSQL for development purposes. You can use it for the export feature.
 
Yash said:
Using MS Access for busy sites might not make any difference to you Logan, but to us as a hosting company it does. It is CPU intensive and can cause unexpected problems, especially with the Access ODBC driver which is sooo unstable.
Click to expand...
Read my post again Yash. I never once suggested that it didn't matter, and didn't suggest that MS Access had or did not have any effect on the server performance.

I simply offered my observations and experience to fellow customer.
 
Yash said:
Using MS Access for busy sites might not make any difference to you Logan, but to us as a hosting company it does. It is CPU intensive and can cause unexpected problems, especially with the Access ODBC driver which is sooo unstable.
Click to expand...

Is this why I keep getting:
Microsoft OLE DB Provider for ODBC Drivers error '8007000e'

[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x2bb8 Thread 0x30a4 DBC 0xad7e074 Jet'.

I've noticed the above error quite often now when I go through my site on occasion, like right now for instance. Is this what you mean by unstable? I've noticed that when this happens, it stays this way for awhile before it fixes itself. I'm beginning to think that this is why my site isn't getting indexed by google at the rate it used too. Do you guys have to restart IIS each time to fix this or does it just go away when the server load lightens up? It's been close to 30 minutes right now and it still doesn't work. I've seen this happen a couple times in a day and am now monitoring it to see exactly how often it happens.

Is anyone else seeing this problem? I'm on win6, here's my connection:

<%
Dim strConnect
strConnect = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="& Server.MapPath("../../pathtodatabase.mdb") &";DefaultDir="& Server.MapPath(".") &";DriverId=25;FIL=MS Access;MaxBufferSize=512;PageTimeout=5"
%>

Anyone have a suggestion for fixing this so I don't see the above error and still be able to use an Access database? This whole site revolves around it's pages indexed in google and with my luck, this probably has been happening everytime the spider visits my site.
 
Thanks for the tip Yash, just switched one of the connections to it to see if this connection still works when the others go down.
 
I downloaded and installed MSDE, but this didn't seem to 'install' Enterprise Manager. I'm not exactly sure what it did do. I did get a folder on my c drive C:\MSDERelA, which contained a setup file for SQL Server. So I tried to run this. But couldn't - it gave me the error message that I need a strong SA password. I've tried to read up on that but it's completely lost on me.

1. Am I on the right track?
2. Would anyone like to try an explain exactly what I need to do here in simple terms?
 
LegalAlien said:
I downloaded and installed MSDE, but this didn't seem to 'install' Enterprise Manager. I'm not exactly sure what it did do. I did get a folder on my c drive C:\MSDERelA, which contained a setup file for SQL Server. So I tried to run this. But couldn't - it gave me the error message that I need a strong SA password. I've tried to read up on that but it's completely lost on me.

1. Am I on the right track?
2. Would anyone like to try an explain exactly what I need to do here in simple terms?
Click to expand...
If I remember correctly, if you run "setup sapwd=Password1" ensuring that "Password1" is a password that contains upper and lower case letters, and also contains at least one numeric digit, then Setup will complete.
 
Logan said:
If I remember correctly, if you run "setup sapwd=Password1" ensuring that "Password1" is a password that contains upper and lower case letters, and also contains at least one numeric digit, then Setup will complete.
Click to expand...

Thanks Logan - I just don't get what sapwd is. The error message tells me to use the switch sapwd.... How do I run/use or set up this password?
 
LegalAlien said:
Thanks Logan - I just don't get what sapwd is. The error message tells me to use the switch sapwd.... How do I run/use or set up this password?
Click to expand...
Just change to the directory where setup is in a command prompt window and enter the line I gave you above.

SA = System Admin, THE admin account for SQL Server
Strong Password = a password that has letters and numbers and upper and lower case.
 
The SA Password is the System Administrator password for SQL Server (MSDE). When you install MSDE, you need to create the SA Password.

There should be an HTML readme file in with the installation files. The switches are explained in detail in there. It's kind of a pain to set up but then it runs ok. Also, don't expect to get Enterprise Manager (EM) once you are successful. It is not included in MSDE. If you have EM from another source, it can be used to manage your installation of MSDE.

I have read elsewhere that you can get the trial version of MS SQL Server (as opposed to MSDE) and it includes EM. Once the trial is over and you uninstall, EM is still there or maybe it just doesn't expire when the server part does or something like that.
 
You must log in or register to reply here.
Back
Top