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
> Which SQL?    
 
   
firlandsfarm
post May 21 2017, 12:57 PM
Post#1



Posts: 235
Joined: 28-April 02
From: Heathfield, England


Hi, I've been thinking of switching from Access to SQL and as I'm looking to revamp my database I'm thinking if not now then not until the next revamp. SQL. That's easy, just install it and learn it! Wrong. There are so many SQL's ... MS, MY, Python to name some names I recognise and others I have just now read about in looking at SQL's! I wondered if I could ask for a little input on which to target. My main requirements are ...
    Ease of use, user friendly, I'm a beginner!
    Able to communicate with an existing MSSQL database (not mine, a third party proprietary database, updated with automatic daily downloads, that I can need access to to use alongside my data)
    Will best fuel a database driven website

I appear to already have the following installed (presumably by the third party database) ...
    Microsoft SQL Server 2008 Installation Centre
    'lots of things' under a Start-Up group "Microsoft SQL Server 2008 R2"
    Microsoft SQL Server Migration Assistant
    Microsoft Visual Studio 2012 (seems to be Command Prompts only)

(I'm not sure if the last one is relevant but I don't remember specifically installing it).

Is it a case of perhaps saying ... you already got what's needed in MSSQL, might as well run with that!

Is that a complete kit to run MSSQL?

If I run with MSSQL and give up the third party database I may lose the license to use it and by then I may have a few databases/websites run by it. Would it be a big job to switch to something like MYSQL rather than pay for an MSSQL license?

These variables are all new to me! smile.gif



--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
GroverParkGeorge
post May 21 2017, 01:53 PM
Post#2


UA Admin
Posts: 29,731
Joined: 20-June 02
From: Newcastle, WA


You have sort of a mish-mash of names there. Let's tease them out into their major groups.

SQL refers ONLY to the Structured Query Language which is the standard syntax for interacting with relational database. It applies across the board to many database products from many vendors.

RDBMS refers to Relational Database Management System, which is the database engine in which your data is stored. You use SQL Statements to manipulate data (create/read/update/delete) records in tables in an RDBMS, E.G. "SELECT Field1 from Table1".

It gets tricky because many vendors, including Microsoft, have included "SQL" as part of the names of their proprietary RDBMSs. SQL Server and MySQL are a couple. Others, though, have names that are quite distinct, Oracle and Postgres, for example. Each RDBMS has its own strengths and weaknesses, so a comparison is not all that simple to do. Many hosting sites offer both SQL Server and MySQL databases for your hosted websites. I lean towards SQL Server because I'm already familiar with it. YMMV.

In addition to the RDBMS itself, you need management tools to work with it.

SQL Server Management Studio (SSMS) is the primary way to work with MS SQL Server for development purposes. Other RDBMS vendors offer their own management tools to work with their products. If you use MySQL, you'll need their management tool, and so on.

Still another set of tools is required to create the user interfaces through which your end users interact with the tables in the database. Access is one such tool. MS Visual Studio is another. Other ways to create UI include PHP and other primarily web-based tools. All them are independent of the RDBMS and can be used to create UI for many different databases. Python is simply a coding language that you use to interact with data, which can be on any RDBMS for which you have permissions. It's "more" like VBA or SQL syntax, in that sense. So lumping it in with SQL as a RDBMS isn't appropriate.

Beyond the basics, you also have to pay attention to which Version of each product you have. It sounds like you have installed MS SQL Server 2008R2. That was a solid version, but the current production version is MS SQL Server 2016. Unless you have a very powerful reason not to upgrade, I'd consider it wise to install the newer version. SQL Server licensing gets steep for individuals, so you might want either the free SQL Server Express 2016, or the low-cost SQL Server 2016 Developer Edition. The developer edition can only be used for development, not production, but it is probably a wise choice for you as a developer because it offers all of the features (or most of the features) of the full licensed versions. If you sell a product, or set up a database for a client, you'll need to have them obtain the appropriate licensing for their organization in any case.

