MySQL Vs. MS SQL Server

[midmented]

I've been running Access backends for 10 years now but the BE's are growing out of control. To expand and grow, we want to upgrade our Access BE's to something better.
Currently our server platform is Novell which comes with MySQl. Our network administrator wants me to use it as our Access BE but from what I have read, I won't see much of a difference. Since I am using DAO to connect to the recordsets, will I have a lot of code re-writing to do?
I understand that SQL Server won't run on Novell and we would have to either virtualize an MS server platform or buy a new server and use an MS server platform. How would this work if users authenticate on a Novell Server? A dual login?
I've called Microsoft and MySQl reps but you really don't get much help in making such a decision.
Any help or guidance would be appreciated. Thanks in advance!

 

[Banana]

Access/MySQL user here.

Because I work for a non-profit which doesn't want to spend $10,000 on a solution, price was definitely a consideration, so I basically chose between SQL Express or MySQL and preferred MySQL only because I didn't want to get hit again by the limitations imposed.

I actually think I get more flexibility with MySQL as BE than when I had Access as BE and used DAO recordsets just fine. The thing I have to watch out is how I query the BE is that if I don't include a PK, I get #Deleted# whenever a row is inserted or updated; data does get saved but not shown correctly. It's well documented and discussed to death over at MySQL website so there are workarounds.

Also thing is that I have to be even more strict with how I write queries. I've since made it a rule to have all my queries accessing MySQL with an IN clause because it seems to performs better than without. I also maintain several different templates; one for using as a rowsource (read only, no locks), another for recordsets (dynaset, locks edited records) and create new queries from those templates.

With carefully written queries, it was then a snap to bind the form to those queries and pretty much forget about it, concentrating on writing in the usual validation logic done.

One cool thing was that I was able to gain *more* control over transactions, especially for form/subform combo, which is impossible to do in full-Access solution; don't know if it can be done with MS-SQL, though. It's also very nice that you have more choices with what storage engines you could use, and you can mix'n'match. In my case, it's exclusively InnoDB because transactions is much important to sacrifice for any speed MyISAM offers.

I don't know about your setup, but in my case, I had remote offices so WAN connections were necessary. We also were obliged by Health Information Portability and Accountability Act (HIPAA) to keep our data secured. To that end, I used .NET libraries to provide SSH functionality and thus avoid pay hefty fees for a SSL certificate or the hassle of self-signed certificate which doesn't always work. If your setup is strictly LAN, then you have it easier as you don't have to secure the connection; just block the server from accepting connection from the world.

I should also ask- am I mistaken in thinking that Access 2007 has since moved to SQL's native driver? If that is correct, then I'm quite sure that Access 2007 may perform faster with MS SQL as there is no ODBC/OLE DB layer to navigate, which was the case with 2003 (and thus to my eyes, made any other RDBMS just as good candidates for connecting as BE).

Basically, I'd be more worried about:

