Introduction

After learning about the Azure Portal, the next step is to automate Administrative tasks in Azure. Azure CLI is a Command Line Interface used to automate tasks not only in Azure SQL, but also in Azure VMs, Azure Websites and several other Azure resources. What is cool about Azure CLI is that it can be installed in Windows, Linux, OS X (Macintosh computers).

In this example, we will use the Windows command line interface to create an Azure SQL Server and an Azure Database on the server.

Requirements

We need the following requirements:

1. An Azure Account.
2. A local machine with a Windows OS.

Getting Started

We will have three sections in this article:

1. We will install Azure CLI and login to the Azure Portal.
2. We will show some basic Azure CLI commands.
3. We will create an Azure SQL Server and a database using Azure CLI.

Azure CLI installation and login

1. We will install the Azure CLI first. Use the following link to install: Azure CLI Installer

2. Once downloaded, execute the Azure CLI installer:

3. Start the installation:

4. Once installed, open a command prompt (cmd) in the Windows OS and write the following command:

Azure Login

The command displays a code and an url:

5; In a Web browser, enter the url and then the code specified in the picture of the step 4:

5. Login with your Azure Account:

6. That is all. You can now connect to your Azure Account using the Azure CLI just installed:

Basic Commands in Azure CLI

Once installed and connected to the Azure Portal, we can now run Azure CLI commands.

The first time that you run a command, you will receive a message to participate in a program to collect data and send to Microsoft your information. This is very useful to improve the tool by Microsoft. You can press "y" to enable data collection or "n" if you do not want to enable this feature:

You can enable data collection using the following commands:

Azure telemetry --enable

To disable data collection use these commands:

Azure telemetry --disable

The most common command is Azure help:

Azure help

Azure help shows all the commands available:

In this article, we are interested in the SQL commands. Run the following commands to see the list of SQL commands available:

Azure help sql

The following SQL commands show how to create Azure SQL Servers , how to list the properties of the Azure SQL Databases, enable firewalls, show server properties and how to create Azure SQL Databases:

How to Create Azure SQL Databases

In this section, we will create an Azure SQL Server and a Database.

To create an Azure SQL Server, use the following command:

Azure sql server create daniel P@$w0rd1234 "South Central US"

Where "daniel" is the username and "P@$w0rd1234" is the password used to connect to the new server created. South Central US is the region where you install the server. For a complete list of available regions, go to the following link: Azure regions

The command will create an Azure SQL Server. The Azure SQL Server name assigns a random name. In this example, the server name is "b6zm1bqt":

To verify that the Server is created, in the Azure Portal go to Browse>SQL Servers:

In the list of servers, you will see the Server created:

In Azure CLI, you can show the server information using the sql server show command:

sql server show b6zxm1bpqt

The information displayed shows the name, location, full name and the status of the server, b6zxm1bpqt:

Now let's try to create an Azure SQL Database inside the server:

azure sql db create b6zm1bqt daniel

The following error message will be displayed:

To create an Azure SQL Database, it is necessary to enable access through the firewall. You can do it in the Azure Portal or using Azure CLI. You need to know your local IP address to allow traffic to pass in the machine with Azure CLI to connect to Azure SQL Server. The following command is used to create a rule in the firewall to allow access to the IP:

azure sql firewallrule create b6zm1bqt allowed 132.251.228.97 132.251.228.97 

Where 132.251.228.97 is the local IP and 132.251.228.97 is the end IP. In this example, we are enabling just one IP, but we can specify a range of IPs. b6zm1bqt is the SQL Server name.

If everything is OK, you will receive the following message: 

You can verify that the IP is enabled in the Azure Portal by clicking the Azure SQL Server created:

Click Firewall in the lower right and you will see the IP added in the list of allowed IPs:

Once that the rule is added, it is possible to run Azure CLI to create the Azure SQL Database:

azure sql db create b6zm1bqt daniel

Where "b6zm1bqt" is the server name, and "daniel" is the database name.

You will receive the following success message after executing the command line:

In the Azure Portal, you can go to SQL Databases to verify that the daniel database was created successfully:

Conclusion

Azure CLI is the command line used to automate tasks in azure. You can install Azure CLI in Windows, Linux and other platforms. In this example, we installed in a Windows Environment. To connect to the Azure Portal it is necessary to access to a URL and specify a code generated by the command line. 

Once installed, you can run the command line. You can easily create an Azure SQL Server, but to create an Azure SQL Database, you need to enable the IP of the local machine with the Azure CLI installed.

References

• Azure CLI commands in Azure Service Management (asm) mode
• Commands to manage SQL Databases
• Microsoft Azure Cross Platform Command Line
• Install the Azure CLI