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.
Further reading:
The AlwaysOn Professional MSDN blog has more examples of connection strings for various applications.
You may also like to review the Application Intent Filtering feature of AlwaysOn at this and this link.
SQLCMD with -K READONLY works fine but when I mention ApplicationIntent=ReadOnly in SSMS, it connects to primary and not secondary. Any suggestions?
Try the trick here:
http://www.sqlservercentral.com/Forums/Topic1378783-2799-1.aspx
If your default database is master, this does not work. To get it to work you should add the following to the parameters assuming your database is named MyDbInAG :
Initial Catalog=MyDbInAG;ApplicationIntent = ReadOnly
The article lists the connection option as “Application Intent = ReadOnly”. Make sure to remove the space between Application and Intent as stated further down in the article.