0

we have a database which is a SQL server PaaS offering, I would like to create a read only replica database which has a dedicated server behind it. The reason behind this is that I do not want to use the read scale out option, as this option requires the read intent to be specified. The users will be using SSMS and not a reporting application where the connection string can have the read intent string.

Data synch is one other option that I have explored, I have concerns with its restriction around every table having a primary key and what happens if during a release the schema is altered and the table involved in data synch.

learner
  • 101
  • 1

1 Answers1

0

The only option you have is to use SQL Data Sync to replicate data from Azure SQL to SQL Server. The restriction of a primary key is maybe not as big problem as schema changes on Azure SQL Data Sync. Schema changes need to be manually replicated.

SQL Server Transactional Replication is not possible to use it in this scenario as Azure SQL Pass can only be a subscriber and in this scenario you need Azure SQL as a publisher and that is not possible.

Read-only replica is free on Premium service tiers of Azure SQL but is often associated with performance problems.

Alberto Morillo
  • 256
  • 1
  • 4