Microsoft SQL

Post by **jordansparks** » Mon Jan 25, 2010 9:51 pm

MySQL seems to be losing its momentum. I'm concerned that it's not as robust as it could be. As just one example, MS has 1000 fulltime developers maintaining MS-SQL. MySQL has significantly less and it does not include the original team. If it included the original team they could do a lot more with a lot less. It's the loss of that team which concerns me the most.

Microsoft SQL is also free as long as the db is less than 4GB. Considering a transition. Any opinions?

Rickliftig · Post by **Rickliftig** » Tue Jan 26, 2010 3:14 am

i believe that my database is only 50-60MB - might take a little while to get to that 4gb size.

I suppose that the transition makes sense. In what ways is mysql insecure? And how much extra security (and what features) will MSsql provide?

My biggest question is how much time will it take to transition to a new database (for your team)? I believe that there are other OD improvements (like the cloud backup) that will be far more fruitful.

Unfortunately, the end user (and some of the companies) have no clue as to what the database engine is. As long as it works, they are happy. Early on in my dental computing experience, I experienced cross-linked tables and corrupt backups - the cross link was duplicated on the M, T, W, Th tapes! It was a learning experience! So happy that this is not much of an issue with the 'SQLs'

Post by **jordansparks** » Tue Jan 26, 2010 6:49 am

I'm talking about this:
http://monty-says.blogspot.com/2008_11_01_archive.html

And with the purchase by Oracle, I predict nothing but stagnation for MySQL. They just aren't going to put the proper resources into it.

Furthermore, I have always had a beef with the MySQL-C# connector. It was written by a mediocre programmer and it will cause problems as we add more features.

drtech · Post by **drtech** » Tue Jan 26, 2010 11:10 am

will this require a windows server?

drtech · Post by **drtech** » Tue Jan 26, 2010 11:12 am

I don't like the idea at all at first glance, cuts out and moves away from the open source model.

Do you have specifics that mySQL does not do for Open Dental that MS-SQL would?

drtech · Post by **drtech** » Tue Jan 26, 2010 11:15 am

sounds like the problem is mainly with the new version...not enough bug testing like you say, not enough resources...

but what keeps us from just using the stable version we are using now? Are people having trouble with it?
What about the web version and third party applications...would they they have to rework too to make it work with a new database engine?

I know I like mySQL because it is so widely used on the web and easy to communicate with and mainly to not get locked into windows. I know this argument has been gone through before, so the main thing I want to know is can MS sql run on a linux server? I am sure it can't. I love linux for a server (never will work for a desktop without major improvements) but I think that would be a bad idea to require a windows server.

wjstarck · Post by **wjstarck** » Tue Jan 26, 2010 3:02 pm

Having dealt more often than I've wished to with dead/dying/loss of interest/on life support software projects over the last 12 years, I'd support the move.

Today's Microsoft is not the Microsoft of even three years ago, thanks to an ascendant Google. In my experience, the technologies that I can use to extend OD functionality more often than not favor MS-SQL over MySQL. While it's true that on the web, PHP-MySQL has become a standard of sorts, there are other options that are less clunky than PHP (WebDNA is one that comes to mind), and what happens to PHP if MySQL dies a slow miserable death? It wouldn't be the first time a company deliberately purchased another to eliminate the competition.

We virtualize Windows 2008 server inside an Ubuntu Linux box running 9.10 and it's quite fast, stable and robust. Our mail server and DNS run on Linux, so we have the best of both worlds. Our web server runs on a Mac Mini, although I've never much cared for Apple's hellish mismash of NeXT and BSD Unix.

By and large, while studies have shown that true open source projects result in better software, I'm convinced that the ultimate quality of a software project is far more dependent on the quality of the lead developer(s) and the team. And ours are very good indeed.

So, at the end of the day, a system that is dependent on a database engine with a questionable future is just as problematic (if not worse) than vendor lock-in...

drtech · Post by **drtech** » Tue Jan 26, 2010 5:41 pm

so, it is felt that mySQL is going to die out?

V Suite · Post by **V Suite** » Tue Jan 26, 2010 6:30 pm

jordansparks wrote:MySQL seems to be losing its momentum. I'm concerned that it's not as robust as it could be. As just one example, MS has 1000 fulltime developers maintaining MS-SQL. MySQL has significantly less and it does not include the original team. If it included the original team they could do a lot more with a lot less. It's the loss of that team which concerns me the most.

