Microsoft Azure SQL Database Tutorial

Microsoft Azure SQL Database

Explore the Microsoft Azure SQL Database tutorial , as azure sql is a powerful cloud-based solution for smooth data management. With this post, discover its scalability, security features, and integration capabilities within the Azure ecosystem. In addition, look into the key functionalities, services, and benefits driving efficient data handling in businesses.

In this blog, you will see how to work with Microsoft SQL Server Management to create a database and tables within it and write some queries to manipulate data. It is better if you know how to work on SQL Server beforehand.

If you want to brush up your skills in SQL, here is a video to help you out:

Some of the other intelligent features (as mentioned earlier) include dynamic data masking, automatic tuning, SQL threat detection, etc.
Now that you have got a rough idea about Microsoft Azure SQL Database, let us go in-depth and understand what Microsoft Azure SQL Database is.

What is Microsoft Azure SQL Database?

Microsoft Azure SQL Database is a relational database-as-a-service that is reliable and secure, and it gives high performance without having to worry about any infrastructure.

It supports relational, JSON, XML, and spatial data structures.
Microsoft Azure SQL Database has three deployment options:

• Single database
• Elastic pool
• Managed instances

In this Azure basic tutorial we will be talking about the single database, and toward the end, it will show you how to create a single database and write some queries.
When talking about databases, how is it possible not to talk about a server? Microsoft has its own on-premise SQL Server apart from Azure SQL Server. Quite confusing, right? Let us see the differences between the two to gain some clarity.

Azure SQL is a relational database platform that is present in the cloud where users can host the data and use it as a service. You can pay for what you have used, like all other cloud services. Azure SQL is built based on the SQL Server and hence it can be quite confusing when making a choice between the two as they share similar qualities. Despite being familiar, there are pretty evident differences that can help you decide which one to choose.

Let us compare the two.

Now that you have seen the differences between Azure SQL and SQL Server, which one will you opt for?
If you are worried about your data being vulnerable in the cloud, here is why Azure SQL should be your choice:
To ward off any threat to customer data, firewalls prevent access to the database server unless it is explicitly granted based on IP address. How to configure the firewall is shown in the hands-on part.
Access is given based on a user’s roles after a thorough authentication of the user.
Azure SQL supports two types of authentication:

• SQL authentication
• Azure Active Directory authentication

With advanced threat protection, it detects any anomaly in accessing or while using the database.
In addition to all these layers, Information Protection ensures that the data is encrypted while in use and in motion.
You can be sure that your data is going nowhere if it is in Microsoft SQL Azure Database. Details on how to secure Azure SQL is will be explained in the next section with its architecture.

There are four layers in Azure SQL Architecture:

• Client Layer
• Service Layer
• Platform Layer
• Infrastructure Layer

Now, let us briefly understand each layer one by one.

Client Layer

To be able to access SQL Database, the client layer acts as an interface for applications. It includes SQL Server tools, Open Database Connectivity (ODBC), ADO.NET, and Hypertext Preprocessor (PHP).
Tabular Data Stream (TDS) transfers data between applications and SQL Databases and also communicates with applications. Hence, ADO.NET and ODBC can connect to SQL without any additional demands.

Service Layer

The next layer in the architecture is the service layer, which is in between the platform and the client layers, that acts as a doorway between the two. As you can see in the diagram, provisioning, billing, and routing connections come under this layer. It validates Microsoft Azure SQL Database requests and authenticates a user. Also, it establishes a connection between the client and the server and routes packets through this connection.

Platform Layer

This layer has systems (data nodes) that host the actual Azure SQL Server in the data center. Each SQL Database is stored in one of the nodes and is replicated twice across two different physical servers. Azure SQL makes sure that multiple copies of servers are kept within the Azure Cloud. It also ensures that the copies are synchronized when clients manipulate their data on them.

Infrastructure Layer

This is the first layer from the bottom of the architecture and is responsible for the administration of the OS and the physical hardware.
So, these were the layers and the architecture of Azure SQL. Let us now look at the pricing of Azure SQL Service.

In this section, a single database will be discussed without touching managed instances and elastic pools.
For a single database, the vCore-based purchase model is the best to opt for because it is flexible and allows you to scale memory and storage based on your needs. Using the serverless compute tier optimizes price and performance.
For 1 GB, you are charged Rs.9.1213/month, but you can scale up your space up to 512 GB.
For weekly backups, your database is copied to RA-GRS and it will cost you Rs.15.864/month.
The pricing model will become even clearer in the next section. So, let us quickly move on to it.

