azure3.0_使用Azure CLI 2.0使用Azure SQL数据库
azure3.0
In my previous article, Getting started with Azure CLI 2.0, I have explained how to get started with the Azure CLI 2.0. I have also explained how to install it on a local machine and demonstrated a few commands. In this article, I’m going to explain how to create a SQL Database using Azure CLI 2.0. Further, in the later part of the article, I’ll also show how to create a backup of the SQL Database using Azure CLI. Creating a SQL Database using the Azure CLI helps us to get things automated and removes the error-prone manual creation of the databases and backups.
在上一篇文章《 Azure CLI 2.0入门》中 ，我解释了如何开始使用Azure CLI 2.0。 我还解释了如何在本地计算机上安装它，并演示了一些命令。 在本文中，我将解释如何使用Azure CLI 2.0创建SQL数据库。 此外，在本文的后半部分，我还将展示如何使用Azure CLI创建SQL数据库的备份。 使用Azure CLI创建SQL数据库有助于我们使事情自动化，并消除了容易出错的数据库和备份手动创建。
This article doesn’t contain any information regarding the Azure SQL Database, instead, it is just a reference on how to create the SQL Database using Azure CLI. We can also create the database using the Azure portal or the cloud shell. The result of all these will be the same. Once the database is created, we can connect the database using the SQL Server Management Studio from a local machine.
本文不包含有关Azure SQL数据库的任何信息，而是仅作为有关如何使用Azure CLI创建SQL数据库的参考。 我们还可以使用Azure门户或云外壳创建数据库。 所有这些的结果将是相同的。 创建数据库后，我们可以使用SQL Server Management Studio从本地计算机连接数据库。
使用Azure CLI创建SQL数据库 (Creating the SQL Database using Azure CLI)
Creating an SQL Database in the Azure involves two steps. In the first step, we need to create the SQL Server instance and in the second step, we need to create the database in the instance. Both these steps can be performed by using the Azure CLI 2.0.
在Azure中创建SQL数据库涉及两个步骤。 第一步，我们需要创建SQL Server实例，第二步，我们需要在实例中创建数据库。 这两个步骤都可以通过使用Azure CLI 2.0执行。
Let us first go ahead and create the SQL Server Instance using the Azure CLI. The command to create an SQL Server instance is “az sql server create“. In order to create a SQL Server instance using the CLI, we need to provide a name for the instance, a username and a password for the administrator to login to the instance. Another requirement that needs to be fulfilled prior to creating the SQL instance is we need to know under which Resource Group are we going to create the SQL Server instance and the location in which the instance is to be created.
首先让我们使用Azure CLI创建SQL Server实例。 创建SQL Server实例的命令是“ az sql server create ”。 为了使用CLI创建SQL Server实例，我们需要提供实例名称，用户名和密码，以供管理员登录到实例。 在创建SQL实例之前需要满足的另一个要求是，我们需要知道要在哪个资源组下创建SQL Server实例以及创建实例的位置。
Let us assume that we will be using the following information to create the SQL Server Instance.
让我们假设我们将使用以下信息来创建SQL Server实例。
- SQL Server Instance Name: sqlshackdemo-server SQL Server实例名称：sqlshackdemo-server
- SQL Username: sqlshackuser SQL用户名：sqlshackuser
- SQL Password: [email protected] SQL密码：！SecuredPassword @ 123
- Resource Group: rg-sqlshack-demo 资源组：rg-sqlshack-demo
- Location: West Europe 所在地：西欧
The CLI command to create the server will be as follows:
用于创建服务器的CLI命令如下：
az sql server create –name sqlshackdemo-server –resource-group rg-sqlshack-demo –location westeurope –admin-user “sqlshackuser” –admin-password “[email protected]”
az sql服务器创建–名称sqlshackdemo-server –资源组rg-sqlshack-demo –位置westeurope –admin-user“ sqlshackuser” –admin-password“！SecuredPassword @ 123”
Figure 1 – Azure CLI Command for SQL Server Instance
图1 – SQL Server实例的Azure CLI命令
Once this command is executed, it might take some time to run and create the instance in the Azure Portal. As soon as the SQL Server instance is created in the portal, the details will be displayed in the console and can be verified easily as follows.
执行此命令后，可能需要一些时间才能在Azure门户中运行并创建实例。 在门户中创建SQL Server实例后，详细信息将立即显示在控制台中，并可以按以下方式轻松进行验证。
Figure 2 – SQL Server Instance Created Using Azure CLI
图2 –使用Azure CLI创建SQL Server实例
You can also verify the same by navigating to https://portal.azure.com and browse the resources created under the resource group that was specified while creating the SQL Server instance. As you can see in the figure below, the new instance “sqlshackdemo-server” has been created under the resource group.
您还可以通过导航到https://portal.azure.com并在创建SQL Server实例时在指定的资源组下浏览创建的资源来进行验证。 如下图所示，新实例“ sqlshackdemo-server ”已在资源组下创建。
Figure 3 – Resource Added under Resource Group
图3 –在资源组下添加的资源
Once the SQL Server Instance is created, the next step is to create the SQL Database using Azure CLI. The command to create an SQL Database is “az sql db create“. In order to create the SQL Database, we need to provide the following details.
创建SQL Server实例后，下一步是使用Azure CLI创建SQL数据库。 创建SQL数据库的命令是“ az sql db create ”。 为了创建SQL数据库，我们需要提供以下详细信息。
- Resource Group: rg-sqlshack-demo 资源组：rg-sqlshack-demo
- SQL Server Instance Name: sqlshackdemo-server SQL Server实例名称：sqlshackdemo-server
- Database Name: sqlshackdemo-db 数据库名称：sqlshackdemo-db
- Service Objective: s3 服务目标：s3
- Collation: SQL_Latin1_General_CP1_CI_AS 排序规则：SQL_Latin1_General_CP1_CI_AS
The CLI command to create the database will be:
用于创建数据库的CLI命令将为：
az sql db create –resource-group rg-sqlshack-demo –server sqlshackdemo-server –name sqlshackdemo-db –service-objective S3 –collation SQL_Latin1_General_CP1_CI_AS
az sql db create –资源组rg-sqlshack-demo –服务器sqlshackdemo-server –name sqlshackdemo-db –service-objective S3 –collation SQL_Latin1_General_CP1_CI_AS
Figure 4 – Creating SQL Database using Azure CLI
图4 –使用Azure CLI创建SQL数据库
Once the database has been created successfully, the CLI will return the details of the database being created as below.
成功创建数据库后，CLI将返回正在创建的数据库的详细信息，如下所示。
Figure 5 – SQL Database Created
图5 –创建SQL数据库
You can also verify the database is successfully created by navigating to the Azure portal and drill down to the server resource.
您还可以通过导航到Azure门户并向下钻取服务器资源来验证数据库是否已成功创建。
Figure 6 – SQL Database created
图6 –创建SQL数据库
Now that the database is created, let us go ahead and try to connect to the instance using SQL Server Management Studio. Open SQL Server Management Studio and provide the following information.
现在已经创建了数据库，让我们继续尝试使用SQL Server Management Studio连接到实例。 打开SQL Server Management Studio并提供以下信息。
- Server Name: sqlshackdemo-server.database.windows.net 服务器名称：sqlshackdemo-server.database.windows.net
- Authentication Type: SQL Server Authentication 身份验证类型：SQL Server身份验证
- Username: sqlshackuser 用户名：sqlshackuser
- Password: [email protected] 密码：！SecuredPassword @ 123
Figure 7 – Connecting to SQL Database using SSMS
图7 –使用SSMS连接到SQL数据库
Once the login is successful, you can see the list of databases that are available in the instance. In this case, since we have created only one database, it is visible. You can use this database like any other database and execute queries against it.
登录成功后，您可以看到实例中可用的数据库列表。 在这种情况下，由于我们仅创建了一个数据库，因此它是可见的。 您可以像使用任何其他数据库一样使用此数据库，并对它执行查询。
Figure 8 – SQL Database Connected
图8 –连接SQL数据库
使用Azure CLI备份和还原现有SQL数据库 (Backup and Restore an Existing SQL Database using Azure CLI)
In the first part of the article, we have seen how to create a SQL Database using the Azure CLI and how to connect to the same using SQL Server Management Studio. Now we will see how to create a backup from the SQL Database using the CLI and how to restore the same using Azure CLI. The backup will create a BACPAC file and we can restore from the BACPAC file. The basic command to back up an SQL Database is “az sql db export” and to restore the database we can use “az sql db import“.
在本文的第一部分，我们已经了解了如何使用Azure CLI创建SQL数据库以及如何使用SQL Server Management Studio连接到该数据库。 现在，我们将看到如何使用CLI从SQL数据库创建备份以及如何使用Azure CLI还原备份。 备份将创建一个BACPAC文件，我们可以从BACPAC文件中还原。 备份SQL数据库的基本命令是“ az sql db export ”，要还原数据库，我们可以使用“ az sql db import ”。
In order to create the backup, we would also need to store the backup file in Azure. For this, we would need to create a storage account which can be used to store the backup file and restore from it when necessary. The storage account can be created in the same resource group as the SQL Server or in a different resource group.
为了创建备份，我们还需要将备份文件存储在Azure中。 为此，我们需要创建一个存储帐户，该帐户可用于存储备份文件并在必要时从中还原。 可以在与SQL Server相同的资源组中或在不同的资源组中创建存储帐户。
Let us go ahead and create the Storage account first with the command:
让我们继续，首先使用以下命令创建存储帐户：
az storage account create –name sqlshack-storage –resource-group rg-sqlshack-demo –location westeurope –sku Standard_ZRS –encryption-services blob
az存储帐户创建–名称sqlshack-storage –资源组rg-sqlshack-demo –位置westeurope –sku Standard_ZRS –encryption-services blob
Figure 9 – Creating the Storage Account
图9 –创建存储帐户
Once the storage account has been created the CLI will populate the results of the new Storage Account.
创建存储帐户后，CLI将填充新存储帐户的结果。
Figure 10 – Storage Account Created
图10 –创建的存储帐户
You can also verify the same storage account by navigating to the Azure portal under the Resource Groups.
您还可以通过导航到资源组下的Azure门户来验证相同的存储帐户。
Figure 11 – Storage Account in Azure
图11 – Azure中的存储帐户
Once the storage account has been created successfully, we need to fetch the Connection String of the storage account to be used while storing the BACPAC file. You can run the command – “az storage account show-connection-string –name sqlshackstorage –resource-group rg-sqlshack-demo” to retrieve the storage account connection string. The important part is the AccountKey information which we will need in the next steps to create the backup.
成功创建存储帐户后，我们需要获取存储BACPAC文件时要使用的存储帐户的连接字符串。 您可以运行命令–“ az存储帐户show-connection-string –名称sqlshackstorage –resource-group rg-sqlshack-demo ”以检索存储帐户连接字符串。 重要的部分是AccountKey信息，在下一步中我们将需要该信息来创建备份。
Figure 12 – Storage Account Connection String
图12 –存储帐户连接字符串
Now that most of the pre-requisites are fulfilled, let’s go ahead and run the command to create the backup file. This is a bit complex as we need to provide the details of both the SQL Server Database and the Storage Account. The command for the CLI is as follows:
现在已经满足了大多数先决条件，让我们继续运行命令来创建备份文件。 这有点复杂，因为我们需要提供SQL Server数据库和存储帐户的详细信息。 CLI的命令如下：
az sql db export –server sqlshackdemo-server –name sqlshackdemo-db –resource-group rg-sqlshack-demo –admin-user “sqlshackuser” –admin-password “[email protected]” –storage-key-type StorageAccessKey –storage-key QfjUyNblz49nCa+3dsoLfknYVEKfVlrMqjBTmF6XHtbVneL6FKTpIUAmL8RrgFBmEIesGosn9AJkrsBC9For6Q== –storage-uri “https://sqlshackstorage.blob.core.windows.net/SqlShackDemo_Backup.bacpac”
az sql db导出–服务器sqlshackdemo-server –名称sqlshackdemo-db –资源组rg-sqlshack-demo –admin-user“ sqlshackuser” –admin-password“！SecuredPassword @ 123” –storage-key-type StorageAccessKey –storage-键QfjUyNblz49nCa + 3dsoLfknYVEKfVlrMqjBTmF6XHtbVneL6FKTpIUAmL8RrgFBmEIesGosn9AJkrsBC9For6Q == –storage-uri“ https://sqlshackstorage.blob.core.windows.net/upS。
Figure 13 – Creating BACPAC in Storage Account
图13 –在存储帐户中创建BACPAC
Once the command has been executed successfully, you can see the backup file is created in the Storage Account.
成功执行命令后，您可以看到在存储帐户中创建了备份文件。
Figure 14 – Bacpac File Created
图14 –创建的Bacpac文件
You can also verify the same by logging in to the Azure Portal and browsing the Blob Storage manually.
您还可以通过登录到Azure门户并手动浏览Blob存储来进行验证。
Figure 15 – BACPAC File Verified
图15 – BACPAC文件已验证
结论 (Conclusion)
In this article, I have explained how to create a SQL Database using Azure CLI 2.0. We have understood the first step in creating the SQL Server instance and then we have created the database in it. We have also learned how to create a backup of an existing SQL Database using Azure CLI and finally creating a BACPAC file with the same. Using Azure CLI is helpful as it can automate a lot of manual functions. Using Azure CLI in the portal, also known as the Cloud Shell, provides the users or admins the ability to store and save the scripts on the cloud and execute them whenever required.
在本文中，我已经解释了如何使用Azure CLI 2.0创建SQL数据库。 我们已经了解了创建SQL Server实例的第一步，然后在其中创建了数据库。 我们还学习了如何使用Azure CLI创建现有SQL数据库的备份，以及最终使用该备份创建BACPAC文件。 使用Azure CLI很有帮助，因为它可以自动执行许多手动功能。 在门户（也称为Cloud Shell）中使用Azure CLI，使用户或管理员能够在云中存储和保存脚本，并在需要时执行它们。
翻译自: https://www.sqlshack.com/working-with-azure-sql-databases-using-azure-cli-2-0/
azure3.0