You have an older version of Visual Studio. VS 2017 is out. I don't think there is any compelling reason to upgrade to that, but it's worth considering. I believe there is a free "express" version of VS 2016 or VS 2017, so look into that as well.

You also have a tool called SQL Server Migration Assistant, or SSMA. It's used to migrate Access tables to SQL Server only.

I'm not sure how to address that last question. If you put a database driven application on a website, you're paying the hosting company to support it. An entry level package could be as little as $5.00 or $6.00 (US). Most of the time, part of that arrangement is going to be that they provide either SQL Server or MySQL, or most commonly both, to you to use. So, as long as you stay with that hosting company, there's nothing to move or replace and you're paying month to month for it. If you want to switch from one RDBMS to the other, knock yourself out. If you want to change hosts, say from GoDaddy to DiscountASP, or whatever, then you also have the issue of whether to change your RDBMS as part of that move.

Your question also seems to raise the possibility that you are thinking in terms of hosting these applications yourself. "If I run with MSSQL and give up the third party database I may lose the license to use it and by then I may have a few databases/websites run by it". I don't really see that happening, unless you plan to open your computer up to outside traffic coming in through the internet to your computer. Is that what you have in mind? If not, more clarity would be useful.

All in all, it's wise to take a thoughtful approach, so good luck.

--------------------
Go to the top of the page
 
GroverParkGeorge
post May 21 2017, 02:00 PM
Post#3


UA Admin
Posts: 29,731
Joined: 20-June 02
From: Newcastle, WA


One more thought.

You will not necessarily be switching from Access to "SQL". You can move the tables from your accdb files into a RDBMS, such as MS SQL Server (or MySQL, or another one). But you can still use Access to create and maintain the user interface for that data.

Keep in mind that we almost always insist on splitting Access applications into a "Front End" and a "Back End". Same thing here. Your BE moves from a file-based accdb on a network share to a server based RDBMS on a server. But it is still "the BE".

The FE can stay in Access. Or you can create a whole new UI for it, using Visual Studio or another coding platform.

--------------------
Go to the top of the page
 
firlandsfarm
post May 21 2017, 11:38 PM
Post#4



Posts: 235
Joined: 28-April 02
From: Heathfield, England


Hi Grover ... thanks for such a detailed reply.

Para's 1 - 4: Known and understood.
Para's 5 - 10: I sort of thought it was all about the management tools, the SQL part is the raw part and the tools the overlay to make life easier, yes? And from what you say then apart from having the most up to date model off the production line I have a full set of management tools, yes? I was a little confused with the Python reference, I came across Python/SQL in a search but couldn't see a clear explanation so wondered if it was an adaptation of 'basic' SQL. (Note: I may not use the term "SQL" in a purist sense, I'm using it in a generic way to label anything to do with SQL and it's packages ... sorry.)
Para's 11 - 12: I'm not thinking of hosting my own websites/databases but I will set up the chosen arrangement on my laptop as well as the Internet (I travel to some places where an Internet connection is just not available or very expensive ... when on my boat I may even be out of range of a phone signal!). I use the data for research as well as updating. It seems to be that because of the choices already made by the hosting suppliers I should limit my choice to MYSQL or a variant of MSSQL otherwise I won't be choosing a hosting service based on their service but on their SQL variant. Are all versions upwardly compatible with SQL Server? I'm assuming that Server 2016 will run anything coded for Server 2008 and Express 2016 and would Express 2016 run something coded for Server 2008 without conversion?

I've also come across something called SQLLite. I assume that is yet another product but from reading about it it seems to be standalone and apps would probably need recoding if migrated to MYSQL or SQL Server when moved to a hosting facility.

Is it OK to have both Server 2008 and Server Express 2016 installed on my laptop?