After knowing about its pricing, let’s learn how to create a database in this Aa Azure basic tutorial.

Azure SQL Database Features

Azure SQL Database offers several powerful features designed to simplify database management:

• Scalability: Easily scale your database resources up or down based on demand, ensuring optimal performance without downtime.
• High Availability: Benefit from built-in high availability and automated backups, reducing the risk of data loss.
• Security: Utilize advanced security measures, including encryption, threat detection, and access control, to safeguard your data.
• Intelligence: Utilize intelligent query processing for improved performance and automated tuning for optimal query execution.
• Compatibility: Effortlessly transfer your existing SQL Server applications to Azure SQL Database with minimal changes.
• Cost-Efficiency: Pay for what you use with flexible pricing models, allowing cost optimization for varying workloads.

Azure SQL Database Tiers

Microsoft Azure SQL Database offers a variety of service tiers that are suited to diverse workload requirements and budget constraints. Each tier provides a unique combination of computing, storage, and I/O resources, enabling you to optimize your database performance and cost-effectiveness. The following tiers are elaborated below:

• Basic Tier: Designed for light workloads and small databases, offering a cost-effective entry point to Azure SQL Database with basic features.
• Standard Tier: Provides balanced performance and storage, suitable for mid-sized databases and moderate workloads, offering improved capabilities over the Basic tier.
• Premium Tier: Designed for high-performance needs, offering advanced features, high throughput, and low latency, and it is best suited for critical applications and heavy workloads.
• General Purpose Tier: Offers a good balance between performance and cost, and is suitable for a wide range of workloads, providing flexibility and scalability.
• Hyperscale Tier: Designed for massive scalability and performance, especially suited for applications with unpredictable workloads or heavy analytical processing.

Azure SQL Database Services

Microsoft Azure SQL Database goes beyond providing an effective cloud database service. It offers an extensive set of additional services that enhance your data management capabilities and address a wide range of data-related needs. 

Azure SQL Database Managed Instance:

Azure SQL Database Managed Instance provides a fully managed SQL Server database engine in the cloud. It allows lifting existing SQL databases to Azure with minimal changes. Microsoft manages the database engine, so there are automatic updates and patches without downtime. Developers get full SQL Server functionality and tools in a scalable and cost-effective cloud environment with built-in security and disaster recovery features.

Azure SQL Database Managed Backup:

Data loss creates a significant risk to any organization, and Azure SQL Database Managed Backup reduces this risk by automating database backup management. This service ensures that your backups are regularly created, stored securely, and readily available for point-in-time restoration in case of data loss or corruption. 

Azure SQL Database Threat Detection:

In today’s cybersecurity environment, protecting your data from evolving threats is most important. Azure SQL Database Threat Detection makes use of advanced machine learning algorithms to safeguard your database against potential security breaches. Real-time threat detection, vulnerability scanning, and threat investigation tools provide a comprehensive approach to identifying, analyzing, and reducing security threats.

The initial steps have been skipped which include creating an Azure account and logging in. This section begins with creating a database and writing simple queries in the portal, and then it will talk about how to create a new database and a table with values and how to do some operations on the data using Microsoft Server SQL Management.
Step 1: Login to the portal first and search for Database under services

1. Select SQL Databases under Database service

2. Select Add to create a new database

Step 2: You will see the following options on your screen where you will have to choose the type of subscription and resource group

1. If your account is on the free trial, please select Free Trial under Subscription

2. If you do not have any Resource group created, click on Create new

Step 3: To create a new resource, you have to name it. Here, the resource group is named as MynewResource

Step 4: Once you have created your resource group, you will now have to fill in some details

1. You will have to name your database. Here, it is named as myDatabase

2. After naming your database, you will have to create a server. Once you click on Create new, you will be prompted to set up some details, which will be explained in Step 5. Once done, close the pop-up window and continue with further steps

3. You will not need the elastic pool and, hence, select No

4. Once you are done creating a server, you will be able to click on the Configure database, where you will have to select pricing options

Step 5:
Enter a server name as your wish. Here, the name is helloworld1

1. Enter the username as per your choice

2. Enter a strong password containing uppercase alphabets, special characters, and numbers

3. Confirm your password by re-entering it

4. Select the location of your server. Southeast Asia has been selected here

