I have a SQL Server database that is hosted in a cloud service as part of one of our ERP systems . Currently we are getting daily backup dumps via sftp which i am restoring to our on prem SQL Server for the departments to use with Services like PowerBI, SSAS and on site SSRS. Recently a request was made to try and increase the frequency of the updates. What i was wondering is if this would be a task for which Replication would be a better tool than more frequent backups?
Can you even setup a Snapshot & Distribution Agent to basically place a file in a location and then have another program pull the snapshot down via sftp and have a subscriber then pull that snapshot in to the local database?
Esentially a push type Replication (merge or snapshot) where the subscriber can not directly connect to the Distributor.
Or is more frequent backups with the associated locks and overhead a better solution?