Is MySQL a viable alternative to MS SQL Server

[ThomasSoerensen]

Hi all

I am going to do a complete overhaul of my database application. Views need to be eliminated in favour of tables and stored procedures.
And I will need to build a GUI for it.

The plan is that it becomes a product I can put in a cardboard box and sell/license to customers.

Since I am going to do so much work I am willing to consider the current production database a kind of prototype and move to a different platform.

I am contemplating moving away from a MS platform because their technologies seem to change a lot and get abandoned. In additions to stories on the internet I can see it at the current contracting client where some tools are not getting updated because they were developed with some MS technologies that are no longer supported by MS.

With the current MS centric path it would be a MS SQL db, GUI and logic in C# and report builder for reporting.

Would it be better in the long term to go for something like a MySQL database with logic in C++ and a GUI from Qt and reporting also in C++ (ore some XML stuff)?

the change in platform would cause a little extra pain now, but I can easily see the possibility of it being a better long tern decision if the non MS technologies are more stable. they just need to be mature enough now as well.

I expect all my potential customers to have the possibility of running my application on top of a windows server. They have windows servers for other reasons in their infrastructure already.

And I need to learn almost all the technologies anyway, so there is not much difference in the learning curve.

If you had free choice in platform and technologies, what would you choose?

[DimPrawn]

I think there's a few more questions that need answering.

What OS's will the GUI run on?

Does the DB and GUI run on the server only?

Would a web based GUI be an option?

How big and complex is the database, how many users, do need to think about multi-user concurrent access and locking?

How will the reports be presented (Excel, PDF, Word docs, charts and graphics)?

How complex will the GUI be? Will it be very slick or plain and simple?

How important is security, reliability, scalability, performance?

[ThomasSoerensen]

I think there's a few more questions that need answering.

What OS's will the GUI run on?

Does the DB and GUI run on the server only?

Would a web based GUI be an option?

How big and complex is the database, how many users, do need to think about multi-user concurrent access and locking?

How will the reports be presented (Excel, PDF, Word docs, charts and graphics)?

How complex will the GUI be? Will it be very slick or plain and simple?

How important is security, reliability, scalability, performance?

GUI will run on windows.

GUI and DB could be on same hardware or GUI could be on end user PC while DB is on a server.

Web based GUI is still an option but my experiences with them are that they are annoying due to lag.

I would so far prefer to write a GUI as an application. Performance of the GUI is important. Performance of the DB is also important as the DB will be big as in millions of rows.

Also, I have an irrational preference for C++ as development language. I believe it is in many ways superior to other languages in that is sits in the sweet spot between being close to the metal and being OO and abstracted.
And it is invented by a fellow Dane

The DB is not that complicated at the core but it will be big.
The core is approx 20 base-tables that are joined into 2 to 3 tables with up to 30 millions records. These tables are queried by fixed queries that are run after the data in the base tables are refreshed with a new data load from a large ERP system.
The output of the queries will be stored in tables for performance reasons and these result tables will be the basis for reporting.

Users will have the ability to create their own queries using a wizard style query builder which inputs parameters into predefined sequences of SQL and the result is a valid query which can be stored and run again and again.

Usually there will not be a lot of users on the DB. 1-5 most likely.

Report output format should be some reports with nice graphs in PDF and some output that are excel or excel equivalent for the user to use in investigating root cause analysis and updating setting in the ERP system.

I would definately like the GUI to be slick and very good looking and pleasant to operate. If possible to move slightly away from MS icons I see that as an advantage.

Importance of security, reliability, scalability, performance?
For security I see the use to be by central employees in a central location, most likely close to physical location of DB.
Reliability is less important as it is not to be used continously every day, but of course when the user wants to use it, it should perform all its operations flawlessly.
Scalability, most data will be purged from one use to the next. A dataset is a complete dataset at one point in time. Only a small portion of aggregated data will persist in order to create statistics.
Performance is very important as with MS SQL now I sometimes have great trouble getting the queries done in a reasonable time, even when on decent hardware (3000 GBP DELLserver).

I hope this helpes in you getting a feel for the nature of the beast.

cheers

[yorkshireman]

Thomas

Who are your potential customers? Will they have a strong preference for a MS solution?

Certainly MySql will be more than able to handle 20 million rows. But regardless of that I would be trying to develop the application with a database abstraction layer so it could use MSSQL/Mysql or whatever Db the customer likes - Oracle springs to mind.

How do you intend to profit from the application? Will it be a shrink-wrapped package you sell or something where ongoing support revenues are the key?

