Spinning up an Azure SQL always-free database

I wanted to do a quick prototype with a friend and we wanted a common database so we looked for a developer-grade free cloud product that we could both reach from our developer machines

Azure has a free tier or promotional program that makes it easy to learn about Azure or create prototypes and demos at a low cost. There are two levels of free.  

  • Free for the first 12 months you have your account.
  • Always free

I've had my Azure account for a few years so I only have access to the always free tier.  This page lists all the always free services. Azure has two free-tier options: Cosmos DB and Azure SQL Database.  We decided to go with the Azure SQL database because it has SQL in the name instead of using Cosmos DB and its SQL persona.

Always Free Azure Databases

Microsoft offers an "Always" free tier for two of their databases.  They are free below a certain level of consumption.  

    Cosmos DB

    Here are a few links that describe the Cosmos DB free tier offering and the Cosmos DB MongoDB compatible free tier offering. We'll look at Cosmos at another time. 
    The Cosmos DB has a lot of features.
    • 1,000 request units per second provisioned throughput with 25 GB storage.
    • Cosmos DB free tier does not support Entra integration according to the links above.

    Azure SQL Database

    Free Azure SQL is pretty restrictive.  It auto pauses when idle which helps some. You can also set the CPU level from 0.5 v-cores to 2 v-cores to give better performance or longer wall time.
    • 100,000 vCore seconds of SQL database serverless usage per month with 32 GB of storage
    • 27 vCore hours per month
    • 13.5 wall hours for 2 vCore database
    • Entra authentication.
    100,000 CPU seconds sounds like a lot more than it is. You can extend the wall time with fewer CPU cores or extend the time with lower performance with fewer cores.



    You should see the offer in the Azure console in the Azure SQL Database blade.  You need to apply this offer to create the free database.  The database will be auto-configured to fit within the offer.



    You get one free SQL Database per subscription. That database runs inside one of your Azure SQL server instances. This is not SQL-Server the product. The Azure console lets you drill into a SQL Database through the SQL server instance or directly from the SQL database shortcut. 

    Assumptions

    Primary assumptions
    • You are working outside of a corporate environment
    • You wish to connect from a machine outside of your Azure account
    • You are doing low-volume work.
    Secondary assumptions
    • You wish to use local, executing on a developer machine,  third-party tools to manage to manage the database.

    Provisioning 

    Azure SQL server and SQL database

    I created an Azure SQL instance called jesse-exploration and a single (free) database called DataExploration.

    We can see both the SQL server and the SQL database inside of it. This page is blank the first time you visit here.  You can create the server here with the Create button on the left-hand side.


    Azure SQL servers

    This is the screen you see if you click on the SQL server link from the page above or if you directly navigate to SQL servers from the console home page. You can create a database inside this server with the Create button on the left-hand side.


    Azure SQL Database

    This is the instantiated SQL database. You can reach this via the Azure SQL above or directly from the Azure console home page. You can have one free database per subscription.  Microsoft auto-pauses this database after some period of idle time.


    Azure SQL in the Azure Portal

    This is the page you see when you click on a database from the server's databases screen. The database shows 32 GB of storage, the max for the offer.



    You can reconfigure the database using the settings menu.  This picture shows that this instance of DigitalExploration is paused, probably due to idle time.


    Database Security

    The Azure SQL database runs in the cloud.  We need to define who can use the database and where they can connect from.

    Determining the Roles and Permission required to connect

    Azure SQL offers a few options for identities and permission/roles.  Our team used Microsoft Entra (Azure AD) identities.  They were bound to our external email addresses.  I invited both of us to be in our AD tenant. 

    This view shows two Contributors who can manipulate tables, columns and other attributes.  We didn't have any basic Reader database user identities.


    Network Connectivity.

    We either need to create a tunnel from our local machine(s) or set up firewall rules that allow connections from our machine.  In this case, we create a firewall rule that lets us connect from two different networks/vnets.  The home networks of myself and the person I'm working with. Azure will auto-populate the address when you click the "+" button to add a rule.


    Connecting and Using

    We enabled connectivity and gave contributor role to two Entra IDs, both of which are really Gmail accounts. Azure Data Studio is going away.  Microsoft guides you to setting up a VSCode Azure database tool but you can really use anything that supports Azure SQL and Entra IDs with MFA.


    Microsoft provides the driver connection strings for various drivers. I found it interesting that they offer PHP and Go but not Python.


    Example: Using DBeaver - Using Entra IDs and Browser Credentials

    These are the screens for DBeaver. Other tools look similar.

    You tell it which database type your are using. Select Azure SQL Server in the upper left corner



    We're going to use Active Directory - MFA. We got the connection string.  Really just the hostname from one of the screens in the Azure Console.



    We're going to use Entra as our identity provider. It will open an authentication provider browser window that will look something like this.


    AD (Microsoft Entra) MFA happens via the browser.

    Azure SQL restricts you to a single active database. DBeaver may try and connect to the master db and your work db. You will get a bunch of error popups telling you that you can't query across DBs.  

                "Not able to do cross database query in SQL Azure"

    You can eliminate these warnings in DBeaver by disconnecting from the master database/schema. 


    Example Connecting and Using Azure Databases VSCode extension

    This is the replacement for Azure Data Studio. Load the extension.


    Connecting to the DB

    Select the SQL Connections view on the left hand side.  You can create a connection by connecting on the Add Connection plus sign. This shows a connection already made.  and the hover tooltip on the create button.

    You'll then get prompted for your database connection string and the credentials.  You'll find the server hostname in one of the screens above where you created the database.


    You'll be asked for the database name inside the server.


    I use Microsoft Entra Id with MFA support.  This lets us use the same IDs across our prototyping.


    Using the DB from VSCode Azure Databases Tool

    The VSCode Extension is browsing the joe table in the guest schema inside the DataExploration database in the jesse-exploration server. The SQL query selects * from guest.joe


    Finish

    Hopefully this is enough to get your started using the Azure SQL Database from anywhere.


    Revision History



    Comments

    Post a Comment

    Popular posts from this blog

    Installing the RNDIS driver on Windows 11 to use USB Raspberry Pi as network attached

    Image
    I do a lot of my development and configuration via ssh into my Raspberry Pi Zero over the RNDIS connection. Some models of the Raspberry PIs can be configured with  gadget drivers  that let the Raspberry pi emulate different devices when plugged into computers via USB. My favorite gadget  is the network profile that makes a Raspberry Pi look like an RNDIS-attached network device.  All types of network services travel over an RNDIS device without knowing it is a USB hardware connection. A Raspberry Pi shows up as a Remote NDIS (RNDIS) device when you plug the Pi into a PC or Mac via a USB cable. The gadget  in the Windows Device Manager picture shows this RNDIS Gadget connectivity between a Windows machine and a Raspberry Pi. The Problem Windows 11 and Windows 10 no longer auto-installs the RNDIS driver that makes magic happen. Windows recognizes that the Raspberry Pi is some type of generic USB COM device. Manually running W indows Update  or  Upd...
    Read more

    Understanding your WSL2 RAM and swap - Changing the default 50%-25%

    Image
    The Windows Subsystem for Linux operates as a virtual machine that can dynamically grow the amount of RAM to a maximum set at startup time.  Microsoft sets a default maximum RAM available to 50% of the physical memory and a swap-space that is 1/4 of the maximum WSL RAM.  You can scale those numbers up or down to allocate more or less RAM to the Linux instance. The first drawing shows the default WSL memory and swap space sizing. The images below show a developer machine that is running a dev environment in WSL2 and Docker Desktop.  Docker Desktop has two of its own WSL modules that need to be accounted for.  You can see that the memory would actually be oversubscribed, 3 x 50% if every VM used its maximum memory.  The actual amount of memory used is significantly smaller allowing every piece to fit. Click to Enlarge The second drawing shows the memory allocation on my 64GB laptop. WSL Linux defaul...
    Read more

    Almost PaaS Document Parsing with Tika and AWS Elastic Beanstalk

    Image
    The Apache Tika project provides a  library capable of parsing and extracting data and meta data from over 1000 file types.  Tika is available as a single jar file that can be included inside applications or as a deployable jar file that runs Tika as a standalone service. This blog describes deploying the Tika jar as an auto-scale service in Amazon AWS Elastic Beanstalk.  I selected Elastic Beanstalk because it supports jar based deployments without any real Infrastructure configuration. Elastic Beanstalk auto-scale should take care of scaling up and down for for the number of requests you get. Tika parses documents and extracts their text completely in memory. Tika was deployed for this blog using EC2 t2.micro instances available in the AWS free tier. t2.micro VMs are 1GB which means that you are restricted in document complexity and size. You would size your instances appropriately for your largest documents.   Preconditions An AWS account. AWS ac...
    Read more