The databases in PRIMARY availability group can be used for read-write access. The databases in the SECONDARY availability group can be used just for read-only access.
An attempt to connect to a SECONDARY availability group database with a normal connection, which is read-write by default, shows the following error message –
Msg 978, Level 14, State 1, Line 1 The target database ('AGDemoDB') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.
To resolve the issue, the connection string needs to have the Application Intent = ReadOnly parameter. How do you pass parameters in a SSMS connection?
SSMS has many options that are not too obvious. One of them is to provide additional connection parameter options. All that is needed to resolve the above error is to use the Additional Connection Parameters screen in the connection dialog and put the parameter there.
The keyword should not have any spaces.
The AlwaysOn Professional MSDN blog has more examples of connection strings for various applications.