What costs of using MS SQL?

[david7777]

What costs are involved in using MS SQL instead of MS Access?

[ionsurge]

Well it depends on your host, but MsSQL tends to come *free with all packages, and intertwined with the webspace you have, so database sizes are incorporated into that field.

It is extremely popular, even more so than Access.



* A few hosts charge for this service, usually yearly, but that is an indicator of unreliability in some cases. Good hosts usually have it ready anyway.

[Fatman]

does it have to be MS sql?

why not something like mySql?

[oracleguy]

If you are going to be running it on one of your machines, you should know it can become very taxing on system resources. Especially RAM. If you aren't feeding live data to it or using it as the back-end for a site thats gets lots of traffic you wouldn't need as beefy of a computer to run it.

[david7777]

Thanx for the replies.

I am using it for a potentially huge site - currently +/- 500 registered users, but growing drastically... The site is getting a total redesign, and the security benifits of ms sql server using stored procedures are very inviting...

It is currently, and will continue to be, hosted on a remote server of an ISP.

I realise that there might be extra hosting costs from the ISP for sql server capabilities, but other then that, are there any downfalls to sql server? (Bearing in mind that I dont need to worry about the hardware it is run on)

[Spudhead]

Well, apart from needing a Windows box to run on, instead of a nice safe Linux or Sun machine :), there aren't many. MSSQL is a big, meaty bit of kit that will handle pretty much anything you're likely to throw at it. You might encounter some SQL syntax differences with Access, but that's Access for you ;)

[Fatman]

if its a linux box youll get Mysql which again has differences to mssql

fatman

[david7777]

does mysql support stored procedures?

[raf]

No. But it's in the pipeline.
http://www.mysql.com/doc/en/ANSI_diff_Triggers.html

Using stored procedures might result in quicker responsetimes, but can be quite taxing on the dataserver + make your app less portable. But you've got a point on the security aspect (that is, if you only allow datamodifications through stored procedures, which means you couldend up with writing a fair bit of stored procedures)

I doubt that MySQL would perform worse then MsServer (even if you'd use stored procedures there). As soon as you've got a fair number of simultanious users and if you use the recordset buffer feature, MySQL will perform better.

Once you go over 500 simultanious users, even DB2 can't keep up with MySQL and Oracle. (i wish i had that much user to notice).

In short, i think that a LAMP will be much cheaper and have better performances then a Windows-IIS-MsSQL-ASP(?) setup

[david7777]

Thanx raf.
How difficult would it be to convert from an access db to mysql?

[raf]

How difficult would it be to convert from an access db to mysql?
Depends (heuheu!)
Access has an export wizzard and accordingly to there helpfile, you should be able to export tables (! --> so no db objects) to any ODBC db ... (

check out this
http://www.mysql.com/doc/en/LOAD_DATA.html

wich holds a lot of usefull info on the subject + this comment
----------------------------------------------------------------------------
Posted by Arcady Glush on Tuesday May 20 2003, @7:22am [Delete] [Edit]

Transfer data from MS Access into MySQL. Easy way:

If your MySQL table structure is identical to MS Access one, then create ODBC DSN pointing to MySQL: (Use MySQL ODBC 3.51 Driver DSN, if you don't have one download it from www.mysql.com). After that open Access Database, attach MySQL table using File/Get External Data/Link Tables. Choose file of type: ODBC Databases (last option) then follow the rules for attaching ODBC tables. After that it is quite simple, you can use Access query to insert data into the attached table(if you wish to skip some fileds), or, if you want to transfer entire table - simply highlight all the records in the opened Access source table, copy them to clipboard (Ctrl+C), then open attached MySQL table and select paste append option. The entire process can be done even quicker then reading the above instructions.

If your attached MySQL table has all the data marked #Deleted - just ignore that.

I have tested it with MySQL 4.0 and MS Access97, but I am pretty sure it will work with Access2000/2002

If you have questions or problems, just drop me an email.

glush@optushome.com.au or arcady_glush@national.com.au (Australia)

[david7777]

Thanx again raf! :thumbsup:

Thats all the convincing i need :D