You are a database administrator for a Microsoft SQL Server 2012 database named
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’)
The correct answer is C
Because ALLOW_CONNECTIONS cannot be set to YES
ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }
http://msdn.microsoft.com/en-us/library/ff878399.aspx
1
0
you are right
0
0
Agreed.. == Allow_connections cannot be set to yes, why do so many places suggest it can!
0
0
A looks like the answer, any opinions?
0
0
No, the question says “Production transaction should be MINIMALLY affected”, so AVAILABILITY_MODE can’t be SYNCHRONOUS_COMMIT
0
0
The correct answer is C
0
0
agreed
0
0
To configure connection access for the secondary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the SECONDARY_ROLE option, as follows:
SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )
Although T-SQL in Answer A is correct, the fact that AVAILABILITY_MODE = SYNCHRONOUS_COMMIT implies that production transactions will be affected while they are committed to secondary. This therefore invalidates A as an option for a valid answer.
0
0
C is the correct answer in my opinion.
ASYNCHRONOUS_COMMIT is correct as SYNC_COMMIT is mandatory only in case of Automatic failover.
Also ALLOW_CONNECTIONS {READ WRITE|ALL} is wrong, it has to be READ_ONLY.
0
0
If you try to run this in a query window
CREATE AVAILABILITY GROUP GROUP1
FOR DATABASE [AdventureWorks2012]
REPLICA ON ‘SecondaryServer’
With (
ENDPOINT_URL = ‘TCP://SeconaryServer:5022’,
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL ,
SECONDARY_ROLE (
ALLOW_CONNECTIONS = YES,
READ_ONLY_ROUTING_URL = ‘TCP://SecondaryServer:1433’)
you get the following error:
Msg 153, Level 15, State 1, Line 9
Invalid usage of the option ALLOW_CONNECTIONS in the CREATE AVAILABILITY GROUP statement.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ‘)’.
However if you change it for ‘YES’ to ‘READ_ONLY’ it parses just fine.
C is the correct answer from what I can tell.
0
0
C is the correct answer.. but not sure what to choose in the exam? are the answers in the dumps are from the solution kit
0
0