1) How will tables be linked and how will it be refreshed/updated
2) How the queries will be written
3) How would Access connect to MySQL and authenticate the user?
4) Are the datatypes compatibles? (e.g. I don't use ENUM or SET as Access cannot understand them, avoid DATE or TIME as it's been reported to be problematic, using DATETIME instead, VARCHAR instead of CHAR to avoid the right-padding (but it may be an error on my part as it's been reported to work), remembering that INT = Long Integer, SMALLINT = Integer)

I hope that helped a bit. BTW, I uploaded a simple demo with World database a while back; you could take a look at it to see how it mesh.

 

[midmented]

Thanks!

Thank you for you reply.

I am running a ton of timekeeping records and draw a lot of different information from them. Some of the calculations take a lot of time and modules to run. (Leave balances based on dated:Starting balance + leave earnings + leave taken (in a date period)) etc, etc,) This is the system showing the most "strain" using the Access BE's.

Most of the facilities carry +300 employees which averages to around 200,000 records per facility when holding timekeeping records for a 2 year period. The 3 facilities Access BE's are seperated because to query an Access recordset of 600,000 records in Access using linked tables on a WAN, well, it doesn't work very well.

I've used MySQl for php projects and had good results. Knowledge outside of that about MySQl is minimal. I made a test run of importing Access tables into MySQl and used the ODBC MySQl connecter (from MySQl web site) to link the tables. The table held the 200,000 records I told you about earlier. I ran the biggest time calculation module I had in Access. It never did finish running and I had to "Break" the run.
To give MySQl a second chance, I hard coded the connection and got rid of the links. It returned an answer but it was 3 minutes later. With an Access BE it normally takes 1 minute.
That first experience wasn't good.

I can only think that if I used stored procedures the return time would be seconds and not minutes. From what I've read, stored procedures and triggers were introduced in MySQl in version 5 but they're not that great.

Again, thanks for that long reply. I know it takes a little time to type in your thoughts/suggestions. I'll check into everything you have listed here and maybe give it another test run.

 

[Banana]

Exactly how does your modules access the data? I suspect the way they were written may have contributed to a lot of forth and back between the MySQL and Access.

You will definitely want to read the MySQL's logs in realtime (I usually use tail -f, but don't know if your MySQL is hosted on a linux box). The first time I set it up, I cringed at how horrible it was, selecting same data again and again among other things. Which was why I emphasized that you had to be careful with how you wrote the queries, and how you organized the form, else you get much ado about nothing. Same is true of VBA recordsets, which are even worse because at least with forms, Jet is involved in selecting only few records and filling up the recordsets in background, but in VBA, it's one big fat SQL statement that can hammer the server, and this isn't what we want to do. So you will have to read the logs and see what MySQL actually gets to understand what effect your code actually has.

