I've been looking at the new features of SQL Server 2012, and how it can help my current situation.

Currently we're on SQL Server 2008 R2, and replicate two OLTP servers' databases to a single reporting server via transactional replication. We then use these replicated DBs for reporting.

I'm interested in whether SQL Server 2012 and the Availability Groups can replace this - so instead of transactional replication, I'd use the new availability groups and mirror the two OLTP servers' databases to the reporting server - where the reporting will be able to access the read-only replicas.

I'm unsure if this will work well, or even at all.

I would like to get away from replication, as it causes issues with my deployment strategy (Using VSDBCMD.exe). I'd also like to get away from replication reinitialisation delays on large DBs.

Does anyone have any good examples or experience with this? Is it possible to mirror with readable replicas from multiple servers to a single server as per normal mirroring in lower versions?

This was originally asked here: https://stackoverflow.com/questions/10415225/mirroring-and-availability-groups-in-sql2012 Sorry I don't think I can migrate questions yet.


2 Answers 2


Swiped from my answer on StackOverflow, only to prevent others from spending effort on the same type of answer.

Personally I think this will work a lot better than transactional replication, though I haven't done any formal comparisons of the two in a true migration scenario. I know that with the amount of troubles folks have with transactional replication, compared to even standard mirroring (and this is an upgrade of that), you are certainly bound to have fewer problems.

The biggest boon is that the secondary can be marked as read only - so you can run all the reporting off of it that you want, and it won't affect the mirroring at all. You just need beefier tempdb (since it essentially uses rcsi to do this).

Of course you do need to be aware that both sides of the AG need to be fully licensed in order to use the replica for read-only operations. And both sides need to be running on specific versions of Windows (Enterprise or better on 2008 R2 and lower; Standard or better on 2012 and above) because they require failover clustering - the SQL instances in AGs don't need to be clustered, but they need to be sitting on top of that infrastructure in the OS.

  • Thanks Aaron, I'll leave it open for other answers as I'm hoping to get more opinion here than Stack Overflow :)
    – Meff
    May 2, 2012 at 20:16
  • No worries, I kind of suspected that's why you repeated it here, and wasn't trying to stifle that. Just trying to help reduce effort. May 2, 2012 at 20:17
  • Just thought I'd point out that Windows Server 2012 does not require Enterprise edition for Failover Clustering. This is now available in Standard.
    – SomeGuy
    Jun 19, 2013 at 13:31
  • @SomeGuy that's true, but isn't it because they took away the Enterprise Edition and graduated Standard? The four editions of Windows Server 2012 are DataCenter, Standard (used to be Enterprise), Essentials (used to be SBS), and Foundations (seems similar to Web?). Jun 19, 2013 at 13:46

Advantages of Transactional Replication over Availability Groups for Reporting Workloads

As a counter-point to the answer above, here are some reasons why transactional replication may be a better solution than availability groups for reporting workloads.

  • You can replicate a subset of tables in the database. Availability Group Replicas are always the entire database. If some tables contain sensitive information, auditing access is easier if all reporting is done against a database that doesn't include that information.
  • You can filter the rows that get replicated providing a fine level of granularity. For example, you could replicate department specific information to a multitude of departmental reporting servers, with each having access to only their information.
  • You can have a completely different indexing strategy between the publisher and the subscriber. With Availability Groups the indexes are the same and even though statistics will be created on the replica and stored in tempdb, but you don't have the ability to tune that replication gives you.
  • Security issues. In general, reporting users connect directly to database by using SSMS, access or excel and you want to restrict theirs access to reporting database only.

If none of these are big issues, then Availability Groups may be the better choice.

Your Answer

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

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