I have a relatively large production environment (150GB) in SQL Server, and I need to duplicate it somewhere else for querying (specifically: I need to create an environment identical to the production so that queries are not hitting the live production environment - I used the word "duplicate" because I do not want to suggest a specific method).
Requirements: 1- the server has only 2 cores with 32GB RAM, so it's not very advanced hardware wise. Production environment is Standard SQL 2014, and receiving environment is Enterprise SQL Server 2017.
2- I need good latency (1 minute): this means that the delay cannot be more than a minute.
3- This is just a one-way duplication. I will not be making changes in the replica and expect it to end up in the production. This is just one-way duplication.
4- "live" connection (meaning changes made to the production must be sent back to the duplicate within 1 minute or so)
What I have done: I thought of doing replication. But this is such a bad idea for a few reasons: a) most tables (there are hundreds of them) do not have PKs. I understand that for replication you need PK. b) there are so many tables, so it's hard to handpick them all for replication. c) I understand that replication puts a lot of load on the production server. So a lighter version is better.
And then I looked at Snapshot. I was wondering if it's possible to use both, and if not, can snapshot alone be enough for above scenario? Any help/direction would be much appreciated.
NOTE: thank you for the recommendation that tables need PKs. We all know this, however, this is a vendor environment. Adding PKs is out of question, please consider that as a requirement as mentioned.