Y104
AdventureWorks2012.
You create an Availability Group defined by the following schema. (Line numbers are included for
reference only.)
You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:
Production transactions should be minimally affected.
The secondary server should allow reporting queries to be performed.
If the primary server goes offline, the secondary server should not automatically take over.
Which Transact-SQL statement should you insert at line 06?
A.
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = ‘TCP://SecondaryServer:1433’)
PRIMARY_ROLE (
ALLOW_CONNECTIONS = READ_WRITE,
READ_ONLY_ROUTING_LIST = NONE)
B.
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = ‘TCP://SecondaryServer:1433’)
C.
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = ‘TCP://SecondaryServer:1433’)
D.
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = YES,
READ_ONLY_ROUTING_URL = ‘TCP://SecondaryServer:1433’)
C of course..
https://msdn.microsoft.com/en-us/library/hh213002.aspx
i think it is C too. Do not have ‘YES’ option.
C
The correct answer is C.
Asynchronous commit because: Production transactions should be minimally affected. (in synchronous commit, the transaction running on the primary waits confirmation before returning to the client. This may cause a usual small delay from the point of view of the application.)
Allow connections: read_only
https://msdn.microsoft.com/en-us/library/hh213002.aspx
SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )
where,
NO
No direct connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting.
READ_ONLY
Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
ALL
All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access.
correct answer C because:
– Production transactions should be minimally affected:
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
– If the primary server goes offline, the secondary server should not automatically take over:
FAILOVER_MODE = MANUAL
– The secondary server should allow reporting queries to be performed:
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = ‘TCP://SecondaryServer:1433’)
C. YES is not a valid option
https://msdn.microsoft.com/en-us/library/hh213002.aspx
Quote from TSQL instructions:
SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )