I have application that requires SQL Server 2000 as database storage. I do not really want to use SQL Server 2000, but I can user MySQL Server instead.
Application uses ODBC to connect to SQL Server Database.
I would like to know if it is possible to make fake SQL Server which will send and receive data to/from MySQL Server
application <---> odbc manager <---> fake SQL Server driver <---> mysql server
Any one if such thing is possible to make?
If your application simply uses vanilla SQL via the ODBC driver, you should be able to use MySQL with few problems. If it uses specific features of SQLServer, then you need SQLServer - you cannot realistically fake it.
You can use a provider model and just switch out which provider your using at run time.
Of course, the biggest issue will be in the differing SQL code support. So you will have to take care that all of your SQL is located inside of each provider and stay away from any sort of embedding it in your application logic.. which you should be doing anyway.
Another way is to simply change the ODBC data source at deployment time, but again, you will have to make sure the SQL code actually works in both environments; which is tough.
Typically supporting multiple database back ends is a art form in itself. Simple things like SELECT TOP 100 for SQL Server 2k versus MySql's LIMIT command are enough to keep people from doing this.
There's no real way of "faking" it because the database servers are fundamentally different. You would end up writing a fair amount of code just to translate a sql call from one to the other... Which is a waste of time.
I'd suggest you just bite the bullet and learn MS SQL Server.
This site shows a very simple example of how SQL Server, Oracle, and MySql differ on just one implementation of a select statement.
Not sure why you "do not really want to use SQL Server 2000" but, if you decide you need to and you have a PC with Windows available, you can use the Microsoft Database Engine 2000 Release A (MSDE2000A.exe). It is the real thing and free to use on a desktop.
http://msdn.microsoft.com/en-us/library/ms811304.aspx
I do not think it is available for download from Microsoft anymore but you might be able to find it somewhere else. If you can't find it, your next best option may be to use the 2005 version (SQL Server 2005 Express Edition) and make sure you do not use any new features since 2000:
I wouldn't.
You're going to spend so long persuading the two to play nicely to no real benefit. You'll have to do most code the SQL Server way to work in this scenario. Given these, you might as well just bite the bullet and learn to use SQL Server directly rather than trying to tie the two together somehow, I'm afraid.