Edit

Quickstart: Create a dedicated SQL pool (formerly SQL DW) with Azure PowerShell

  • Article

Create an dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics using Azure PowerShell.

Prerequisites

If you don't have an Azure subscription, create a free Azure account before you begin.

Important

Creating a dedicated SQL pool (formerly SQL DW) may result in a new billable service. For more information, see Azure Synapse Analytics pricing.

Note

We recommend that you use the Azure Az PowerShell module to interact with Azure. See Install Azure PowerShell to get started. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.

Sign in to Azure

Sign in to your Azure subscription using the Connect-AzAccount command and follow the on-screen directions.

Connect-AzAccount

To see which subscription you're using, run Get-AzSubscription.

Get-AzSubscription

If 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 = "WestEurope"
# 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

Create an Azure resource group using the New-AzResourceGroup command. A resource group is a container into which Azure resources are deployed and managed as a group. The following example creates a resource group named myResourceGroup in the westeurope location.

New-AzResourceGroup -Name $resourcegroupname -Location $location

Create a server

Create a logical SQL server using the New-AzSqlServer command. A server contains a group of databases managed as a group. 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

Create an server-level firewall rule using the New-AzSqlServerFirewallRule command. A server-level firewall rule allows an external application, such as SQL Server Management Studio or the SQLCMD utility to connect to a dedicated SQL pool (formerly SQL DW) through the dedicated SQL pool service firewall.

In the following example, the firewall is only opened for other Azure resources. To enable external connectivity, change the IP address to an appropriate address for your environment. 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

Note

SQL endpoints communicate over port 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. If so, you won't be able to connect to your server unless your IT department opens port 1433.

Create a dedicated SQL pool (formerly SQL DW)

The following example creates a dedicated SQL pool (formerly SQL DW) using the previously defined variables. It specifies the service objective as DW100c, which is a lower-cost starting point for your dedicated SQL pool (formerly SQL DW).

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: 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: The name of the dedicated SQL pool (formerly SQL DW) that you're creating.
  • ServerName: The name of the server that you're using for creation.
  • ResourceGroupName: Resource group you're using. To find available resource groups in your subscription use Get-AzureResource.
  • Edition: Must be "DataWarehouse" to create a dedicated SQL pool (formerly SQL DW).

Optional Parameters are:

  • CollationName: The default collation if not specified is SQL_Latin1_General_CP1_CI_AS. Collation can't be changed on a database.
  • MaxSizeBytes: The default max size of a database is 240TB. The max size limits rowstore data. There is unlimited storage for columnar data.

For more information on the parameter options, see New-AzSqlDatabase.

Clean up resources

Other quickstart tutorials in this collection build upon this quickstart.

Tip

If you plan to continue on to work with later quickstart tutorials, don't clean up the resources created in this quickstart. 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

You've now created a dedicated SQL pool (formerly SQL DW), created a firewall rule, and connected to your dedicated SQL pool. To learn more, continue to the Load data into a dedicated SQL pool article.