UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL Database Server - Which Is Best And Free?, Access 2016    
 
   
martyl
post May 1 2020, 04:32 AM
Post#1



Posts: 14
Joined: 16-March 19



Dear all,

I work for a non-profit organisation, we are looking to migrate our MS Access database to an SQL database,
the Access front-end will remain the same, it's only the backend that will be migrating.
However, we can't afford to buy a commercial SQL database server. I am looking for an open source one, prefer one which is reliable and not too complicated.
There are many options - MySQL, SQL Server Express, PostgreSQL, MariaDB, etc. But MySQL might not be free for our purpose.

Would anybody have any recommendation for me?? In summary, a database server which is preferably free, not complicated and reliable.


Thank you in advance.
Martyl

P.S. dear moderator, I have posted at the wrong sub-forum. Could you move my post to the General sub-forum?? Thanks.

This post has been edited by martyl: May 1 2020, 04:44 AM
Go to the top of the page
 
DanielPineault
post May 1 2020, 05:33 AM
Post#2


UtterAccess VIP
Posts: 7,343
Joined: 30-June 11



Why migrate? What are you hoping to acheive? This may help us better guide you.
Is this a multiuser database? How many users?

SQL Server Express is normally the next step up from Access. That said, I have clients running PostgreSQL, MySQL dbs without issue and have been for years.

Are you aiming from on-prem database or cloud?

You do realize that normally such an 'upgrade' requires reworking existing queries/forms.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
cheekybuddha
post May 1 2020, 05:36 AM
Post#3


UtterAccess Moderator
Posts: 12,810
Joined: 6-December 03
From: Telegraph Hill


Hi,

MySQL, SQL Server Express, PostgreSQL, MariaDB are all free.

>> MySQL might not be free for our purpose <<
What is the purpose that makes you think this?

Where are you planning to host the server? Internally on a server, or even a user PC? In the cloud?

Will you require access to the database over the internet, or just from within your local network?

How many users will need to access it simultaneously?

How large is your database?

>> Which Is Best <<
Too subjective!!

All of them will have a learning curve. Your frontend db may well need some tweaking to make it work efficiently with a RDBMS.

IMHO, the rough running order in terms of ease would be:
1. SQLServer Express
2. MySQL/MariaDB
3. Postgres

SQLServer Express limits the amount of cores it can use on the server and also the number of concurrent connections, but is more likely to have a usable ODBC driver installed already on the client machines.

MySQL/MariaDB are not hampered in the same way but you will have to install the ODBC driver to each client machine. Postgres similarly.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post May 1 2020, 07:08 AM
Post#4


UA Admin
Posts: 37,263
Joined: 20-June 02
From: Newcastle, WA


I can't add much to what has been said except to echo the sentiment that "best" is subjective, not objective, and really depends almost entirely on your needs and preferences.

I will say that one hurdle is probably going to be the redesign of the Front End to work effectively and efficiently with a remote or server-based database, especially if it is "in the cloud".

An excellent resource is linked in my signature. Another is available here.
Something I expect you'll be disappointed in is the expectation that it can be uncomplicated, especially in comparison to Access.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
martyl
post May 1 2020, 07:41 AM
Post#5



Posts: 14
Joined: 16-March 19



Thank you for all your responses, DanielPineault, cheekybuddha, GroverParkGeorge.


>>Why migrate? What are you hoping to acheive? This may help us better guide you.
>>Is this a multiuser database? How many users?


We are migrating because we wish to setup user security and data protection, etc.
It is a multiuser database with 2 users currently.



>>>> MySQL might not be free for our purpose <<
>>What is the purpose that makes you think this?
>>Where are you planning to host the server? Internally on a server, or even a user PC? In the cloud?
>>Will you require access to the database over the internet, or just from within your local network?
>>How many users will need to access it simultaneously?
>>How large is your database?


Information from Oracle website says that using MySQL for open source projects is free, otherwise need to pay for a license.
Although we are a non-profit organisation, we do earn revenue through course fees and membership fees. So can we use MySQL for free?

We host the server on a PC with a NAS drive, users will be connected to this server through the NAS drive. It won't be in the cloud.

Currently there are only 2 users, might be more in the future.

