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.
E sounds more logical.
C
Cannot be C, C is not high availability.
C is high availability: https://technet.microsoft.com/en-us/library/bb510695(v=sql.105).aspx
“Transactional replication provides the lowest latency and is most commonly used for high availability.” Threw me out too, Availability Groups aren’t the only form of high-availability solutions.
I agree with Matt, it’s D or E, and “The data in both SQL Server nodes needs to be indexed” seems to point towards D.
https://blogs.msdn.microsoft.com/alwaysonpro/2015/03/03/recommendations-for-index-maintenance-with-alwayson-availability-groups/
what if indexes are created on the primary and replicated on the secondary?
E
E
Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.https://technet.microsoft.com/en-us/library/ms151196(v=sql.110).aspx
so it is transactional replication – C