Azure Synapse Analytics

You need an Azure portal account to set up the Azure Synapse Analytics instance. Once you have an account, you need to create a Synapse workspace.

1. Access the Azure portal in one of the following ways

• Sign in to an existing Azure account:

  1. Navigate to azure.microsoft.com and click Sign in.

  2. Specify the email address that has access to the Azure portal.

  3. Specify your password and click Sign in.

• Sign up for an Azure free trial, which credits $200 and is valid for 30-days.

  Note: A free trial allows you to access Azure services, such as Azure Active Directory (Azure AD) and Synapse Analytics. If you decide to continue using the Azure services, you can move to a pay as you go model.

  1. Navigate to azure.microsoft.com and click Start free.

  2. In the Sign in dialog, do one of the following:

     • Log in using an existing Microsoft account, a phone number, or a Skype account.

       

       Note: You should not have already signed up for a free trial with this account.

     • Click Create one! to create an account by performing the following steps:

       

       1. Specify an email address and click Next. You can also set up a new email address at this time.

       2. Specify a password and click Next.

       3. Complete the required steps to create your Azure account

  3. Log in to the Azure portal and add a subscription to activate your free trial. A subscription must exist to be able to create an Azure Synapse workspace.

2. Create a Synapse workspace

Note: You must have an active subscription with billing enabled and a user with the Azure RBAC Contributor role in that subscription to perform the steps listed in this section.

1. Log in to the Azure portal, and in the Search bar, type azure synapse.

2. In the search results, under Services, click Azure Synapse Analytics.

   

3. On the Azure Synapse Analytics page, click Create.

   

4. On the Create Synapse Workspace page, Basics tab, specify the following to create your workspace:

   

   • Subscription: A container comprising related business and technical resources that are used and billed together. Select the Azure subscription in which you want to create your workspace from the drop-down.

   • Resource group: A collection of related resources that may be logically grouped based on factors such as geography or team functions. Select an existing resource group from the drop-down or create one now.

     Note: To access an existing resource group, you must have the Azure RBAC Owner or Contributor role.

   • Managed resource group: A container that holds the supporting resources created by Azure Synapse Analytics for your workspace. A managed resource group is created for you and a default name is assigned to it when your workspace is created. You can also specify a name of your choice for it now.

   • Workspace name: A unique name for your workspace.

   • Region: The geographical location in which you want to create your workspace. Select an Azure region from the drop-down list. It is recommended to co-locate your workspace with your data and client applications such as Hevo.

   • Select Data Lake Storage Gen2: The storage account and container that Azure Synapse uses to store the workspace data. Only Azure Data Lake Storage (ADLS) Gen2 accounts for which hierarchical namespace (HNS) is enabled are displayed. In these accounts, data objects and files are organized in a hierarchy of directories for efficient access.

     Select your account in one of the following ways:

     • From subscription:

       • Account name: Specify a unique name for your storage account if you are creating one now. Alternatively, you can select an existing storage account available in your subscription from the drop-down list. Read Prepare the existing storage account for information on how to use it with Azure Synapse Analytics.

       • File system name: Specify a unique name for your data storage container if you want to create one. If you select an existing storage account, this field is populated with the name of the storage container associated with it.

     • Manually via URL:

       • Account name: Specify the name of an existing HNS-enabled ADLS Gen2 account, which is prefixed to dfs.core.windows.net to create its URL. For example, if the URL of your ADLS Gen2 account is hevodocstorage1.dfs.core.windows.net, the account name is hevodocstorage1. You can obtain this account name from your Azure account administrator.

       • File system name: Specify a unique name for your data storage container.

   • Click Next: Security >.

5. (Required) In the Security tab, configure the options to secure your workspace:

   

   • Authentication method: Select Use both local and Azure Active Directory (Azure AD) authentication.

   • SQL Server admin login: Specify a unique name for the user with admin access to the workspace’s SQL pools. Default value: sqladminuser.

   • SQL Password and Confirm Password: Specify a strong password for your admin user.

   • Allow network access to Data Lake Storage Gen2 account: If selected, it gives your workspace network access to the ADLS Gen2 account, selected in the Basics tab. The check box is disabled for storage accounts that do not restrict network access.

   • Workspace encryption: An option to double-encrypt your data at rest in the workspace. Default value: Disable.

   • Click Next: Networking >.

6. (Optional) In the Networking tab, configure the network connectivity settings for your workspace:

   

   • Managed virtual network: An option to allow public network access to the workspace through a managed virtual network. Default value: Disable. Perform the additional steps displayed on the screen if you enable this option. Read Create an Azure Synapse workspace with a managed virtual network for more information.

   • Allow connections from all IP addresses: If enabled, it adds a default firewall rule, allowing connections from all IP addresses to your workspace. Default value: Enabled.

     Note: This option is only available for Synapse workspaces not managed through a virtual network.

   • Minimum TLS version (Non-editable): The TLS version needed by the workspace to access your storage account’s data. Default selection: TLS 1.2.

   • Click Next: Tags > if you want to categorize your resources, or click Review + create and skip to Step 8.

7. (Optional) In the Tags tab: Specify name-value pairs if you want to categorize your resources. Tagging enables you to consolidate resources for purposes such as billing.

