Azure SQL Database to store App data

Managing data is a critical component of any business. Relational databases, and specifically Microsoft SQL Server, have been among the most common tools for handling that data for decades.

If we want to manage our data using the cloud, we can just use Azure virtual machines to host our own Microsoft SQL Server instances. Sometimes that’s the right solution, but Azure offers another way that is often much easier and more cost effective. Azure SQL databases are a Platform-as-a-Service (PaaS) offering, meaning much less infrastructure and maintenance to manage yourself.

To understand better, let’s consider a scenario: you’re a software development lead at a transportation logistics company, Contoso Transport.

The transportation industry requires tight coordination among everyone involved: schedulers, dispatchers, drivers, and even customers.

Your current process involves piles of paper forms and hours on the phone to coordinate shipments. You find that paperwork is often missing signatures and dispatchers are frequently unavailable. These holdups leave drivers sitting idle; and as a result, some important shipments arrive late.

Customer satisfaction and repeat business are crucial to your bottom line, so your team decides to move from paper forms and phone calls to digital documents and online communication. Going digital will enable everyone to coordinate and track shipment times through their web browser or mobile app.

You want to quickly prototype something to share with your team. Your prototype will include a database to hold driver, customer, and order information. Your prototype will be the basis for your production app, so the technology choices you make now should carry over to what your team delivers.

From the Azure portal menu or the Home page, select Create a resource. Select Databases, then select SQL Database.

Screenshot of the Azure portal showing the Create a resource pane with the Databases section selected and the Create a resource, Databases, and SQL Database buttons highlighted.

fillout the form and review+create

on the toolbar, click Notifications to monitor the deployment process.

Screenshot of the Azure portal showing the Notifications menu with the Pin to dashboard button from a recent deployment success message highlighted.

Set the server firewall

Your Azure SQL database is now up and running. You have many options to further configure, secure, monitor, and troubleshoot your new database. You can also specify which systems can access your database through the firewall. Initially, the firewall prevents all access to your database server from outside of Azure.

For your prototype, you only need to access the database from your laptop. Later, you can add additional systems, such as your mobile app.

For now, let’s enable your development computer to access the database through the firewall.

  1. Go to the overview pane of the Logistics database. If you pinned the database earlier, you can click the Logistics tile on the dashboard to get there.
  2. Click Set server firewall.
Screenshot of the Azure portal showing a SQL database overview pane with the Set server firewall button highlighted.

Click Add client IP, this will automatically add the IP address for your development computer.

Screenshot of the Azure portal showing a SQL database Firewall settings pane with the Add client IP button highlighted.

Click Save.

The az commands you’ll run require the name of your resource group and the name of your Azure SQL logical server. To save typing, run this azure configure command to specify them as default values.

az configure –defaults group=learn-9080acd6-de3b-4076-a8df-5d7052b90e5e sql-server=[server-name]

az sql db list

az sql db list | jq ‘[.[] | {name: .name}]’

az sql db show –name Logistics

az sql db show –name Logistics | jq ‘{name: .name, maxSizeBytes: .maxSizeBytes, status: .status}’

az sql db show-connection-string –client sqlcmd –name Logistics

sqlcmd -S tcp:contoso-1.database.windows.net,1433 -d Logistics -U martina -P “password1234$” -N -l 30

CREATE TABLE Drivers (DriverID int, LastName varchar(255), FirstName varchar(255), OriginCity varchar(255));
GO

SELECT name FROM sys.tables;
GO

INSERT INTO Drivers (DriverID, LastName, FirstName, OriginCity) VALUES (123, ‘Zirne’, ‘Laura’, ‘Springfield’);
GO

SELECT DriverID, OriginCity FROM Drivers;
GO

UPDATE Drivers SET OriginCity=’Boston’ WHERE DriverID=123;
GO

DELETE FROM Drivers WHERE DriverID=123;
GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s