I would like to seek your advice in designing a high availability architecture at my current work place.

We are moving to SQL Server 2012 platform and we currently have a set of OLTP databases serving for the application usage and these databases are being replicated (transactional, every 3 min) to another box for reporting purposes, I'm thinking of replacing replication with SQL Server 2012 High availability groups (asynchronous) (one of the reasons to bring high availability groups in to picture is to have HA in cases like database corruption (ex: page level corruption))

Q: as the indexing on report databases is different to indexing on application dB’s, the failover between app db and reporting db will not provide better performance to application or reports on failover, this makes it a bit hard to design an effective cluster.

Please let me know if you can suggest a better solution for this situation.

Appreciate your help.

Cheers, Kiran

| improve this question | | | | |
  • With divergent schemas, you would need to have a copy of both production and reporting to use any variant of AlwaysOn because it works at a lower level than Replication does. – Jon Seigel Mar 18 '14 at 17:12
  • Firstly, Clustering and AG are two separate technologies! Neither will solve the issue you want to solve. Both of these technologies are designed for HA, not creating duplicate sets of data for reporting. For pure reporting, you would want a separate reporting server, with a separate database. Your method for getting the data to that server will greatly depend on how close to "real time" you want to be. – Steve Mangiameli Dec 5 '14 at 15:29
  • The closer you want to be, the more expensive it becomes. I've implemented solutions within hours of real time and solutions a day apart. Which are you looking for? – Steve Mangiameli Dec 5 '14 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.