How to create an Azure SQL database

In this azure tutorial, we will discuss How to create an Azure SQL Database. Apart from this, we will also discuss the below topics

• What Is Azure SQL Database
• Azure SQL Database Tutorial
• What is Microsoft Azure SQL Database
• History of Azure SQL
• Key Features Of Azure SQL Database
• Advantages of Azure SQL database
• Microsoft Azure SQL Database Step By Step
• How To Create Free SQL Database In Azure
• Query the SQL database in Azure
• How To Create Tables In SQL Azure Database
• How to create an Azure SQL database using PowerShell
• Why Create an SQL database on Azure
• Azure SQL Database Is Paas or Iaas
• SQL Azure Costs
• How Is Azure SQL Database Auditing Configured?
• Azure SQL database is an example of what type of service

How to create an Azure SQL database

Before discussing the topic How to create an Azure SQL database, let’s discuss a few things on the Microsoft Azure SQL Database like what is azure SQL database, the History of Azure SQL, the Advantages of Azure SQL database, etc.

• Azure SQL database vs SQL server

What Is Azure SQL Database

Azure SQL Database is a managed cloud database that uses the SQL Server database engine in the Azure Cloud platform. It becomes easier for the developers/programmers to develop their products as there is less involvement in the maintenance part of the product.

Azure SQL Database Tutorial

In another way, Azure SQL Database is a platform as a service (PaaS) database that can help you to perform different database activities such as DB backups, upgrading, monitoring, etc without your involvement. Microsoft takes care of all these activities.

It supports modern cloud applications with managed database service.

What is Microsoft Azure SQL Database

Microsoft Azure SQL Database is an excellent, scalable, durable, intelligent database service that is built for the Cloud. Microsoft takes care of resource management, Storage Size, Infrastructure, etc.

Another most important part is, the resources scale on-demand automatically and it’s always up to date and AI-Driven.

History of Azure SQL

Azure SQL is originally announced in the year of 2009 but actually it got released on the year of 2010.

Flavors Of SQL Server In Azure

There are different flavors of SQL server that Azure provides us and those are as below

• Azure SQL Database
• Azure SQL Database Edge
• Azure SQL Managed Instance
• SQL Server on Azure Virtual Machine

Let’s discuss here Microsoft Azure SQL Database Step By Step.

• Microsoft Azure naming conventions
• Azure web app for containers

Key Features Of Azure SQL Database

There are many essential features available for Windows Azure SQL Database. We will discuss here a few key features of Windows Azure SQL Database that are available.

1- Highly Secure

One of the important features of the Microsoft Azure SQL Database is that it is highly secure. As we all know that when it comes to our data, security is key and is very important.

Microsoft provides advanced security mechanisms with extra layers of protection with the help of Authentication with Active Directory integration, different data encryption facilities,audit logs, data-masking, etc.

Another important feature in terms of, security is,  Azure SQL Database provides an Adaptive threat detection feature that actually helps you to identify harmful attempts and Suspicious activities.

2- Scalability and Resource Management

Microsoft Azure SQL Database scalability helps to provide the information on what you need for growth. The important point here is it provides you support with Elastic Pools where you can push your large databases for cool performance and the Elastic Pool also helps you to manage the resources based on the rising and falling of your business needs.

The Elastic Pool feature also helps you to manage the changes that happen in the Workload and also it makes sure all the databases are performing good keeping costs in mind.

Microsoft Provides 4 workloads and those are Basic, Standard, Premium, and Premium RS that helps you to create robust and high-performing applications and cool databases with zero downtime.

3- Performance Tuning

Microsoft provides you inbuild performance tuning with the Azure SQL database that provides you the best performance for your database. It always monitors and manages the changes that are actually needed immediately to make sure that your databases are performing well.

It has batch mode memory grant feedback, batch mode adaptive joins and interleaved execution

Different batch processing features like interleaved execution, batch mode memory grant feedback and batch mode adaptive joins help to enhance the performance.

4- Responsive SLA-based support 

Microsoft Azure SQL Database supports the responsive SLA-based support that actually helps you to keep the resources up at all the time which is a key factor for a business.

5- V12 Portability

