Database alternatives


Over the years I've developed an application in visual studio with an access database, I'm starting to have a few issues with the database and I was now looking at some alternative solutions that could be available, and where migrating these tables wouldn't be too difficult, I have about 33 tables in the database, some of them only have a few records in them.

I don't know much about other database systems out there, and I was wondering if I could get some suggestions from you, I currently have the access database on my windows server and the application is installed on the workstations,

Thank you for the information,
Who is Participating?
Nitin SontakkeDeveloperCommented:
Why look further....go straight to Microsoft SQL Server. Any reservations / questions / issues / concerns, please do let experts know.
SQL server express.  The underlying functionality dictates,.
The issue deals with a large dataset reaching the db Max size?

SQL, servera press

Install SQL server express

You develop a web based, or win32 client?
Do you rely on access forms, views, reports to get data out?

Gustav BrockCIOCommented:
SQL Server  is nearly native to Visual Studio, so aim for that.
You may even take advantage of the tiny-install (and free) LocalDB  version:

SQL Server 2016 Express LocalDB
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Yes, SQL Express is a good choice if the number of users does not exceed certain level. If you have let say 10 users accessing SQL Express database simultaneously then queries are much slower than you would expect... BUT if you are switching from the Access database and everything was good till now then it should be also OK with MS SQL Express.

You could also think about MySQL or PostgreSQL which are free and more suitable for higher load than MS SQL Express.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Unless you're dealing with massive amounts of data, you'll never outstrip MS SQL Express, and you'll find that working with MS SQL in Visual Studio is dead easy.

You may even take advantage of the tiny-install (and free) LocalDB  version:
I love LocalDB, but it's not multiuser, so be aware of that.
BUT if you are switching from the Access database and everything was good till now then it should be also OK with MS SQL Express.

Wrong.  Just swapping a SQL Server BE for Jet/ACE will result in SLOWER response if the developer used the typical old style "Access" techniques of binding forms to tables and filtering locally.  This gives NO benefit to using SQL Server because because Access has to download ALL data from the server rather than just what the user needs.  Other problems arise if queries use UDF's and Access or VBA functions in clauses other than the Select clause.  Since SQL Server has no knowledge of VBA, there is no way for the server to process these functions and so Access has to eliminate them from the query it sends to the server and then apply them locally when the recordset is sent back from the server.

Not that I'm a big fan of Jet/ACE but in many cases, they provide faster performance than SQL Server.

@FCapo, don't let that deter you.  SQL Server express is your best option if you need something free.  If the app is slow, you'll need to tweak the queries and form processing.
The original application was developed in Visual Studio with Access database... So we cannot talk about classic Access Forms and data bindings and local filtering most probably. Possible UDFs must be converted to SQL Server functions but FCapo should clarify whether are they used or not.

So, what VS methods and classes do you use for data access? In theory if everything should go smoothly then just the connection string will change in VS application.
John TsioumprisSoftware & Systems EngineerCommented:
I guess the original question should contain info on what kind of issues you encounter ...
As others pointed out MSSQL is more than great...also MySQL is a great alternative if you are looking to go the cheap way online (cheap VPS,Linux.MySQL)..but maybe you are using Access for storing Documents so the ideal database should be Firebird..
For each need there is a good solution so give some more info...
Gustav BrockCIOCommented:
Suggestions provided.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.