How to create an Azure SQL Database using the Cloud Shell

Introduction

Cloud Shell is a new feature just released on May 2017. It is currently in beta.

The idea behind the Cloud Shell is to run Azure commands using the Azure Portal directly. In the past, it was necessary to install the Azure CLI on a local machine. You could install on a Windows, Linux or a Mac machine or you could use Docker. Now, you can use the Azure Portal.

In this article, we are going to create an Azure SQL Server and an Azure SQL Database inside the server using the Cloud Shell. Specifically, we are going to use bash in Azure.

Requirements

A subscription to Azure

Get started

To start, in the Azure Portal, you will be able to see the new icon >_. This icon will be used to enter to the Cloud Shell:

Figure 1. The Cloud Shell icon

You will receive a message to create a storage to persist your $Home directory:

Figure 2. A new storage created when you use Cloud Shell for the first time

When you select the option, the storage account will be created. Also, a resource group and a File Share are created.

To verify, in the Azure Portal, press the > icon and select Storage accounts:

Figure 3. Verifying the Storage Account created

When we created a storage account a resource group named cloud-shell-storage-eastus was created and a storage named cs2994fc6e1ea72x40edx8df:

Figure 4. The Storage Account created

Inside the storage account, we created a File share. To verify it, click on the storage and go to Files:

Figure 5. Verifying the file shares

You will see a File service created. In our example, the File service name is cs-10037ffe8ccf1801 of 6 GiB. Click on it:

Figure 6. Checking the file service created

If you press Connect you can create a file share to Windows. This is very useful to copy files from your local Windows machine to Azure or vice versa:

Figure 7. Creating the commands to create a drive in your local machine

In the command line (cmd), copy the command, but assign a drive letter like this:

net use z: \\cs2994fc6e1ea72x40edx8df.file.core.windows.net\cs-10037ffe8ccf1801 /u:AZURE\cs2994fc6e1ea72x40e

On your local machine, you will see that the new drive was created. You can copy and paste files there:

Figure 8. The z: drive created from Azure in the local machine

Return to the Cloud Shell that was activated in Figure 1. You will connect to Bash.

Bash is a popular shell in Unix and Linux and now you can use it on Azure. If you are a Windows User, we can say that it is more powerful than the cmd and similar to PowerShell:

Figure 9. The connection information

As you can see, PowerShell is not supported yet in the Cloud Shell yet, but it will be supported soon:

Figure 10. The PowerShell anouncement

The first command that we will try is the help. It will show you soome links to a Microsoft web site with basic information about Cloud Shell:

Figure 11. The help command

Az help is another command that will list the different commands in bash for Azure:

Figure 12. az help shows the commands

The command az –version, displays the version of the different Azure Cli components:

Figure 13. Checking version of each component

Creating an Azure SQL Database

To create an Azure SQL Database, it is necessary to create:

An Azure Group
An Azure SQL Server

First, we are going to create an Azure group named mysqlshackgroup. The parameter –l is used to specify the location (like eastus, northeurope, eastasia and more) and –n is used to specify the name:

Az group create –l southcentralus –n mysqlshackgroup

Figure 14. Creating Azure Groups

Note that the results are displayed in JSON format.

In the next example, we are going to create a server named shackserver it will be created in the group mysqlshackgroup just created above. The parameter –s is to specify the server name, -u is to specify the user name (in this example the user name is admin) and the password uses the parameter –p.

The location is southcentralus and it uses the –l parameter:

Figure 15. Creating a new Azure SQL Server

To verify in the Portal, you can click on the > icon and in the search text box write sql server and click on the SQL servers icon:

Figure 16. Checking if the SQL Server was created successfully

You will be able to see the database created:

Figure 17. The SQL Server created with Cloud Shell

To verify that the Azure SQL Server was create you can use the az sql server list command.

You only need to specify the Azure Resource Group and it will show all the SQL Servers in the group:

az sql server list –g mysqlshackgroup

Figure 18. The list of SQL Server properties

Note that the session can be idle for 10 minutes after that it is disconnected and you need to reconnect the console by pressing Enter:

Figure 19. The session is disconnected after 10 minutes idle.

To create the database, you need to specify the name of the database (in this example newsqlshackdb), the Azure server name (shackserver) and the Azure Resource group (mysqlshackgroup):

az sql create –n newsqlshackdb –s shackserver –g mysqlshackgroup

Figure 20. The default size is 268435456000 bytes

If you check the maxSizeBytes parameter, it has 268435456000 bytes (250 Gb), you can verify that the Database was created in the portal if you click on the SQL icon:

Figure 21. The database created

The minimum size allowed is 100 MB. To reduce the size, you can update the Azure SQL Properties. The following commands will change the maximum database size to 100 MB:

az sql db update -n newsqlshackdb -s shackserver -g mysqlshackgroup –max-size 100MB

In the Azure Portal, you can go to databases and check the MAX SIZE in the properties section. As you can see, it was successfully changed to 100 MB:

Figure 22. Verifying that the size is now 100 MB

You can verify the database properties of the databases in the group mysqlshackgroup by using the az sql db list:

az sql db list -s shackserver -g mysqlshackgroup

There are two databases, the master database, which is a system database not visible in the Portal UI and the new sqlshackdb.

Figure 23. The master database information

The property maxSizeBytes shows the maximum size of the database in bytes. The Maximum Size is 100 MB:

Figure 24. The database newsqlshackdb information

Conclusions

Cloud Shell is a new feature that helps to run the command line without installing new software in the local machine. It is very useful and it creates a Storage Account with a file share that allows to copy and paste local files from and to Azure easily. At the moment, Bash is available, but soon we will be able to run PowerShell in the Cloud directly and automate the tasks easily.

Introduction

Requirements

Get started

Creating an Azure SQL Database

Conclusions

Related posts: