4

The other day, someone told me that one feature in SQL Server 2016 involving read-only secondaries for reporting was that the read-only secondary could have different indexes than the primary. I've searched for this everywhere and haven't found this to be the case. In fact, Microsoft seems to indicate the opposite here (in this manner, indexing is similar to 2012).

Just curious if I'm reading this correctly; read-only secondaries with different indexing would provide an option for reporting, but I don't see them being compatible with AG groups in any version of SQL Server.

  • 1
    Perhaps they were thinking of transactional replication, which does allow different indexes on each subscriber, though that's a very old feature, not at all new. – Anti-weakpasswords Feb 15 '16 at 4:55
  • I have seen this "requested" in certain forums as a "good to have" several times but I have never seen any statement from MS that this would ever be implemented (certainly not in 2016). Architecturally it would be a huge change since currently index page data is replicated in exactly the same way that data pages data is. Bear in mind that there are synchronisation considerations for an over utilized secondary replica, so when you want to start using one for Data-warehouse style loads then perhaps a data warehouse is what is needed :) – Mark Broadbent Feb 15 '16 at 14:11
5

I can say with a very high level of confidence that there is no plan to support alternate indexes on AG secondaries of any kind in SQL Server 2016.

Perhaps your friend is confusing indexes and statistics - you can create alternate statistics on read-only secondaries (details here).

1

My guess is the feature you heard is related with the new "Real-Time Operational Analytics" which lets you run both OLTP and Analytical workloads by letting you create both Traditional b-tree indexes and updatable columnstore indexes on your tables. Currently it is not supported to have different indexes in your AlwaysOn AG replicas but if you architect your indexes cleverly in a way that you have both b-tree and updatable column store indexes then in your primary replica your OLTP queries will take advantage of the traditional B-tree indexes and in the secondary replica used for reporting the column store indexes will be leveraged.

Your Answer

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

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