V12 Portability is another excellent feature provided by the Azure SQL Database. You can consider it as a core benefit of the Azure SQL database that allows you to move a database to the cloud and from the cloud and off it or into such services as IaaS and Paas.

In earlier days, it was quite challenging to migrate the SQL server to the Azure SQL database but now Microsoft provides many tools that help you quickly migrate the SQL server DB to Azure SQL.

6- Query Store 

Query Store helps you to track the performance and history of the databases across many different hardware, different platforms, or service tiers. It helps you to track your database performance metrics.

Query store feature can get you the historical data that will give you the desired information about the database that helps the organization to take different decisions on databases.

These are a few of the Key Features Of Windows Azure SQL Database.

Advantages of Azure SQL database

• Azure SQL comes with High-Speed performance and with very Minimal Down Time.
• Another big advantage is Azure SQL is scalable in nature.
• The pricing structure of Azure SQL is the pay as you go, model, meaning pay that much how much you are actually using.
• Azure SQL is highly available. This is another big advantage with Azure SQL.

Microsoft Azure SQL Database Step By Step

Well, it’s quite easy to create the Azure SQL database using the Azure Portal.

How To Create Free SQL Database In Azure

Follow the below steps to create a free SQL Database in Azure.

Step-1: Login to the Azure portal (https://portal.azure.com/)

Step-2: Search for Azure SQL and select it .

Step-3: Click on the Add button On the Azure SQL page.

Step-4: On the Select SQL deployment option window, click on the Create button from the SQL database option.

Step-5: On the Basics, Fill the below details.

• Subscription: Select the correct subscription.
• Resource group: Create a new resource group by clicking on the Create new link.
• Database name: Provide a meaningful database name.

Server: Click on the Create new link to create a new server. Enter the below details on the New server form.

• Server name: Enter a unique server name
• Server admin login: Enter azure user.
• Password: Enter a password and enter it again in the Confirm password field.
• Location: Choose a location here

Click on the OK button

Step-6: Compute + storage: It will load a default one. if you want to reconfigure the defaults, select Configure database link.

On the Configure page, you can choose the below details.

• You can change the Compute tier from Provisioned to Serverless.
• Change the settings for vCores and Data max size.
• Click on the Change configuration button to change the hardware generation.

Now click on the Apply.

Step-7: Click on the Next:Networking> button to go the Networking tab.

In the Networking tab, Choose the below options

• Select the Connectivity method as Public endpoint.
• Choose the No option for Allow Azure services and resources to access this server.
• Select Yes option for Add current client IP address.

Step-8: Click on Next : Additional settings > button to go to Additional settings page

• Select the Sample option for Use existing data
• Click on the Review + Create button.

Step-9: Finally, click on the Create button which will create the Azure SQL database.

Note: Do not forget to read the terms properly before clicking on the Create button.

This is how we can create Microsoft Azure SQL Database Step By Step.

Query the SQL database in Azure

Once your data base is created by using the above steps, now time to query the database.

To query the database follow the below steps.

Step-1: Login to the Azure portal and search for SQL databases and select it.

Step-2: From the SQL databases window select the database you have created.

Step-3: Select the Query editor (preview) option from the left menu from the database page.

Step-4: Enter your credentials under SQL server authentication which you have generated earlier on the New server page and then click on the OK button.

Step-5: Enter your SQL query in the query editor and then click on the Run button to execute the query. It will show the output at the Results pane.

How To Create Tables In SQL Azure Database

Now, the time to create a table in Azure SQL database. Follow the below simple steps to create a table in Azure SQL DB.

1. Connect to Azure SQL DB Instance using SQL Server Management Studio (SSMS)
2. Locate the Database node –> Right-click on the Database –> click on the New Query option.

3. On the Query window, write the query as below.

CREATE TABLE Student
(
    StudentId INT IDENTITY PRIMARY KEY,
    FirstName NVARCHAR(128) NOT NULL,
    LastName NVARCHAR(128) NOT NULL,
    DateOfBirth DATE NOT NULL
)

4. After writing the Query click on the Execute button as shown below.

5. Now, you can able to see that it is showing Commands completed successfully.

6. To, verify if the table is created, you can expand the Database node –> Expand the Tables –> You can able to see the dbo.Student table has been created successfully without any issue.

This is How To Create Tables In SQL Azure Database.

How to create an Azure SQL database using PowerShell

Let’s discuss how to create an Azure SQL database using PowerShell. Follow the below steps

Set the subscription details

Use the below PowerShell cmdlet to set the subscription details.

$MysubscriptionId = '1cdf4300-dee5-4518-9c9c-feaa72a5cbd1'
Set-AzContext -SubscriptionId $MysubscriptionId 

Creation of the Resource group

Use the below PowerShell cmdlet to create the resource group.

$MyresourceGroupName = "newresgroup-$(Get-Random)"
$Mylocation = "Eastus"
$MyresourceGroup = New-AzResourceGroup -Name $MyresourceGroupName -Location $Mylocation

Creation of server

To create the server, use the below PowerShell cmdlet.

$MyresourceGroupName = "newresgroup-$(Get-Random)"
$NewserverName = "myservernew-$(Get-Random)"
$Mylocation = "Eastus"
$NewadminSqlLogin = "SqlAdmin"
$Newpassword = "[email protected]"

$Myserver = New-AzSqlServer -ResourceGroupName $MyresourceGroupName `
    -ServerName $NewserverName `
    -Location $Mylocation `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $NewadminSqlLogin, $(ConvertTo-SecureString -String $Newpassword -AsPlainText -Force))

Creation of Firewall Rule

We can use the below PowerShell cmdlet to create the firewall rule

$MyresourceGroupName = "newresgroup-$(Get-Random)"
$NewserverName = "myservernew-$(Get-Random)"
$NewstartIp = "0.0.0.0"
$NewendIp = "0.0.0.0"

$MyserverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $MyresourceGroupName `
    -ServerName $NewserverName `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $NewstartIp -EndIpAddress $NewendIp

Create a blank Database

Create the blank database using the below PowerShell cmdlet

$MyresourceGroupName = "newresgroup-$(Get-Random)"
$NewserverName = "myservernew-$(Get-Random)"
$NewdatabaseName = "mynewdb36845"

$mydatabase = New-AzSqlDatabase  -ResourceGroupName $MyresourceGroupName `
    -ServerName $NewserverName `
    -DatabaseName $NewdatabaseName `
    -RequestedServiceObjectiveName "S0"

Run the below complete script to create azure sql database using PowerShell in Azure

$MysubscriptionId = '1cdf4300-dee5-4518-9c9c-feaa72a5cbd1'

$MyresourceGroupName = "newresgroup-$(Get-Random)"
$Mylocation = "Eastus"
#Admin login and password for your server
$NewadminSqlLogin = "SqlAdmin"
$Newpassword = "[email protected]"

$NewserverName = "myservernew-$(Get-Random)"
$NewdatabaseName = "mynewdb36845"

$NewstartIp = "0.0.0.0"
$NewendIp = "0.0.0.0"
Set-AzContext -SubscriptionId $MysubscriptionId 
$MyresourceGroup = New-AzResourceGroup -Name $MyresourceGroupName -Location $Mylocation
$Myserver = New-AzSqlServer -ResourceGroupName $MyresourceGroupName `
    -ServerName $NewserverName `
    -Location $Mylocation `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $NewadminSqlLogin, $(ConvertTo-SecureString -String $Newpassword -AsPlainText -Force))
$MyserverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $MyresourceGroupName `
    -ServerName $NewserverName `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $NewstartIp -EndIpAddress $NewendIp
$mydatabase = New-AzSqlDatabase  -ResourceGroupName $MyresourceGroupName `
    -ServerName $NewserverName `
    -DatabaseName $NewdatabaseName `
    -RequestedServiceObjectiveName "S0" 

Now after running the above script you can login to the Azure portal and check if the SQL database is created

Why to Create an SQL database on Azure

Maintaining the on-premise environment is a headache. You can easily create the SQL database in Azure and no headache for the infrastructure.

You can easily move the databases between different machines on the cloud environments.

The main benefits of creating an Azure SQL database are

• You can use the unlimited storage
• Quick and easy access
• Enhanced security features
• Backups and restores are easy

Azure SQL Database Is Paas or Iaas

Microsoft Azure SQL Database is Platform as a service (Paas) that is built on a well set of hardware and software which is hosted, owned, and managed by Microsoft. Not only that, but Microsoft also takes care of the patching, backups, and monitoring, etc. You no need to worry about that part.

Basically, Azure SQL Database is a Paas offer from Microsoft. Whereas, remember that the SQL server inside the Azure Virtual Machine is an Iaas offer.

SQL Azure Costs

For an entry-level, You have to pay $4.99 per database per month with maximum storage set at 150 GB per database.

Check out the Azure SQL Pricing now for complete information.

How Is Azure SQL Database Auditing Configured?

Well, you can easily configured the Azure SQL Database auditing in the Azure Portal using few easy steps as below.

• Log in to the Azure Portal (https://portal.azure.com/)
• On your Azure SQL Database page, on the left navigation, click on Auditing which is present under the Security heading. You can click on the View server settings link if you want to view or modify the settings as highlighted below.

• Now to enable the Azure SQL Auditing, switch Auditing to ON.
• Then, you need to choose the audit log destination where you want to store or write the audit logs. The options are Storage account, Log Analytics, Event Hub. Based on your requirement, you can click on the Configure option. After configuring the Audit log destination you need to click on the Save button to save the changes.

• Now, if you want to configure the Audit Log Destination as a Storage account or you want to write the audit logs to the storage account then click on the Configure option.
• On the Storage settings window, provide the below details

1. Subscription: Provide a valid subscription that you want to use here.
2. Storage account: Select an existing storage account or if you don’t have an existing storage account, you can click on the Create button to create a new storage account.
3. Retention days: You can select some specific days based on your requirements by sliding the Retention (Days) slider. If you won’t change anything then the default value for the Retention days is zero that means unlimited retention days.
4. Storage access key: Select Primary or Secondary based on your need.

Finally, click on the Ok button to Save the changes.

• If you will choose the Audit Log Destination as the Log Analytics option, then click on the Configure option, on the Log Analytics Workspaces window, click on the + Create New Workspaces button to create a new Log Analytics workspace or you can select an existing workspace if you already. You can also select the default workspace if you want.

If you want to select the Audit Log Destination as the Event Hub, then click on the Configure option and on the Select event hub window, you need to provide the below details

1. Subscription: Select a valid subscription that you want to use here.

1. Event hub namespace: You need to provide the Event hub namespace.
2. Event hub name: It is optional but if you want, you can provide a name for the Event Hub.
3. Event hub Policy name: You must provide the Event hub Policy name.

Finally, click on the Ok button.

Once You have configured any of the Audit Log Destination. Finally, click on the Save button on the SQL Database page. This is how to configure Azure SQL Database Auditing.

Azure SQL database is an example of what type of service

Azure SQL database, a relational database, and Platform-as-a-Service (PaaS), consists of a set of hardware and software which is hosted, owned and managed by Microsoft. You no need to take any headache for the environment and infrastructure as Microsoft takes care of this part.

You may like following Azure tutorials

• How To Change User Id And Password For Azure SQL Server Database
• The ‘Set-AzContext’ command was found in the module ‘Az.Accounts’ but the module could not be loaded.
• Microsoft Azure naming conventions
• How to backup Azure SQL database

Conclusion

In this Azure tutorial, We have discussed How to create an Azure SQL database and along with that, we have also discussed the below topics

• What Is Azure SQL Database
• Azure SQL Database Tutorial
• What is Microsoft Azure SQL Database
• History of Azure SQL
• Key Features Of Azure SQL Database
• Advantages of Azure SQL database
• Microsoft Azure SQL Database Step By Step
• How To Create Free SQL Database In Azure
• Query the SQL database in Azure
• How To Create Tables In SQL Azure Database
• How to create an Azure SQL database using PowerShell
• Why Create an SQL database on Azure
• Azure SQL Database Is Paas or Iaas
• SQL Azure Costs
• How Is Azure SQL Database Auditing Configured?
• Azure SQL database is an example of what type of service

Hope you have enjoyed this article !!!