Results 1 to 7 of 7

Access 2010 to SQL Azure migration

  1. #1
    Aurelius7 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    4

    Access 2010 to SQL Azure migration

    This is for anyone thinking of moving their Access data to a SQL Azure platform and using an Access front-end file to link to it...and maybe for those who have already gone through the process themselves and want to add their story (horror or otherwise) or suggestions. The net result for me: it's slow and prone to errors that are not always easy to resolve.

    A client of mine wanted to get their Access data "in the cloud" while retaining their Access front-end which has many forms/reports. They had been using a single front-end (forms/reports) Access 2010 file linked to several other Access 2010 "back-end" files (roughly 70 tables) residing on several networked file shares. Their user base is about 10-15 and is divided between two states in the U.S. They were all accessing the SINGLE Access front-end from their 64-bit (and 32-bit) Windows 7/XP systems, with some of the users having to remote desktop to it in order to use it. Needless to say, it was very slow.

    The desired target environment was a client-server setup, with each user using their own copy of a distributed Access 2010 front-end linked to the SQL Azure data.

    I first migrated all the Access 2010 data files to a single, on-premises SQL Server 2008 R2 database using the SQL Server Migration Assistant for Access. After resolving a lot of table structure issues (missing primary keys, data type conversion issues, etc.) I pushed the schema to a SQL Azure database using the RedGate SQL Compare and SQL Data Compare tools. The Access 2010 front-end was modified to use VBA which, upon startup, refreshed the table links in the front-end to point to the Azure tables via a "DSN-less" ODBC connection (meaning, no ODBC file or DSN was required on the user's system or on a central file share). I also created login, password-reset and password update forms in the Access front-end which used DAO/ADO VBA routines to authenticate users and to ensure the ODBC linked tables were updated with the user's credentials.

    To ensure the users were working with the latest Access front-end, I created a VBscript "updater" that the user clicked on their desktop. It first checks for a newer front-end version on a file share (based on the modified date, which is actually a bit problematic) and downloads it to their Windows user profile folder. Initially, I had the script download either a 32 or 64-bit compiled Access 2010 file (a .accde file) to the user's system, since I wanted to use the fastest-performing front-end possible. However the compiled version would not work on all of the client systems. Since each user has either a different Windows version (7, XP, even 32 or 64 bit), Access version (32 or 64-bit again, and yes, even the Service Pack applied!), the compiled front-end would fail upon startup on some systems. So I reluctantly decided to lock down the uncompiled (source) version of the file as best I can and use that for everyone.

    All of this worked (well, sort of), but there were many bumps along the way. For starters, I found that each PC needed to have the absolute latest SQL Server 2008 Native Client driver installed, either 32-bit or 64-bit, depending on their OS. This was important since (more?) SQL Azure support was added to the most recent versions of the driver.

    Secondly, several forms in the front-end had a tab control on them filled with subforms. This means that the main form and all the subforms were loaded when the user hit the main form. This was painfully slow. To lessen the loading time, I made the subforms initially unbound and only bound them to the record source when the parent tab was clicked.

    Another issue was that of complex queries (sometimes even nested queries) which, when joining multiple tables (Azure tables) across the wire, caused huge processing delays. I took the most complex ones and converted them to T-SQL functions and/or stored procedures to offload the query processing to the server. This helped minimize the delays for specific forms, or in some cases allowed the form results to at least appear.

    There are also ODBC call errors (3146 comes to mind) that pop up seemingly randomly whether you are actively using the application or not, and which require that the user completely restart the Access front-end before the linked tables can reconnect. I'm trying to develop some type of global error handler to deal with this one but not sure it will work, since ODBC is sort of outside the bounds of Access error-handling as far as I know.

    The bottom line is unless you can convert all your existing Access queries to pass-through SQL for processing on the server, Access-to-Azure is slow...at least that has been my experience. For instance, using VBA to loop through all the linked tables and refresh them using the Access "RefreshLinks" method takes at least two or three minutes when refreshing them to Azure, while it only takes a couple of seconds when refreshing to my locally networked 2008 R2 instance. And I'm not sure converting all the queries to VBA-generated SQL would make the inherent Azure latency issues any more tolerable.

    Good luck if you also find yourself working toward this goal.

  2. #2
    AccessMSSQL is offline Expert
    Windows XP Access 2010 64bit
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75
    Thanks for the information on this. We are about to start a project that is very similar to yours but first time doing cloud based solution. Good to know the issues. I'm sure you will hear from me again once we get started! Thanks for the post!

  3. #3
    Aurelius7 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    4
    Quote Originally Posted by AccessMSSQL View Post
    Thanks for the information on this. We are about to start a project that is very similar to yours but first time doing cloud based solution. Good to know the issues. I'm sure you will hear from me again once we get started! Thanks for the post!
    No problem. By the way, a lot of the ODBC errors we were getting have dramatically decreased over the last couple of weeks. Most of the ODBC Call Fail errors now only occur if you keep a table (or a query based on table(s)) open and idle for a period of time. The forms can stay open (idle) for as long as you want and it immediately resumes loading data, etc., when you start navigating the various forms again. For some reason, Azure doesn't seem to like the behavior of opened tables/queries.

    It's difficult to pinpoint exactly what was responsible for the decrease in ODBC errors and I wonder if Microsoft may have changed something on the SQL Azure end of things, such as duration of timeout or some such thing.

    The only events I can attribute to this are:

    • I made a property in one of my VBA modules to store a reference to the current database (CurrentDb) and use that in any Recordset or other db-related method calls.
    • Our network admin had done some research on the company firewall to ensure port 1433 was allowing traffic and he may have changed the way the ports open/close, and doesn't recall what he tweaked.
    • More related to recovery from ODBC errors, but I changed the way I was storing the logged in user credentials and database info. I originally had set up VB Let and Get properties in the login form module to store/retrieve that information, but when the application encountered an error it would of course lose this info. So I created invisible form textboxes to hold all this information and it seems to recover from errors much better now.

    On top of this, I have replaced a lot of the more complex queries (multiple joins, etc.) with SQL table-valued functions and stored procedures. I can't tell you how much time this saves when returning data to a form. The only problem with this is the recordset is not updateable when it's a server-side function or stored procedure, so you cannot add new records to a form (or update/delete them). In this case, you would have to create additional "pop-up" forms for adding/updating records and buttons to perform delete operations.

    It's a lot of work but I think for the most part, you can bend Access to your will. Good luck with your project!

  4. #4
    AccessMSSQL is offline Expert
    Windows XP Access 2010 64bit
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75

    SQL Azure co-admin can't see master db

    Thanks for the info again. I just started setting up the development database in SQL Azure. My subscription is set up as a co-administrator through Windows Azure. My database is created and now trying to add sql server logins but as a co-administrator I do not see the master db. Only the service administrator account seems to be able to see this. Have any advice on this? I can't seem to find any threads related to co administrator not seeing master DB. Click image for larger version. 

Name:	SQLAzure.jpg 
Views:	19 
Size:	34.2 KB 
ID:	7450

  5. #5
    AccessMSSQL is offline Expert
    Windows XP Access 2010 64bit
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75
    My issue was resolved. Unlike SSMS - the master DB doesn't appear in the manage server window. All other dbs on the server will be listed except master. I had to go into the Manage Database.

  6. #6
    AccessMSSQL is offline Expert
    Windows XP Access 2010 64bit
    Join Date
    Apr 2012
    Location
    Los Angeles
    Posts
    75
    Hi Aurelius7:
    So I'm moving along with the migration and was able to move all of my tables to SQL Azure and set up logins and such. I have a routine in Access VBA that runs against SQL Server databases that basically creates the ODBC DNS dynamically and then re-links the tables. I use this routine all the time in my other applications. I'm attempting to modify it for SQL Azure and having some issues.

    My code is failing whenever I try to register the database using SQL Native Client 10.0 rather than SQL Server. See code below. Any suggestions? Also, whenever I try to add the UID and PWD arguments in my connection it also fails to create the ODBC connection

    this line of code fails to create the ODBC DSN

    Code:
     DBEngine.RegisterDatabase l_sqldbname, "{SQL Server Native Client 10.0}", True, "Description=" & l_sqldbname & Chr(13) & "Server=" & l_servername & Chr(13) & "Database=" & l_sqldbname & Chr(13) & "Trusted_Connection=No"
    this line of code works
    Code:
    DBEngine.RegisterDatabase l_sqldbname, "SQL Server", True, "Description=" & l_sqldbname & Chr(13) & "Server=" & l_servername & Chr(13) & "Database=" & l_sqldbname & Chr(13) & "Trusted_Connection=No"
    The only difference is I'm changing the driver name from SQL Server to SQL Native Client 10.0. I referenced this link also to find more information:
    http://www.connectionstrings.com/sql-azure

    Any suggestions would be greatly appreciated!

  7. #7
    Aurelius7 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    4
    Hi AccessMSSQL:

    For some reason I'm not receiving email notifications on this thread even though I'm obviously subscribed (thread creator).

    I haven't used any RegisterDatabase methods in my applications so I don't know why the two pieces of code you mentioned would not work. I just build a new SQL Azure connection string as below, create a tabledef object that points to the "DSN-less" ODBC linked table that I want to refresh, then use the RefreshLinks method to update the table with the new connection string.

    Example of connection string:
    Driver={SQL Server Native Client 10.0};Server=tcp:azure_server_name.database.windows.net;Database=database;UID=user_name@azure_server_name;PWD=password;Encrypt=Yes

    I assume by now you may have figured out your method or found a workaround.

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

Similar Threads

  1. Access 2010 and SQL Azure
    By drexasaurus in forum SQL Server
    Replies: 2
    Last Post: 09-20-2012, 07:58 AM
  2. Replies: 1
    Last Post: 01-05-2012, 12:34 PM
  3. Migration to web
    By thbaig1 in forum Access
    Replies: 1
    Last Post: 11-22-2010, 08:04 AM
  4. Migration from Access 2003 database to Access 2007 database.
    By cyclus2 in forum Import/Export Data
    Replies: 1
    Last Post: 09-10-2010, 10:43 AM
  5. Replies: 0
    Last Post: 04-16-2009, 12:44 AM

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
  •  
Tech Forums: Microsoft Office Forums