Microsoft SQL is also free as long as the db is less than 4GB. Considering a transition. Any opinions?

Jordan,
I understand your position. I am simply concerned about data stability. Would OpenDental users be required to switch to MSSQL immediately? Or would OpenDental continue to support the current MYSQL installations? And if the former, would the transition be safe?
In addition, IIRC it is possible to store the images in the MYSQL database (though deprecated again IIRC), which would increase the size way beyond 4GB.

jordansparks wrote:I'm talking about this:
http://monty-says.blogspot.com/2008_11_01_archive.html
And with the purchase by Oracle, I predict nothing but stagnation for MySQL. They just aren't going to put the proper resources into it.
Furthermore, I have always had a beef with the MySQL-C# connector. It was written by a mediocre programmer and it will cause problems as we add more features.

I agree that it strongly seems that Oracle will not develop MySQL further. Is there any version of MySQL that we should download to tide us through the stormy future whether or not OpenDental transitions to MSSQL?

Meanwhile will MariaDB (the alternative database server software by the same monty cited above, the founder of MySQL) work in place of MySQL for OpenDental?

drtech · Post by **drtech** » Wed Jan 27, 2010 5:46 am

MariaDB...interesting
http://askmonty.org/wiki/index.php/MariaDB

Jordan, is this a viable alternative you think from the original founder of MySQL?

richardwaite · Post by **richardwaite** » Wed Jan 27, 2010 6:08 am

Please consider PostgresQL. It is open source, BSD licensed, and is much closer to Oracle than any of the other open source databases. It runs well on Windows as well as Linux. The similarity to Oracle especially should provide you with an easier time supporting that DB.

Post by **jordansparks** » Wed Jan 27, 2010 3:48 pm

For those who read this thread later and do not appreciate the very complex topic, let me first say that the solution that we have right now is very stable. I'm not concerned about current stability. It's far more complicated than that.

This discussion is instead a very long-term strategic discussion. We are thinking big picture. One database supporting hundreds/thousands of clinics. Massive databases with many images stored in them. We are trying to plan the best way to add features and write software that is bug free the first time out. We are trying to predict which database(s) will be extremely stable as well as cost effective.

One big reason to consider MS-SQL is that it will pair very nicely with C#.

It's not so much that I think MySQL will die out. But I do think it will become a less realistic option for enterprise applications. Regardless of what technologies we choose, I expect there to be periodic migrations to newer technologies. The fewer the better of course. Mogo irritated a lot of people when they migrated databases. Perhaps this is why Dentrix and SoftDent have delayed for much longer than most people would have liked. Migrations are not necessarily without risk.

We do not plan to change databases anytime soon. It would be years at least. We have many other higher priorities at the moment.

Post by **jordansparks** » Wed Jan 27, 2010 7:48 pm

To revist the cost issue. The price of MS-SQL is very much worth it for what it does. For those who need the features, it is worth it. But we would take a phased approach which would very likely result in users always having a choice between MySQL and MS-SQL.

drtech · Post by **drtech** » Thu Jan 28, 2010 6:31 pm

what are some of the features that MS SQL would give?

Post by **jordansparks** » Thu Jan 28, 2010 9:13 pm

