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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Why look further....go straight to Microsoft SQL Server. Any reservations / questions / issues / concerns, please do let experts know.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.