I read something re MYSQL yesterday when browsing that said 'choose which data engine you want' followed by a long list of those available and said that was not an exhaustive list ... yeah, right, first do your Doctorate in SQL then choose your data engine!

As I said the Visual Studio thing in the Start menu is just a group with a list of 5 links to "command prompts" (the DOS looking window). For example one is called "VS 2012 x86 Native Tools Command Prompt" and the opened directory in the Command Prompt Window is "C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>" which makes it look like it's only there to receive command line instructions and not be of any help, frightening! shocked.gif

One question I would like to be assured on, am I right to think that whichever SQL I install it will be able to read the tables in the existing SQL Server 2008 database because that is the must be, no cross, red line.

I have ruled out staying with Access because of the future website requirement and some of the slow speeds I am encountering which has been discussed on other threads here. Also I was discouraged from using Access in a web environment in a thread I started asking about it and I read recently that Microsoft are to withdraw all support for Access webapps in 2018. And that now takes us to ... Power Apps! I only saw this the other day, could this be a new 'management tool' for SQL and other resources?

I first looked into SQL a few years ago when there was a download for MYSQL but now I see the MYSQL Community Downloads page now lists 11 downloads and the Windows page lists another 6! It's all so complicated now just to find out what you want/need.

I think I'm moving towards Server Express 2016 from reading similar questions on the Internet because of the expected compatibility with all things Microsoft and that when help is needed it may be easier to find someone to help with MSSQL rather than MYSQL as it's the database mainly used by professionals. One of the problems thgough with Internet postings answering question that seem so old! I couldn't see one that was 2016 specific, many were talking 2008 and edit updated with 2012.
This post has been edited by firlandsfarm: May 22 2017, 12:24 AM

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
ScottGem
post May 22 2017, 07:09 AM
Post#5


UtterAccess VIP / UA Clown
Posts: 32,079
Joined: 21-January 04
From: LI, NY


I'm not sure if you are getting the gist of this. A database application is composed 2 major components. The data store and the User Interface. Access includes both components in one package, however Access can interface with many other datastores than its own native one. Most SQL based datastores DO NOT include the UI component, it has to be purchased separately.

Most UI dev platforms are not as easy as Access. They require much more extensive coding to do what Access does built in. For example when I took a vb.Net course I was astounded to find that a combobox required a minimum of 9 lines of code to do what Access does built in with no code.

So when choosing a platform for a database application, you need to select a datastore, a set of management tools AND an UI development tool.

The management tools are more for the DBA types who manage your data store.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
GroverParkGeorge
post May 22 2017, 08:13 AM
Post#6


UA Admin
Posts: 29,731
Joined: 20-June 02
From: Newcastle, WA


"...the SQL part is the raw part ..." "...choosing a hosting service based on their service but on their SQL variant..."

Well, no. That's overly simplistic, and therefore potentially misleading.

Often we are somewhat ambiguous in talking about this stuff. We're humans, so ambiguity is often accepted and acceptable. In fact, ambiguity is essential in humor, poetry and politics. Not in discussions of computers. So, while it's okay in informal conversations to refer to "Access" and to "SQL", here you are trying to get to an understanding of the concepts involved and that means ambiguity isn't a good thing. I strongly advise that you stop thinking in terms of a generic SQL. It's convenient, but inaccurate. And it can lead you into blind alleys.

Once again, "SQL" means ONLY "Structured Query Language". It is the language of database queries. We use "SQL" to Create new data in tables; Read existing data from tables; Update existing data in tables; and Delete existing data from tables. That's SQL, not the DB itself.

There are many varieties of SQL, including the one MS Access uses, the one MS SQL Server uses (called T-SQL), the one Oracle uses (called PL/SQL), and so on. Virtually every Relational Database has its own version of SQL, often with only slight variations from the standard, ISO described SQL.

We simply should not think of SQL as anything other than the LANGUAGE itself.
If you want to talk about databases on a server, do that: MS SQL Server©, or MySQL©, or Oracle©, and so on. Just not "SQL".

