Must I pay for MS SQL Server?

Can I run Freeware MySQL on Windows server 2003 instead?

[jgar]

We believe we will be able to improve reliability by moving from Access databases to SQL server. Do we need to pay for it?

I would welcome any other tips regarding increased server performance ...

Cheers, Jgar

BACKGROUND
Our new Dell Poweredge 1600C server running Windows Server 2003 with a 256K connection is not performing too well. IIS needs restarting every 60 minutes as current requests build up to about to about 10. Site uses Access databases. The site was working better on a shared Windows 2000 server, and that server had many more sites on it and was a lower specification.

[Sinner_G]

You can run MySQL on a Windows 2003 Server. The problem is more the pages themselves. I guess you are now using ASP with your Access DB. If changing to MySQL, I think you will have to change to PHP, which would mean lots of changes in your pages, as opposed to using SQL, which also uses ASP.

[rpking]

As far as I know, mySQL will work fine with ASP on Server 2003... it has on all previous versions of IIS type servers.

[jgar]

Thanks for the replies.
Yes, we do use ASP, and changing to PHP is probbaly not an option at the moment.

Is it really not possible to integrate MySQL using ASP?

[IanTurner]

If MySQL is ODBC compliant it should run fine with ASP.

[Sinner_G]

Have to correct myself. MySQL is supposed to work with ASP if you install the MyODBC API. Never tested it though.

[jgar]

Thanks for that input.

MyODBC - using this, will I have to reprogram the asp scripts? Can I just add MySQL DNSs and create the SQL databases - Can it be that simple?

MS SQL - If I did buy this, which version is necessary to run a website - some versions seem to cost many thousands of $

[txbakers]

I run ASP with mySQL on A Windows server flawlessly. Yes, you only need to install the DSN with the ODBC driver and point all your connections to that instead of Access.

Your problem is not with the server, it's with Access. Access is NOT a server. it's a very nice little desktop database but it doesn't have the guts to support hundreds of reads/writes every hour like mySql or SQLServer can. That's why the big boys charge so much.

There are very few syntax differences between mySQL and Access, most notably dates: dates are always formatted as yyyy-mm-dd in mySQL. In the SQL Statement you would reference the date in single quotes, where in Access you needed the "#" symbol.

When I made the switch from Access to mySQL performance increased thousand-fold.

Go for it.

[mattglet]

MySQL works fine on an IIS box as long as you use the MyODBC adapter. my webhost supplied MyODBC for me. the only thing you have to change in your code if you are to use it, is the connection string to the database:

conn_string = "driver={MySQL ODBC 3.51 Driver};server=localhost;uid=username;pwd=password;database=db;"

-Matt

[jgar]

Thanks for those replies.

It sounds we will get much better performance if we use MySQL & MyODCB and make a minimum adjustment to the code.

How about creating MySQL databases from Access databases? I have looked around a bit and it seems that you have to pay for software to do this, and that not all products are reliable.

Can you convert directly from Access 2000?
Or does that only work if you're using MS SQL?

By the way, an error occured:
I have downloaded MySQL and MyODCB onto our XP machine.
When test the connection using the test button in MyODCB it say 'Can't connect to MySQL on 'localhost'

Thanks for any more thoughts ...

[txbakers]

How about creating MySQL databases from Access databases? I have looked around a bit and it seems that you have to pay for software to do this, and that not all products are reliable.

You don't need to buy anything to convert from Access to mySql.

First, I would create the tables in mySQL. Table creation in a real database is more involved than access because you have to specify the specific type and number of places.

Then, once the tables match, I export my access data to a text file (easy to do in access) and use the mySQL import feature to upload the data into mySQL. It's a nice method.

You can still use Access as a type of GUI front end to mySQL if you wanted to, but there are several GUI programs for mySQL.

On XP (which doesn't run services like mySQL natively) you have to manually start the server each time. On 2000/2003 you can install mySQL as a service which will start every time the machine reboots.

[RobinC]

Got to disagree re MySQL as a service on XP - that's how I'm running it, I've never had to manually start it (though stop/start is needed for upgrading). I can't remember exactly how I set it up to do this - but as I don't know everything about winxp, I must have followed the installation guide (if it wasn't automatic).

Robin

[aspdaddy]

>The site was working better on a shared Windows 2000 server, and that server had many more sites on it and was a lower specification.

Why are you changing the db server then? Why not just go back to Win2k

If you are using ODBC it will be slow, its old technology
ODBC vs. OLEDB [db.ittoolbox.com]

[jgar]

Thanks for all the help.

Just to update, we have spent 2 days implementing SQL Server. The results are FANTASTIC, so thank you for the encouragement.

WE CHOSE MS SQL:
We chose MS SQL in the end over the MySQL solution mainly because it seemed the easiest/quickest solution (our site was crashing regularly with Access databases, so we needed a quick solution - Also, since we are running Windows 2003 Server it seemed easier to use the MS product. Also, they have a good tool for importing Access databases. It is expensive though!)

WHAT ABOUT MySQL?
We did try the MySQL solution, but time was against us for solving some of the problems. If we had had more time, we would have persevered.

CONCLUSIONS: GO FOR IT!
We have been putting off the move from Access to SQL Server for years. In the end, it took just 1 day to implement (helped by your encouragement!). So, to anyone else considering this move: Go for it!

Cheers, Jgar

[plumsauce]

WE CHOSE MS SQL:

good choice ;)