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
> Getting Started With Mysql Or SQL Server    
 
   
fogline
post Jun 14 2020, 01:17 PM
Post#1



Posts: 228
Joined: 5-August 15
From: Ringgold, GA. USA


I am trying to getting started with MySQL or SQL Server.
I also use Godaddy hosting.

Okay what I am looking to do is, I have an Access FE and I need to move the BE to a server.
I have customers of my Access program with multiple users that need to access the database
ether networking in one office or in different office locations.
They more than likely will not be more than 5 to max 10 users per company.
But each customer needs there own BE on the server.
I will send out the FE to each customer.

1. Should I start out using MySQL or SQL Server.
2. After I get the server setup do I setup a BE database for each customer so they can have there own data files
or do you setup one database and every customer uses that one database but each company has there own login
which will give them there own copy of that one database? Not sure how that part works.

Thanks for any help getting me on the right path to get started.

--------------------
Ray White - Fog Line Software LLC.
Email
Go to the top of the page
 
theDBguy
post Jun 14 2020, 01:31 PM
Post#2


UA Moderator
Posts: 78,447
Joined: 19-June 07
From: SunnySandyEggo


Hi Ray. This is just one person's opinion, but if you're going to use Access as a front end to your online database, I would suggest using SQL Server because Access and SQL Server are both from MS, and I am hoping they will play nicer together. However, it doesn't mean Access won't play nice with MySQL too. Also, using SQL Server would mean licensing cost. I think MySQL is free. Is that right? Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
fogline
post Jun 14 2020, 01:40 PM
Post#3



Posts: 228
Joined: 5-August 15
From: Ringgold, GA. USA


Hi DBGuy
Good to hear from you.
Yes MySQL is free.
I think I can get SQL Server Express with my GoDaddy account.

After I get the server setup do I setup a BE database for each customer so they can have there own data files
or do you setup one database and every customer uses that one database but each company has there own login
which will give them there own copy of that one database? Not sure how that part works.

--------------------
Ray White - Fog Line Software LLC.
Email
Go to the top of the page
 
cheekybuddha
post Jun 14 2020, 01:44 PM
Post#4


UtterAccess Moderator
Posts: 13,005
Joined: 6-December 03
From: Telegraph Hill


Whether you choose SQLServer or MySQL, check first that you can connect directly with GoDaddy.

If not, then find a proper host.

hth,

d

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


Regards,

David Marten
Go to the top of the page
 
MadPiet
post Jun 14 2020, 01:44 PM
Post#5



Posts: 3,778
Joined: 27-February 09



If you're using SQL Server 2016 or later, you can put everything in one database and use Row-Level Security to horizontally partition the data. Then they wouldn't be able to see or affect one another's data. Ask Albert Kallal and see what he suggests. But RLS basically limits a group to only seeing/modifying their own records.
Go to the top of the page
 
fogline
post Jun 14 2020, 01:55 PM
Post#6



Posts: 228
Joined: 5-August 15
From: Ringgold, GA. USA


Thanks David and MadPiet

After I get the server setup do I setup a BE database for each customer so they can have there own data files
or do you setup one database and every customer uses that one database but each company has there own login
which will give them there own copy of that one database?
Not sure how that part works
How do I get each of my customers there own copy of the Database on the server?


--------------------
Ray White - Fog Line Software LLC.
Email
Go to the top of the page
 
AlbertKallal
post Jun 14 2020, 06:13 PM
Post#7


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


Each customer will have there own database running on that one SQL server. After you play with SQL server for a bit, then this will make sense.

First, I would probably consider having SQL server running on each customer’s side.

SQL Express is free, and can handle just about any sized small business needs. And even a medium sized one with 60 workstations is not much of any problem for SQL express.

Now, you CAN consider hosting the SQL server database in the cloud, or say on Go Daddy.

So, for each customer? Well think of your “one” BE as now being “one” database.

So, a given instance of SQL server allows you to have “many” databases on that server.

So, for each customer, you could/would simple create a new database, and then migrate your access BE to that one database you crate on SQL server. And of course for each customer you would/could create a single logon that you use to link the tables to that database.

The main issue (challenge) is that with a hosted database, then the performance challenge is “large” and “looming”. I do like the EASE in which you can setup such systems. You can provide the customer with an install package or even a download link. That FE running on each computer thus can work with any internet connection. But, the performance challenge will exist.

If the customer “mostly” just needs to run the application “on-site”, then I would go with running SQL server on EACH customer’s site. This means that performance, security and a good number of issues (such as performance) will not be a problem. But, of course such customers will be limited to the local “LAN” just like they would be with a split FE/BE.

However, most companies do have, or can setup a VPN to the work network. So, any shared folders, printers and yes that instance of SQL server can now be used anyplace, and anytime.

