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.
Best Answer
Transactional Replication is the best fit for this scenario.
Your tables should have primary keys, which is a pre-requisite for Transactional Replication. The load on the publisher is minimal. The bulk of the work is done by the Distributor, which can be on the same instance as the Publisher, or the Subscriber, or a completely separate SQL instance.
If you are willing to upgrade the production database to SQL Server 2017 Enterprise Edition you can create a readable replica on the secondary with AlwaysOn Availibility Group, with or without joining the servers in a Windows Server Failover Cluster.