More rigorous management of connection pooling including disposal of stale connections. Bug free connector (we are stuck on connector version 1.09 because every time we try to move to a newer version, it's buggy). Proper handling of very large data such as images. Large data must be broken up into chunks and then reassembled reliably on the other end. That is not easy for a connector to do. Some data seems to come back as byte arrays instead of the data type we are interested in. Proper handling of date types. In MySQL, we end up with 00-00-0000 as a minimum date. That's in invalid date which should be 01-01-0001. Converting between the two on the fly is impossible for very complex reasons. The MS-SQL database would be a closer match to the C# date type. Well-thought-out framework. For example, earlier versions of MySQL connector use % for parameters, and then they suddenly switched to @. That's a breaking change which they made very casually and without remorse. A breaking change requires a rewrite on our end. Management tools. Support is easier and more reliable when we have a good interface to the database. MySQL does not come with such an interface, depending on 3rd party software. That slows us down. Not to mention that the 3rd party software for MySQL is frequently junk. Far too many bugs. Backups. Backups would be easier in MS-SQL because of proper management interfaces. MySQL dumps are big, very slow to load, crash when loading, etc. This make building good backup tools harder. A platform that I know is going to continue to grow for 20 years rather than stagnate. A better installer. We shouldn't have to be afraid to upgrade. MySQL has stopped caring about bugs and has purposely released buggy versions. They lost my trust.

Another disclaimer for those not familiar with the above technical topics is that we currently use a very stable version of MySQL. What we are concerned about is what will happen if we decide to move to newer versions of MySQL. Bugs are not an acceptable option.

apollonia · Post by **apollonia** » Sat Jan 30, 2010 7:41 am

having played around just a little bit with C# and MS SQL and also playing around with C# programming in visual studio 'connected' to mySQL, i can confidently agree with Jordan's strategic thinking. As the world moves forward, there will be more tight integration of programmer friendly new features in the MS world that will increasingly make bitrot a problem for the OD development team.

as much as i hate to admit it, one of my primary reasons for liking OD was it's mySQL component, which made it easier for me to use its data for other purposes.

if the long range interests of all are considered, and given the pain it will cause, i would still support the conversion.

it's very analogous to my recent conversion from Schick CDR to DICOM, which installs and converts the existing data to a standardized format that runs on MS SQL. i hated the idea, but still support its implementation.

There are also some significant changes that might allow electronic data records to be standardized, and that would also push toward the eventual cloud version of OD that is a way off, but which all can see must eventually happen.

"the more you learn about OD, the more you will like it"

V Suite · Post by **V Suite** » Sat Jan 30, 2010 8:59 pm

jordansparks wrote:1. More rigorous management of connection pooling including disposal of stale connections.
2. Bug free connector (we are stuck on connector version 1.09 because every time we try to move to a newer version, it's buggy).
3. Proper handling of very large data such as images. Large data must be broken up into chunks and then reassembled reliably on the other end. That is not easy for a connector to do. Some data seems to come back as byte arrays instead of the data type we are interested in.
4. Proper handling of date types. In MySQL, we end up with 00-00-0000 as a minimum date. That's in invalid date which should be 01-01-0001. Converting between the two on the fly is impossible for very complex reasons.
5. The MS-SQL database would be a closer match to the C# date type.
6. Well-thought-out framework. For example, earlier versions of MySQL connector use % for parameters, and then they suddenly switched to @. That's a breaking change which they made very casually and without remorse. A breaking change requires a rewrite on our end.
7. Management tools. Support is easier and more reliable when we have a good interface to the database. MySQL does not come with such an interface, depending on 3rd party software. That slows us down. Not to mention that the 3rd party software for MySQL is frequently junk.
8. Far too many bugs.
9. Backups. Backups would be easier in MS-SQL because of proper management interfaces. MySQL dumps are big, very slow to load, crash when loading, etc. This make building good backup tools harder.
10. A platform that I know is going to continue to grow for 20 years rather than stagnate.
11. A better installer.
12. We shouldn't have to be afraid to upgrade. MySQL has stopped caring about bugs and has purposely released buggy versions. They lost my trust.

Another disclaimer for those not familiar with the above technical topics is that
13. we currently use a very stable version of MySQL. What we are concerned about is what will happen if we decide to move to newer versions of MySQL. Bugs are not an acceptable option.

Jordan

I numbered your points to more easily reference them. Now, I understand where you are coming from, and I agree from the standpoint that for my business, I just want a solution that works. Thus far OpenDental/MySQL has worked, which is a testament to all the programmers involved (, especially Jordan, for many, many reasons... programmers would understand more of what he would have to do to get C#, dotNet, Oracle, MySql, etc to work together). I appreciate the pace of new features being added and the need for a stable maturing database platform to allow more.

So, just for information,

2. Is the Connector built into OpenDental?
3. Are images stored in the database apart from those normally stored in the A-Z folders?
7. Is Navicat okay(ish)?
8/13. Which version of MySQL 5.0 is currently being used? And which version can we safely update to in the interim? (will it be as stable or more stable?) How do we tell what version of MySQL is installed?

Finally, since monty, the original founder of MySQL, has organized a team around the development of MariaDB (supposedly a drop in open source replacement database server software for MySQL), Will MariaDB work in place of MySQL for OpenDental?

V Suite · Post by **V Suite** » Sat Jan 30, 2010 9:16 pm

richardwaite wrote:Please consider PostgresQL. It is open source, BSD licensed, and is much closer to Oracle than any of the other open source databases. It runs well on Windows as well as Linux. The similarity to Oracle especially should provide you with an easier time supporting that DB.

Jordan,

I get the impression from the OpenDental site that Oracle compatibility was abandoned some time now, would this preclude consideration of PostgresQL? Then again, does PostgresQL have the features we need? will it require rewriting queries? Is it bug-free and stable? Is it being developed actively and is it likely to continue? What is its marketshare?

This Wikipedia page compares several different databases in various areas http://en.wikipedia.org/wiki/Comparison ... nt_systems

Cheers

Post by **jordansparks** » Sun Jan 31, 2010 8:46 am

As a reminder to everyone, this is the sort of complexity we would deal with if supporting a second database engine:
http://www.opendental.com/manual/oracle.html
It would probably be just as complex if we choose to support MS-SQL.

Now, about all the reasons I listed. We can work around nearly all of them. And working around various issues is probably going to be much less complex than a full-scale move to MS-SQL.

2. The connector is a dll. Easy for us to upgrade to a newer version, but there are bound to be issues when we do.
3. Some icons are currently stored. An outside programmer added image support in the db, but it was half baked, so we removed it.
7. Navicat is OKish. I got fed up with it and use SQLyog which is also OKish.
We use 5.0.22 from May of 2006. There are good reasons why we stay on that version. We prefer all of our customers to have the exact same version of MySQL to simplify troubleshooting and support. If we were to upgrade to a newer version, and we were to see a bug, we would have the complexity of trying to decide if the bug was in our software, the change in MySQL version, or the connector. It's more efficient to stay at 5.0.22 unless we have a good reason to change. An example of a good reason to change would be if 5.0.22 would not install properly on Windows 7. If we change MySQL versions, we would change connector versions at the same time.

There are many good reasons to stay on MySQL and that is probably what we will do. I just thought it was good to keep options open.

ajoshi · Post by **ajoshi** » Sat Mar 13, 2010 7:25 am

Hello,

Don't know if you are still thinking about it.

My two cents:
- Would prefer a migration to another opensource or at least continued support for opensource.
- Migrating from one db to another is not trivial (I have done it for a major project from MySQL to Oracle so I know). It will take at least 6 months before things are stable.
- The latest versions of MySQL seem to be quite stable (5.1.41) but I can vouch that MySQL does not do well with gigabytes of data.
- Given a choice I would prefer to see Postgres - capabilities similar to Oracle and yet free - it is very actively developed and the development teams focus (I am not connected to them in any way) has always been stability and robustness. It is used in may document management systems that manage gigabytes of data. Also supports more datatypes natively than pretty much any other database there is.
- For non-opensource (but free) one I would vote for Oracle Expreess. Oracle is way ahead of the other databases in performance, stability etc. but obviously pricey when going beyond the free edition.

Misc MySQL notes:
- Try MySQL Workbench - it is amazing and definitly better than using Navicat for development.
- I thought there is an official .NET connector for MySQL (http://www.mysql.com/products/connector/net/) - I would imagine that is stable.

Disclaimer: My self interest is in seeing Opendental on Linux (having started to try and compile it just today).

Amit

Post by **jordansparks** » Sat Mar 13, 2010 8:21 am

We do use the official connector.

tomle · Post by **tomle** » Mon Mar 22, 2010 11:07 am

Hi Jordan,

Take a look at this database, http://www.postgresql.org/

It has been around since 2000 and it is very good.

michael_k · Post by **michael_k** » Sat May 22, 2010 10:34 pm

I am installing OpenDental in a new office and would like to use Microsoft SQL Server 2008 instead of MySQL.

How can I achive this?

Thank you!

-Michael

Post by **jordansparks** » Sun May 23, 2010 7:12 am

I'm sorry, that's simply not an option. We would have to overhaul numerous queries throughout the program to allow for that. Even though they both use SQL, the SQL is not 100% cross-compatible.

apollonia · Post by **apollonia** » Sun May 23, 2010 9:01 am

i don't know if it's stockholm syndrome at work, but i find mySQL to by around 10,000 times easier than microsoft's SQL Server.

working with SQL Server seems to be like herding squirrels, where mySQL is a walk in a park.

it might be some sort of left-brain/right-brain thing (no brain?), but ple-e-e--e-ase don't take my mySQ-eLLLL away-ay-ay-ay

michael_k · Post by **michael_k** » Sun May 23, 2010 5:10 pm

Personally, I find Microsoft SQL Server is easier to manage than MySQL. While some features of MS SQL Server may be more appealing to an enterprise than a dental office, security and ease of management are my biggest drivers to use SQL Server. Based on http://www.microsoft.com/sqlserver/2008 ... .aspx#none SQL Server has had less than 10 vulnerabilities according to the third party National Vulnerability Database. MySQL has had more than 70 vulnerabilities. Third party Security Innovations found that SQL Server on Windows is more secure than MySQL on Linux, with 46% fewer vulnerabilities and 48% fewer days of risk. ESG reported that SQL Server is “years ahead” than MySQL... SQLServer is integrated with Microsoft Update for security updates. MySQL has no automatic update patching, has no C2 or CC certifications (mk:government security criteria). Not saying that MySQL has to be removed, but perhaps there is a possibility to leverage both products. My 2 cents…

Post by **jordansparks** » Mon May 24, 2010 6:12 am

Good to know. Yes, one of my main arguments for including MS-SQL is to attract large enterprise customers.

sushil_india2000 · Post by **sushil_india2000** » Sun Jun 27, 2010 4:48 am

I am using mysql from last several here is some some important fact about mysql.

1) MySQL has been known to handle databases as large as 60,000 tables with more than five billion rows. MySQL can work with tables as large as eight million terabytes on some operating systems, generally a healthy 4 GB otherwise.
2) MySQL is used by NASA and the United States Census Bureau, among many others.

In my region, doctor has widely appreciated dental software and they like availability on Linux operating system as well. In this direction MONO team is doing job to run windows application on Linux.

I am doubtful, if Microsoft will consider having a SQL Server version for Linux in future. In this case we will be loosing our Linux development effort and running dental on Linux OS.

Sushil

snecklifter · Post by **snecklifter** » Sat Aug 14, 2010 7:31 am

michael_k wrote:Personally, I find Microsoft SQL Server is easier to manage than MySQL. While some features of MS SQL Server may be more appealing to an enterprise than a dental office, security and ease of management are my biggest drivers to use SQL Server. Based on http://www.microsoft.com/sqlserver/2008 ... .aspx#none SQL Server has had less than 10 vulnerabilities according to the third party National Vulnerability Database. MySQL has had more than 70 vulnerabilities. Third party Security Innovations found that SQL Server on Windows is more secure than MySQL on Linux, with 46% fewer vulnerabilities and 48% fewer days of risk. ESG reported that SQL Server is “years ahead” than MySQL... SQLServer is integrated with Microsoft Update for security updates. MySQL has no automatic update patching, has no C2 or CC certifications (mk:government security criteria). Not saying that MySQL has to be removed, but perhaps there is a possibility to leverage both products. My 2 cents…

michael_k: Sorry, but taking comparisons of security vulnerabilities from Microsoft's site is _very_ poor form and will never give an un-biased view that this topic requires.

jordansparks: I understand your concern following Oracle's acquisition and I share it. There is PostgreSQL though I'm not sure how realistic this is as an option. MariaDB is available as an upgrade path and would retain cross-platform compatibility and support going forward. I would be sad to see an open source dental system run on a closed source database - I think that is my main issue here.

navakas · Post by **navakas** » Mon Nov 01, 2010 3:21 am

I would strongly support MS SQLServer. MySQL is losing momentum, and the tools for managing it are woeful compared to the brilliant SQL Server management studio, profilers, etc with MS.
mySQL is not ACID compliant unless you use the slower InnoDB tables, and quite frankly any database that allows illegal dates in a date field is not serious. For my 2 c, go SQL Server

michael · Post by **michael** » Wed Nov 03, 2010 10:47 am

Advertising from Oracle suggests big improvements to MySQL (http://www.oracle.com/dm/11q1field/58124_mysql_5.5.html). I think MySQL may have better support under Oracle than it did under Sun.

Is Oracle providing better support of MySQL? Does this change anything?

Post by **jordansparks** » Thu Nov 04, 2010 7:16 pm

Yes, Oracle is amazingly supporting MySQL well. As a result of recent quality improvements, we were able to upgrade our aging connector in version 7.4. We are also moving into MS products for our in-house needs, including MS-SQL and SharePoint, so an MS-SQL version is not unrealistic.

However, due to the older posts on this thread no longer being accurate, I'm locking this thread.

Open Dental Forum

Microsoft SQL

Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL

Re: Microsoft SQL