8. In the Review + create tab: Review the specified configuration, and click Create to provision your workspace resources. The provisioning may take around 10-15 minutes.

You can use the dedicated SQL endpoint of this workspace as the server name, the SQL Server admin login as the username, and the SQL password as the password, while configuring your Azure Synapse Analytics Destination with the Enter Connection Settings Manually option.

Use the steps in this section to create a dedicated SQL pool from within an existing Synapse workspace. A dedicated SQL pool is the data warehouse in Azure Synapse Analytics into which your data is replicated.

1. Log in to the Azure portal, and in the Search bar, type azure synapse.

2. From the search results, under Services, click Azure Synapse Analytics.

   

3. On the Azure Synapse Analytics page, click on your Synapse workspace name. For example, asea-workspace-doc.

   

4. On the Overview page, click + New dedicated SQL pool.

   

5. On the New dedicated SQL pool page, Basics tab, provide the essential information for your dedicated SQL pool:

   

   • Dedicated SQL pool name: A unique name for your data warehouse.

   • Geo-redundant: An option to replicate your data to another data center in the Azure region of your workspace. Default value: No selection.

     Note: This option may not be displayed for certain Azure regions, such as South India. Read Regions with availability zones and no region pair.

   • Performance level: The size of your data warehouse. Use the slider to change the value. Default value: DW1000c. The Estimated price displays the cost based on this size.

   • Click Next Additional settings >.

6. (Required) In the Additional settings tab, customize the settings for your dedicated SQL pool:

   

   • Use existing data: The data source from which your dedicated SQL pool must be populated. Select Backup or Restore point if you want to restore data to your dedicated SQL pool. Default value: None.

   • Collation: The rules to sort and compare data in your dedicated SQL pool. Default value: SQL_Latin1_General_CP1_CI_AS. Read Help collations to obtain a list of all supported collations.

     Note: You cannot change this value after the dedicated SQL pool is created.

7. Click Review + create to review your configuration, and click Create to provision the resources for your dedicated SQL pool.

You can provide this dedicated SQL pool as your database name while configuring your Azure Synapse Analytics Destination with the Enter Connection Settings Manually option.

You can use any SQL tool that allows you to connect to your dedicated SQL pool (data warehouse) to create the database user and grant privileges to it. For example, Azure Data Studio or mssql for Visual Studio Code. In this section, we have used Azure Data Studio.

Perform the following steps:

1. Create a connection for your SQL database.

2. Create a login user and map it to a database user, to connect to your SQL database.

3. Grant privileges to the database user.

1. Create a connection for your SQL database

1. Obtain the connection settings of your SQL database.

2. Access Azure Data Studio.

3. In the left navigation pane, click Connections, and in the SERVERS sidebar, click the New Connection ( ) icon.

   

4. In the Connection Details sidebar, Input type field, select Parameters, and specify the following:

   

   • Connection type: The type of database. Default value: Microsoft SQL Server.

   • Server: The fully qualified server name. For example, asea-doc-ws.sql.azuresynapse.net.

   • Authentication type: The method with which you want to connect. Default value: SQL Login. Here, we will connect using SQL authentication.

   • User name: The SQL admin user for your database. For example, sqladmin.

   • Password: The password for the specified user name.

   • Database: The database to which you want to connect. For example, master.

   • Name (optional): A name for your connection. For example, MasterDB.

5. Click Connect.

2. Create a database user

1. Access Azure Data Studio and in the SERVERS sidebar, select the connection name that you created above.

   

2. Click the Expand chevron to open Databases, System Databases, and then master.

3. Right-click on master, and click New Query.

   

4. In the query editor, enter the following command to create a login user and click Run:

   CREATE LOGIN [<login_name>] WITH PASSWORD=N'<strong_password>'
   GO
   

   Note: Replace the placeholder values in the command above with your own. For example, <login_name> with hevouser.

5. At the top of the query editor, select your SQL database name from the drop-down list. For example, master.

   

6. In the query editor, enter the following command to create a database user for the login user in the selected database:

   CREATE USER [<username>] FOR LOGIN [<login_name>]
   GO
   

   Note: Replace the placeholder values in the command above with your own. For example, <username> with jerome.

3. Grant privileges to the database user

The following table lists the privileges that Hevo requires to connect to and load data into your Azure Synapse Analytics Destination:

Perform the following steps to grant the privileges:

1. Access Azure Data Studio and in the SERVERS sidebar, select the connection name that you created above.

   

2. Click the Expand chevron to open Databases, <your SQL database name>. For example, docsqlpool.

3. Right-click on your SQL database name, and click New Query.

   

4. In the query editor, enter the following commands to grant privileges to the database user created above and click Run:

   GRANT ALTER, SELECT, INSERT, UPDATE ON SCHEMA::<schema_name> TO <username>
   GO
   GRANT CREATE TABLE, INSERT, SELECT, UPDATE ON DATABASE::<database_name> to <username>
   GO
   GRANT ADMINISTER DATABASE BULK OPERATIONS on DATABASE::<database_name> to <username>
   GO
   

   Note: Replace the placeholder values in the commands above with your own. For example, <schema_name> with dbo.