1

Good day, I know very little about this topic and need some advice on where to start. I have two Microsoft SQL production databases that I would like to replicate so that I can run my reporting on them instead of the production databases.

Question 1: Does it make sense to use replication for the above scenario? Question 2: What do I need in terms of Microsoft licenses for replication?

Thanks for reading.

Improve this question

2 Answers 2

Reset to default
2

Transactional replication works well in this scenario for replicating your data to your reporting databases.

It's cheaper than availability groups as you can replicate back to your single server instance to create a reporting database, rather than having an availability group where you have to pay for a second licence if you are reading the secondary data.

SQL Server 2014 Standard edition supports transactional replication for the publisher (you can use a lower edition for the subscriber as per the editions here), whereas AlwaysOn Availability Groups require Enterprise edition.

Your options for replication setup would be

  • Production Instance > Separate Distributor > Reporting Instance
  • Production Instance > Separate Distributor > Production Instance
  • Production Instance > Distributor on Production Instance > Production Instance

Option 1 requires a licence for each instance, however would remove reporting load from the production server if required (remember you can use lower editions for subscribers)

Option 2 requires a licence for the production instance and the distributor, but gives a balance between cost and load on your production instance

Option 3 just requires the single licence but can cause additional load if you're replicating a lot of data.

You can read more about it here, although I'm sure you have checked it out already. others may have views on other replication methods, but this works in a environment I work on well. It all depends on balancing cost vs up time (AlwaysOn AGs vs replication)

Improve this answer
1
  • Option 4: Production Instance → Distributor on Production Instance → Reporting Instance. TL;DR - the choice of where to put the distributor and the reporting database are orthogonal.
    – Ben Thul
    Commented Apr 5, 2016 at 12:17
1

It does make sense, actually it's a rather common scenario. Both servers have to be fully licensed. Alternatively, with AGs, you can use read-only replica for reporting. It does have it's limitation (eg, you cannot modify schema to suit the reporting needs), not least being an enterprise-only feature (but not any more with 2016, I believe).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.