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:
- Two servers, different data center. AG group with synch commit. Active secondary.
- Two servers, same subnet. AG group with synch commit.
- Two servers, log shipping every 10 minutes.
- A couple diffrent answers with a-synch commit.
- SQL Server that includes application database configured to perform transactional replication.
- SQL Server that includes application database configured to perform snapshot replication.
- 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?