I am looking for solution in SQL Server 2016 by which I can create read-only copy of production database for reporting purpose which refresh in every 2 hours from production.
My current production database server in own managed and in AWS. Reporting server is also own managed and is in different AWS account and domain.
You'll be looking at things like using something similar to logshipping and or database snapshots depending how you are wanting this to play out, but cross AWS tenancies unsure if you will be able to get to the S3 buckets cross tenancy (I'm not an AWS guy).
But a 2 hour delay on the logshipping restore then you can drop and recreate the snapshot each time. Only problem is you cannot gear up the snapshot to be a reporting side so you cannot add extra indexes etc to make reports better.
If you want something where you can modify the reporting replica your looking at doing things with replication or SSIS.
15 People are following this question.