Again, in informal conversations, we often do get lax and talk about "SQL", when we really mean "Microsoft's SQL Server© database management system". Sometimes that gets even sloppier, and we talk of "SQL" as including tools provided by other vendors (MySQL being the most common), but to be clear in the context of this discussion, "SQL" can only be used to refer to the language used to query the data.

And now I'm going to contradict myself.

There are commands, using SQL syntax, that you can use to manage the objects within the database, including the tables themselves. This is more properly known as the Data Definition Language (DDL), and it includes syntax to create new tables, for example. So, in a more careful discussion, we also have to distinguish between the two -- SQL to manage data and SQL to manage the database.

I'm not sure what you refer to as "... having the most up to date model off the production line ..." Are you talking about SQL Server Management Studio? If so, then the most recent version is 2016. This is ONE of the tools you can employ in managing your MS SQL Server databases. It works only with MS SQL Server. If you choose a different RDBMS, like MySQL, you'll have to obtain the management tool that works with MySQL (and, of course, not with MS SQL Server). As a matter of fact MS Visual Studio© also includes tools to manage databases, but SSMS© is probably the most handy.

I am a bit concerned by the disclosure that you need to have access to your database while traveling where there is either no internet connectivity, or where it is too expensive to be practical.

You need to be aware, and I'm sure you are, that working with a database on a remotely hosted site requires that connection. Period. No connection, no database. While you can keep a separate copy of the data on your laptop, you will have to provide a way to synch that data with the Master copy in your primary database. Not easy, but definitely possible.

SQLite is indeed a Relational Database Engine. It is in the public domain, which means it is free. I know very little about it, so I won't comment further. It looks interesting for some types of applications.

"Are all versions upwardly compatible with SQL Server?" Well, it depends, versions of WHAT? Which version of MS SQL Server?

Certain features are available on newer versions of software that are not available on older versions. That's true of virtually all software, so the question, IMM, is whether there are features you need which are available only on a newer version of the software. It just depends. I would not worry about that so much. I have one client who is just now in the process of migrating from SQL Server 2000 to a newer database. It has worked like a champ for 15 years, but it's time to move on. As long as your interface can work with the database, you'll be fine.

"may be easier to find someone to help with MSSQL rather than MYSQL as it's the database mainly used by professionals." I'm not sure that is true, except in a limited sense. MySQL is widely used by all sorts of organizations large and small, as is MS SQL Server. The specific application for which it is used is probably more important than the status of the person using it.

Read Scott's concise statement again, please.
This post has been edited by GroverParkGeorge: May 22 2017, 08:16 AM

--------------------
Go to the top of the page
 
GroverParkGeorge
post May 22 2017, 08:37 AM
Post#7


UA Admin
Posts: 29,731
Joined: 20-June 02
From: Newcastle, WA


I apologize.

I failed to distinguish between Database ENGINES, and Database MANAGEMENT SYSTEMS.

It's easier to understand that, I think, in Access, where all of the components -- including both the engine, called ACE, and the interface are together. In other tools, where there is no interface component, it's easier to get lax and forget that the db Engine is one part of the whole system.



--------------------
Go to the top of the page
 
firlandsfarm
post May 23 2017, 07:22 AM
Post#8



Posts: 235
Joined: 28-April 02
From: Heathfield, England


