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 Server Setup And Cost, Any Versions    
 
   
azizrasul
post Jul 12 2018, 03:43 AM
Post#1



Posts: 1,396
Joined: 18-July 00
From: Faisalabad, Pakistan


Hi,

Just started working for a new smallish (30 people) company. Using front and back end MS Access databases which is very slow due to the server being a 100 miles away and using VPN with a wireless connection.
I have been asked whether we can use a back end SQL server database instead to speed things up. However I don't know anything about SQL server and what the cost implications are in purchasing it and if there are any ongoing costs involved. Could I even use an older version to cut down costs?

Also is it easy to substitute the tables in the back end MS Access database to SQL server and simply link the tables in the front end MS Access database? Each user will have their own front end. I assume I will have to use pass through queries to the SQL server database in order to get the data?

Any help would be appreciated as I am out of my depth on this one.
Go to the top of the page
 
isladogs
post Jul 12 2018, 04:09 AM
Post#2



Posts: 636
Joined: 4-June 18
From: Somerset, UK


There are several versions of SQL Server starting with the free Express version and others which are very expensive.
All offer numerous advantages over Access BEs including larger file size, greater security and improved stability.
The Express version allows up to 10GB files, paid versions even more
Crashes should become a thing of the past
What it won't do 'out of the box' is magically improve speed / performance

The expense for the free version comes in terms of the learning curve

You can upsize from Access to SQL Server using the built in Access upsizing tool (deprecated in A2016)
Or better still, use the more powerful Migration Assistant for SQL Server

It will take time but is perfectly 'doable' as long as your tables do not contain unsupported datatypes e.g. attachment fields, MVFs
If you have any of these, you need to redesign your database from the beginning

BUT even with SQL Server as a BE, using a VPN with a wireless connection is a very risky thing to do - corruption will almost certainly occur at some point.
Much better to use Windows Terminal Services or Citrix which builds upon that technology - both have a hefty price tag
I have used the first method but know nothing about setting it up

Alternatively, I would look into Azure which may be well suited to your setup ...but I have no experience of using it

HTH
This post has been edited by isladogs: Jul 12 2018, 04:13 AM
Go to the top of the page
 
GroverParkGeorge
post Jul 12 2018, 06:14 AM
Post#3


UA Admin
Posts: 33,919
Joined: 20-June 02
From: Newcastle, WA


If performance were the main or only reason to consider a change to SQL Serverver, you’ll porbably be disappointed. There are good reasons to make the change, though.

Consider the suggestions offered to begin. Should you make the move, you might find that data corruption is less likely because SQL Server is more robust
Go to the top of the page
 
azizrasul
post Jul 12 2018, 06:21 AM
Post#4



Posts: 1,396
Joined: 18-July 00
From: Faisalabad, Pakistan


That's helpful. Do you know if you can you use a MS Access front end and connect to the Microsoft Azure SQL database using ODBC and if so send SQL scripts to the Azure db? I assume the speed performance would greatly improve using Microsoft Azure SQL database?
Go to the top of the page
 
cheekybuddha
post Jul 12 2018, 06:31 AM
Post#5


UtterAccess VIP
Posts: 10,538
Joined: 6-December 03
From: Telegraph Hill


>> Using front and back end MS Access databases which is very slow due to the server being a 100 miles away and using VPN with a wireless connection. <<

I would bet that using SQL Server (or an Azure version) will magically improve speed / performance compared to what you have now.

hth,

d
Go to the top of the page
 
GroverParkGeorge
post Jul 12 2018, 06:46 AM
Post#6


UA Admin
Posts: 33,919
Joined: 20-June 02
From: Newcastle, WA


I would not bet money on any speed improvements, but only valid testing can tell what would happen in YOUR environment, which is unique. Given the reliance on a WAN and wireless, I'd race to upgrade the system for security and stability anyway.

SQL Azure works extremely well. I run it for most of my personal use databases and at least one current client. Great setup, but performance is NOT one of the advantages.
Go to the top of the page
 
azizrasul
post Jul 12 2018, 06:53 AM
Post#7



Posts: 1,396
Joined: 18-July 00
From: Faisalabad, Pakistan


Am I right that if we go for the Azure option, I can send pass-through SQL scripts from my FE MS Access db to the BE Azure db and get the data that I need?
Go to the top of the page
 
BruceM
post Jul 12 2018, 07:04 AM
Post#8


UtterAccess VIP
Posts: 7,718
Joined: 24-May 10
From: Downeast Maine


Although I have limited experience using SQL Server with wireless (and rather limited experience with SQL Server in general), I disagree that it is very risky and corruption is likely. SQL Server (and MySQL and others) use a fundamentally different approach than the native Access database engine.

It is something like this: with the native Access engine, an instance of the engine is created locally. Processing takes place there, and the result is written to the tables. It is the write operation where corruption is most likely to be a problem. Even a very brief interruption during the write operation, which is not unlikely with a WAN, VPN, or wireless, can cause corruption.

With SQL Server, the data to be processed are sent to SQL Server. When everything has arrived (I don't know how the engine knows this, perhaps there is a stop bit or something of the sort), processing is performed and data written to the tables all within the same computer, greatly minimizing the chance for corruption during the write operation.

The difference I have described is the difference between client-based (Access) and server-based (SQL Server and others). I expect I omitted important details, but I believe the flyover view is reasonably close.

Also, depending on the nature of fields (lookup, multi-value, attachment) not supported by other systems, it may not be quite as bad as starting over. For instance, it should be possible to parse MVF data into a related table in Access, make appropriate changes to queries and forms and so forth, and use the modified Access design as the basis for the SQL Server import. It is extra work to be sure, but if you have just a few fields of the sort it shouldn't be too drastic.

One thing I have discovered (this is very general) is that performance is improved by performing joins and other processing in SQL Server. For instance, a combo box Row Source that involves more than one can usually be handled more efficiently using a pass through query than by using an Access query on the linked tables. You can link to SQL Server, rename the linked tables to match the names of the Access tables, and use the database as you have been, but a performance hit is likely.

On the other hand, SQL Server has some capabilities not directly available to Access queries. For instance, there is a CASE function, similar to Select Case, but it can be written directly into SQL. If you are using Select Case in a user-defined function in Access to perform a calculation in a query, you can instead use CASE in the SQL Server to do the same processing very efficiently.

I won't try to get into any more details now, but having recently done some conversions (with much help from the good folks here at UA) I can tell you that moving to SQL Server has a lot to recommend it.
Go to the top of the page
 
azizrasul
post Jul 12 2018, 10:12 AM
Post#9



Posts: 1,396
Joined: 18-July 00
From: Faisalabad, Pakistan


Thanks all.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th November 2018 - 11:38 PM