Sunday, June 4, 2017

Sometimes You Just Have to Scratch Your Head....

It was a cloudy Saturday afternoon; I was out walking in the woods near my house when the call came. I let it go to voicemail; nothing good comes of such calls I've found. If it's a friend or family member, I hate trying to talk and walk at the same time, especially on uneven surfaces. If it's a client, of course, there's not much I can do until I get home again anyway. And if a client calls on a weekend, that's a particularly unpleasant call.

This one was a client. Their Access database application had stopped working. This particular business is particularly busy on weekends, due to the nature of the services they provide. It is actually an Access/SQL Azure "hybrid" application. That means the interface is an Access accdb. The database is in SQL Azure, out there in the cloud. But not just any SQL Azure database. We'd set it up using the Access Web App environment as the quickest, least costly way to migrate their data "to the cloud". That allows them to expand operations into two different states, hundreds of miles apart, without having to incur the cost of a re-write to the interface. Worked okay for months, although performance sometimes left a little to be desired.

Yesterday afternoon, it started failing with an ODBC connection failure error. The accdb couldn't connect to the data. Bad juju. No data, no schedule of activities for the next two days.

It turns out that sometime Friday night, the 3rd of June, or Saturday morning, the 4th, Microsoft had MOVED the database from one SQL Azure instance to another. No advance notice to clients, no transition. Just slam-bam, over and done with. New server, new database, new credentials needed to connect.

What that meant, of course, is that existing external connections using the prior Server, Database, UID and PWD were no longer valid.

For me, the solution was fairly straightforward. For clients, it falls somewhere between "Damn, this is inconvenient." and "OMG, what am I supposed to do now?" And fortunately for them, I DID return the voicemail after I got home, showered, and sat down at my desk.

It occurred to me after I had posted this article that maybe I ought to mention the solution to this particular problem.

Open the accdw file which controls the Access Web App.

Click the File tab on the ribbon to get to the back stage.Here you can see that I've already re-enabled connections for this AWA. You'll see a different message if yours does not have connections enabled.

Connections must be enabled to use the SQL Azure database with accdb's, xlsx's, PowerBI and other external data consumers

 Click on "Connections". This opens the external connections dialog. You'll need to make sure two settings are correct here. Select "From Any Location", to allow external connections to your SQL Azure db from, well, from any location. Then select "Enable Read-Write Connection".

Allow connections from any location and enable Read-Write connections

Now, click on "View Read-Write Connection Information" to get the name of the new server, the name of the new database, the new external writer UserName (UID) and new password.
Use the Connection Information here to relink your client application to the SQL Azure tables

Use that connection information to relink your accdb, xlsx, PowerBI or other data consumer application to use the new database. The data is still the same, only the location was changed.


Another follow up. I've now heard from AWA users in Sweden, Italy and the UK, as well as US based users in several states (meaning different server locations). That qualifies as a wide-spread problem, IMO.


All of which leaves me scratching my head and wondering just how detached from their customers Microsoft has become. A move like that had to be instigated at a fairly high level. It had to be planned for some time. And it had to be coordinated among a lot of people. Didn't anybody over there in Redmond ask, "What'll happen to our customers if we do this without telling them?"

Guess not.

Update: Tuesday, June 6, 2017
I have heard from the Access team. They are looking into what happened and will reply directly when they have a better picture of the events.
As I thought, the Access team themselves were not involved in the move; it doesn't mitigate the pain, of course, but it does suggest the people who made the change were not aware of the potential problems it would cause.

Update: Wednesday, June 7, 2017
The Access team has responded to my request for clarification.
This was a result of a planned maintenance operation on SPO servers and tenants.
They will work with the owners of that operation to improve communication in the future.