Grover, you have put a lot into this one I can see that from the two 'essays' you have written but I don't think we are getting very far and it seems to be getting more complicated between us than it perhaps needs be. I had a grasp of the principles of just about all of that before starting this thread but was just looking for confirmation and clarification of where the bits I have fitted into an SQL based set-up. Maybe I didn't use the precise exact technical name for some things but I certainly understood that I had to be connected to a database to use it! I appreciate front ends, back ends, subtleties of different languages that masquerade in the same class, that an engine does the work etc. ... I grew up using BBC BASIC, MS BASIC, IBM BASIC to name a few but we always referred to them as 'BASIC' in the full knowledge that we knew there was more than one variant (the distinction between them was only discussed when needed to be). We knew full well that BASIC was only the language but we also had the understanding that if someone said they used BASIC they weren't just referring to the magnetic contents of a cassette tape or floppy disk lying on a shelf. Maybe we weren't as name perfect as nowadays but we communicated in a normal way and the whole of the desktop computing industry came from that inexact beginning so we couldn't have done too much wrong with our simplistic but obvious approach to discussing the subject.

In my initial post in this thread I was simply asking what SQL set-up might be worth considering for the requirements I had and from the components I listed I asked if I had a complete set-up already installed. I know that the tools, engines, management systems, interfaces or whatever you want to call them that work in an SQL Server based environment are not for MYSQL set-ups so knowing that (and assumed you know that) I didn't see the point in constantly listing items individually when we know that they form and work in a group, it's easier and more efficient to refer to them as the group they are. In my second post I was simply trying to confirm I was with you by translating your input into a common language ... English. KISS ... Keep It Simple S.....!

I'm not sure we are going to gain much more by pursuing this further in any detail so I'm going to thank you for your input and call it a day. You did lead me towards some issues I can investigate further such as Server Express 2016 but I would be grateful if you could just clarify a couple of points I raised in my previous post.
    In my question about version compatibility I did give a couple of actual examples

      will SQL Server 2016 run anything coded for SQL Server 2008 (should I migrate to a hosting service that operates on SQL Server 2016) and Express 2016 (should I develop my database with that language), and

      would Express 2016 run something coded for Server 2008?

    Is it OK to have both Server 2008 and Server Express 2016 installed on my laptop? (no clashes)

    Am I right to think that whichever variant SQL based environment I run it will be able to read the tables in the existing SQL Server 2008 database just as Access 2016 does.

I think they have renamed Visual Studio Express as Visual Studio Community 2017

Again thanks for your help.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
GroverParkGeorge
post May 23 2017, 07:54 AM
Post#9


UA Admin
Posts: 29,731
Joined: 20-June 02
From: Newcastle, WA


I appreciate your point. As you put it, BASIC refers to a group of similar LANGUAGES. SQL, in the same vein, refers to a group of similar LANGUAGES. That's a good way to understand it, both are language groups. Period.

Newer versions of MS SQL Server probably will work with stored procedures, functions, or views created in previous versions. Newer versions tend to introduce new features that make some things easier, faster. more flexible, and so on. However, like opening an mdb from 2003 with Access 2016, chances are pretty high most things will work okay.

So the answers to both of your first two questions is, probably so. The Express versions of SQL Server do have limitations, such as using only one CPU, whereas full versions can use whatever CPUs are available, and having a maximum storage space of 10 GB. Other features available on full licensed versions of SQL Server are not turned on in the Express versions. However, for the kind of work you are likely to do, it shouldn't matter.

I have multiple versions of SQL Server installed on some of my development computers. Five on one of them. They run fine because one is not necessarily aware of the other. They are Services that just run under Windows. You can have two or three versions, yes.

If you can connect to the tables in your existing SS 2008 instance, you can use them, yes.


--------------------
Go to the top of the page
 
firlandsfarm
post May 23 2017, 09:16 AM
Post#10



Posts: 235
Joined: 28-April 02
From: Heathfield, England


Thanks George ... From what I'm picking up they all do the same basic things but sometimes get there in different ways. I'm only looking for basic stuff. As I said my main reason for migrating is speed and compatibility towards eventual website integration. I feel disinclined to try and use the existing installation, I think it will be better to use a brand new installation so that I know it's clean ... I'm going to go and see if I can find a comparison list between Server Express 2016 and MYSQL.

Thanks again.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th June 2017 - 07:15 AM