Results 1 to 7 of 7
  1. #1
    Bertus is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    4

    SQL Server backend migrate to Azure SQL

    My client runs an Access front-end on an SQL server backend.
    They want the backend migrated to Azure, and ask me about the (im)possibilities/consequences in Access.



    I'd like to find out:
    Is functionality in the Azure environment the same as in SQL server? Do stored procedures, functions, views still work or are modifications necessary?
    Does the front-end connect to the backend the same way? There are about 40 linked tables (all to one and the same BE).
    In VBA ADODB connections all use one connection string. Does this work similarly after migration?

    I know my way around Access (though not as well as probably 99% of you all) allright, know how to modify stored procedures in SSMS, but no idea of the trouble I may get myself into after this migration.

    Any help or pointers greatly appreciated!

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,065
    There is very little difference between Azure SQL and a standard SQL server in terms of functionality.

    In Azure SQL you do lose the JobAgent, so if that is important you may need to look at a managed instance, but that is more expensive.
    Azure access is very much based around IP address firewalling, that can prove a little interesting if you have home workers connecting directly how aren't on fixed IP addresses.

    If your current SQL server is local, then there will be some trade-off in speed, so ensure your forms are optimised to only pull in the minimum data you need, but generally I find the performance more than adequate.
    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
    Bertus is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    4
    Thanks!

    On the web, I read:
    runtime
    SQL Azure does not support common language runtime (CLR). This means system views, stored procedures, triggers, and user-defined functions are not available in Azure.
    This confuses me.
    Is there an Azure SQL interface at all, in which I can create and modify tables, views, functions and stored procedures? Similar to the SSMS?

    My client's users (mostly working from home at the time) and myself connect to a virtual environment by Cisco VPN. In that environment they simply run the Access frontend connecting them to the SQL-server.

    Please have patience with me, as language and inexperience slow me down a bit. (might as well be my signature...)

  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,065
    I have no idea what that means.

    A majority of the databases I am responsible for are hosted in Azure. I use SSMS as my main development tool for the Azure databases, outside of FE development in Access.
    Stored Procedures, Views, Functions etc etc all behave in exactly the same way as a normal SQL server.
    Table design, triggers etc. etc. All the same.

    The only thing I miss from traditional SQL Server is the Job Agent, and Database Mail, both of which I found very handy, however there are alternative methods to achieve the same or similar functionality.

    The other bonus is that it's constantly up to date. New features just "Appear" without the pain of installing a SQL server update, or worse still a full version change.
    I'm a fan. (in case you hadn't realised)
    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 ↓↓

  5. #5
    Bertus is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    4
    Okay, I've a couple last questions (for now...)

    Why use SSMS as your development tool?
    Is connecting to Azure SQL just a matter of rewriting the ODBC connection-string in the Access Link-manager?
    Users now open Access and connect to the BE without a password required. Also, when in VBA a connection to the BE is opened, there's no pwd involved. Can this stay the same in this cloud environment?

    Also, the string in VBA is
    Code:
    "Provider=Microsoft.Access.OLEDB.10.0;" & _                
      "Persist Security Info=False;" & _
      "Data Source=servername;" & _
      "Integrated Security=SSPI;" & _
      "Initial Catalog=databasename;" & _
      "Data Provider=SQLOLEDB.1"
    and is used in all ADODB connections. Will there be any substantial changes there?

  6. #6
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,065
    I use SSMS for all the all the Azure SQL development stuff, writing stored procedures, creating views etc.

    Connecting to Azure is exactly the same, except that the integration with your current log-ons would need to be linked with a Azure Active Directory https://docs.microsoft.com/en-us/azu...ct-user-signin
    You will need to add the office external facing IP address to the Azure Firewall to gain access.

    If your AD is on premise I'm not sure how that would work, simply because we either store the password with the table connections (environment is secure, so it doesn't matter), or have a locked down method of encrypting the connection password.
    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 ↓↓

  7. #7
    Bertus is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    4
    Thanks Minty!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 11-20-2019, 03:22 AM
  2. ADO VBA and Azure SQL Server
    By nd0911 in forum SQL Server
    Replies: 2
    Last Post: 02-27-2019, 04:09 AM
  3. Migrate MS Access Backend to MySQL
    By SierraJuliet in forum Access
    Replies: 4
    Last Post: 01-14-2019, 03:08 PM
  4. Migrate Access to SQL Server
    By myukas in forum Access
    Replies: 5
    Last Post: 06-04-2018, 03:51 PM
  5. Replies: 3
    Last Post: 07-30-2014, 05:04 PM

Tags for this Thread

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