快速入门:使用 Azure PowerShell 创建并查询 Synapse SQL 池Quickstart: Create and query a Synapse SQL pool with Azure PowerShell

使用 Azure PowerShell 在 Azure Synapse Analytics 中创建 Synapse SQL 池(数据仓库)。Create an Synapse SQL pool (data warehouse) in Azure Synapse Analytics using Azure PowerShell.

先决条件Prerequisites

如果没有 Azure 订阅,请在开始前创建一个试用帐户If you don't have an Azure subscription, create a trial account before you begin.

重要

创建 SQL 池可能会产生新的计费服务。Creating a SQL pool may result in a new billable service. 有关详细信息,请参阅 Azure Synapse Analytics 定价For more information, see Azure Synapse Analytics pricing.

备注

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

登录 AzureSign in to Azure

使用 Connect-AzAccount 命令登录到 Azure 订阅,并按照屏幕上的说明进行操作。Sign in to your Azure subscription using the Connect-AzAccount command and follow the on-screen directions.

Connect-AzAccount -EnvironmentName AzureChinaCloud

若要查看正在使用的订阅,请运行 Get-AzSubscriptionTo see which subscription you're using, run Get-AzSubscription.

Get-AzSubscription

如果需要使用与默认订阅不同的订阅,请运行 Set-AzContextIf you need to use a different subscription than the default, run Set-AzContext.

Set-AzContext -SubscriptionName "MySubscription"

创建变量Create variables

定义在本快速入门的脚本中使用的变量。Define variables for use in the scripts in this quickstart.

# The data center and resource name for your resources
$resourcegroupname = "myResourceGroup"
$location = "ChinaEast"
# The server name: Use a random value or replace with your own value (don't capitalize)
$servername = "server-$(Get-Random)"
# Set an admin name and password for your database
# The sign-in information for the server
$adminlogin = "ServerAdmin"
$password = "ChangeYourAdminPassword1"
# The ip address range that you want to allow to access your server - change as appropriate
$startip = "0.0.0.0"
$endip = "0.0.0.0"
# The database name
$databasename = "mySampleDataWarehouse"

创建资源组Create a resource group

使用 New-AzResourceGroup 命令创建 Azure 资源组Create an Azure resource group using the New-AzResourceGroup command. 资源组是在其中以组的形式部署和管理 Azure 资源的容器。A resource group is a container into which Azure resources are deployed and managed as a group. 以下示例在 chinaeast 位置创建名为 myResourceGroup 的资源组。The following example creates a resource group named myResourceGroup in the chinaeast location.

New-AzResourceGroup -Name $resourcegroupname -Location $location

创建服务器Create a server

使用 New-AzSqlServer 命令创建逻辑 SQL 服务器Create a logical SQL server using the New-AzSqlServer command. 服务器包含作为组进行管理的一组数据库。A server contains a group of databases managed as a group. 以下示例使用管理员用户名 ServerAdmin 和密码 ChangeYourAdminPassword1 在资源组中创建随机命名的服务器。The following example creates a randomly named server in your resource group with an admin user named ServerAdmin and a password of ChangeYourAdminPassword1. 根据需要替换这些预定义的值。Replace these pre-defined values as desired.

New-AzSqlServer -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

配置服务器级防火墙规则Configure a server-level firewall rule

使用 New-AzSqlServerFirewallRule 命令创建服务器级防火墙规则Create an server-level firewall rule using the New-AzSqlServerFirewallRule command. 服务器级防火墙规则允许外部应用程序(例如 SQL Server Management Studio 或 SQLCMD 实用程序)通过 SQL 池服务防火墙连接到 SQL 池。A server-level firewall rule allows an external application, such as SQL Server Management Studio or the SQLCMD utility to connect to a SQL pool through the SQL pool service firewall.

在以下示例中,防火墙仅对其他 Azure 资源开放。In the following example, the firewall is only opened for other Azure resources. 要启用外部连接,请将 IP 地址更改为适合你环境的地址。To enable external connectivity, change the IP address to an appropriate address for your environment. 若要开放所有 IP 地址,请使用 0.0.0.0 作为起始 IP 地址,使用 255.255.255.255 作为结束地址。To open all IP addresses, use 0.0.0.0 as the starting IP address and 255.255.255.255 as the ending address.

