We have a geo-replicated database in Azure SQL (Platform as a Service). This is a master/slave type arrangement, so the geo-replicated database is read-only.
We want to download data regularly from this Azure SQL database to a SQL Server database on-premise that has the same schema, without it impacting performance too much (the Azure Database is the main database used by the application).
We originally looked at Azure SQL Data Sync, to hopefully read data from the geo-replicated data and pull that down to on-premise, but it needs to create triggers + tracking tables. I don't feel overly comfortable with this, because it won't be possible to run this against the read-only slave database, and so it must be setup on the transactional master database (impacting application performance), which in turn will re-create these extra data-sync artifacts on the geo-replicated database. It seems messy, with bloated data (we have a large number of tables and data, and Azure PaaS databases are limited in size as it is) and we also use Redgate database lifecycle management, which can potentially blow these schema objects and tracking tables away every time we perform a release, as they're not created by us and are not in our source control.
What other viable options are there (other then moving away from PaaS and making a clustered IaaS VM environment across on-prem and cloud, with SQL Server installed, patched, etc). Please keep in mind, we are resource stretched in terms of staff, which is why PaaS was an ideal place for our database originally.
I should mention, we want the On-Premise database to be 'relatively' in sync with the Azure database, but the data on-premise can be up to an hour old data.
Off the top of my head, some options may be SSIS packages? Or somehow regularly downloading a Bacpac of the database and restoring it on-premise every 30 mins (but it's a very large database).
Note, it only needs to be one-directional at this stage (Azure down to on-premise).