SQL Server replication between standard and developer editions

[shizuka]

I have one SQL Server Standard (2019), I want to add one server to developper (not used by production so the version if developper), it's possible to replicate data ? what is the best methode ? we want to test one batch with data production.

Thanks

---

Thanks.

I want one way to have a server with production data only to test our batch. Replication between standard and developper editions it's possible (technologically) ? For licenses I think it's not problem because only batch for test will connect this. I have never use replication in sql server, I have 2019 standard, what is the best method ? Thanks

[juvenalb]

As long as the second server is also the 2019 version, then I don't think you'll have any issues technologically.

But you have to be careful not violate your licensing agreement with Microsoft by using the Developer edition server for any production purpose. I'd recommend speaking to Microsoft or your Microsoft var to verify that side of things.

To your follow up question on which type of replication you should use, it depends on your use case and requirements:

1. https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver16 is probably the most commonly used kind. It is very configurable and flexible, and it replicates changes in near-realtime. (It replicates only the changes after the initial snapshot is synchronized.) One of the biggest requirements to use it is that any replicated tables need a primary key defined on them. A workaround for this is to create an indexed view which is applicable for replication because of its unique clustered index.

2. https://docs.microsoft.com/en-us/sql/relational-databases/replication/snapshot-replication?view=sql-server-ver16 is probably the next most commonly used kind of replication. It can be scheduled to run at a frequency of your choice but it drops and re-creates the entities being replicated, every time. This makes it a heavier type of replication than Transactional, and usually is not used for realtime synchronization.

3. https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication?view=sql-server-ver16 is another form of replication similar to Transactional, and can be used for realtime synchronization. I don't believe it has the same requirements as Transactional, in the sense that it can replicate tables without primary keys on them. But it has its own requirements. It's also useful for when your Subscriber database needs to be applicable for data changes that need to be propagated back to the Publisher, which is not usually possible with regular Transactional Replication (but I believe Transactional Replication with Updatable Subscriptions does allow this).

4. https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-transactional-replication?view=sql-server-ver16 is a fourth kind of replication that is also a type of Transactional Replication. (I never had a use case for it, so I don't know much about it, but please see the linked docs.)