New-AzSqlServerFirewallRule -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -FirewallRuleName "AllowSome" -StartIpAddress $startip -EndIpAddress $endip

备注

SQL 终结点通过端口 1433 进行通信。SQL endpoints communicate over port 1433. 如果尝试从企业网络内部进行连接,则该网络的防火墙可能不允许经端口 1433 的出站流量。If you're trying to connect from within a corporate network, outbound traffic over port 1433 may not be allowed by your network's firewall. 如果是这样,则无法连接到服务器,除非 IT 部门打开了端口 1433。If so, you won't be able to connect to your server unless your IT department opens port 1433.

创建 SQL 池Create a SQL pool

以下示例使用以前定义的变量创建 SQL 池。The following example creates a SQL pool using the previously defined variables. 它将服务目标指定为 DW100c,这是针对 SQL 池的低成本起点。It specifies the service objective as DW100c, which is a lower-cost starting point for your SQL pool.

New-AzSqlDatabase `
    -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -DatabaseName $databasename `
    -Edition "DataWarehouse" `
    -RequestedServiceObjectiveName "DW100c" `
    -CollationName "SQL_Latin1_General_CP1_CI_AS" `
    -MaxSizeBytes 10995116277760

所需的参数有:Required Parameters are:

  • RequestedServiceObjectiveName:请求的数据仓库单位的数量。RequestedServiceObjectiveName: The amount of data warehouse units you're requesting. 增加此数量会增加计算成本。Increasing this amount increases compute cost. 有关支持值的列表,请参阅内存和并发限制For a list of supported values, see memory and concurrency limits.
  • DatabaseName:要创建的 SQL 池的名称。DatabaseName: The name of the SQL pool that you're creating.
  • ServerName:用于创建过程的服务器名称。ServerName: The name of the server that you're using for creation.
  • ResourceGroupName:要使用的资源组。ResourceGroupName: Resource group you're using. 若要查找订阅中可用的资源,请使用 Get-AzureResource。To find available resource groups in your subscription use Get-AzureResource.
  • Edition:必须是“DataWarehouse”才能创建 SQL 池。Edition: Must be "DataWarehouse" to create a SQL pool.

可选参数有:Optional Parameters are:

  • CollationName:在不指定的情况下,默认排序规则是 SQL_Latin1_General_CP1_CI_AS。CollationName: The default collation if not specified is SQL_Latin1_General_CP1_CI_AS. 在数据库上不能更改排序规则。Collation can't be changed on a database.
  • MaxSizeBytes:数据库的默认最大大小为 240TB。MaxSizeBytes: The default max size of a database is 240TB. 最大大小限制行存储数据。The max size limits rowstore data. 列数据的存储不受限制。There is unlimited storage for columnar data.

有关参数选项的详细信息,请参阅 New-AzSqlDatabaseFor more information on the parameter options, see New-AzSqlDatabase.

清理资源Clean up resources

本系列中的其他快速入门教程是在本快速入门的基础上制作的。Other quickstart tutorials in this collection build upon this quickstart.

提示

如何打算继续学习后续快速入门教程,请不要清除本快速入门中创建的资源。If you plan to continue on to work with later quickstart tutorials, don't clean up the resources created in this quickstart. 如果不打算继续,请在 Azure 门户中执行以下步骤,删除本快速入门创建的所有资源。If you don't plan to continue, use the following steps to delete all resources created by this quickstart in the Azure portal.

Remove-AzResourceGroup -ResourceGroupName $resourcegroupname

后续步骤Next steps

现已创建 SQL 池、创建防火墙规则、连接到 SQL 池并运行了几个查询。You've now created a SQL pool, created a firewall rule, connected to your SQL pool, and run a few queries. 若要了解详细信息,请继续阅读将数据加载到 SQL 池一文。To learn more, continue to the Load data into SQL pool article.