Integrating your MSSQL database with Alooma is easy (but if you're using Change Tracking replication, make sure you have configured it first):
How do you want to connect to Alooma? If it's via an SSH server, check out how to connect via SSH. Otherwise, you'll need to whitelist access to Alooma's IP addresses:
Is your MSSQL hosted in Amazon? Check out how to whitelist IPs in Amazon.
Add and name your MSSQL input from the Plumbing screen and enter the following details:
Hostname of the MSSQL server (default port is 1433)
User name and Password
If your MSSQL server is behind an SSH server you can connect to MSSQL via SSH.
Choose the replication method you'd like to use for MSSQL replication:
For full dump/load replication, provide:
The database/schema you want to replicate.
A space- or comma- separated list of the names of the tables you want to replicate.
The frequency at which you'd like to replicate your tables. The more frequent, the more fresh your data will be, but the more load it puts on your MSSQL database.
For incremental replication, provide:
The database/schema you want to to replicate.
A set of table/update indicator column pairs for each table you want to replicate.
If you're using incremental replication for your database, and not using xmin, we recommend — especially for larger databases — that you create an index on the update indicator columns (
dtLastModifiedin SQL Server/Oracle, for example).
If you do not index the indicator column, your database will:
Create a potentially large temp table, which could end up filling the disk.
Sort the table at load, which is CPU intensive and will add time to the process.
Follow the instructions for creating an index here: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql
For log replication (Change Tracking), provide:
Make sure you performed the required set up steps for configuring Change Tracking on SQL Server.
Provide a space- or comma-separated list of the names of the tables you wish to replicate (or leave blank to replicate all tables) across all databases.
Choose whether you'd like to dump and load an initial snapshot of your tables into your target data warehouse. This option only works if you've selected specific tables, and works best for small tables (under 100M rows). If you have larger tables or want all tables, please contact [email protected].
Keep the mapping mode to the default of OneClick if you'd like Alooma to automatically map all MSSQL tables exactly to your target data warehouse. Otherwise, they'll have to be mapped manually from the Mapper screen.
That's it! You're done integrating MSSQL and Alooma.