Azure SQL Integration
In this article, we’ll integrate a Microsoft Azure SQL Database data source with Y42.
Part of the Azure SQL family, Azure SQL Database is an intelligent, scalable, relational database service built for the cloud.
Overview
Authentication
Your database credentials allow you to log into your database, either locally or remotely. A credential is a record that contains the authentication information. This information is used internally by server hosting the SQL database. The credentials entail the host address, the IP hosting the server/database, the port number, the default port on Azure SQL Database is 1433, the user and the password.
You may also use SSH tunneling or use a Y42 IP address [35.198.102.73, 35.234.125.155, 35.198.107.177, 35.242.197.68, 35.241.201.46 ] for whitelisting.
SSH tunneling is a method of transporting arbitrary networking data over an encrypted SSH connection. The SSH connection is used by the application to connect to the application server. With tunneling enabled, the application contacts to a port on the local host that the SSH client listens on. The SSH client then forwards the application over its encrypted tunnel to the server. See the configuration example page for detailed configuration instructions. The SSH command line options and SSH server configuration file pages may also be helpful.
To make the SSH connection work when connecting in Y42, you will need to chain the SSH tunnel configuration and the IP whitelisting as illustrated below:
Meanwhile, whitelisting means taking manual steps to ensure that a certain IP address isn't blocked from accessing your application by some automated security process. Whitelisting IP addresses enables you to control who can (or cannot) access your database. You can add an IP to Whitelist on the security configurations of your database.
Import Settings
This integration has no import settings.
Schema
There is no predefined schema for this integration. The integration will dynamically load the tables & columns which are defined within your specific system.
Updating your data
You have the option of scheduling Full imports or Incremental Imports by the month, weeks, days, and even by the hour. With the incremental import you will only add the new rows to that table, while a full import will actually re-import the entire table completely.
Incremental imports significantly increase the import speed, we import your table incrementally based on the last imported state e.g. id - If the table does not have a state, a full import will be triggered - This is done automatically.
We recommend therefore to schedule an incremental import regularly, but also schedule a full import with a certain frequency -- this is because only the full import will guarantee that old rows that have been updated on the data source will also get updated on your Y42 integration destination.
Azure SQL Database Setup Guide:
- On Integrate, click on "Add..." to search for Microsoft Azure SQL Database and select it.
- Name your integration.
- Authorize access with your database credentials. Use SSH tunnel(s) to connect or use a Y42 IP for whitelisting.
- After authentication, you are good to go and start importing your tables.
- Select the tables you need and click import. You can start accessing the tables once the status is “Ready”.