This week, we addressed a service request from a customer who wanted to keep two tables synchronized across different databases on the same Azure SQL Database server. Unfortunately, in Azure SQL Database, it’s not possible to directly call the database in the operation, for instance using the command
select * from master.sys.sys_databases. For this reason, I’d like to share an alternative to tackle this limitation.
This is a way to do it, and it’s fine if there’s only one entry point for the data and you’re okay with writes taking more than twice as long. But this is a perfect use case for transactional replication. The only problem is, Azure SQL Database doesn’t support being a transactional replication publisher or distributor, only a (push) subscriber.