How to Create a Single Database in Azure SQL

In this Azure tutorial, we will discuss how to create a single database in Microsoft Azure SQL. We recommend you read What is Azure SQL? article before creating a database. This will help you to understand how to choose the appropriate set of resources according to your requirements.

• Create a Single Database in Azure SQL
• How to create a single database in Azure SQL using Azure Portal
• How to create a single database in Azure SQL using Azure CLI
• How to create a single database in Azure SQL using powershell

There are two types of databases that you can create in Azure SQL:

1. Single Database: A single database is a fully-isolated database in Azure SQL. This database and its resources are isolated from other resources. In short, a single database do not share its resources with other databases.
2. Elastic Pool database: An elastic pool database can share iits resources with other databases. This is a very good solution if you have multiple database and you do not know the amount of resources each database will consume.

Prerequisites: You need to have a Microsoft Azure account. After creating a Microsoft Azure account, you can follow the below steps to create a database in Azure SQL.

Create a Single Database in Azure SQL

In this section, you will learn how to create a single database in Azure SQL. We can create a single database in Azure SQL using one of the three methods:

1. Azure Portal: The official Microsoft Azure Portal.
2. Azure Cloud Shell: A bash provided by Azure to manage things with commands.
3. PowerShell in Azure: A PowerShell provided by Azure i.e. PowerShell on the go.

Read How to Connect to Azure SQL database

How to create a single database in Azure SQL using Azure Portal

You can create a single database in Azure SQL using the official Microsoft Azure portal. Using this method is more convenient as the Azure Portal is a GUI-based user-friendly platform to manage your Azure resources. Follow the below steps to create a single database in Azure SQL:

• Open any browser and navigate to https://portal.azure.com.
• Login with your Microsoft account and you will be redirected to the Azure Portal.
• Click on Create a resource to create a resource.

• Click on Databases and then SQL Database.

• Now you need a resource group that will have the access to this newly created resource. You can select one of the resource groups that you have already created. If you do not have one, you can also create a new one.

• We have created a new resource group named GroupAzureSQL.
• Now enter the name of the new database and select the server on which you want to create a database. We are creating a serverless database, therefore, we have to create a new logical server. We will use this server name in connection strings while connecting to the database.
• Also, select the Want to use SQL elastic pool option to off, if you want to create a single database. Otherwise, if you want to create an elastic pool database, you can select the Yes option here.

• Now click on Configure database to configure the storage and computing power.
• Select the General Purpose service tier and the Serverless compute tier if you want to create a serverless database. You can also select other options according to your requirements.

• Also, set the maximum and minimum number of vCores that you want to assign. You can do this by clicking and draggiing the slider.
• Keep checked the auto-pause option if you want to use auto-pause feature. This feature is very useful as you do not have to pay for the compute power while the database is paused. You can set the time limit after which the database will be paused.
• Select the maximum storage of your database and click Apply.

• Once you have configured the database, you have to select the backup storage and redundancy option. Select the appropriate option. For this demo, we will choose the Locally-redundant backup storage option. Click on Next-Networking.

• Select the Public endpoint connection so that you can connect to the database. You can configure it later and choose how you will connect to the database to ensure database security.
• Select the Add current client IP address to Yes. After selecting this option, your current IP address will be able to connect to the database.
• In the Connection policy option, you can choose whether you want to make a direct connection to the database or use a proxy server in the middle. We are choosing the Default option here.

• In the next page, you can enable the Azure Defender. It is a security tool you can use to protect your database from any threats. But, you have to pay a price for this. After selecting the required option click on Next: Additional Settings.

• You can start with a blank database, restore data from a backup or populate your database with some default tables and views.

• You can assign tags in the next page. Otherwise, click on Review+Create.
• You can review the options that you have selected. You can also see the estimated cost of your database. After reviewing the database options, click on Create.

• It will take some time to deploy the database.
• You will see the following screen once the database is created:

• You can click on the Deployment Details option to see the deployment details. You can also download these details by clicking on the Download option.
• Click on Next Steps and then Go to resource to monitor the database.
• Click on Query editor and you will need to login into the database for executing queries in the database.

• You can use either the SQL Server authentication or the Active Directory Authentication for logging into the server. Active Directory authentication means yoy can use the login credentials of the Azure portal for authentication.
• Once you have logged into the server, you can query the database in the Query editor window. We have also populated our database with sample tables. We can also query those tables and views and also see a list of all the obejcts in the database. For example:

Thus, you might have learned how you can create a database in Azure SQL using the Azure portal.

Also Read: Pause and resume Azure SQL database

How to create a single database in Azure SQL using Azure CLI

We can also use the Azure cloud shell to create a single database. The Azure cloud shell has some libraries preinstalled that we need while using the Azure services. Follow the steps below to create a database using the Azure Cloud Shell:

• Navigate to the given link https://shell.azure.com/ to open the Azure Cloud Shell.
• Log in if you are not already logged in.
• In the upper left corner of the screen, make sure that the terminal is set to Bash. See the below image for reference.

• Now we will write the commands in order to create a database in Azure SQL.
• Firstly, we will set some parameter values.

# Setting the resource group name and location of the server
resourceGroup=GroupAzure
location=eastus

# Setting an admin login and password for the database authentication
adminlogin=adminadams
password=********

# Setting a server name that is unique to Azure DNS 
serverName=mysqlserver1000

# Set the ip address range that can access your database
startip=169.149.247.13
endip=169.149.247.13

• Let us discuss the above parameters.
• The resourceGroup parameter is the name of the new resource group that you are going to create.
• The location parameter is the geographical location where you will create teh server.
• adminlogin is the administrator name and password is the password for the administrator authentication.
• serverName is the name of the server that you will create. Its name must be unique to the Azure DNS because there may be a server already existing with the same name in the Azue DNS. We can use this server name to connect to the server using any IDE.
• We have defined the IP range between the startip and the endip parameters that an access your database. This helps to improve security because only the specified IP addresses would be able to connect to the database.

• After setting the parameter values, we will create a resource group with the az group create command. This command takes few arguments and creates a resource group.

az group create --name $resourceGroup --location $location

• Now we will create a server with the az sql server create command. This command creates a new server for our database using the credentials that we have provided.

az sql server create \
    --name $serverName \
    --resource-group $resourceGroup \
    --location $location  \
    --admin-user $adminlogin \
    --admin-password $password

• The creation of server can take some time.
• Once the server is created, we have to configure some firewall rules. We will allow only a particular IP address to connect to our database. To implement this firewall rule, we will use the az sql server firewall-rule create command.

az sql server firewall-rule create \
    --resource-group $resourceGroup \
    --server $serverName \
    -n AllowYourIp \
    --start-ip-address $startip \
    --end-ip-address $endip

• Now only the IP addresses specified in the range can access the Azure SQL server.
• After configuring the firewall rules, we can create the database. The az sql db create command will take some parameters and create a database for us.

az sql db create \
    --resource-group $resourceGroup \
    --server $serverName \
    --name DemoDatabase \
    --sample-name AdventureWorksLT \
    --edition GeneralPurpose \
    --compute-model Serverless \
    --family Gen5 \
    --capacity 1 \
    --auto-pause-delay 60 \
    --backup-storage-redundancy Local \
    --max-size 13GB

Let us discuss some parameters provided to create the database.

• –name is the name of the database we are going to create.
• The Azure SQL provides us an option of a sample database that we can create along with the new database. –sample-name is the name of the schema that will be automatically created as the sample data.
• –edition is the service tier that you want to opt for. In this region and using the serverless computing model, you can only use the General Purpose service tier.
• –compute-model is the compute tier you want to choose. You can opt for either Provisioned or Serverless computing tier. We have chosen the serverless compute tier for our database.
• –family is the harware family you want to use. You can only use the Gen5 hardware family in a serverless compuitng model.
• –capacity defines the number of maximum vCores you want to assign to the database. We have assigned the maximum of 1 vCore.
• You can also assign the minimum number of vCores using the –min-capacity parameter. By default, the minimum vCore capacity is 0.5 vCore. We have opted for the default minimum capacity.
• We have set the auto-pause delay for 1 hour i.e. if the database is inactive for 1 hour, it will be paused. The database will resume when it faces some activitiy. This helps to minimize the compute power and save cost. The –auto-pause parameter can be used to set the time period after which the database will be paused. You have to specify the time in minutes.
• The backup-storage-redundancy defines the type of backup you want to use. You can choose local, zone pr geo redundant backup type.
• The –max-size parameter defines the maximum storage size of the database. Specify the size in proper units. By default, it will take the size in bytes if no unit is specified.

To verify if the database has been created, you can open the Azure portal and open the Resource groups tab.

