Solved SQL Server for Moving from Access Backend to SQL Server (1 Viewer)

Pac-Man

Active member
Local time
Today, 13:44
Joined
Apr 14, 2020
Messages
408
Hello,

I've looking into option for Moving from Access Backend to SQL server for they last two days in google. I'm bit confused in SQL Express versions. i.e. Basic, Advanced, LocalDB, with Tools etc.

I've read that with tools have management tools included, advanced have text search and reporting etc. Since I need text search in my frontend app and it also have reports like almost all frontends do, so do I need to use advanced SQL server express or other.

My purpose is just to shift my backend tables to SQL and access them in my frontend just like I'm using backend. My app backend contain only tables (no queries). My frontend contain all the forms, search forms and reports. Now which version of SQL server do I have to use?

Best regards
Abdullah
 

Minty

AWF VIP
Local time
Today, 08:44
Joined
Jul 26, 2013
Messages
10,353
Text search is a SQL function / feature that enables a clever text indexing (Generally on long text fields) to enable highly efficient searches.
Have a read up here: https://www.red-gate.com/simple-tal...derstanding-full-text-indexing-in-sql-server/

Your searches I'm guessing are using the inbuilt access search or wildcard features so you don't need to worry about it.

To simply move your tables, the most basic version will be more than adequate.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:44
Joined
Oct 29, 2018
Messages
21,357
Hi. I'm just curious. Why are you moving your backend to SQL Server?
 

Isaac

Lifelong Learner
Local time
Today, 01:44
Joined
Mar 14, 2017
Messages
8,738
You probably don't need local DB unless you want to have an instance of the server running on your own machine for additional testing, but you can probably do all the testing and development that you need to on the machine where you are installing the server
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:44
Joined
Feb 19, 2002
Messages
42,970
If this is an application that will be used by a business, you will need one of the full versions of SQL Server. If this is for your own use or for testing then SQL Server express will be all you need.

It looks like all three options are included in the download and you specify what you want installed. I would go with Advanced. It is not clear whether the download includes SSMS. If it doesn't, you will need to download that separately to give yourself a GUI to manage the database.
 

Isaac

Lifelong Learner
Local time
Today, 01:44
Joined
Mar 14, 2017
Messages
8,738
SQL Server Express can legally be used for business.
Just not SQL Server Developer.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:44
Joined
Feb 19, 2002
Messages
42,970
SQL Server Express can legally be used for business.
I would like to see that in the MS license if you have a reference.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:44
Joined
Feb 19, 2013
Messages
16,553
link to the license

pretty much anywhere you search for sql server express you will see something like
"SQL Server 2019 Express is a free edition of SQL Server, ideal for development and production for desktop, web, and small server applications."

taken from this site -scroll down to Express

 

Isaac

Lifelong Learner
Local time
Today, 01:44
Joined
Mar 14, 2017
Messages
8,738
It's been that way always, I believe.

Microsoft SQL Server 2019 Express is a free, feature-rich editions of SQL Server that is ideal for learning, developing, powering desktop, web & small server applications, and [even!] for redistribution by ISV

By way of contrast:
Other specialty editions of SQL Server 2019 include Developer Edition, which is licensed for non-production
use; the freely downloadable and distributable Express Edition.


SQL Server Express as a Production Database (logicalread.com)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:44
Joined
Feb 19, 2002
Messages
42,970
Thanks. I haven't downloaded a new version in a few years and so I've never seen anything that said it was licensed for production work. This is what the latest version download says and it doesn't mention production:
Download Microsoft® SQL Server® 2019 Express from Official Microsoft Download Center
SQLExpressJPG.JPG
 

Isaac

Lifelong Learner
Local time
Today, 01:44
Joined
Mar 14, 2017
Messages
8,738
See previous posts it's OK and a reasonable choice for small business data
 

Pac-Man

Active member
Local time
Today, 13:44
Joined
Apr 14, 2020
Messages
408
Thanks for reply. My backend PC or network is slow and form take longer to load (even though my db is small with limited number of records right now) and moving to SQL server might increase the speed of database. Most importantly, I want to learn and get new experience of using SQL server.
 

Pac-Man

Active member
Local time
Today, 13:44
Joined
Apr 14, 2020
Messages
408
You probably don't need local DB unless you want to have an instance of the server running on your own machine for additional testing, but you can probably do all the testing and development that you need to on the machine where you are installing the server
Thanks for reply @Isaac, it means I can go with either SQL server with tools or SQL server advanced.
 

Pac-Man

Active member
Local time
Today, 13:44
Joined
Apr 14, 2020
Messages
408
Text search is a SQL function / feature that enables a clever text indexing (Generally on long text fields) to enable highly efficient searches.
Have a read up here: https://www.red-gate.com/simple-tal...derstanding-full-text-indexing-in-sql-server/

Your searches I'm guessing are using the inbuilt access search or wildcard features so you don't need to worry about it.

To simply move your tables, the most basic version will be more than adequate.
Thanks for reply @Minty, yes I'm using Access search using VBA. I guess in that case with tools should work.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:44
Joined
Oct 29, 2018
Messages
21,357
Thanks for reply. My backend PC or network is slow and form take longer to load (even though my db is small with limited number of records right now) and moving to SQL server might increase the speed of database. Most importantly, I want to learn and get new experience of using SQL server.
Hi. Thanks for the additional information. It may not turn out as you're expecting at first, but I wish you all the best. Please let us know how it goes.
 

Isaac

Lifelong Learner
Local time
Today, 01:44
Joined
Mar 14, 2017
Messages
8,738
When it comes to speed I'll throw a few of my thoughts in there:

- I agree with CJ if you just link access to sql tables and continue doing EXACTLY what you were doing in access, you may not notice any speed increase.
- But if you use pass through queries with T-SQL, and if larger amounts of data are being queried/inserted/whatever, you will probably notice that sql server is more forgiving with, say, 50,000 records and a poorly written query, than Access would be with an Access table with 50,000 records and a poorly written query. I think this 'speed' is somethign people are referring to--but not necessarily something you should strive for, if that makes sense.

Optimize, optimize optimize.
 

Pac-Man

Active member
Local time
Today, 13:44
Joined
Apr 14, 2020
Messages
408
Thanks a lot @Isaac for reply and suggestion. Since I'm new and have only few months experience and still learning, I'll try to optimize the db as much as I can. Thanks again to everyone who spared their valuable time and replied.
 

Users who are viewing this thread

Top Bottom