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 a primary database with scheduled log shipping to the secondary database
configured.
B.
You should include two servers configured in an Active-Passive SQL Server 2012 Cluster.
C.
You should include a primary SQL Server 2012 database that uses transactional replication to
replicate data to the secondary database.
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.
Availability groups don’t work here because indexes can’t be built on the secondary replicas.
by the way, the other reason why transactional replication might be best is because it can just replicate some tables and not the entire database.
Henry Figgins says:
March 7, 2016 at 8:16 am
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.
who told you the indexes must be built on the secondary replicas only?
The data in both SQL Server nodes needs to be indexed.
so if it’s both then all indexes from the primary will be replicated to the secondary replicas.
Answer: E
Skip to 172, the following questions are 161 questions
A
I really means B :p
E i think
You can’t place indexes on tables in a Secondary Replica, except you promote it to become the Active node.
okay.. A ..
I was confused with a similar kind of question where single server has to be indexed.
so in Both cases its transactional replication I think.
I think C is correct
https://msdn.microsoft.com/en-us/library/ms151196(v=sql.110)
http://www.aiotestking.com/microsoft/what-should-you-include-in-your-solution-3/