• Then select the newly created resource group.

• You can see that the database is sucessfully created.

• You can click on the database and open the query editor window to start querying the database. But first you have to login into the database. You can use the credentials specified or use the Active Directory authentication.

• Once you have logged into the database, you can query the database. You can also see the sample data that you opted to create.

Thus, you might have learned how to use the Azure cloud shell or Azure CLI to create a database in Azure SQL.

Read How to rename a database in Azure SQL

How to create a single database in Azure SQL using PowerShell

In this section, you will learn to create a single database in Azure SQL using PowerShell. We will create a single serverless database using Powershell. The Azure PowerShell has some preinstalled libraries that you generally need while working with the Azure. To create the database, follow the below steps:

• Navigate to this link https://shell.azure.com/ and make sure the terminal is in powershell mode. See the below image for refernce.

• Firstly, we will create some variables and store some values which we will use in the further process.

$resourceGroupName = "GroupAzure"
$location = "eastus"
$adminLogin = "adminadams"
$password = "**********"
$serverName = "mysql1000"
$dbname= "DemoDB"
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"

• Let us discuss some important variables.
• $resourceGroupName is the name of the resource group that you will create.
• $location is the location where we will create the server.
• $adminLogin and $password are the admin login credentials that you will use to connect to your database.
• $serverName is the name of server you have to create on which you will create the database.
• $dbname is the name you want to give to your database.
• We will specify the IP range that will be given access to connect to the database using the $startIp and the $endIp variables.
• After defining all the variables, we will create a server on which we will create the database. We will use the New-AzSqlServer command along with the required parameters to create the server as follows:

$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-Location $location `
-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `
-ArgumentList $adminLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

• Once the server is created, we can set up the firewall rules for our server. This helps to improve the security of the database. We will use the New-AzSqlServerFirewallRule command along with required parameters to create the firewall rules for our server.

$serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp

• Now the final step is to use the New-AzSqlDatabase command to create the database. This command takes some input parameters that we will discuss further.

$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $dbname `
-Edition GeneralPurpose `
-ComputeModel Serverless `
-ComputeGeneration Gen5 `
-VCore 1 `
-MinimumCapacity 0.5 `
-SampleName "AdventureWorksLT" `
-AutoPauseDelayInMinutes 60 `
-BackupStorageRedundancy "Local" `
-MaxSizeBytes 13GB

• -Edition parameter determines what kind of service tier we want to choose. We are using the serverleess model in a region where we can choose only the General Purpose tier.
• -ComputeModel is the computing model we want to choose. We can choose either the serverless or the provisional computing tier.
• -ComputeGeneration is the hardware family we want to opt for. We will choose the Gen5 hardware family.
• -VCore defines the maximum number of vCores we want to allocate to our database.
• -MinimumCapacity parameter defines the minimum number of vCores that you want to assign. The range of the minimum number of vCores depends on the maximum number of vCores assigned.
• -SampleName is the name of the sample schema that will be created along with the database.
• -AutoPauseDelayInMinutes defines the auto-pause time delay. This is the time period after which the database is paused if it is not active. We need to pass the time in minutes. The range of this time delay is from 60 minutes to 7 days.
• -BackupStorageRedundancy is the type of backup you want to create. It can be Local, Zone or Geo.
• -MaxSizeBytes defines the maximum storage capacity of the database.

After following the above steps, you might have created the database successfully. To verify this, navigate to the Azure Portal and see if there is a resource group that you have created. Click on the resource group and you will see the list of resources.

There will be two resources: The database and the server.

• Click on the database and open the Query Editor window to query the database. But before start querying, the database will ask for authentication. You can login with the admin credentials you provided while creating the database. You can also use the Active Directory login to directly login into the database.

• Once you have logged into the database successfully, you can see the below query editor window and start querying the database. You will also find that there is some data already in the table. This is the sample data that we have opted to create along with the database.

Thus, we have learned the different ways to create a database in Azure SQL.

• Create a Single Database in Azure SQL
• How to create a single database in Azure SQL using Azure Portal
• How to create a single database in Azure SQL using Azure CLI
• How to create a single database in Azure SQL using PowerShell

You may like the following Azure SQL tutorials:

• Cannot drop schema because it is being referenced by object
• Azure SQL Database Schema
• Cannot bulk load because the file could not be opened. operating system error code (null). azure
• Azure SQL database query history
• Read only replica Azure SQL