SQL Server – Secondary Replica Indexing in Availability Groups

availability-groupsreplicationsql serversql-server-2012

I have a question, about a question, that is part of the 70-462 Microsoft exam.

I had this question today, and I'm not completely sure of the correct answer. While I did pass the exam, I would like to understand the correct solution given this situation.

If I recall the question correctly, you administer a 2012 SQL Server, the requirements are to offload up to the minute reporting, for an OLTP database, to another server, and also be able to add indexes to the secondary database.

The answers for the question were along the lines of:

  1. Two servers, different data center. AG group with synch commit. Active secondary.
  2. Two servers, same subnet. AG group with synch commit.
  3. Two servers, log shipping every 10 minutes.
  4. A couple diffrent answers with a-synch commit.
  5. SQL Server that includes application database configured to perform transactional replication.
  6. SQL Server that includes application database configured to perform snapshot replication.
  7. Two servers in a windows failover cluster. SQL Server configured as clustered instance.

Initially I ruled out the a-synch commit options (latency), the synch commit AG group in different data centers (latency?), log shipping (delay), and snapshot replciation options.

This left me with the synch commit AG group on the same subnet, or SQL server with transactional replication.

Based on https://msdn.microsoft.com/en-us/library/ff878253.aspx, I read that if you want indexing on your active secondaries, you have to create them in your primary. I interpreted the question as requiring to be able to create indexes on the seconday, that do not exist on the primary (covering indexes for reporting, or what not).

The other answer, refers to SQL Server singularly, which doesn't sound like we're adding "another server" to offload the work to.

Have I misinterpreted the question/requirements? What should the correct answer/solution be?

Best Answer

  • #5: transactional replication.

Why is this an option? Because the documentation explains exactly why it is possible there - once initialized, index changes are not replicated in either direction.

Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

I also think you are reading the text far too literally. If you have configured replication at all, it is highly unlikely you should assume that it would be configured to replicate to another database inside the same instance of SQL Server - I'm not even sure if that is possible, or if there really is a plausible use case for that, but I don't think the wording of the question was slanted to point you that way. To be honest, I just think the quality of questions in those exams, in general, is shady. I wouldn't read too much into them.

Why are none of the other options possible? Because:

  • In no Availability Groups configuration can you create indexes on the secondaries, since the secondaries are essentially read only (though you can create statistics; these, however, live in tempdb). IIRC there have been requests to support something like this for indexes too, but no plans. This rules out #1, #2, and #4.
  • Log shipping isn't a good fit for the requirement, since you can't change the destination database and continue applying logs. So - since the question states that logs are restored every 10 minutes - you'd have to restore the database with recovery every 10 minutes, create the indexes, then kick everyone out 10 minutes later to do it again. This is not "up-to-the-minute" reporting. This rules out #3.
  • Snapshot replication can't be updated once initialized, since it is literally a snapshot - in order to create indexes, you'd have to do it every time you restored a snapshot, since this is an all-or-nothing operation. It is not possible to provide "up-to-the-minute" reporting in this model. This rules out #6.
  • In failover clustering, there is only one copy of the data, and only one instance of SQL Server, and the data is only visible to any workload (read or write) on the primary ("active") node. So, by definition, it is not possible to make changes to a copy of the data, since there is no "copy." This rules out #7.
Related Question