Azure Synapse Setup

let's connect your azure synapse database go ahead and gather the basic details about azure synapse database host/servername user information azure synapse pre requisites have or create a dedicated sql pool (formerly sql dw) update the firewalls on your azure synapse workspace to ensure to whitelist our datalakehouse io ip addresses for the required network access create a specific azure synapse role and user for dlh io integration a new or specific user is required, and it should not be the default service administrator because that administrator user is fixed at a small memory resource class which cannot be changed, thus limiting your performance pre requisites instructions create your dedicated sql pool / sql warehouse or capture an existing sql pool's details in a dedicated sql pool, the database name is usually the same name as the dedicated sql pool itself capture the server name of the sql pool you just created, and be sure you get the fully qualified domain name (fqdn) of the sql pool server it is also known as the workspace sql endpoint this is usually something like, mysynapseaccount sql azuresynapse net update the azure firewall setting to allow the dlh io ip addresses for incoming connections to your azure synapse instance, if needed to restrict access for security reasons, otherwise leave it open to all public trafic capture your azure synapse analytics connection details by clicking on the sql pool link from the left side after navigating to manage > analytics pool (for example "built in" or the server name from the sql pool you created in the previous step) create the dlh io user with your default synapse analytics administrator role or similar, using your favorite ide or synapse studio you can also use a basic sql script from azure synapse analytics by clicking develop from the left menu then clicking the "+" icon to add a "sql script" for example run this code against your synapse workspace sql pool using the sql script for the username , we suggest, dlh user svc or similar and use the same for the login username switch to the master database, in your connection create login \<username> with password = '\<password>'; \ switch to your sql pool database (same name as your sql pool usually) create user \<username> for login \<username>; grant create table, create schema, select, insert, alter, update, delete, administer database bulk operations to \<username>; assign the new synapse workspace user to a resource class role that allows greater memory for any workload service level run the following statement for our basic recommendation of memory to service level alignment, replacing \<resource class name> with the value staticrc20 , and \<username> with the username created in the previous step switch to your sql pool database (same name as your sql pool usually) exec sp addrolemember '\<resource class name>', '\<username>' (optional) create a new azure blob storage docid\ xzsw42q dritzyjlljjoq source connector in dlh io, which will allow any transient data from the synchronization to run through your cloud storage instead of the default dlh io storage instructions (basic connection form setup) scroll down to ensure you review all steps, as needed remember dlh io connects to your instance with credentials supplied by you we may store your credentials securely with our bank grade protocols to maintain speed and consistency of your data pipelines get all the information from the pre requisite steps enter your credentials and other information in the fields enter in the name/alias field, the name you'll use within dlh io to differentiate this connection from others enter in the target schema prefix field, the prefix of schema(s) that gets created on your destination target connection database for each of the schemas or connectors you load into this as a target connection so, if your source connection to be synchronized is a database and it has a schema named 'dbo' this target connection database when the data is synced will have a schema created in the target database named the value of this field + ' dbo' alphanumeric characters only prefix must start and end with a letter but can contain an underscore( ) enter in the server/host field, the name of the workspace server name enter in the port field, where this database is accessible and the firewall restrictions are open enter in the database field, the name of the database to connect enter in the username/alias field, the username of user you created in the steps above to give access to datalakehouse io leave the auth type field alone it is set to password because dlh io is using tls and requires username and password credentials to access the database enter in the password field, the password for the user you created in the steps above click save & test this attempts to connect to your synapse database with the credentials provided a message of success or failure will be shown if successful, you'll be prompted with the schema objects of the database and will need to complete the final steps for configuration shown below if the test connection fails, the connection is still saved, but you will need to correct the failure based on the information provided within the error message instructions (continued & final setup) this section of steps ensures you have coverage of other important steps required on your database side and in datalakehouse io once you have completed the above test connection successfully ensure setup of allow tcp/ip protocol and whitelisting of ips remember datalakehouse io connects to your database instance with credentials supplied by you we store your credentials securely with our bank grade protocols dlh io will connect through ssh tunnel tcp/ip or via standard internet traffic you must have enabled access to your azure synapse workspace(s) and ensure that you have whitelisted/grantlisted our datalakehouse io ip addresses https //datalakehouse io/whitelist ip addresses that will access your sql server database(s)