Given that you are still in the early stages I would at least consider hosting the database in the "Cloud". If you are crunching millions of rows down to small query result sets then the performance difference may not be noticeable anyway.

I also would consider what a web 2.0 GUI might provide. I know it won't compare in slickness with a .Net client - but increasingly users are recognising that the benefits of an available-anywhere web app more than compensate for thick client bells and whistles.

Cloud Computing gives one-man contracting outfits a real chance to build an ongoing revenue stream deploying reliable, low-maintenance, database driven web apps without the need for onsite support or owning a big data centre. It doesn't take many clients to really supplement the income stream!

Good luck with your app whatever you decide.

[Ardesco]

I think postgres is more stable and better at dealing with load than MySQL, so may be worth investigating.

[ThomasSoerensen]

Thomas

Who are your potential customers? Will they have a strong preference for a MS solution?

Certainly MySql will be more than able to handle 20 million rows. But regardless of that I would be trying to develop the application with a database abstraction layer so it could use MSSQL/Mysql or whatever Db the customer likes - Oracle springs to mind.

How do you intend to profit from the application? Will it be a shrink-wrapped package you sell or something where ongoing support revenues are the key?

Given that you are still in the early stages I would at least consider hosting the database in the "Cloud". If you are crunching millions of rows down to small query result sets then the performance difference may not be noticeable anyway.

I also would consider what a web 2.0 GUI might provide. I know it won't compare in slickness with a .Net client - but increasingly users are recognising that the benefits of an available-anywhere web app more than compensate for thick client bells and whistles.

Cloud Computing gives one-man contracting outfits a real chance to build an ongoing revenue stream deploying reliable, low-maintenance, database driven web apps without the need for onsite support or owning a big data centre. It doesn't take many clients to really supplement the income stream!

Good luck with your app whatever you decide.

The nature of the data in the database makes it impossible to do it as cloud computing.
The nature of the data is also what makes us go for a vendor side analysis and report-building business model to a on-client-site db and analysis and reporting because the customers feel more comfortable if the data never leaves their control.

Main income will be from selling/licensing the product and then some income from installing and initial configuration and end user training.

The idea with a db abstraction layer is good - I will look into that.

[d000hg]

MySQL is certainly viable, many big applications use it. For giant DBs, maybe not (though eBay does use it IIRC) but for any normal application it's fine.

More perplexing is wanting to use C++ for a modern GUI app, IMHO, if the client is Windows-only? MFC has still been getting some attention but winforms is so much easier to work with, and doesn't mangle your code the way MFC does... if you like C++ because it's 'clean' then MFC seems abhorrent C++ also has less standardised tools for DB access, unlike Java/.Net.

Web-based client... if you do it well lag isn't an issue any more than desktop i.e firing requests to the server for DB data. Flex/Silverlight for instance. Whether this is worthwhile for a corporate client, I couldn't say.

I'd probably go conservative and do C#/Winforms desktop app, using NHibernate possibly to abstract DB access, and give MySQL a go if you see it being a benefit.

[minestrone]

I think mySQLs growth will be stunted now oracle have bought it.

When it got nested queries and stored procs I think oracle crapped it and stepped in.

Anyway, to anser the question, yes.

[Freamon]

MySQL is the main RDBMS used within Google, so I think that demonstrates it's certainly viable for something like your application. But MS SQL server shouldn't have any problems either, so if you are having performance problems with SQL Server you probably need to look at your configuration, data model, indexing and partitioning strategy.

But as others have said, the way to go is definitely to build an abstraction layer and make your application platform-agnostic. Lots of organisations already have a preference for a certain RDBMS (because their BAU staff are already trained in it, and they have DBAs etc on staff to deal with it). If your application is basically a front end then anything which you can package into a windows installer should be fine - this gives you a lot of choices these days. Its even possible to build a JRuby app which will deploy through a windows installer (this is my personal favourite, but C++ or .net would be fine).

Is your application akin to a BI/reporting/data warehousing app? Have you looked into other technologies that could support it? e.g. in the MS space there is SQL Server Analysis Services / Reporting Services, and there are similar (in some cases open source) apps which would fit with MySQL or other RDBMSs.

[d000hg]

MySQL is the main RDBMS used within Google, so I think that demonstrates it's certainly viable for something like your application.

I think this is a little misleading. It might be used within Google for internal projects but Google use their own "megabase" for their main products... Google are really into NIH (wiki).

My own example is perhaps slightly misleading too, eBay use some custom modifications. However the fact remains unless you're doing something rather unusual, MySQL should be fine.