Basic SQL Server Setup

let's connect your sql server database go ahead and gather the basic details database host or ip address database port # (default is 1433) sql server specifications required sql server versions 2012+ (talk w/ support if other versions needed) determine which way to connect tls or ssh tunneling as of july 2021 only the default tls approach is available this is just like if you were to connect from sql server management studio for example microsoft has some good https //docs microsoft com/en us/sql/database engine/configure windows/enable encrypted connections to the database engine?view=sql server ver15#transport layer security tls firewalls on your database server should allow incoming connections through the public internet on your sql server port (typically 1433 unless your network guys have changed it) be sure to whitelist our datalakehouse io ip addresses for your database server network access instructions (basic connection setup) scroll down to ensure you review all steps, as needed remember datalakehouse io connects to your database instance with credentials supplied by you we store your credentials securely with our bank grade protocols grant the new user permissions on database, schemas, tables the new user needs select permissions for the database, schemas, tables, or any specified columns that will be synchronized we recommend granting select access to everything in your specified database for this connection remember if you need to sync more databases, you need to create a separate source connection save the credentials somewhere and use them in the next steps please see other grant select on schema and tables in our documentation, if you need to get more granular for security purposes enter your credentials and other information in the fields enter in the name/alias field, the name you'll use within datalakehouse 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 you load in this connection so if your database has a schema named 'dbo' the target connection when synced will have a schema in that 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 public server name or the ip address (most customers use the ip for this field) 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 datalakehouse 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 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 enable change tracking or change data capture to capture incremental loads of the database records being synchronized and to reduce cost we use this native feature of sql server through change tracking (ct) or change data capture (cdc) you could enable both if that is your desire but most companies set up one or the other based on the needs/content of the table you must set up at least one of these on each table that you require to be synchronized determine which you will use and on which tables use cdc typically if you want to capture incremental changes when you are synchronizing data incrementally through the day (or more inline with the frequency of data change) we recommend that you do not use the net changes option when setting up cdc on your tables using the ms sql server commands use change tracking (ct) typically when you just care about the change that happens in general during the sync frequency, or said another way, just getting the change at the end of the day ensure setup of allow tcp/ip protocol is enabled remember datalakehouse io connects to your database instance with credentials supplied by you we store your credentials securely with our bank grade protocols datalakehouse io will connect through ssh tunnel tcp/ip or via standard internet traffic you must have enabled sql server configuration to provide access to your sql server database(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) to verify or setup port configuration on your instance via the sql server configuration, please follow these instructions on a standard managed sql server machine, access your sql server configuration manager and access the sql server network configuration expand and access "protocols for mssqlserver" from the left menu ensure that tcp/p is with an enabled status (if it is disabled, right click and select 'enable') confirm the port and the ip address by right clicking on tcp/ip > properties click on the ip addresses tab and scroll down to the ipall section confirm the ip address which is usually 1433 enter the ip address of 1433 if not previously entered already click the "apply" button, then click the "ok" button on the prompt return to the main area of the sql server configuration manager and expand sql native client xx x configuration click on the client protocols option then in the right side ensure the tcp/ip status is set to enabled right click on the tcp/ip row and verify the default port settings this is typically 1433, but confirm the port or enter 1433, then click the "apply" button return to the main area of the sql server configuration manager, and in the left side find, one of the top options, "sql server services" right click on "sql server services," then click the "restart" option this will confirm all the above (especially if any settings were modified) the restart should typically be complete in under 90 seconds reference for the above from microsoft sql server documentation https //docs microsoft com/en us/sql/database engine/configure windows/configure a server to listen on a specific tcp port?view=sql server ver15