5. Tick the box for Azure Services to access the server

6. Click on Select and close the window

Step 6: After completing Step 5, go to the 4th point of Step 4 for pricing and storage options. You will be redirected to where you can configure it according to your convenience. Select vCore as shown below:

Step 7: After selecting vCore, you can see options like Compute tier which has two options. Select Serverless and you can select the size of your data from 1 GB to 512 GB. Here, it is kept at the minimum level, 1 GB, and it will cost Rs.11.86 from Rs.13,300 credits which you get for a free account
After configuring your settings, click on preview under Preview Terms.

1. Tick the box

2. Select OK

Step 8: Your changes will be saved, and you will be redirected to the screen with options as in Step 4, but with all the details filled in. Select Next: Additional settings > which is at the bottom and do the following steps:

1. Under Use existing data, select Sample because you will be working with the sample data that is already present (and will create your own later)

2. Select Not now to enable ADVANCED DATA SECURITY

3. Click on Review + create

You can see a pop-up that has your configurations and select Create. Wait for some time as it takes a little long for deployment.



Step 9: Once done with the deployment, when you go to SQL Databases, you can see your database that has been created. Select your database and do the next step

Step 10: Now that you have your database created, write a query on your data. Click on Query editor, and you will be prompted to give your log-in details that you set in Step 5


Step 11: After you login with the right credentials, you can see the database name on the left, expanding which you can see the option Tables

1. Expand Tables to see the tables in your database

2. Expand the table SalesLT.Customer, for example, to see the columns, so that you can write a query

Step 12: Let us write a query on the designated area now.

1. Here, the first query is to print the CustomerID from the SalesLT.Customer table.

2. To execute the query, click on Run. You can see your result below

3. Note down one CustomerID, for example 12, and write another query to print the first name of the customer with the ID 12.

You can see the result after it gets executed. But you won’t be able to view a full-size table.

1. To see the entire table, use this: Select * from SalesLT.Customer; 

2. Run the command

3. Select the option Export data as .CSV which is present right above the query window

Here is the data of the table in an Excel sheet.

Step 13: Now set some security on your database. To do that, select your database from the SQL Databases list and do the following:

1. Copy your server name for future reference

2. Click on the Set server firewall

Step 14:

1. Click on Add client IP

2. You can see the details once you do the first step

3. Save the configuration

Step 15: Now create your own table using Microsoft SQL Server Management.
Once you have installed the software, you have to connect it to your server. For doing that, open the application and you will see a pop-up asking you to enter credentials.

1. Enter the server name that you copied and kept earlier

2. Under the Authentication drop-down menu, select SQL Server Authentication

3. Enter the username that you specified in the portal

4. Enter the correct password and select Connect

Step 16: After getting connected, you can see your server name on the left-hand side. Right-click on the name of your server and select New Query

Step 17: Now write a query to create a new database on your server.
You can see the center screen which is meant for writing queries. To execute it, select Execute which is above the editor. Once your query gets executed, you can see your new database name on the left bar. In this case, the new database is new1

Step 18: Let us now create a table inside your database. Let us create a simple table with the name ‘customer’ with columns customerID, first name, and address. You can see the results below the editor

Step 19: When you expand your database, you can see the table that you have created. And, expand the table to see the columns
A table without entries does not look good. So, let us insert a row


Step 20: Now that you have one entry, you can write some queries on it. As did in the portal, let us print the first name of the customer whose ID is 1

Step 21: If you want to add more data and write queries, please go ahead.
But make sure that before logging out you delete your resources to save credits

1. Go to Resource groups

2. Select your resource group

3. Select Delete by clicking on the three dots

You will have to re-enter the name of your resource group to confirm the deletion.

With this, you have reached the end of this blog, and hopefully, you know by now how to work with Microsoft SQL Azure Database.

Microsoft Azure Certifications

• Administrator
• Developer
• Architect

Now that you have learned the Microsoft Azure SQL Database in this Azure Basic tutoria, you can probably go and get certified in any of these domains. Intellipaat provides a range of courses for you to learn from experts. In case you want to become a certified professional in Azure, here are the certification courses that might interest you:

• Best Microsoft Azure Training for Administration (AZ-103 = AZ-100 and AZ-101)
• Microsoft Azure Developer Associate AZ-203 Certification Training Course
• Microsoft Azure Solutions Architect Certification Training (AZ 300 – 301)