Use Azure Synapse Dedicated SQL Pool for Prepare Instance Storage

Note: This article is intended for Azure database administrators. Ensure the following tasks are completed by someone with the appropriate Azure database administration privileges.

Note: Azure Synapse Dedicated SQL Pool storage is available as part of the standard, premium, or enterprise package.

For more information on standard, premium, or enterprise package, contact Product Support.

Azure Synapse Dedicated SQL Pool (previously Azure SQL Data Warehouse) is a massively parallel processing (MPP) database that can be used as Prepare instance storage. See What is Azure Synapse Dedicated SQL Pool? for an overview.

Create an Azure Synapse Dedicated SQL Pool

See the Microsoft documentation on how to create an Azure Synapse SQL Pool. The basic steps are as follows:

1. In the Azure portal, click Create a Resource and search for Dedicated SQL Pool.
   1. Use an existing Azure SQL Server or create a new one.
   2. Note the SQL Server admin username and password — these will be required later.
2. Create a server-level firewall rule.
   1. In the Azure SQL Server, click Firewalls and virtual networks, click Add client IP, and click Save.

Connect to the server

1. Get the fully qualified server name.
   1. On the Synapse Dedicated SQL Pool resource Overview page, locate the Server name and click to copy it.
2. Connect to the database using SQL Server Management Studio (SSMS).
   1. Server name: the fully qualified server name copied above.
   2. Database: the name of the Dedicated SQL Pool.
   3. Username & Password: the SQL Server administrator credentials.

Create SQL Server logins

Create SQL Server logins by running the following queries against the database in SSMS. See Authorise database access to SQL Database for more information.

Assign users to a resource class

By default, each user is a member of the dynamic resource class smallrc. Adjust the resource class depending on the workloads you will be performing. Smaller resource classes have a lower memory allocation but support higher concurrency. The xlargerc class can use up to 70% of available memory but is limited to 1–2 concurrent jobs. See Resource classes for workload management for more information.

Note: Be mindful of the concurrency limits of your resource class when configuring the maximum threads in your Jet Analytics Data Integration execution packages.

Sample statement:

EXEC sp_addrolemember 'mediumrc', 'USERNAME'

Create a master key

A master key is required before creating objects in the database. See CREATE MASTER KEY (Transact-SQL) for more information.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'STRONGPASSWORD';

Configure Dedicated SQL Pool storage

1. In the Add Prepare instance menu in the Jet Analytics Portal, set Server Storage Type to SQL — Dedicated SQL Pool.

   The Jet Analytics portal is managed by the insightsoftware Support team. For assistance or additional information, reach out to Product Support.

2. In the Server name box, enter the name of the server. For named instances, include both the server name and the instance name.
3. In the Database box, enter the name of the database to create.
   Note:

   Jet Analytics Data Integration will create this database automatically. After saving, open the instance in Jet Analytics Data Integration, right-click the instance, select Edit Instance…, and click Create Storage…

4. In the Authentication list, select the authentication mode:
   • Windows Authentication: Uses the logged-in Windows user's credentials.
   • SQL Server Authentication: Uses a SQL Server login. Enter the username and password in the corresponding fields.
   • Azure AD Password Authentication: Uses Azure AD credentials from a domain that is not federated with Azure AD. Enter the username and password.
   • Azure AD Integrated Authentication: Uses the logged-in Windows user's credentials, provided the user is signed in with Azure AD credentials from a domain federated with Azure AD.
5. In the Connection timeout box, enter the number of seconds to wait before terminating a connection attempt. Enter 0 to wait indefinitely.
6. In the Command timeout box, enter the number of seconds to wait before terminating a command. Enter 0 to wait indefinitely.
7. In the Encrypt connection list, configure encryption for the connection:
   • No: Communication is not encrypted (default).
   • Yes: Communication is encrypted and the server certificate is verified by a certificate authority.
   • Yes, trust server certificate: Communication is encrypted but the server certificate is not verified. Not recommended for public networks.
8. (Optional) Enter any additional connection string properties in the Additional connection properties box. These override values entered in the other fields.

Create storage and required objects

1. In Jet Analytics Data Integration, open the Prepare instance.
2. Right-click the instance, select Edit Instance…, and click Create Storage…

   

3. Right-click the data area and select Create required objects for SQL Data Area.

   

Troubleshooting

Error:An error occurred during executing this statement — DELETE FROM [dbo].

Right-click the Prepare instance in Jet Analytics Data Integration and select Create required objects for SQL DW as described in step 3 above.

Error:Please create a master key in the database or open the master key in the session before performing this operation.

Create the master key by running the SQL query directly against the database. See the Create a master key section above.

References

• Create an Azure Synapse SQL Pool (Microsoft Docs)
• Authorise database access to SQL Database (Microsoft Docs)
• Resource classes for workload management (Microsoft Docs)
• CREATE MASTER KEY (Transact-SQL)