Quickstart: Create and query a dedicated SQL pool (formerly SQL DW) in Azure synapse Analytics using the Azure portal

Quickly create and query a dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics using the Azure portal.

Important

This quickstart helps you to create a dedicated SQL pool (formerly SQL DW). To create a dedicated SQL pool in Azure Synapse Analytics workspace and take advantage of the latest features and integration in your Azure Synapse Analytics workspace, instead use Quickstart: Create a dedicated SQL pool using Synapse Studio.

Prerequisites

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

   Note

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

2. Download and install the newest version of SQL Server Management Studio (SSMS). Note: SSMS is only available on Windows based platforms, see the full list of supported platforms.

Sign in to the Azure portal

Sign in to the Azure portal.

Create a SQL pool

Data warehouses are created using dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics. A dedicated SQL pool (formerly SQL DW) is created with a defined set of compute resources. The database is created within an Azure resource group and in a logical SQL server.

Follow these steps to create a dedicated SQL pool (formerly SQL DW) that contains the AdventureWorksDW sample data.

1. Select Create a resource in the upper left-hand corner of the Azure portal.

   

2. In the search bar, type "dedicated SQL pool" and select dedicated SQL pool (formerly SQL DW). Select Create on the page that opens.

   

3. In Basics, provide your subscription, resource group, dedicated SQL pool (formerly SQL DW) name, and server name:

   Setting	Suggested value	Description
   Subscription	Your subscription	For details about your subscriptions, see Subscriptions.
   Resource group	myResourceGroup	For valid resource group names, see Naming rules and restrictions.
   SQL pool name	Any globally unique name (An example is mySampleDataWarehouse)	For valid database names, see Database Identifiers.
   Server	Any globally unique name	Select existing server, or create a new server name, select Create new. For valid server names, see Naming rules and restrictions.

   

4. Under Performance level, select Select performance level to optionally change your configuration with a slider.

   

   For more information about performance levels, see Manage compute in Azure Synapse Analytics.

5. Select Additional Settings, under Use existing data, choose Sample so that AdventureWorksDW will be created as the sample database.

   

6. Now that you've completed the Basics tab of the Azure Synapse Analytics form, select Review + Create and then Create to create the SQL pool. Provisioning takes a few minutes.

   

   

7. On the toolbar, select Notifications to monitor the deployment process.

   

Create a server-level firewall rule

The Azure Synapse service creates a firewall at the server-level. This firewall prevents external applications and tools from connecting to the server or any databases on the server. To enable connectivity, you can add firewall rules that enable connectivity for specific IP addresses. Follow these steps to create a server-level firewall rule for your client's IP address.

Note

Azure Synapse communicates over port 1433. If you are trying to connect from within a corporate network, outbound traffic over port 1433 might not be allowed by your network's firewall. If so, you cannot connect to your server unless your IT department opens port 1433.

1. After the deployment completes, select All services from the menu. Select Databases, select the star next to Azure Synapse Analytics to add Azure Synapse Analytics to your favorites.

2. Select Azure Synapse Analytics from the left-hand menu and then select mySampleDataWarehouse on the Azure Synapse Analytics page. The overview page for your database opens, showing you the fully qualified server name (such as sqlpoolservername.database.windows.net) and provides options for further configuration.

3. Copy this fully qualified server name for use to connect to your server and its databases in this and other quick starts. To open server settings, select the server name.

   

4. Select Show firewall settings.

   

5. The Firewall settings page for the server opens.

   

6. To add your current IP address to a new firewall rule, select Add client IP on the toolbar. A firewall rule can open port 1433 for a single IP address or a range of IP addresses.

7. Select Save. A server-level firewall rule is created for your current IP address opening port 1433 on the server.

8. Select OK and then close the Firewall settings page.

You can now connect to the server and its SQL pools using this IP address. The connection works from SQL Server Management Studio or another tool of your choice. When you connect, use the ServerAdmin account you created previously.

Important

By default, access through the SQL Database firewall is enabled for all Azure services. Select OFF on this page and then select Save to disable the firewall for all Azure services.

Get the fully qualified server name

Get the fully qualified server name for your server in the Azure portal. Later you use the fully qualified name when connecting to the server.

1. Sign in to the Azure portal.

2. Select Azure Synapse Analytics from the left-hand menu, and select your workspace on the Azure Synapse Analytics page.

3. In the Essentials pane in the Azure portal page for your database, locate and then copy the Server name. In this example, the fully qualified name is sqlpoolservername.database.windows.net.

   

Connect to the server as server admin

This section uses SQL Server Management Studio (SSMS) to establish a connection to your server.

1. Open SQL Server Management Studio.

2. In the Connect to Server dialog box, enter the following information:

   Setting	Suggested value	Description
   Server type	Database engine	This value is required
   Server name	The fully qualified server name	Here's an example: sqlpoolservername.database.windows.net.
   Authentication	SQL Server Authentication	SQL Authentication is the only authentication type that is configured in this tutorial.
   Login	The server admin account	Account that you specified when you created the server.
   Password	The password for your server admin account	Password that you specified when you created the server.

   

3. Select Connect. The Object Explorer window opens in SSMS.

4. In Object Explorer, expand Databases. Then expand mySampleDatabase to view the objects in your new database.

   

Run some queries

It is not recommended to run large queries while being logged as the server admin, as it uses a limited resource class. Instead configure Workload Isolation as illustrated in the tutorials.

Azure Synapse Analytics uses T-SQL as the query language. To open a query window and run some T-SQL queries, use the following steps in SQL Server Management Studio (SSMS):

1. In Object Explorer, right-click mySampleDataWarehouse and select New Query. A new query window opens.

2. In the query window, enter the following command to see a list of databases.

   SELECT * FROM sys.databases
   

3. Select Execute. The query results show two databases: master and mySampleDataWarehouse.

   

4. To look at some data, use the following command to see the number of customers with last name of Adams that have three children at home. The results list six customers.

   SELECT LastName, FirstName FROM dbo.dimCustomer
   WHERE LastName = 'Adams' AND NumberChildrenAtHome = 3;
   

   

Clean up resources

You're being charged for data warehouse units and data stored your dedicated SQL pool (formerly SQL DW). These compute and storage resources are billed separately.

• If you want to keep the data in storage, you can pause compute when you aren't using the dedicated SQL pool (formerly SQL DW). By pausing compute, you're only charged for data storage. You can resume compute whenever you're ready to work with the data.

• If you want to remove future charges, you can delete the dedicated SQL pool (formerly SQL DW).

Follow these steps to clean up resources you no longer need.

1. Sign in to the Azure portal, select your dedicated SQL pool (formerly SQL DW).

   

2. To pause compute, select the Pause button. When the dedicated SQL pool (formerly SQL DW) is paused, you see a Resume button. To resume compute, select Resume.

3. To remove the dedicated SQL pool (formerly SQL DW) so you aren't charged for compute or storage, select Delete.

4. To remove the server you created, select sqlpoolservername.database.windows.net in the previous image, and then select Delete. Be careful with this deletion, since deleting the server also deletes all databases assigned to the server.

5. To remove the resource group, select myResourceGroup, and then select Delete resource group.

Want to optimize and save on your cloud spending?

Azure services cost money. Azure Cost Management helps you set budgets and configure alerts to keep spending under control. Analyze, manage, and optimize your Azure costs with Cost Management. To learn more, see the quickstart on analyzing your costs.

Next steps

• To learn more about loading data into your dedicated SQL pool (formerly SQL DW), continue to the Load data into a dedicated SQL pool article.