MySQL vs SQL Server

[V2008]

We are planning for open source MySQL instead of Microsoft SQL Server. I have worked on SQL Server for a long time.
Would I face any challenge to switch MySQL or would I miss something of Sql Server feature which is not in MySQL.

Does MySQL provides a utility to convert Sql Server to MySQL. If not, then what is the path I should follow.

Thank you!

 

[r937]

the sql is slightly different -- the main issues relate to things like date functions, concatenation, and minor data type differences

the mysql.com site has information on converting

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ingresman]

On the MySQL site there is an interesting TCO (Total Cost of Ownership) comprason between MySQL, SQLServer, DB2, Sybase and Oracle. It makes intersting reading.
to add to Rudys comments, you don't have the "raw" power of SQLServer in MYSQL, by this I mean you have no real access to the underlying platform using system stored procedures or all the other cool things that system stored procedures bring you. TSQL as you know is very powerful and you dont get that power on MYSQL.
Personaly I don't like too much business logic in a stored proc so those things don't bother me much.
Another cool feature of SQLServer is the remote servers functionality, where you can treat remote SQL Servers (and maybe any ODBC compliant source???) as part of single query giving you some quite powerful intergration features.
Perhaps these things will come one day in MYSql if enough people ask for the them and I can't see that really.

 

[DonQuichote]

As an extra to the above comments:

you will get "unlimited" connections with MySQL. You don't pay for a number of connections, so there is no reason to treat them as scarce. For instance, if you need an extra connection for error logging (to prevent a rollback rolling back all evidence to the cause of trouble), just open it.

Also, a connection is like a session. All variables you set remain active and defined until you close the connection. This also means there is no "GO" and every statement nicely ends in a semicolon. To define stored procedures you need a special trick because of this.

And *everything* is done in SQL. There is no proprietary diagram editor, for instance.

Finally, check out the query log. You can define an option that causes all queries to be logged. Not really recommendable on a production server, but a great development tool on your development machine. Combine it with a log viewer like Baretail or multitail and you can see what is communicated to your database. Live.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[ingresman]

Don,
Interesting point about connections. I belive that you can license SQLServer on a CPU basis rather than active connections. (or use express which is free)
What I would say is you should always treat database connections as scarce, they take a (comparativly) long time to create, and more importanty take more resource in the server. Interesting that you might open a new connection just before you rollback, I suppose it depends on why you roll back. Using MTS on IIS gives you connection pooling so you can connect quickly and with minimum resource usage.
And I don't really understand what you mean by the GO point, I think that's only used in the query anayser not a programatic connection.
And everything is done is SQL , no propriatary diagram editor, again can you clarify this ?

 

[DonQuichote]

What I remember from working with SQL server is that it comes with a management application that allows you to create graphical database schemes. Alas, they are some kind of BLOB that is not understood by anything but the management application. Working with different versions of the server and that management application was hell. Schemes can hardly be backed up or copied to another instance. Also, database backup and restore is done through the same management application. I sincerely hope that backup has improved since I worked with it, because backup always succeeded, but restore worked in less than 30% of all cases.

For MySQL, there is no irreplaceable management application. It comes with command-line applications and you can download graphical ones from MySQL, but you can use any other kind as well (like the web-based phpMyAdmin or a commercial front-end program). Most programs that offer backup possibilities create SQL files. These files can be edited in case they contain errors.

The rollback issue is as follows: your program starts a transaction and something fails (an exception is thrown or whatever). The standard error handling code is then usually:
[ol][li]Log the error.[/li]
[li]rollback the transaction as it cannot be completed anyway.[/li]
[li]inform the user[/li]
[/ol]
In that order, and error logging should never fail. If it is the database connection itself that failed, error logging should pick a file, e-mail or whatever to log to. But if you are logging to the database, the rollback will nicely delete the error log! So error handling should always go through separate channels. This does not mean I always open two connections. I open the extra one only if needed. But it is a real problem if one error triggers a "maximum connections exceeded" when it comes to logging the error.

I think you should reconsider the weight of connections when switching to MySQL. Opening a connection takes some resources off course, but not that many. There are drivers or libraries that offer connection pooling for MySQL, but you open a big can of worms if you do that. Remember that a connection is like a session. If you inherit an existing connection, you inherit all temporary tables, handlers, SQL variables, prepared statements and possibly open transactions. You should think twice and twice again before you burn yourself on connection pooling.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[ingresman]

If you inherit an existing connection, you inherit all temporary tables, handlers, SQL variables, prepared statements and possibly open transactions. You should think twice and twice again before you burn yourself on connection pooling

I don't think that that is correct. If it is it's a huge security hole.
Connection pooling is another minefield and much has been said about it.
The backups in SQLServer do work now (and I recall them working as far back as verison 6). You can do backups with tsql by issueing the BACKUP DATABASE command.

 

[DonQuichote]

Reading the documentation from the PHP library function mysql_pconnect, it seems to do just that. It specifically mentions locking by lock statements and transactions.

I did not test the temp tables or variables, but I do not know of any way to reset a connection and keep it open. Without such a way, or any way to clear all variables, connection settings, etc, I would surely expect them to be carried over.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[ingresman]

Yes ,fair enough , another reason not to use mysql I think !

 

[V2008]

I feel the discussion is strong and helpful.

However, I still have to investigate and that if I convert Sybase Stored Procedures to MySQL or MS SQL Server stored procedures; how much extra effort I would have to spend if I use SQL Server instead of MySQL.

 

[ingresman]

SQLServer grew out of sybase so they might be fairly close to each other in terms of syntax. If you have functional strored procedures you mght have some issues.
Best to look at the respective manuals.
As you can see most of us can talk for ever on the releative merits of a database, it's a great way to learn !