Microsoft SQL Replication

Posted on

Question :

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.

Answer :

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)

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).

Leave a Reply

Your email address will not be published. Required fields are marked *