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?
Best Answer
In order for replication to work, the publisher and subscriber need to be able to talk to the distributor directly so I don't think this will work in your current setup.
Depending on the amount of data you're producing, a version of log shipping may give you what you're looking for, but you are limited by the database being in a state that allows you to restore further logs and you will still get some disruption when the logs are restored.
The two 'seamless' options I can think of to give you more frequent updates are: 1) restoring your backups to a new database on the on prem server and then using SSIS or stored procedures etc to push this into your actual database(s). 2) using Enterprise Edition and AlwaysOn groups to restore the data to the primary server, which will then be replicated over to the read only secondary which your reporting services etc are pointing at.