Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: http://stackoverflow.com/questions/10415225/mirroring-and-availability-groups-in-sql2012 Sorry I don't think I can migrate questions yet.

share|improve this question

2 Answers 2

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.

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

share|improve this answer

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.

share|improve this answer
    
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 '12 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. –  Aaron Bertrand May 2 '12 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 '13 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?). –  Aaron Bertrand Jun 19 '13 at 13:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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