Exploring Azure SQL

Introduction

In this blog we will be exploring Azure SQL and its various features such as downloading queries, exporting data, uploading scripts, and much more. We would use the sample data that is already available within Azure SQL to speed up our learning curve and focus on the functionalities.

Learning Objectives

1. What is Azure SQL?
2. Advantages of Azure SQL
3. Architecture of Azure SQL
4. Hands-On: Exploring Azure SQL

What is Azure SQL?

Azure SQL is a part of the family of cloud-based SQL databases providing flexible options for application migration, modernization, and development. It is a managed cloud database provided as part of Microsoft Azure. A cloud database is a database that runs on a cloud computing platform, and access to it is provided as a service. Managed database services on the cloud, take care of scalability, backup, and high availability requirements.

Advantages of Azure SQL

• Fully Managed and always up to date.
• We can spend more time innovating and less time patching, updating and backing up data.
• Only cloud solution with evergreen SQL that automatically applies the latest updates and patches. your databases are always up to date, eliminating end-of-support hassles.
• Even complex tasks like performance tuning, ensuring high availability, disaster recovery, and backups are automated, freeing you to focus on applications.
• It is built on the same SQL Server technology that you’re already familiar with, so you don’t need to relearn your SQL skills when you make the move.
• We can develop an application once using the existing SQL skills and deploy it on any Azure SQL cloud database on Azure.
• Protect your data with built-in, real-time intelligent security.
• Save with the lowest total cost of ownership.

Architecture of Azure SQL

(Source: Microsoft Azure Cloud)

Hands-On: Exploring Azure SQL

Step 1: Creating and Exploring a Sample Database

To get started with AzureSQL, first search for SQL Database in the search bar and select the Azure SQL Databases.

Now click on the “+ Create” button present on the top left section of the page.

Under the Basic tab, select your resource group. Then give a name to your database (in our case it’s dcdemo). Finally, click on the Create new button in front of the Server option.

Now, give your server a name (in our case it’s dcserver), enter admin as your username, and create a password as well.

Once this is done, click on the Create button.

Go to Additional Settings tab and for Use existing Data, under the Data Source option, select Sample.

After this click on the Review + Create button.

Then, finally click on the Create button.

It will take a couple of minutes to create the SQL Database.

Now go to the Resource Group and click on the SQL Database that you have created.

From the left-most section, click on the Query Editor (Preview) icon.

It would ask for the username and password — enter the ones that you have created while creating the server. Enter user-id & password and click on OK.

After clicking ok, you would be getting this error message:

The server firewall is blocking our IP address. To resolve this error, we must add our IP address to the firewall of the server that we have created.

For doing so, open the resource group from home in a separate tab and click on the server that you have created.

Now, from the leftmost section, scroll down to Security and Click on Firewall and Virtual Networks.

After this, click on the “+ Add Client IP” button and then click on save.

Your IP is read automatically and is now added to the server firewall.

Now, let’s head back to the login page for SQL Database and try again. This time we would be able to login to the system successfully.

On left hand navigation pane, we have three options — Tables, Views, and Stored Procedures.

Let’s explore each of them one by one. First of all start with Tables by clicking on its entry:

In the sample database that was used to create our database, there are already many tables available that are shown in the expanded tree. We will use these tables for our demo purpose.

Now, let’s see what is available in Views in the sample database. .

We can see that there are 4 predefined views available.

Now, finally, let’s see if we have some predefined Stored Procedures.

There are two predefined stored procedures available.

Step 2: Running SQL Queries

Now let’s explore the data which is available in the database Tables. In the Query tab, run the following query:

SELECT * FROM [SalesLT].[Customer]

There is a wonderful option in AzureSQL that we can download the data that has been extracted after running a query. For doing so, click on Export Data as button, present above the query editor.

We have three options for downloading the data file in desired file format — JSON, CSV and XML. For this example, let’s go for CSV.

A file must have been downloaded on your system.

We can even download all the queries as a sql script, so that we can reuse the queries when required. For doing so, click on the Save Query button, present above the Query Editor.

It would download the .sql file on your system.

In this way you can take the backup of your database queries as SQL scripts. You can later use this script for performing all the operations on another SQL database by just uploading and running this script.

Now, let’s see how we can upload and execute a SQL script to AzureSQL. For the demo, we would use the same sql script that we have downloaded just now. For doing so, click on the Open Query button at the top of the page.

Now, in the Open SQL query dialog, browse for the .sql file that was downloaded recently and click on OK.

In the query editor, all the commands from the sql file would get displayed. If you want, you can edit the uploaded sql script.

Now, simply click the Run button to execute these commands.

Step 3: Working with Views

Let’s create a view named my_new_view using the table named as [SalesLT].[Customer]

CREATE VIEW my_new_view AS SELECT * FROM [SalesLT].[Customer];

Type the above query in the query editor and click on the Run button to execute it.

You can expand the Views in the left hand side navigation pane to check your just created view.

Step 4: Working with Stored Procedures

After this let’s see how we can create and use a stored procedure.

Type the following SQL statements into the query editor:

GO
CREATE PROCEDURE PersonWithoutMiddleName
@Lastname nvarchar(50),
@Firstname nvarchar(50)
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, CompanyName
FROM [SalesLT].[Customer]
WHERE FirstName = @Firstname AND LastName = @LastName
AND MiddleName IS NULL;
GO

Click the Run button to execute the command to create the stored procedure.

In the left hand side navigation pane, you can see your stored procedure.

Now, let’s execute this procedure. Type the following command in the Query editor and click on Run.

EXECUTE [dbo].[PersonWithoutMiddleName] @FirstName = N’Keith’ , @LastName = N’Harris’;

The results of the execution are shown below the query editor.

We have successfully explored the Azure SQL service.

In the Next blog we would see how we can implement the Star and Snowflake schema in Azure SQL. This will provide you much more depth about how we can work efficiently with Azure SQL. Till then Keep Learning, Keep Exploring, and Keep Practicing.

𝐒𝐭𝐚𝐲 𝐜𝐨𝐧𝐧𝐞𝐜𝐭𝐞𝐝 𝐰𝐢𝐭𝐡 𝐮𝐬!

Subscribe to our YouTube channel for more such informative Content!

Follow us on: 𝐅𝐚𝐜𝐞𝐛𝐨𝐨𝐤, 𝐓𝐰𝐢𝐭𝐭𝐞𝐫, 𝐋𝐢𝐧𝐤𝐞𝐝𝐈𝐧, 𝐈𝐧𝐬𝐭𝐚𝐠𝐫𝐚𝐦

Join our meetup family of having 3700+ members, for re-equipping yourself with various new technologies for free. Click here now to join our knowledge-sharing family.

For virtual instructor-led Class, please reach out to us at operations@datacouch.io