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.
We need the following requirements:
- An Azure Account.
- A local machine with a Windows OS.
We will have three sections in this article:
- We will install Azure CLI and login to the Azure Portal.
- We will show some basic Azure CLI commands.
- 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:
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 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 126.96.36.199 188.8.131.52
Where 184.108.40.206 is the local IP and 220.127.116.11 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:
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.