Microsoft Access and Cloud Computing with SQL Azure Databases

Microsoft AccessSQL AzureWe at FMS are very excited about cloud computing and started developing solutions using Microsoft Azure including SQL Azure well before it was released to the general public. I feel cloud computing represents the next big platform change in the software industry and the most significant transformation since the introduction of the Internet in the mid-1990's. It will transform the internal hardware, application hosting, and data storage business the same way electric companies eliminated most organization's need to generate their own electricity.

Windows AzureWhile there's been lots of discussions of Azure with .NET and SQL Server, we also see lots of opportunities with Azure and the Microsoft Access/Excel/Office community. In fact, we're busily working on a way to integrate Access data and files with the cloud. Meanwhile, we'd like to share some tips and techniques for linking Access databases directly to tables in SQL Azure. This opens up huge new opportunities to create and deploy Access databases using a more robust, cheaper, and highly scalable platform that is enterprise quality.

 I look forward to your feedback on two new papers:

34 thoughts on “Microsoft Access and Cloud Computing with SQL Azure Databases

  1. If the front-end remains in an Access MDB/ACCDB file, the queries should all still work.

    The other issues you’ve raised about intergration with other desktop applications is a general question of building a Windows or web application, which is independent of Access.

    The advantage of changing a web app in one place is definitely addresses some deployment issues. We get around that in Access with our Total Access Startup program (http://www.fmsinc.com/MicrosoftAccess/VersionLauncher.asp) that also lets us Access centrally, so new versions of our application are automatically deployed and we can control which version of Access they launch.

    The issue of SQL Azure is just a question of where you want your SQL Server database. The application needs to be updated to use a SQL Server database. You can then decide where that goes. For security reasons, a SQL Azure database needs to know the IP addresses that will call it, so it may not work if people are using the database on-the-road with random IP connections. No problem with fixed locations. This would also be an issue if you host your SQL Server database elsewhere. You could allow it to be accessible from any IP address, but that’s a security hole. This is a non-issue if you create a web application because the web .NET application would be connected to the SQL Server database from a fixed location that wouldn’t change and is independent of the user’s location.

    Let me know if you’d like to talk to our Professional Solutions Group (http://www.fmsinc.com/Consulting). They can help you make the transition with upsizing your existing Access application to SQL Server, to rewriting it on .NET for Windows and/or the web.

  2. The application has grown to over 50 users and the data is being served up remotely to 6 locations. In addition, we have concerns with data security/privacy and reliability of this mission-critical app. So, especially after reading your migration paper, I believe it is time to convert the back end to SQL. There are many action queries, crosstabs, and parameter queries that will need to be rewritten. So this is not simply just re-linking the data.
    The next issue is the complexity of the functions in the front-end. It implements some pretty sophisticated functions such as automation to Word, electronic signatures, image processing, and complex graphical reports with subreports. The UI has some pretty nice features as well. All of these things must carry forward to the .Net app. My impression with what I have learned so far in ASP.NET is that a web app might not be able to reproduce this functionality. In the current VS 2010 environment, does it matter anymore? Can you develop a web app that is functionally equivalent (and as easy to develop) to a desktop app?
    I like the idea of a web app since you can remotely make changes to the app transparently to the user population.
    But with an eye toward eventual Azure implementation perhaps, does it make any difference if it is an ASP.net app or a Windows desktop app?
    I hope this helps identify for you what my dilemma is. I want to be sure I choose the right tool for this retooling effort.

  3. Hi Jen,

    So much of a decision is based on what your objectives are. If you’re creating a web site, then .NET certainly makes sense. If you’re upsizing an Access application to SQL Server, you simply need to make sure you’re doing it for the right reasons and make the change. If you use linked tables rather than an ADP, the migration is pretty straightforward if the table and field names don’t violate SQL Server constraints.

    The other questions on approaches to creating a .NET application depend on the specific application. Are you making a Windows or web app? We like MVC.

    Luke

  4. My comment relates to upsizing Access data to SQL Azure. In my experience, there is no magic wand for upsizing an Access application to SQL whether it is SQL Server on a local server or SQL Azure on the cloud. For advanced Access front end applications, there are many queries and static sql statements in code that will need to be rewritten to be compatible with SQL Server. That being the case, at that point a developer should be looking at the different platforms available for rewriting the front end. Tweaking the Access application is one option. But how do you determine if rewriting it in .NET is the better way to go? Should you rewrite it as a web application using ASP.NET or MVC? or should you rewrite it as a .NET Windows Forms app?
    While I’m a pretty advanced Access application developer, I’m a newbie in the .NET world. To think of adapting a lot of the 3rd party .OCX controls, interfaces with email, Adobe document management, etc. that I have in many of my Access apss, I just shudder at what is ahead for me in the .Net world.
    Perhaps you can direct me to some white papers FMS or other sites may have to guide me in strategizing future app development plans.

  5. Hi Nick,

    Are you saying SQL Azure is behaving differently from a regular SQL Server deployment of a database?

    There would be a difference if you’re moving lots of data between a web site vs. internal database on your network. SQL Azure should be compared to hosted databases that need to be used from multiple sites not on the same network.

  6. Hi Chuck,

    This works the same way your Access application would work linked to a SQL Server database located anywhere else. Yes, your front-end Access Jet database (ACCDB/MDB) links to the SQL Server tables, and your VBA code, queries, forms, reports, etc. work assuming they don’t do anything that’s a problem with SQL Server. If you use an ADP, you connect directly to it.

    For security reasons, your SQL Azure database needs to be setup with the IP address of the sites that are going to link to it.

    Luke

  7. Hi John,

    Unfortunately, that’s a limitation of the File DSN which is beyond the control of Access. You can link the tables directly within Access and store the login information in some sort of encrypted manner and programmatically open it when it launches, but that’s a bit complicated and not entirely secure. I’ve asked some people at Microsoft for a preferred way of doing this but didn’t get a reply.

    Luke

  8. Hi Luke,

    Have you been able to get data sync to work well?
    I got my initial data (300 MB, 100+ tables) pushed up, but data sync drags and eventually throws errors that make no sense
    Worse, it really bogs down in the inhouse backend to the point of making things unusable
    What’s your testing been like?

  9. Hi Luke,

    The article is very informative, thanks! I don’t understand about how the Access front end works.
    Do you have to install the front end on your client’s machine and then just use the tables in Azure?
    My Access app is full of VBA code, will it work with Azure?
    Does the client still have to have Access installed on their machine to run an Access application that uses Azure for the tables?

    Thanks for your help.

    Chuck

  10. Hi Luke,

    With ASP.NET it’s possible to encyrpt the SQL Azure password. With Access and a file DSN, the password is sitting out on a file share in a text file, unprotected. Have you thought about how to protect the DSN file from being hacked?

    John

  11. Luke,

    Can you shed some light on using VBA to update the connection strings for linked tables?
    I am trying

    Function Refresh_Table_Link()
    Dim TD As TableDef
    Dim intSubStringLoc As Integer
    For Each TD In CurrentDb.TableDefs
    If Len(TD.Connect) > 0 Then
    intSubStringLoc = InStr(TD.Connect, "DATABASE=My_db")
    If intSubStringLoc > 0 Then
    TD.Connect = "Description=TI_Azure;DRIVER=SQL Server Native Client 10.0;SERVER=xxxxxxxxx.database.windows.net;UID=myid@xxxxxxxxxxx;PWD=password;DATABASE=My_db;"
    TD.RefreshLink
    End If
    End If
    Next
    End Function

    But I get no joy

  12. That’s just the way SQL Server stores tables and not specific to Azure. The dbo represents the owner name. You can have multiple tables with different owners and the same table names.

    As you found, you can rename the linked table to anything you’d like in Access, so the Access side can still reference the same table name. In Access, with a little code, you could loop through the list of table names and if it starts with dbo_, rename the table without that prefix.

  13. I noticed that when I migrated my data tables to SQL Azure using SQL Serve Migration Assistant for Access it added the dbo schema and in Azure the tables are now named dbo.tblSomeName, and when you link them to Access they are called dbo_tblSomeName. In Access this wreck havoc with my queries and VBA code. To combat this In Access I have renamed the linked tables by removing the dbo_ so they are back tblSomeName but I assume I will have to do this every time I re-link or link a new copy of the database. Do have any short cuts for this?

  14. Thanks Luke, I suppose the problem I’m having is that I don’t understand why the DSN password isn’t a form of security. Are the Login ID and Password sent accross the internet unencrypted?

  15. Hi Darren,

    Maybe you’re comfortable letting anyone connect to an Access database across your network. That may be fine, but when you place a SQL Server database on the web, it’s considered best practice to restrict the IP addresses that can connect to it directly. That’s a level of security that’s built into SQL Server and something you should only weaken as needed. Yes, you can specify a range of IP addresses (not sure how wide it can be) but why would you do that if you didn’t need to? Is it simply for your convenience? Do you believe there’s no security threat? Maybe your data isn’t confidential or valuable. In that case, you can simply use one of many web hosting sites that support SQL Server databases. They aren’t the same level of security and up time as SQL Azure, but should be fine for what sounds like your needs.

  16. Thanks Luke. So I’m right to say that it’s possible?

    I’m only a humble Access Developer, so while I’d like to branch out into web apps, it’s just not possible in the near future.
    If it’s not too much of a hassle, could you explain why a strong password for the DSN isn’t adequate security? Is it because the Login ID and Password are sent accross the internet unencrypted?

    If specifying an IP Address range is possible, I think you can actually set a range to limit access from specific regions. For example, maybe only IP Addresses from one country, or even only from one state or city. I don’t know for sure, but this would at least make it more secure than open to anyone.

    Thanks for your time, it’s much appreciated.

  17. I can see how requiring specific IP addresses in advance could be considered a hassle, but I would be uncomfortable allowing that in any Access applications we build.

    One can’t claim to have security while allowing a SQL Server database on the web let any IP address get to it. A more secure approach is to isolate SQL Server completely and have only web service calls (from a .NET application) control how people get to and from the database. That eliminates the use of a linked Access database but offers a web application that is useable from anywhere. Direct connections to the database would be reserved for the developers who would have a set of known IP addresses.

    For people that want to connect to the database outside their office, one solution would be to have them use VPN and remotely run a machine in their office. That would have the known IP address. There are definitely tradeoffs but skimping on security could be very expensive after an incident occurs.

  18. Hi Luke,
    thanks very much for the info on SQL Azure. However, my heart sank when I read about having to specify the IP Addresses that will access it. This counts out most, if not all, my small business clients. Clients who would’ve liked to use the application on their home computers/laptops. Also usage at Wi-Fi hot spots is now out of the question.

    UNLESS the following is correct.

    Is it possible to submit a range of IP Addresses? Maybe even a range that covers the whole internet? The following article seems to say that you can. But I’d be much more assured if it came from you.

    Here’s the article:

    http://www.silverlighthack.com/post/2009/11/11/Connecting-to-SQL-Azure-with-SQL-Server-Management-Studio-2008-R2.aspx

    A little past halfway down it states:

    "…you could add a rule that opens up the entire IP stack of the Internet by entering 0.0.0.0 -> 255.255.255.255"

    What do you think?

    Thanks Before (As they say in Indonesia)

    Darren M

  19. I’ve updated my paper, http://www.fmsinc.com/microsoftaccess/cloud/link-to-azure-sql-database.html, to include a section about what you need to install on your machine to link to SQL Azure.

    I originally stated that you need to have SQL Server 2008 R2 installed on your machine. That would work but is not accurate. After all, if you’re using SQL Azure, you don’t need to have SQL Server running as a server. However, you will need to manage the data you place on SQL Azure, so you’d want to have SQL Server Management Studio (SSMS) for SQL Server 2008 R2 installed. That comes with SQL Azure and is availablef for free from Microsoft: http://www.microsoft.com/express/database

    Hope this clarifies what’s needed.

  20. Hi Edwin,

    Glad you found my papers and the discussion here useful. Let me clarify some of the issues you’ve raised.

    1. It’s my undertstanding that SQL Azure replicates your database so there are three instances on separate machines. It’s still one database, and you connect to it via SQL Server Management Studio like you normally would.

    2. When an Access database (MDB or ACCDB) is linked to SQL Server, you can use DAO to work with your linked table like any other Access table. If you want to go to the SQL Server objects directly and not use your local linked tables, you’d use ADO. SQL Azure is no different than SQL Server for this.

    3. I guess you’ll need to test the responsiveness of SQL Azure vs. your local architecture. My presumption is that performance on your network is going to be faster than over the Internet. One advantage of having it on SQL Azure is that you can get to it from multiple locations. That may or may not matter in your case.

    4. I’m not aware of any benchmarks. A proper comparison would be to see how it works against another ISP hosting a SQL Server database. Internal vs. external locaion hosting would probably be a test of the internet speed.

    FYI, there are some things you don’t get in SQL Azure that you would from regular SQL Server. Things like transaction logs, SQL plans, etc. aren’t available.

    Hope this helps.

  21. Luke, as always your paper is great!
    And like you said, it bringing a lot of buzz arround . .

    I have read this blog and i need to ask other questions, maybe ask some other again but with more details to better understand in my specific scenario.

    We deploy our application to many companies, each of them is required to have their own server, most of the time is just a small server down the hall or in next room with sql express , actually it suports all our needs, for bigger companies where the db is over 1gb we recoment the sql standard to get better performance it support more memory and more processors…

    1) With the New SQL Azure, i read the db will be in the cloud, there will be 3 databases spread over some servers somewhere, and we will be able to use SQL Server Management Studio to directly connect to the db, is this correct?
    2) in your whitepaper about creating the links without sql R2 installed, you said developers can connect with ADO, can we also use DAO?
    3) Azure will store our sql db in the cloud and our application will be installed in the desktop what about speed of the application, will it be like a locally stored db in server down the hall?
    4) Does any bechmark has been done so we can see/compare the results

    This is very interesting because many clients when they grow they see the need of hire some it personel to manage their servers and their dbs, with this new technology they will not be required to have a server in their premises, just somewhere in the cloud, and is secure WAO…

    Edwin Blancovitch, President
    Urb. El valle, Rosales # 40, Lajas PR 00667
    http://www.advdev.net || http://www.easypayroll.net || http://www.creceonline.net || http://www.aprobadomusic.com
    Phone/Fax : 787-899-7063 || Mobile: 787-528-2980

  22. Hi Michael,

    Thanks for your interest! I’ve followed up these two articles with another article discussing how to deploy Access databases that are linked to SQL Azure which should give you some additional details: http://www.fmsinc.com/MicrosoftAccess/cloud/deploying-linked-azure-sql-databases.html

    1. No. SQL Azure provides the tools you need. Downloads of the SQL Server programs are provided at the bottom of the Azure page: http://www.microsoft.com/windowsazure/sqlazure/default.aspx

    2. You’ll need to have at least the ODBC drivers installed which is covered in my deployment paper mentioned above.

    3. You can use RDC on your machines as normal but you can’t remote to the SQL Azure host.

    4. SQL Azure is just like SQL Server but more scalable and managed. So you should be able to run as many jobs as you can with regular SQL Server.

    5. With SQL Azure you’re paying per database, not per server, so you can have as many as you’d like starting at $10 per month for the first gig.

    6. Backups and restores need to be manually handled. SQL Azure automatically keeps three copies of your database at all times.

    7. As discussed in my paper, you can link to it through the designated IP addresses you provide. We use a .NET web site and web pages to use SSL. I’ll need to investigate how the secure certificates would be implemented for the SQL Azure databases for direct connections.

    8. Yes, it’s very scalable. For security reasons, each user’s IP address needs to be registered though.

    9. Yes.

    10. Pricing includes both storage and bandwidth pricing and is on the Microsoft Azure site: http://www.microsoft.com/windowsazure/offers/default.aspx.
    Here are some numbers: $9.99 per database up to 1GB per month, $49.95 per database up to 5GB per month

    11. See the link above for transfer pricing. It’s pretty generous.

    12. Total cost depends what you’re doing. Think of it as electricity. The more you use, the more you pay. It’s at least $10 a month for the first GB. You go from there.

    Hopefully my papers already discuss how Azure differs from a database hosted on a single machine at an ISP. Basically, it’s enterprise quality up-time support and scalabilty. It’s replicated with three instances but all at the same location (some organizations may need the data in multiple sites).

  23. Hi Luke,
    I’ve been following your blog on cloud computing (Azure) and I have a few questions about how it all works.

    1. Do I have to own a copy of SQL Server?
    2. In your blog you said that one of the steps was to install SQL Server 2008 R2. This kind of goes with question number 1 above.
    3. Can I access the machine via RDC?
    4. Can I run as many jobs on the instance of SQL Server that I want?
    5. What about the number of databases?
    6. What about database backups/restores?
    7. Can I connect to the SQL Server database just like I do now using SSL over the Internet?
    8. Can I add as many logins to the databases that I want?
    9. How do I access the instance of SQL Server, do I use SQL Server Management Studio.
    10. What is the cost breakdown of databases over 1 G?
    11. Is there a charge for quantity of data transferred?
    12. What are all the costs associated with setting up a SQL Server database in the cloud?

    I currently have multiple remote servers running SQL Server and my applications connect to them via the Internet. I connect using RDC to manage the database with SQL Server Management Studio.

    I really want to understand the differences of cloud computing Vs. hosted remote servers.

    Thanks,
    Michael Stalford
    Project Manager
    Business Software International Inc.

  24. Hello!

    We like your papers a lot here in Brazil.

    We have a growing user community and I would like to translate and post these papers, preserving your name and respecting your rights. Can we have your authorisation?

  25. Hi Dale,

    There are several differences between Azure and a traditional ISP hosted database. Based on my understanding, Azure automatically has the database mirrored so that there are three copies at all times. It’s hosted at a highly secure location, robust platform, high bandwidth capacity, etc. It’s also highly scalable so that multiple CPUs (on the Windows Azure side) can be added as needed. On the database side, the capacity can be increased economically up to 50 GB.

    As for the authorized IP address, for security reasons, that’s something one should have if the front end is from fixed locations. If you want to host a database that people can access from any location directly that may be a problem but that’s not something we usually support. If you want to create a web site, Windows Azure can do that with a .NET application connected to SQL Azure. The .NET application can be available to anyone. It has the data access but the user wouldn’t interact with the database directly, so the fixed IP address is a non-issue.

  26. Currently I have a MySQL database on the web and I am using an ODBC connection to link to the tables as I would from a normal network based source. How is Azure different from that? The authorized IP address requirement will probably kill this product for small business users.

  27. Hi Bruce,

    Not sure what you mean by 3rd party add-ons.

    If you host data on SQL Azure, it’s a SQL Database in the cloud and whoever you give permission to it could use it but that’s not very standard. If it’s Windows Azure with a .NET solution, then you have a web application that others could see and use. Depending on what you expose, maybe other programs could extend it, but that’s no different from Microsoft hosting it or someone else.

    Now if you create a .NET application on Azure, that application of course could use any web applications it can interact with using things like web service calls.

    Does this help?

  28. Hi Luke – I agree this article was well written: especially for folks like me who are tech savvy but not informed about the Cloud at all.

    Question: I expect that having Microsoft host our WiseGuys CRM application, in a SQL environment, will facilitate 3rd party software vendors to provide add-on services. For instance, MailChimp or other email broadcast apps. Am I correct that the Microsoft hosted platform will allow 3rd party access?

  29. I’ve been waiting to join SQL Azure for a few month in order to get more information on the setup and the pros and cons.
    Thanks Luke for this article, I’ve decided to step in now !!
    Please keep us up to date !

    Best regards
    Ruud Jeursen

  30. Glad you find the article helpful. The issue with the Authorized IPs is both good and bad. From a security perspective, the fixed IP address is obviously preferrable. As for small businesses, if they are using high bandwidth connections, it usually includes at least one fixed IP address. I know our local Verizon and Cox Cable business accounts include that.

  31. Thanks Luke for your great article on Access with SQL Azure, the step by step guide will be a great help for me if I decide to use their services. One of the most significant drawbacks you pointed out may be a deal killer: Authorized IPs. Most of my applications designed for the web, (Access frontend with SQL Server backend), are for small businesses that use Internet services with no fixed IP addresses or VPN connections. Azure would be unusable in that kind of situation.

    Kind Regards,
    Juan Soto