Of course the issue/problem is network speed with a VPN. While the company might spend extra money on a good high speed internet plan, the work at home people are going to be using their own internet plan. Or you stuck at a coffee shop, and using that Wi-Fi. So, you can’t control the quality of the internet that the users have.

And using a VPN to the company network TENDS to be slower than a host edition of SQL server. This is due to the fact that even when a company spends extra money for a better internet plan? Well, the up-load speeds tend to be SILL somewhat limited. Remember, if you are at home – say VPN, and you launch your Access FE? Well, the BE is now SQL server running at the work location. When you pull data, then from the work network point of view that is UP-LOAD SPEED! In other words, while at home, you pulling data from SQL server, on the other end, that network is limited to its up-load speed. So, your bottle neck is the up-load speed of your work network.

When you host SQL server, then that up-load bottle neck is removed. Most hosting providers have a rather nice pipeline into the internet, so when you pull data from that SQL server, you getting a good download speed, and there is no up-load speed to the internet first.

I don’t recall if Go-daddy still supports external connections to SQL server. (You have to check – they might not).

Remember, when you go shopping on amazon.com, the web site and server can freely hit the database system. However, anything OUTSIDE of the web server, such as the wild and crazy internet cannot by-pass the web server and directly connect to SQL server.

I mean, if you allow direct connects to SQL server, then I can attempt to connect also!!!

Do you really want to open up your SQL server to open and outside connections? That internet is a VERY wild and crazy place!!!