The Access database backend is not too big, its only 28MB.

This post has been edited by martyl: May 1 2020, 07:42 AM
Go to the top of the page
 
Jeff B.
post May 1 2020, 08:04 AM
Post#6


UtterAccess VIP
Posts: 10,482
Joined: 30-April 10
From: Pacific NorthWet


To reinforce what others have already pointed out, regardless of the size of your current Access database, connecting an Access front-end to a SQL-Server (or other) back-end will probably benefit from/need modifications to the front-end to enhance performance.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
DanielPineault
post May 1 2020, 08:06 AM
Post#7


UtterAccess VIP
Posts: 7,343
Joined: 30-June 11



Can I ask why you wouldn't just encrypt both the front-end and back-end with passwords, lock down the folder so only authorized users can even access the files. You may like to review https://www.devhut.net/2016/09/01/securing-...base-front-end/ for some more ideas on the subject.

I'm confused by
QUOTE
users will be connected to this server through the NAS drive

Do you have a PC/Server or is it a NAS?
I guess, with the current information, the easiest way forward would be SQL Server Express, but you will have some work ahead of you. 'not complicated' is relative especially when doing something for the first time. Also, depending on the db being upgraded, the process can be relatively straightforward or have hurdles. Do you use Lookup fields, Multi-value Fields for instance (see http://access.mvps.org/access/lookupfields.htm )? You may like to review http://www.UtterAccess.com/forum/Beginning...e-t1732935.html for starters.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
nvogel
post May 1 2020, 09:29 AM
Post#8



Posts: 1,104
Joined: 26-January 14
From: London, UK


"Best" is obviously very subjective but I would go for either SQL Server Express or PostgreSQL. Cloud-based is the most cost-effective and hassle-free option. Both Microsoft and Amazon offer "serverless" database services so that you don't have to manage your own servers at all.
Go to the top of the page
 
PhilS
post May 1 2020, 09:57 AM
Post#9



Posts: 697
Joined: 26-May 15
From: The middle of Germany


QUOTE (martyl)
So can we use MySQL for free?

Yes.
Look at the product matrix at: https://www.mysql.com/products/
Only features marked with 1 are in only available in the commercial editions. Anything else is also in the free/open source Community Editions.

PS: Also look at MariaDB, which is an entirely Open Source offspring of MySQL.
This post has been edited by PhilS: May 1 2020, 09:58 AM

--------------------
A professional Access developer tool: Find and Replace for Access and VBA
Go to the top of the page
 
martyl
post May 1 2020, 12:07 PM
Post#10



Posts: 14
Joined: 16-March 19



PhilS,

I can use MySQL for free?? Really?
Didn't Oracle say it's free only for open source projects? But ours is not one, I think.
This post has been edited by martyl: May 1 2020, 12:08 PM
Go to the top of the page
 
PhilS
post May 2 2020, 12:02 PM
Post#11



Posts: 697
Joined: 26-May 15
From: The middle of Germany


QUOTE (martyl)
I can use MySQL for free?? Really?
Didn't Oracle say it's free only for open source projects? But ours is not one, I think.


Yes, you can. - The Community Edition!

QUOTE
MySQL Community Edition is the freely downloadable version of the world's most popular open source database. It is available under the GPL license and is supported by a huge and active community of open source developers. (Source: https://www.mysql.com/products/community/)


However, you need to be aware of the GPL clause. You must not bundle and redistribute MySQL with any product you create, otherwise the GPL indeed requires you to open-source your own product as well!
As long as you only use it in-house there is no problem at all.
If you redistribute your own solution to third parties, you need to tell them to get and install MySQL independently before installing your product. - And, just to be safe, you should have this checked by your legal team.
This post has been edited by PhilS: May 2 2020, 12:03 PM

--------------------
A professional Access developer tool: Find and Replace for Access and VBA
Go to the top of the page
 
nvogel
post May 3 2020, 04:28 AM
Post#12



Posts: 1,104
Joined: 26-January 14
From: London, UK


PostgreSQL has a less restrictive licence than MySQL so that might be of interest to you if the MySQL licence doesn't suit your needs.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th May 2020 - 10:44 PM