What's wrong with SP and triggers? I've used them and were quite satisfied with them... (I have several triggers all over the crucial tables to create timestamps for INSERTS and UPDATES, as the default TIMESTAMP data type cannot do both on two different columns. Also, I used a SP to return a full list of tables that I'm able to link with Access so I can refresh the links without maintaining a list of tables locally and this bodes well for security as well.

FWIW, I used MySQL 5.1 Source Code distribution, MyODBC 5.1 and Access 2002-2003 file formats, if that's any help.

 

[datAdrenaline]

The way your indexes are defined and what you are retrieving from the tables has a HUGE effect on elapsed time to perform an operation. Also, if you are getting the data for just calculations, then do not forget about the everpowerful Passthru query!! ... It will pass a SQL statement directly to the server, the server then processes the request, then spits out the result to the requestor.. So if you need just two records from that 200000, then you can use a PT query to tell the server you just what those two records, and they will be returned to you in the blink of an eye ... but they are read only! You can also use passthroughs to issue UPDATE statments though ... so in VBA you can create an UPDATE (or INSERT INTO) literal update statment and issue it to the server.

... Note ... I am a SQL Server user, and have NO MySQL experience, but I do know the Passthru's work pretty much the same in each enviroment....

 

[tokoloshi]

The decision to move to an enterprise class BE is normally taken after a bit of "pain". You have experienced where Access, as a DB comes short and are hoping to squeeze a bit more performance out of the BE by migrating to a one of the top 4 enterprise class DB.

You choices are MySQL, SQLLite, OracleXE or MS SQL Express if you are looking at entry level, free products.

Divide the 4 main competing products into 2 categories. The first 2 fall into the totaly free lunch category . The advantages of both of these products is that they perform reasonably well and exceed all of the expectations of a user migrating from a lower end DB environment, but then again so do the other 2.

The latter 2 are both the express editions baby brothers of 2 really big players in the field.

MySQL is the preferred flavour of the opensource crowd, while SQLLite is still finding its market (in comparison to the other 3 that is).

If you are looking at a totally non-Microsoft environment (ie Linux on the desktop, Linux on the server, linux on the radio) then you should seriously consider OracleXE. The upgrade path as your solution grows is simple.

The obvious issues are with regards to licensing costs when you eventually have a need to start paying for licenses as a result of your increased needs. This WILL happen - in the same way that you know for a fact that less experienced users who find Excel a fantastic tool and start coding in VBA will eventually end up in the Access space.

The question then is what the various databases offer at the top end.

Both SQL Server and Oracle offer a very wide range of Business Intelligence tools that cuts your workload down to fractions.

What is an add-on or a bolted-on solution in MySQL & SQL Lite is stable & standard in both Oracle XE & MS SQL.

Things like triggers, XML data types and caching technologies are expected from all of these databases, but Oracle and MS SQL excel - and have been doing so for a long time, while the first 2 are still playing catch-up.

These features are not however what makes the decision easier to make.

The tools provided by SQL Server and Oracle when it comes to adminstration and rapid application development is where the dollars invested by their owner prove themselves.

I will discuss SQL Server here, but take it for granted that Oracle will be able to match most of what MS has to offer. If not in the current version, then they will at least have plans to implement it in the next.

SSIS:
SQL Server has an exceptionally powerful tool set referred to as SQL Server Integration Services. This is a server based set of tools that comes with SQL Server for free and is use to perform Extract, Transform Load operations. The interface is graphical and extremely easy to implement.

I have used earlier versions of SSIS (called DTS for Data Transformatin Services in SQL 2000) to perform migration from Oracle on Novell to Oracle on Unix. I have also used SSIS to migrate data on a scheduled basis from several other data sources to as many other data sources on a range of operating systems.

SSIS is built into SQL Server Express, so you still get it for free. There are obviously some limitations - for example you need to have a fully licensed copy of SQL Server to do some of the intricate work, but you can then roll out the package on top of SQL Server Express.

SSRS
SQL Server Reporting Services is one of the best additions to SQL Server 2005. It has been improved with SQL2008, but its capabilities are very far reaching. you can develop an entire reporting system for your organisation without ever needing to look at something like Chrystal Report Writer or similar.

Your users can access their reports, they can subscribe to reports and have those reports delivered to them via email in PDF, XPS, Html ,XLS or even .DOC format.

SSRS is free with SQL Server Express. Obviously there are several features that are only available in the full edition, but what is deployed is perfect.

SSAS (SQL Server Analysis Services), Notification Services, Service broker, etc
There are many other FREE services bundled in with SQL Server that are absolutely essential to the management of enterprise data that I could not imagine having to do without.

If you had invested in SQL Server Express and found that you needed to have the functionality of it big brother you know that nothing need to be changed in your schema or on the front end.

SQL Server can run off a stand alone server, using SQL Authentication. You can authenticate from within your application against NDS, then connect to the DB, so there is no need to stress about the fact that your organisational standard is NDS with all of the power that is provided through that environment.

SQL Server will not integrate with NDS at this stage. Microsoft has deliberately tied their 2 flagship server products into ADS. Exchange Server and SQL Server both have a far higher degree of security when you use Windows authentication, but a little bit of thought can give you as much security and performance without having to invest in a retooling of your IT infrastructure around Microsoft and away from the Novel environment.

Much of what I have mentioned about SQL Server is applicable to Oracle as well, but my personal preference is for SQL Server because of the level of integration it offers to the other Microsoft products - such as Access 2007.

Additional Tools
There are many times after you have implemented a given solution that you wish that you can get to inspect your server machine at a level that can help you understand the reason for poor performance. I find that tools like SQL Profiler and the standard Windows System Monitor, when correctly used, are invaluable tools.

My suggestion to you would then be to consider downloading a free copy of SQL Server 2008 express edition and see if you can get a handle on it. You can also download a free copy of Oracle XE 10g from the Oracle site and see what it has to offer.

If your environments needs grow to the point that you need to go through this entire thought process again within the next 3 years, at least you know that the decision to upgrade to the big brother of either Orace or SQL Server will not force you to reconsider all of your code or to go through yet another learning curve.

Sean

 

[Banana]

Sean, that was an excellent exposition.

Just one thing I should make clear: SQLite is not a full-fledged RDBMS; it acts alot like JET; a file server that writes data to its file. Therefore, it has similar advantages and liabilities as JET does (e.g. limited concurrency, poor networking robustness, etc).

And just to throw out some other alternatives, I understand that postgreSQL and Firebird has their marks as well. postgreSQL forum has a page dedicated to interoperating with Access. Beyond that I have no clue.