You have been hired as a Database Consultant by ABC.com to design a SQL Server 2012
database solution.
You are tasked with designing a scale-out and high-availability SQL Server 2012 Online
Transaction Processing (OLTP) database solution that will maintain copies of data across
two server instances.
Your solution must provide scale-out of read operations by distributing the reads from clients
across two SQL Server 2012 nodes. The data in both SQL Server nodes needs to be
indexed.
What should you include in your solution?
A.
You should include two servers configured in an Active-Active SQL Server 2012 Cluster
B.
You should include a primary SQL Server 2012 database that uses transactional
replication to replicate data to a secondary database.
C.
You should include two servers configured in an Active-Passive SQL Server 2012
Cluster.
D.
You should include two servers in an Asynchronous-Commit Availability Mode Availability
Group.
E.
You should include two servers in a Synchronous-Commit Availability Mode Availability
Group
E is a possible answer too.
http://technet.microsoft.com/en-us/library/ff878253%28v=sql.110%29.aspx#Indexing
“To optimize read-only workloads on the readable secondary replicas, you may want to create indexes on the tables in the secondary databases. Because you cannot make schema or data changes on the secondary databases, create indexes in the primary databases and allow the changes to transfer to the secondary database through the redo process.”
I thought the answer could be E. I would have chose B if I only wanted to create indexes on the secondary server only. It states that the data in BOTH sql server nodes need to be indexed, so there isn’t a reason why you couldn’t use Synchronous or Asynchronous commit Groups,=.
The question doesn’t specify high performance or low risk of loss which are associated with Availability groups so D or E could be equally correct.
There is something missing in this question.
This is why it is B:
https://msdn.microsoft.com/en-us/library/ms151196(v=sql.110).aspx
it seems that the article et posted is a “winner winner chickendinner”! Its the point that you need p2p transactional replication for this task!
“Solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2012 nodes”
Skip the rest, its 461 questions. Also, its E. It’s E over D because E is the highest availability as transactions won’t commit until its safely in both servers. Also, it’s E over B because B doesn’t distribute the reads from the client. B uses agents to distribute data around and then an app has to pick the server to read. E uses a listener to distribute reads between the primary and the secondary. There’s a similar question where the answer is B because indexes only have to be built on the replica. This isn’t it.