2

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.

2

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.

  • Thank you David for your great answer. But tables do not have PKs as I mentioned. This is a vendor-provided software and we do not have an option of adding them. It's difficult and expensive. Furthermore, we cannot upgrade the production to Enterprise either because of high cost involved. This is a fairly straightforward task and the price tag that comes with those changes will not be accepted at my organization. – LearnByReading Nov 24 '20 at 16:21
2

As @DavidBrowne mentioned, Transactional Replication is usually the best route to go and you should strive to design your tables to have primary keys (this is generally just best practice for a number of reasons).

But of course we can't always control the data we're given, and as you have researched Snapshot Replication is a decent alternative for tables lacking primary keys.

You can actually implement a hybrid solution that uses both Transactional Replication and Snapshot Replication. Your Transactional Replication solution will be near-realtime (usually within a few seconds when everything is working correctly). The Snapshot Replication will generate a SQL Agent Job that can be scheduled to run every 1 minute (and actually you can script out the time to be as often as every second - though depending how big your tables are and the power of your source & distributor server(s) you might not want to have it execute that often).

A hybrid solution will result in your creating two Replication Publications, one for the Transactional Replication Publisher of your source Database, and the other will be for the Snapshot Publisher of the same Database. In the UI of the Add Publisher Wizard, you'll be able to choose the appropriate tables for each Publication. (The Transactional Publication will disable the tables missing Primary Keys, so that it's not possible to accidentally select the wrong table.)

Also as David Browne pointed out, the load on the Publishing server (source server) itself is fairly lightweight. It's the Distributor (and server where the Distributor database lives) that most of the work occurs. So you can choose to implement the Distributor on either your source server, your destination server, or entirely on it's own server to separate performance concerns.

  • 1
    Thanks J.D for letting me know that hybrid is an option (a few tables have PKs). As mentioned, it's not an option to add PKs to most of them. I must have missed something: I thought Snapshot replication was similar in implementation to Replication. So this is actually done through a SQL Agent job?? – LearnByReading Nov 24 '20 at 16:25
  • SOrry, forgot to ask: is the version difference: Standar d2014 vs. Enterprise 2017 going to be a problem? Thanks again – LearnByReading Nov 24 '20 at 16:26
  • @LearnByReading I'll clarify my answer (I'm still adding more info to it as I think of it), but when you use Replication it creates SQL Agent Jobs (behind the scenes) for the different pieces of the replication. – J.D. Nov 24 '20 at 16:26
  • @LearnByReading As far as I'm aware those version differences should not be a concern with using Replication. It's actually a pretty flexible tool that Microsoft implemented. (If you think of any other questions, it may be better to update your post, and I'll update my answer accordingly, just to keep the comments section from becoming too verbose.) – J.D. Nov 24 '20 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.