Oh, the love/hate relationship with PowerShell will only deepen, on both sides.

Yeah, that's an approach that will work and get done what you're looking for. However, I'd strongly suggest you take a look at dbatools. It's the best way...

This is a very complicated procedure and generally should not be the norm for most queries. I see a series of issues.

First thing I see is several DISTINCT...

Hey!

I work for Redgate, just wanted to state that up front.

Instead of using Compare and Data Compare, have you tried looking at SQL Source Control? It...

It does. It's called merge replication, but it's more than a little bit difficult to implement and maintain. The showstopper for most people is that one of the first requirements...

So, if one is read only and the other is not... How high volume is the database with writes? If the volume is relatively low, you could use transactional replication...

Maybe it's your query. You have columns in the WHERE clause for a table in a LEFT JOIN. Move those out of the WHERE clause up to the JOIN criteria...

Here's some great documentation on what's supported within Azure SQL Database for compression. That should fill in all the gaps.

Yeah, I don't have an immediate answer to that one, sorry.

This is a three year old question without an answer.

I haven't had to deal with service broker, but if I did and I was using SSDT, I'd suggest...

Well, every team I was rooting for is out of the cup. We're down to "Not France" in my house.

How has everyone else been?

The ISNULL command is absolutely going to lead to table scans with no possibility of seeks. You need to get rid of it. If you have to use an OR...