Access 365 Desktop App Pointing to Azure DB

The points for this question will probably be given to either Jack Leach,  Joe Anderson, or  Jim Dettman for the great job they did on this  Question .
All opinions are invited.

Below questions presume the person knows SQL Server and Access

Question #1
If in Access 365/2013 if you want to build a Desktop App hooked to, SQL Server (Azure) back end on the MS's cloud
how easy is it to
      Build Tables
      Build Views
      Build SP
      Build Functions

Question #2
Is there someplace to get an introduction to the above - not a book, but a place on the internet that give a good, but short explanation?

Thanks in advance
Who is Participating?
Jack LeachConnect With a Mentor ProprietorCommented:
You called? :)

For the most part, you can use Azure as easily as  BE as you can with an on-premise SQL Server installation.  The only real consideration is the fact that you're working, of course, over a remote connection to your data, and as such your application's design needs to accommodate for it, specifically in retry logic and in putting emphasis on server-side data processing instead of client-side.

Before I go much further, I'll point out that the link Russell gives is specifically for use with a Desktop application that's connected to a pre-configured Azure database that an Access Web App is using... this is different than what you're asking.  Access Web Apps use Azure as a BE, and we can connect to them for CRUD (Create, Read, Update, Delete) operations, but that's about it - views, procedures, tables, all the objects are restricted and cannot be modified (as they must conform to their requirements to host the Web App).

With that out of the way, here's how to get started with Azure as a cloud-version of SQL Server rather than part of a Web App that you happen to be able to connect to:

- Don't try this on Windows XP
- Sign up for Azure
- Create the database, get your connection string from the Azure Mgmt portal
- Install SSMS on your computer (2012 version or newer)
- Connect to the Azure DB and you can start creating objects almost identically to how you would in on-premise (eg, using SSMS)
- Get the Native Client 11.0 Driver for your Access application
- Setup a file based DSN* in Access
- Connect via DSN file
- Convert connection via DSN-Less Connections (google it, by Doug Steele)
- Make good use of passthrough queries and capitalize on server-side processing
- Avoid heterogeneous SQL from Access (eg, don't write queries that require all the Azure data being brought to Access via ODBC, this is slower than death, way faster to import a temp cache to a local table and process it Access like that, or send the required data to Azure and let your sprocs process them).

In a nutshell, that's it - not much different than a normal SQL Server BE with an Access FE.

*DSN: the only tricky part here is the server name,1433

All that said, Azure is indeed a subset of SQL Server and has some limitations.  You'll find that the greater majority of these limitations is on the Administration end of things, and not so much on the data-processing end of things.  We don't have access to physical storage administration, various import/export utilities (some, but not all), stuff like that.  Data replication, etc.  Although, they are making some significant changes early next year (currently in preview) that address many of these administrative type shortcomings.

For the most part, for our purposes as database application developers in need of a cloud based BE, Azure is a great option.  I've had much success with it.

You may find a lot of helpful information from MVP Alumni Patrick Wood at - he's written a fair amount on the subject.  I know of few enough other places to find info... after the initial setup per above, you'll basically be using SQL Server practices rather than Azure specific practices, which of course are widespread.

hope that helps...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.