Thanks Rama

Solid catch with that CAST —Really sometimes it's the smallest tweak that makes all the difference.

Also it came as surprise  how just a VARCHAR(100) vs VARCHAR(50) can silently mess up...

Here’s how to fix the issue with SQL Server not finding the system databases:

1. Check if the D: drive still exists

• Open File Explorer and see if D: is there.

...

Given your scenario, your best bets are:

1. Merge Replication--> (Not real time, Inbuilt mechanisms to handle conflict and schema changes easy )
2. SQL Server Peer to Peer Replication(Near real time,...