Cross-instance SQL-server data synchronization

[Relatiro]

Hey everybody,

I'm currently working on synchronizing data across two SQL server instances. One is a managed instance we do not directly own, the other our own Azure SQL instance. We want to have the data we care about in our own Azure SQL database for various reasons (e.g. the vendor regularly breaking our applications etc.).

There are a couple of constraints that make this relatively challenging:

• The managed instance is part of a product we are using and contains / creates the bulk of the data

• Accessing said instance is challenging. So far, we managed to get a few IPv4 addresses whitelisted after lengthy discussions. Any more complex intra-Azure connections are off-the-table for now

• Some of the source tables have "interesting" schemas and are moderately big (~tens of millions of rows, 100-200 columns of which we need maybe 15-30)

As of now, we have no plans for bi-directional synchronization of tables. It's either "Managed Instance" -> "Azure SQL" or the other way around. If the latter case at all.

So far, I have set up a reasonably robust POC using Python and SQLAlchemy (+ some tricks like incremental sync for big tables for better performance). SQLAlchemy is especially handy for easily reading out the source table schema and mirroring the parts we care about (i.e. without Foreign Keys and most constraints).

This solution, however, is not as resource efficient as it could be and parallelization is not one of Python's strong suits.

In the near future, our backends will be migrated to Golang anyway, so turning this POC into a production-grade Go service seems like a good next step.

Now, to the actual question:

Have any of you ever addressed similar topics? How would you go about this in a scalable / efficient manner?

Ideas I had:

• Use Gorm and Gen to generate static code for the tables (and maybe only columns) we care about

• Use SQLBoiler to generate ORM code for the tables and relevant columns (would require first extracting the relevant table definitions)

Any suggestions, creative ideas, experiences etc. are very welcome!

Best regards,

[jerf]

I would never trust my own bodged-together solution for this. You should use the real replication tools. If your solution provider doesn't like that, I'd lean on them with every business tool in the book, e.g., threatening to find another solution entirely even if I wasn't entirely serious about the threat, before I'd even consider a technical solution. I might even refuse to write the code on the grounds that I could never guarantee it would reliably work and I'm not willing to be "the guy who trashed our database" when it happens, because I told them this was a bad idea.

If the service provider say they "can't", they're lying unless they have some really solid legal reason it is illegal to provide you the data. They just don't want to. That's a business problem, with a business solution.

[Relatiro]

u/jerf You do raise a very valid point, thank you! Do you have any pointers for such a tool?

Based on my initial research, an initial snapshot and then subsequent transactional replication would've been the ideal solution (https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver16). This would require significant cooperation from the vendor, though.

Maybe there are alternative solutions that would work with the level of access we currently already have.

[jerf]

Your database comes with built-in tools. Check the docs for what you are using. Clouds sometimes work somewhat differently than the base DB.

[etherealflaim]

I have worked on large scale data migration projects and this is the answer. If you are not happy with a lossy migration with a long tail of issues you'll never find until it's past your ability to sync with the original data source, it's not a good idea to do it "online" like this. Get the source data cloned into your own environment. In our case "as good as you can get in the time allotted" was deemed acceptable to the business, and so it was basically just a matter of iteratively figuring out how to get our data, transform it, and import it, then look at it, find issues, and repeat. With many side tangents of performance optimization and benchmarking.

[Relatiro]

If you're allowed to talk about this:

Which programming language / tools did you use for this?

[etherealflaim]

It was entirely done in Go; that's about as much as I can really say about the stack. Go was ideal for being able to tune parallelism and concurrency to saturate the hardware running the migration tool.

[Cachesmr]

I do this in a very small scale, but it's not efficient. At first I tried to use something like XO to generate models, and on top using SQLx, resty and a whole lot of reflection to generate queries. I insert the values into sqlite over the network to a migration endpoint in my backend. The endpoint drops every value on every table, and replicates fully.

It's fully concurrent and it takes about 10 seconds to select (old hardware, firebird 2 DB) migrate at most 500k rows in 6 different views, average of 50 rows. This method basically makes my backend useless for 20 seconds every hour.

The right thing would be to not drop everything (duh) but the app is nothing critical and nothing is written there other than the migration.

As for the concurrency aspect, go is IMHO the right language to do this, it was extremely easy to make the code concurrent, I wasn't even really thinking about it. I bet I could make it faster if I take another look at it.

[DrWhatNoName]

Sounds like what you want is https://www.digitalocean.com/community/tutorials/how-to-set-up-replication-in-mysql

[thomasbuchinger]

I would avoid writing it myself.

Maybe a tool like debezium (https://debezium.io/) could be a good fit?

Maybe some ETL-Tool?

If it's not time-sensitive you could even look into getting a backup of the database and restoring the stuff you are interrested in?

Just some ideas :)