Support Databases that don't have md5 or fast md5 (MSSQL)

[pnelson-de]

I was browsing through the list of supported databases on your readme and noticed MSSQL is not listed although it can be found in databases.py. Just wondering if this was intentional and also what the status of that connector is at the moment.

Cheers

[erezsh]

Hello!

Yes, we originally intended to include MSSQL, and even wrote the code for it. But the md5 function ended up being way too slow (x100 slower than postgres iirc). We considered it unusable, so we decided to put it aside for now.

If we find a solution to the performance issue, I'll be happy to add MSSQL officially.

[sirupsen]

Hey @pnelson-de, thanks for writing in :)

In extension of Erez' reply, something he and I have discussed is that we will need to support a variety of databases where md5 hashing aggregation not supported. ElasticSearch is another example. For those, we may have to 'negotiate' a weaker form of hashing, such as simply a sum.

However, it's not a super safe default as it's not entirely unlikely you'll end up with the same number for segments even if the data is wrong...

However, there's already plenty of types in play that need to hash correctly to the same values across databases. 😅 Once the testing infrastructure is in place for that, we would look at databases that don't have proper support for checksumming.

[visch]

Guessing https://orderbyselectnull.com/2018/05/31/hashbytes-scalability/ is related / similar problem. Was looking to do an Oracle MSSQL check with this tool myself so I'll +1 this. I don't have a better idea for you all though at this moment.

Took a quick peak at https://docs.microsoft.com/en-us/sql/t-sql/functions/checksum-agg-transact-sql?view=sql-server-2017 but I'm not confident

[sirupsen]

@visch are you looking to test Oracle MSSQL to another Oracle MSSQL?

[sirupsen]

@visch the problem with CHECKSUM_AGG and CHECKSUM is they don't specify the hashing algorithm. If you can find out if it's SHA1, CRC32, etc. by testing some strings against HASHBYTES on the same instance with some selects, then we should be able to use it and 'negotiate the algorithm' with the other databases. Similar to how we, as of, negotiate the precision of timestamp(n) fields in https://github.com/datafold/data-diff/pulls?q=is%3Apr+is%3Aopen+sort%3Aupdated-desc .

If it's not a checksum we can reproduce https://github.com/datafold/data-diff/pulls?q=is%3Apr+is%3Aopen+sort%3Aupdated-desc and add support for negotiating a common algorithm, in the case of MSSQL, it'll be sum() instead of md5(). It shouldn't be too hard.

[visch]

@visch are you looking to test Oracle MSSQL to another Oracle MSSQL?

Oracle DB to Microsoft SQL

[sirupsen]

@visch if you're up for implementing what I suggested above, let us know. You can join us in #tools-data-diff in the Locally Optimistic Slack..

If you have a supported database pair and wiling to test and provide feedback, we'd be eager to hear it :) Even just Oracle -> Oracle on the same table would be really useful for us.

[sirupsen]

Looks like CHECKSUM uses their own fairly simple algorithm.. So not something the others would easily support. So I think we'd go with the sum() based approach for it to start.

[visch]

I don't have the time right now to look at this but in case someone wants to dive in

https://gitlab.com/vischous/oracle2mssql/-/blob/master/script.sh is a nice podman (replace that with docker if you want) script to spin up oracle / mssql dbs. Note there's a few extra commands you have to run with Oracle here https://gitlab.com/vischous/oracle2mssql/-/blob/master/oracle.sql

[joshcrichman]

Just adding my vote for SQL Server support! Best of luck solving the technical issues.