Is there a guide on how to configure airbyte for l...
# replication-ask-ai
y
Is there a guide on how to configure airbyte for large data ingestion (hundreds of tables with TB of data?) I am ingesting data from sql server to redshift and it's terribly slow compared to other product that we are evaluating against.
k
A message from kapa.ai
Yes, there are guides and tutorials on how to configure Airbyte for large data ingestion from SQL Server to Redshift. Here are the key steps: 1. Configuring SQL Server as the Airbyte source: You need to ensure that the DB instance's public accessibility is set to Yes to allow external connections. You also need to update the security group to allow connections from Airbyte Cloud IP and your local machine. [Source](https://airbyte.com/tutorials/replicate-sql-server-database-to-redshift) 2. Setting up Redshift as the Airbyte destination: You need to allow connections from Airbyte Cloud to your Redshift cluster. This involves noting down the endpoint for your cluster, editing the inbound rules for the security group for your Redshift cluster, and enabling publicly accessible settings. [Source](https://airbyte.com/tutorials/replicate-sql-server-database-to-redshift) 3. Setting up a SQL Server to Redshift connection: You can set the Replication frequency depending on how often you want Airbyte to replicate your data. You can also choose which tables to sync and set the sync frequency and the sync mode for each table individually. [Source](https://airbyte.com/tutorials/replicate-sql-server-database-to-redshift) Airbyte uses two replication strategies for Redshift: INSERT and COPY. The COPY strategy, which replicates data by first uploading data to an S3 bucket and issuing a COPY command, is recommended for large data loads as it scales well. Airbyte automatically picks an approach depending on the given configuration. [Source](https://docs.airbyte.com/integrations/destinations/redshift) If you're experiencing slow data ingestion, it might be worth checking your sync settings and ensuring you're using the COPY strategy for Redshift. Also, consider using the [Change Data Capture (CDC)](https://airbyte.com/blog/change-data-capture-definition-methods-and-benefits) method, which Airbyte supports, for efficient data replication. If you're still facing issues, consider reaching out to the [Airbyte community](https://airbyte.io/community) for further assistance.
g
Hey, you might have more luck in the #C021JANJ6TY channel when it comes to optimizing a specific connection. Do you know where the bottleneck is in your current load? There’s no one answer here because it depends on the setup of your sqlserver, Airbyte instance, and redshift
y
I have ruled out the source (sql server) as the issue, and network is not saturated. What I realized is that Airbyte chooses to ingest data into destination and normalize after the fact. That puts load on our destination and I am pretty sure that is a good part of the performance slowness. but I am not sure how to quantify it. I am comparing the performance against existing commercial product which is way faster using same source but much smaller destination
g
I would try disabling normalization for that connection and comparing the performance. I’m guessing if you’re moving that much data, you might not want the default normalization anyway. What commercial tool are you testing out of curiosity?
y
we are existing fivetran user