I remember in 2004 I decided to try using SQL server over the internet. So, I had a free version of SQL server setup on my home computer (in fact it was the free edition called MSCE edition. That is the free edition that shipped on every office disk. (2000, 2002, 2003, and I think 2007). But now we use the far better SQL express edition.

Amway, so I had SQL server running on one home computer. I then on my Linksys router forwarded the SQL ports to that computer. What that means is I can now use that SQL server from anywhere on the planet earth if I have an internet connection.

Well, in about 20 minutes of time?
I check checked the server logs. Already automated internet “scanning” bots were trying to logon. The logs looked like:


User:sa Password:Password
User:sa Password:sa
User:sa Passwrod:1234

User:system password:system

And so on.

In other words? It was barely 20 minutes already people from the planet earth were attempting to logon to that server!

When we finally get a trans-planet internet connection to Mars? Well, then those people ALSO no double would start attempting to logon to that server.

So, due to the above? Well, less and less internet providers allow you to JUST open up the database server to the wild and crazy internet.

So, the web server being hosted can hit and consume data directly from that database. So, the database and web server can happy talk to each other (connect). But OUTSIDE connections to the SQL BE are not allowed, and not even possible. So, you can order a book on amazon, but you can’t just by-pass the web site/server and directly open tables on the database. If you open up SQL server to direct connections, then I can by-pass the web site, and use a standard connection and linked table to that SQL BE. But SO CAN AUTOMATED scanning bots!!!

Most here are likely too young to remember the movie “war games”. Back then, people would load up a text file with parts of their city phone book. Then you feed that text file of phone numbers to some software (even simple BASIC). It would read the first number, then dial that phone number. If the phone on the other end responded back with a computer tone, then the software would “log” that number, and keep on dialing.

When done, out of a list of 1000’s of phone numbers you would now have a list of potential computers that you can dial up and call. Then you start part two – dial up the computers in that list and start hacking away and trying passwords.

Today? Well, you don’t have that modem. And the “list” of phone numbers is now a range of public IP address. So, you ping them all one at a time (and this can occur at high speed). If one the IP address responds back? Well, now you start pinging and looking to see if that is an open remote desktop port, or in this case SQL server. Then you start trying logon’s over and over.

So, I not all convinced you want to try this with SQL server running that allows OPEN connections to the wild public.

This is much why I suggest you better off to install + setup SQL server on each site. And then their IT folks can setup a VPN, and you now have a secure connection to the company network and thus use of SQL server (which thus does NOT have open IP or PORTS to the wild and crazy internet.

As noted, each BE database you have becomes a separate and new database on SQL server. So, SQL server allows “many” databases, and you can think of each database as a new separate BE.

I would most certainly install and setup SQL express on your local computer, and become “comfortable” working with SQL server. Once you comfortable hen the connect of a new database, or a different BE will become second nature to you.

For the FIRST round of working with SQL server? You have to learn to swim in this lake. Split a database, and get SQL server up and running at a customer’s site. Lean how the setup works, and THEN consider using a cloud based version of SQL server. In fact even cloud based systems can (often) have IP restrictions setup. This means that the company pays $5 more per month, and their IP address is thus FIXED and does not change. Now you can open up the SQL server (hosted) to accept incoming connections, but ONLY from that one IP address, and it will ignore all others.

And if workers on the road VPN into work network? Well, now when you connect to SQL server (hosted), then the IP address will appear as the work address, and no the current coffee shop IP address you are using.

But, I strong suggest you get your feet wet FIRST with a LAN setup, and then once you swimming with that setup, then you move on to phase two where you attempt a WAN setup. (WAN = Wide Area network).

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
MadPiet
post Jun 14 2020, 06:34 PM
Post#8



Posts: 3,778
Joined: 27-February 09



Oh, right... Some days I'm extra thick...

You can't just use Row-Level Security, because each database may need different security, depending on who owns/uses it. If you were to do that, you wouldn't be able to let each company administer their own security etc. So bad plan to go that way.
Go to the top of the page
 
fogline
post Jun 14 2020, 08:51 PM
Post#9



Posts: 228
Joined: 5-August 15
From: Ringgold, GA. USA


Thank you Albert
For all of the Information, That gives me a lot to think about.
I think you are right about I need to install the SQL Server on my computer and really learn how to use
it before I try to run it from a internet host.
Thanks thumbup.gif

--------------------
Ray White - Fog Line Software LLC.
Email
Go to the top of the page
 
AlbertKallal
post Jun 15 2020, 12:36 AM
Post#10


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


I am just suggesting you approach this like Access.

Once of the GREAT features of Access is that you can start out easy. You build some tables. Build some form and reports.

It is OVER time that you learned about splitting. It seems all so simple, but the first time you split is kind of “cool”.

The same goes with SQL server.

Get it up and running on your local machine. You find the table designer, and the basic use of creating tables etc. VERY much the same as in Access.

Then try moving a BE to SQL server. You can use SSMA. It is REALLY confusing the first few times.

I blew out a WHOLE after noon trying to use SSMA, and was wondering why the migrate data option from the menu was grayed out! A whole afternoon!

Turns out the SIMPLE solution was to just click on (high light) the access database on the left side tree view. But it was driving me crazy.

With that comfort zone?

Well, now you can migrate a working application to SQL server. Say you have a case with 15 users – but they are having some corruption issue.

So, get your feet wet in smaller steps.

So you get up to speed linking tables, creating tables. Again, really not much different then linking to an accDB BE. And not too different then creating tables in Access.

That will get you “in the door”. Now you can look for or jump at the change to migrate working system to SQL server.

The reason again is that you have a “flat” learning curve. And often it means you getting paid to do this, and learn at the same time.

Once you done some migrations, then you off to the races here.

And in no time, then you be comfortable with SQL server.

And then when you attempt to use a hosted version of SQL server. And all these working parts will now make sense.

But you ONLY learning the hosted part and not “all of” SQL server in on shot.

SQL server will open more doors for Access then you realize.


If there is ONE skill set I can recommend to ANYONE using any system, any platform, and any technology?

Learn SQL server! It is a long term play and technology to use.

Want to scale up an existing Access application? You use SQL server.

Want to web enable your Access application? You use SQL server.

Want to web enable JUST SOME parts – say allow customers to check status of a project, or look up some information in an existing Access application?

Once again, your Access application can remain “as is”. You can work on Access, but have some web people setup and develop the web parts. Again, this opens doors.


Have a customer telling you that Access is not a good database?
Well, tell them you using SQL server for the database which is an industry standard. Tell them you don’t use the Access database.

It does WONDERS to eliminate the customer worry in this area, and you using Access as the front end is not a big deal!

So, I do high recommend jumping on this wagon.

Do, get SQL express running on your local computer. Once you gain JUST enough comfort, the you are ready to do a migration. And if you’re lucky, it will real work – so you lean better and faster!

I find it really hard to just sit down and do something I not using for work. But you can’t just jump onto SQL server migrations if you not done one before!

I mean if your first Access work was some huge complex monster application? It likely wold have failed. It is that “over time” that is the key to success with Access.

So get SOME play time and get JUST enough skills to pull this off!

After you first go around? You love the setup!

And as noted, learning SQL server will open up more doors then any computer skill you can learn right now.

I think SQL server is fast becoming a must have skill for Access.

Want to have some on-site Android phones connect and interact with your data? Again, SQL server will allow this.

I dare say that with SQL server and Access? You open many doors, and remove a huge number of limitations of Access. It not even a question of database size or speed. It simply that of Android phones, web sites, use of Accounting data, higher reliability, higher security etc.?

So, the list of benefits are huge. And they apply to typical software running in just about any business environment.

Good luck!

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Go to the top of the page
 
fogline
post Jun 15 2020, 08:10 AM
Post#11



Posts: 228
Joined: 5-August 15
From: Ringgold, GA. USA


Thanks Albert
I did get SQL Server Express installed
and SSMA for Access the newest version 8 they just came out with.
I have been using Access for many years I just have never jumped into SQL Server
I am running behind on that part.
Thanks for all of you help. thumbup.gif

--------------------
Ray White - Fog Line Software LLC.
Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    5th July 2020 - 06:40 PM