I am hoping to get some input as to what the best replication method/tool would be for my situation. I have to make a new system and the business needs are as follows :
- They need to have access to the data
- They want to import a few million rows or so historic data
- Data needs to be close to realtime (5 min or less)
- I don't want to give them access to the PROD DB
- I don't want the historic data in the PROD DB
- The PROD DB is going to be HA via Availability Group on SQL 2016 Standard
The PROD DB is going to be completely on its own instance and server. The Replicated copy would also be on a completely different server OR could be on the Prod Instance as a separate DB (obviously the server will be specced accordingly). Both cases would be in the same Datacentre.
What would be the best replicable process for this? What replication/mirroring technology is there that would allow me to have historic data only in the copy? What would help me keep the 2 DB's in sync, but have separate permissions?
I think Replication is the closest, but I don't know if I can have the historic data and the new sync data and its a fair bit of setup and config and I just want to make sure there is not a simpler or better method. Would log shipping work (i don't have any experience with SQL Log Shipping so am a bit clueless there.)
Thank you very much