Solved

MS SQL hosting for Access Front End

Posted on 2011-09-10
9
339 Views
Last Modified: 2012-05-12
I am looking for a MS SQL 2008 (preferrable R2) database.  I have looked and read through many many reviews but would like the opinions of you professionals.  

Basically the front end will be MS Access and connect through ODBC to my hosted DB.  One step further I am looking at Sharepoint hosting for some of the Access web forms (2010), but the DB is the most important part as of now.  

I have looked very close at 1&1, WebHost4Life, ServerMart and a couple others.  These three stand out to me the most but what is your opinions?  Thanks in advance.
0
Comment
Question by:pskeens
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 36517838
I have to question whether you will get acceptable performance with the FE-BE connection at WAN speeds.  You may be hosting the Access web forms sooner than you think.
0
 
LVL 2

Author Comment

by:pskeens
ID: 36517847
I have done it before and performance was pretty good.  THis is one reason I want a good host with good performance and stability.  
0
 
LVL 7

Expert Comment

by:Kishan Zunjare
ID: 36518650
You can go with ServerMart.

http://www.sqlservermart.com/Domain.aspx
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36518664
If you want an enlightened advice about how a hosted SQL Server database would perform using an Access Front end, you must to provide more details about connectivity, network bandwidth and host database size.  You also need to provide us with information as to how many people will be using the database front end.  

If on the other hand, you already made up your mind onto the solution and simply want us to provide you with a good host for SQL Server, then the question seems to have been already answered.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36518665
<<I have done it before and performance was pretty good.  >>
Some words of caution: the fact that you had good performance by using the system by yourself does not mean you will get good performance if many people connect at the same time.   Sorry but that is the way SQL Server works.

Hope this helps...
0
 
LVL 9

Accepted Solution

by:
Armen Stein - Microsoft Access MVP since 2006 earned 250 total points
ID: 36519069
You can't use Access 2010 web forms for data hosted in SQL Server.  They are restricted to SharePoint tables only.  And Access client forms cannot be converted to Access web forms.  So if this is a direction you think you might go, be aware that you might be looking at a rewrite later.

You'll need to make sure your Access FE is optimized for using SQL Server, especially over a WAN.  It can be done, but you have to be careful.  I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

http://www.JStreetTech.com/downloads

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Regarding SQL Server hosting, there are a lot of options.  There are many hosting companies that provide it, including us.  You can get shared hosting (yours is one database among many on a server) or you can get virtual private server (you get the whole server).  In all of these, prices vary greatly based on capacity, reliability, flexibility, customer service, etc.  I've heard stories of problems with the cheap ones.

And then there's SQL Azure - have you considered that?  It has its own pros and cons over regular SQL Server hosting.  Luke Chung has an article on using it:
http://www.fmsinc.com/microsoftaccess/cloud/link-to-azure-sql-database.html

Hope this helps,
Armen
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36520665
@ArmenStein>
Some very good and relevant comments.  I do concur...

The proposed configuration does not scale well and does not migrate to Sharepoint Access Services without abandoning SQL Server.  If the backend is SQL Server hosted over a WAN connection, then you need a middle tier that's also hosted such that you can get much higher bandwidth between the application and the data.

0
 
LVL 2

Author Comment

by:pskeens
ID: 36540456
dgmg

What are you referring to as a middle tier?

I have it hosted now over WAN and the performance is good for development.  Yes this will differ when I get concurrent users but we are only talking about 5-8 concurrent users at the same time maximum.  On average it will be 2-3 I would imagine for now.  

The future plan is to move it all in house but I have to get it running for now.
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 250 total points
ID: 36540678
>Middle Tier
The FE is at the workstation. The FE presents info to the user and accepts user input, makes requests to the middle tier when, for example the "submit" button is pressed.  The middle tier processes the input and submits requests to the data BE.  

This is the architecture of a 3-tier client-server.  In ASP, for illustration, the browser is the FE, the ASP page is the middle tier, and the DBMS is the back end.  Sharepoint Access Services is another example of a middle tier: the browser on the workstation, Sharepoint in the middle and SQL Server in the backend.

The important part of the equation is that there is relatively little traffic on the WAN between the FE and the middle tier.  But the middle tier can be connected to the BE at LAN speeds which are needed for moving lots of data.

Two tier configurations (which you are considering) work fairly well at LAN speeds, but they don't scale so well at WAN speeds.  It's not so much about the number of users, but about the amount of data moving between FE and backend.  For example, it's entirely possible that Access will pull an entire table over the network in order to extract a single record from it.  And performance obviously goes south.

Having said that, Access is really pretty good about pushing data work to the backend.  And good designs, use of passthru queries and other server-side mechanisms can minimize the amount of traffic that flows between the FE and the BE.  But, it still takes a fair amount of dilligence and server-side expertise to avoid performance issues when the FE and BE are connected over the WAN.  






 



   
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question