SQL Azure for Access Developers

Mike Wolfe -

For a long time now, I've been thinking I should at least be familiar with what's involved with migrating from SQL Server on-premises to SQL Azure, Microsoft's cloud version of SQL Server.

Well, I finally decided to bite the bullet and give this thing a go.  I've also decided to take you all along for the ride.

In this series, I will be hyper-focused on a specific use case: mine.

Setting the Scene

Let me explain the parameters of my use case.  You probably won't be in exactly the same situation as me, but if you are a regular reader of this blog then chances are there will be a lot of similarities.  By listing as many relevant details as possible, my hope is that you will see where our situations differ and take that into account as you read the rest of the series.

(Note, the name of my company is Grandjean & Braverman.  I'll refer to it as G&B below.)

  • I have a Microsoft 365 Business Standard license ($12.50/month)
  • I have six of these licenses for me and my employees (3 full-time, 2 part-time)
  • I have multi-factor authentication (MFA) enabled on my work Microsoft account
  • I have both a Microsoft work account (mike [at] grandjean.net) and a Microsoft personal account (mike [at] nolongerset.com)
  • G&B does not have any sort of file server or Active Directory server
  • We use Sharepoint sites and shared OneDrive for Business folders to enable file sharing (neither of which is a good fit for hosting backend .mdb/.accdb files)
  • Two of our developers (one part-time and one full-time) are fully remote
  • Two of our full-time developers and I work mostly on-premises
  • We do not currently have a static IP for our office internet, but we could add that feature for a nominal monthly charge

Intended Usage

I have several small utility Access databases that are only used internally by me and my employees.

Most of these databases were created when we were a two-person company.  And even then, we operated almost more like two one-person companies: it was very rare that we both worked on the same project at the same time.  As the company has grown, that approach is not scaling very well.

These small utility databases use backend .mdb files to store data.  The backend .mdb files get passed back and forth depending on who needs them at the moment.  This is inefficient and often leads to two people working on a database at the same time, requiring a manual merge of the data.

These backend databases are very small.  The total size is maybe 200 MB.

The applications themselves are used a few times a week to a few times a year, depending on the application.  Here is a sampling of the applications:

  • G&B Billing: used only to manage our annual maintenance contracts since we moved to QuickBooks for billing in 2021
  • Schema Updater: an application for managing schema changes among multiple clients, multiple environments (dev/test/prod), and multiple databases
  • SQL Upsizer: an application to automate the process of migrating data from .mdb/.accdb to SQL Server
  • SQL Server Admin: an application that generates T-SQL scripts for managing merge replication of a database to several dozen subscribers with high device turnover
  • Tax Billing: an application used to generate tax bills for four different counties in Pennsylvania

Topics

The following list of topics may change as the series evolves.  Here's my initial plan:

  • Part 1: Pricing
  • Part 2: Creating a Database
  • Part 3: Establishing a Connection
  • Part 4: Creating Linked Tables in Access
  • Part 5: Migrating Existing Data from .mdb/.accdb's

Disclaimer

If it's not already abundantly clear, I am no expert on SQL Azure.  

I will be learning in public.  If you think you know one or more of these topics better than I do...you're probably right.  

But, if you are new to this whole SQL Azure thing, then you can rest assured that I can empathize with your situation.  Why is that? Because I hardly know what I'm doing myself. 😂

Let's have some fun!

Image by Nika Akin from Pixabay

UPDATE [2022-01-21]: Removed "Part 2: Signing Up" from the series because (A) I'm not sure I made the right choices when signing up (e.g., I believe I chose the "Pay as you go" option from the outset and skipped the "Free for 12 Months" option) and (B) I thought I had screenshots and/or a screen recording from when I signed up, and I had neither.  In any case, I don't think this is a big loss from the series.