If I’m using a SQL Server on Azure Windows Virtual Machine what would be the most time efficient way to create a read only replication instance?

admin

Currently, I’m using SQL Server on an Azure VM (DB B) to read from Power BI. DB B updates via log shipping from a primary SQL Server (DB A) and it locks me out from reading DB B two times an hour. I’ve considered caching strategies with Power BI to help, but I’m not certain that will solve the problem in the long term.

Requirements for Suggested Solution(s)

  • Changing the log shipping method to another method for updates from DB A is not an option.
  • Migrating DB B to Azure SQL Database/managed DB and eliminating SQL Server on Azure VM is not an option.
  • The log shipping updates happen at the same times each hour.
  • Near real-time replication would be ideal.
  • Transactional replication cannot be used because each table does not have a primary key.
  • Minimizing cost would be ideal.
  • Reading from DB B should always be available regardless of data consistency.

What are some recommended solutions?

Read more here: Source link