Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185

    Azure SQL Hosting / Local SQL Hosting


    Hi All

    I have built a split Access database, however upon deployment its works painfully slow over WAN (using company VPN connecting to HQ file server).

    I know I have basically two options:

    1. Run a Microsoft SQL server on one of our servers and convert the tables to SQL using the SSMA tool.

    I dont know a lot about SQL servers but from the research I have done so far it seems fairly simple, I install Microsoft SQL server on one of our HQ servers, then using the SSMA tool I convert my Access Database to Frontend/SQL Backend, fix any errors (I dont think there should be many, its a reletively simple Database and its just the tables) and then it should work?

    My questions are:

    How much of a performance increase should I expect? Over WAN with the current split database its painfully slow to the point its unusable, will converting to SQL backend make it workable for a small company (50 employees spread over 5 cities, not all using it at the same time, would be odd if 5 people used it in a day)?
    What potential issues should I expect, will my queries/forms still work with SQL backend tables, they can be quite complicated?



    2. Do the same but host with Microsoft Azure

    From research this is the fastest option, however does anyone have any experience with Microsoft Azure SQL, the website seems like its targetting massive multinationals for large projects integrating multiple systems and functions, all I want is a fast/smooth SQL database for a Access frontend to work with, I couldnt quite figure out the costs either as the pricing calculator came out at over 2k a month which seems massively excessive.



    Are there other options available?

    Thanks

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    With a multi-site user base Azure would definitely be the way to go.
    It certainly shouldn't be 2K a month. Use a Azure SQL database - not a managed instance, they are expensive.
    It shouldn't cost you more than $100 a month tops, probably a lot less if it's not data and processing heavy.

    You need to allow for IP address management if you have home users, as the Azure access is very much based around an IP address firewall.

    You initial issue - WAN via VPN will be painful.
    A remote desktop solution (Citrix or Terminal services) would be another possibility.
    You could then upscale to SQL Server Express (Free) hosted wherever the RDP server is. That would give you a very secure fast solution.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Moving to sql server/azure will not necessarily solve your performance issues. You should also review your application to ensure it has been optimised for the web. For example forms should be designed to open with an empty Recordset which is only populated once the user has supplied some criteria. Don’t have forms based on tables, don’t use lookups in tables, avoid domain functions, etc

    you might find this link helpful for reviewing your app
    https://www.access-programmers.co.uk...issues.291269/
    Last edited by CJ_London; 03-15-2021 at 05:00 PM.

  4. #4
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by Minty View Post
    With a multi-site user base Azure would definitely be the way to go.
    It certainly shouldn't be 2K a month. Use a Azure SQL database - not a managed instance, they are expensive.
    It shouldn't cost you more than $100 a month tops, probably a lot less if it's not data and processing heavy.
    Good to know, I was looking at the pricing page but couldnt figure it out, I have asked for a sales call back.

    Quote Originally Posted by Minty View Post
    You need to allow for IP address management if you have home users, as the Azure access is very much based around an IP address firewall.
    What does this mean exactly, we have at least 3 home users but likely more at times like these, they usually log into VPN to do file server stuff, will that help with Azure?

    Quote Originally Posted by Minty View Post
    You initial issue - WAN via VPN will be painful.
    A remote desktop solution (Citrix or Terminal services) would be another possibility.
    You could then upscale to SQL Server Express (Free) hosted wherever the RDP server is. That would give you a very secure fast solution.
    Can you expand on the remote desktop solution more?

    Quote Originally Posted by Ajax View Post
    Moving to sql server/azure will not necessarily solve your performance issues. You should also review your application to ensure it has been optimised for the web. For example forms should be designed to open with an empty Recordset which is only populated once the user has supplied some criteria. Don’t have forms based on tables, don’t use lookups in tables, avoid domain functions, etc

    you might find this link helpful for reviewing your app
    https://www.access-programmers.co.uk...issues.291269/
    I think I might run into problems, I have attached the current non-split database, any chance you could give me some pointers?

    Freight Rate Program Master v0.58.zip

    Make sure to click the tick box "Include Expired Rates" top right of form to actually show data

    Thanks

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I peeked st your dB and see several things that I would be concerned about. It appears that this is probably a production dB, so I think changes might be difficult.

    There are spaces in object names. There should never be spaces in names - especially in field names.
    The 2nd line in EVERY Module should be "Option Explicit". I did not see any variable declared, so all variables default to Variant data type.
    Every table has a PK field name of "ID".
    It looks like (to me) the tables are in spreadsheet design........


    What are:
    20GP BAF
    40GP BAF
    40HC BAF
    20GP GRI

    Maybe account numbers?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    agree with Steve - lot's of things that potentially will give problems

    Relationships not defined so difficult to work out what the app is supposed to do (other than something to do with freight)

    performance concerns

    - forms based on whole tables, not a query limited by criteria. To test impact on performance, change one of your forms to a query something like

    SELECT * FROM myTable WHERE False

    this won't return any records, but does it improve performance? If so that can be built on

    - not sure but are all relevant fields indexed?
    - use of union queries indicates a non normalised data structure

    What does this mean exactly, we have at least 3 home users but likely more at times like these, they usually log into VPN to do file server stuff, will that help with Azure?
    unlikely - since your issue appears to be your vpn.

    Can you expand on the remote desktop solution more?
    search for 'terminal server' or 'citrix' for corporate solutions, Alternatively search for 'remote desktop' which will bring up a range of apps you can use to connect to another computer. Useful if your users have a machine in the office on the network to which they can connect from another computer at home.

  7. #7
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Basically, its a tool to look up the cost of freight between ports, and update them (an admin user task).

    The form grabs info from FRT_Table + FRT_Additionals_Table + Transits_Table and combines the data together using queries (Master_Data + Master_Data_Offset + Master_Data2) the final product is Master_Data2, which is then used to populate the large central list box on the form.

    Various DLookups in the blue boxes below when a line is clicked on in the listbox.



    In regards to performance which is the main thing I'm concerned about at the moment (I can tidy up the non-performance related issues over time as I update it), what are the main problems I should tackle?

    - Spaces in field names, is that a performance issue, I know that there shouldnt be spaces but the reason is that otherwise the forms list box looks unprofessional.

    - Declaring variables in VBA, is this a performance issue, I'm fairly proficient at VBA and usually my final code is all declared, I just find it easier to write undefined as I go as I'm still forming ideas on how to achieve what I want with VBA.

    - Every table PK as ID, is this not a good idea? I thought every table needs a PK ID field, I don't actually use them for much in the actual workings of the database? Is this a performance issue?

    - Tables are indeed "spreadsheet" design, the main reason for this project to begin with is to make updating the data quicker in labour hours, and the data mostly comes in spreadsheets, if you look under the admin section all of that is about updating from spreadsheets so its quick and no user error.

    - 20GP BAF etc are costs, that are added together (in a specific way of course) to get a "All In" costs in the forms listbox

    - Form based on whole tables, the main listbox as above is based on the Master_Data2 query, which is the result of other queries and tables. In regards to performance I can have it blank until the criteria's have been entered yes (criterias being all the various controls along the top of the form). So that is an easy fix and I'm sure I can convince my boss that the form doesnt need to show "All" data to start with as thats not practible anyway.

    - Relevant fields indexed, this is where my knowledge of access and databases falls short, no idea how or why to do this, so probably not?

    - Again don't really know what this "use of union queries indicates a non normalised data structure" means, the data comes externally provided (in spreadsheets) I have built this the best way I could to encompass that and present it in the way that is required.


    In regards to overall performance, I know this is a bandaid workaround idea but couldnt I set up the 4 actual tables of data (FRT_Table, FRT_Additionals_Table, Locals_Table & Transits_Table) on a SQL server, then have the Access frontend upon opening simply copy all from those tables to local tables for the form to work with, obviously this wouldnt be "live" but it would work as rates are not updated by normal users, and admin users would only update rates a couple of times a month at most.

    This would mean a "loading" time at start up (hopefully not minutes), but then local seemless performance thereafter?


    search for 'terminal server' or 'citrix' for corporate solutions, Alternatively search for 'remote desktop' which will bring up a range of apps you can use to connect to another computer. Useful if your users have a machine in the office on the network to which they can connect from another computer at home.
    I did a search of that, and it does sound interesting, we use remote desktop all the time for various IT tasks, my question would be can Access be used in a way that remote desktop only opens the Access runtime window, instead of showing the user the whole server computer? For example we use a commercial software called Cargowise, and that I'm fairly sure it what this is, we connect to their servers and work from them directly over remote desktop, but we only see the Cargowise application window only.

  8. #8
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    In answer to your last question , I believe this is known as a remote desktop app. That is the remote connection opens the app directly and when whatever app is closed the remote desktop session is closed.
    We used it for road warriors and an Access app a long time ago, but I didn't set it up.

    Each user will need their own folder on the remote server with a copy of the front end in it.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Spaces in field names - no, but can slow development and generate unexpected errors

    not declaring variables- potentially yes

    pk performance issue yes but was referring to the name- and you should use them

    indexing see the link I provided there is another link to the a thread about the importance of indexing


    spreadsheet design performance issue yes

    union queries do not use indexing

  10. #10
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by Ajax View Post
    not declaring variables- potentially yes
    Cheers, I'll get onto to finishing my code

    Quote Originally Posted by Ajax View Post
    pk performance issue yes but was referring to the name- and you should use them
    Do I need to change the name so they are different on each table to improve performance?

    Quote Originally Posted by Ajax View Post
    indexing see the link I provided there is another link to the a thread about the importance of indexing
    Thanks, will take a look. So I should create indexes for all fields that are commonly searched? Does it matter if they would container duplicates? Can I have multiple fields indexed separately or is too many indexes a problem?

    Off the top of my head I'd imaging POL Name / POD Name / Carrier & Valid From / To should all be indexed on FRT_Table for example, as they are the fields that the form uses to create the listbox.

    Can you add indexing to a query result?


    Quote Originally Posted by Ajax View Post
    spreadsheet design performance issue yes
    What do you mean by this exactly, what is spreadsheet design? When I created this project I made the tables in Access first then exported them to excel to use as my upload templates, what do I need to change to make them "not" spreadsheet design?

    Quote Originally Posted by Ajax View Post
    union queries do not use indexing
    Hmm, thats unfortunate, as my main queries is Master_Data2 and its a union query.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Do I need to change the name so they are different on each table to improve performance
    no - just makes the development phase easier an less prone to error. It's about being organised about what you do. Up to you but every time you provide examples with ID as a field name, you will get responders saying you should change it to something more meaningful and unique to the project, not just the table.
    So I should create indexes for all fields that are commonly searched? Does it matter if they would container duplicates? Can I have multiple fields indexed separately or is too many indexes a problem?
    read the thread, no, yes and no. - read the thread

    Can you add indexing to a query result?
    no

    What do you mean by this exactly, what is spreadsheet design?
    you tables look like spreadsheets

    a spreadsheet design
    CustomerName..CustomerAddress..Phone1...phone2.... contact1...contact2

    a database design for the same data

    tblCustomers
    CustomerPK
    CustomerName
    CustomerAddress

    tblCustomerPhones
    PhonePK
    CustomerFK
    PhoneNumber

    tblCustomerContacts
    ContactPK
    CustomerFK
    ContactName


    If you don't know what PK and FK stand for - google/bing 'normalisation'

    Hmm, thats unfortunate, as my main queries is Master_Data2 and its a union query.

    like I said - implies a spreadsheet design. To do anything significant in a database with a spreadsheet design such as above would potentially require a union query when you want to look at or search for data such as phone number or contact names. Or you start to have unnecessarily complex queries which become difficult or impossible to manage as business rules evolve.

    With respect to performance, if you don't follow good database design you will end up with a model T Ford and wonder why it doesn't perform like a Ferarri.

  12. #12
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Thanks, will work on the above.

    In the meantime I noticed that Azure has a free trial so I have started that to have a look to see what works and what doesnt.

    So I created a Azure SQL database, and using SSMA I connected to it and ran the wizard, however I get an error on linking of tables:



    Any idea on what causes this and how to fix?
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  13. #13
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Was the database in use by anyone else - sounds like it needs exclusive access?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  14. #14
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    No just me, no one else even has it and its a non-split database currently.

  15. #15
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    So I tried working through this:

    https://support.microsoft.com/en-us/...a-0db3613f5166

    However when I get to the point of linking the tables it only shows these tables from my Azure server:

    Click image for larger version. 

Name:	Linked Tables.PNG 
Views:	35 
Size:	24.7 KB 
ID:	44764


    In that giant list it does not include any of my actual tables (FRT_Table etc)

    I'm guessing I need to put a better path than "tims-sql-server.database.windows.net"



Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Hosting
    By Perfac in forum Access
    Replies: 1
    Last Post: 09-27-2018, 01:34 PM
  2. Web hosting
    By Perfac in forum Access
    Replies: 2
    Last Post: 05-04-2018, 04:02 PM
  3. DB hosting solutions
    By ahill48 in forum Access
    Replies: 8
    Last Post: 08-20-2017, 10:26 AM
  4. Web Hosting
    By momodoujimnjie in forum Access
    Replies: 1
    Last Post: 09-01-2015, 09:59 AM
  5. Replies: 3
    Last Post: 01-22-2013, 03:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums