Tutorial : Microsoft Azure SQL Database

 

Microsoft Azure SQL Database Hands-on

After you have signed in to your account follow the simple steps to create your Azure SQL Database.

Step 1: Navigate to Azure Portal. 

Step 2: Once, in the Azure Portal click on + ‘Create a resource’ option from the upper left corner and search for Azure SQL. 

Step 3: Select create in the resulting Azure SQL page. 

Step 4: Review all the Azure SQL options that are available, and then in the SQL databases title, ensure a single database is selected and select create.

Step 5: A created SQL database page will be popped up. Fill in the necessary information as follows:

• Subscription: Select your Azure subscription in which you wish to create the resource. 
• Resource group: Choose the resource group where you wish to create the resource or create a new one by clicking on create new option and entering the name of your choice.

• Database name: Enter the name you wish to give to the database. 
• Server: Select create new option and create a new server with a unique name in any location. Use SQL authentication and specify your name as the server admin login and a suitably complex password (for security)

• Want to use SQL elastic pool? : Choose whether you wish to use SQL elastic pool or not. 
• Compute + Storage: Choose or leave unchanged. 
• Backup storage redundancy: Choose locally-redundant backup storage. 

Step 6: Next click on ‘Next’: Networking and on the Networking page you can configure the networking settings. For now, in the Network connectivity section select Public endpoint. Then select Yes for both options in the Firewall rules section to allow access to the database server from Azure services and our current client IP address. 

Step 7: Next select ‘Next’: Security option to configure the security for the database. For now, set the Enable Microsoft Defender for SQL option to Not now.

Step 8: Next select NextCreate: Additional Settings option to configure some additional settings for the database. For now, set the use existing data option to Sample. 

Step 9: Finally, select Review + Create and review the different configurations of the database. 

Step 10: If everything is fine select Create to create an Azure SQL database.

Step 11: Wait for deployment to complete and go to the resource that was deployed.

Querying using Azure SQL Database

Step 1: Go to the Azure SQL database resource that was deployed. 

Step 2: In the pane on the left side of the page, select the Query editor and then sign in using the administrator login and password we had specified for our server. 

Note: If you receive an error message stating that the client IP address isn’t allowed, select the Allow list IP link at the end of the message to allow access and try to sign in again.

Step 3: Expand the Tables folder to see the tables in the database. 

Step 4: In the Query 1 pane that appears, enter the following SQL code:

SELECT * FROM [SalesLT].[SalesOrderHeader]

Step 5: Select the Run option above the query to run it and view the results, which will include all the columns for all rows in the table as shown below: 

Step 6: This way we can run all our SQL queries Close the query editor pane which will discard our edits.

This is how we can create Azure SQL database resources and use them in our cloud applications.

Pricing Of Azure SQL Database

There are two types of purchase models available in the Azure database. They are:

1. vCore ( virtual cores)
2. DTU (Data Transaction Units)

1. vCore (virtual cores) Pricing

Serverless compute:

A better option for databases with erratic usage patterns is the Serverless Compute tier. It guarantees effective resource usage by automatically adjusting compute resources in accordance with demand. This tier offers flexibility for changing workloads and streamlines cost management with compute consumption invoicing computed per second.

• Hyperscale: Optimizes cost performance for a range of workloads by combining compute and storage auto-scaling up to 100 TB. With storage fees determined by real allocation, scalability is possible without incurring excessive charges for underutilized resources.
  • Standard-series (Gen 5): Offers dependable performance with vCore usage-based pricing for the majority of relational database systems. The Hyperscale tier’s dynamic storage cost allocation between 10 GB and 100 TB guarantees effective resource use. Redundancy choices influence backup storage costs, offering flexibility for data protection requirements.

2. DTU (Data Transaction Units)

Single database model:

with pricing based on Database Transaction Units (DTUs) and included storage. Offers a cost-effective option for smaller applications with basic performance requirements. There are few models like basic, standard, premium and extra storage models.

• Basic: Azure’s Basic tier offers 5 DTUs and includes 2 GB of storage, ideal for basic workloads, priced at $0.0068/hour.

Deleting Databases in AzureSQL

Simple and Easy-to-Understand Examples:

There are different ways to delete a database in Azure SQL. In this article, we will discuss two methods:

Method 1: Delete a Database using Azure Portal

Step 1: Log in to the Azure Portal using your credentials.

Step 2: Navigate to the Azure SQL instance that contains the database you want to delete.

Step 3: Click on the “Databases” option on the left-hand side of the page.

Step 4: Select the database you want to delete from the list of databases.

Step 5: Click on the “Delete” button located at the top of the page.

Step 6: Confirm the deletion by clicking on the “Yes” button.

Method 2: Delete a Database using Azure CLI

Step 1: Open Azure CLI on your computer.

Step 2: Login to your Azure account using the command

"az login".

Step 3: Select the subscription that contains the Azure SQL instance by using the command “az account set –subscription <subscription name>”.

az account set --subscription <subscription name>

Step 4: Delete the database using the command “az sql db delete –resource-group <resource group name> –server <server name> –name <database name>”.

az sql db delete --resource-group <resource group name> --server <server name> --name <database name>

Step 5: Confirm the deletion by typing “y” and pressing Enter.

Conclusion

In conclusion, deleting a database in Azure SQL is a straightforward process that can be done using either the Azure portal or Azure CLI. Before you delete a database, make sure you have backed up the data and that you have the necessary permissions. Azure SQL is a powerful platform that allows users to manage their data in the cloud, and deleting a database is just one of the many tasks that can be performed using Azure SQL.