Request for Beta Testers: Source MSSQL

[rodireich]

Certified Source MSSQL is open for early access!

Hi Airbyte community,

We are excited to inform you that the Airbyte team will soon be supporting source-mssql! The updated connector for SQL Server brings you:

• Terabyte sized table support: source-mssql can now do incremental reads of terabyte-sized tables without incurring any performance degradation.
• Reliability improvements: Failed reads, (for any reason e.g connection disruption), can now resume reading from a last known checkpoint, rather than having to start over.
• Various bug fixes: fixes have been implemented for many issues discovered in the community connector, including full support of datetime data types.

How to Enroll

We are actively seeking beta testers. We are soliciting feedback and hoping to further refine these features prior to general availability. Here's how you can try out the beta:

1. Comment Under this discussion to get in contact with our team to be included in our beta program. Also take a look at the #announcements channel in our community slack to opt-in there.

Keep in mind that the early access version is NOT suitable for production and we may ask you to reset your data.

Request for Feedback

As you explore the new functionality, we ask that you pay special attention to the reliability of reading incremental changes. We want to ensure that all records show up on your destination, and the speed and cost of your syncs improves.

We look forward to hearing about your experience with beta source mssql. Please don't hesitate to share any feedback or questions in the comments below. You can also join us on Slack if you would like to chat directly with the team about this feature.

[fvgm-spec]

Hi I am interested in being included in the beta testers program for the MSSQL connector. I have been using Airbyte for data integration since 2022.

[rodireich]

Hi @fvgm-spec if you're on our slack feel free to connect there.
I sent you a message on LI

[aviad-deri]

Hi,
I'm using Airbyte for the last year and I want to be part of your pilot.
I have some massive, huge mssql tables that I need to transfer into BQ.
It seems that this connector will really help us.
Thanks!

[marcosmarxm]

This definitely its going to put the new connector under some stress test!

[gustavoleo]

Please I want to be part of it

[evantahler]

Great! When you get into slack, DM me and I'll add you the proper channel

[rodireich]

Hi @gustavoleo I contacted you on Slack

[ExitoLab]

Hi I am interested in being included in the beta testers program for the MSSQL connector.

[evantahler]

You've been invited to the slack channel!

[spencerr]

Hi, I may be interested!

One question I have is if it's possible for the connector to support multiple databases at once, or if you have to create a source per database. I have a multi-tenant system with thousands of databases spread across a few servers. Ideally a connection per server would be great, unless there is no scalability concerns with thousands of connectors being created for CDC syncing.

[evantahler]

The MSSQL is one connector/source per DB at the moment. Especially if you use CDC, you'll want to partition the reader per a logical group of tables so slow-read A doesn't block slow-read B.

Airbyte has APIs and Teraform providers that should make making lots of connections not-so-terrible.

[evantahler]

I've added you to our beta slack channel, @spencerr

[walker-philips]

Is the new source built on the existing java code or has it been migrated to a new approach? We had to build a custom connector to handle a current inability of the current mssql-source to authenticate to AzureAD. I believe the fix is extremely simple and would essentially make the source-mssql connector capable of integrating to MS Dynamics 365 which is one of the major CRM platforms out there. Seems like a quick win for the team to cater to more enterprise customers for Airbyte Cloud.

If there is a way to get involved and help with something like that let me know.

[walker-philips]

I just tried to take a first stab at things. I have zero Java developer experience but tried to do a little legwork here. #35928.

Our custom connector is written in Python and is specifically for Microsoft Dynamics 365. It is currently suffering from some performance problems, specifically that Dynamics Databases can become unwieldy (our instance has multiple tables with 700+ columns and 1023 tables overall). Our solution is specifically tailored to our instance. I was hoping the source-mssql has the ability to push down column selection from the UI to the SQL statements themselves that retrieve data and we could migrate over to it. I presume the performance is much higher in general and if column push down is an actual feature, then the wide tables can be appropriately sliced apart from the UI and dramatically speed up the read process. My understanding is for many connectors the UI column selection occurs on the data after it retrieved from the source, at this point, the optimization is too late.

[rodireich]

@walker-philips the source-mssql connector does have column selection which filters at the sql statement level.
If you unselected a column its data will not be sent over the wire.

[walker-philips]

@rodireich That sounds extremely promising for solving a lot of the performance issues we are encountering. As I said though, the authentication issues prevent us from using the mssql-source connector as is to try and hook on to this Dynamics product. I will also say certain sql server functionality like execute and certain system tables are unavailable due to the permissions and read-only nature of the Database.

If you guys can solve the authentication issues, I am happy to evaluate the mssql-source against our CRM and see if we can find a way for Airbyte to reach more enterprise use cases.

[rodireich]

Thanks @walker-philips
For scoping the issue, which of the different authentication methods is the one(s) critical to your use case?
Is there any workaround with current supported methods?

[walker-philips]

@rodireich We use ActiveDirectoryPassword. Our workaround was a python custom connector where we used SqlAlchemy to make the connection. We already had a Python framework for SQL connections that we had been using for some very obscure legacy databases. In order to build the catalog in Discover(), we read from sys.tables, sys.columns, sys.types, and sys.databases. As stated earlier, we are unable to call stored procedures due to the read-only/restricted nature of the CRM database. Our use case is built around an incremental sync rather than CDC.

[evantahler]

The connector has been released and is now certified! Closing this discussion.