SSMS Registered Servers with ReadOnly Application Intent

Question

We are working on SQL Server 2014 POC testing with AlwaysOn and one of the user asked about saving the configuration of SSMS with ReadOnly Intent using the registered servers in Local server group. This way they don't have to type in the alias name every time they need to access the ReadOnly replica.

Unfortunately there is no option within registered servers to add the ApplicationIntent Option unlike the regular object explorer.

I came across this article from Microsoft about changing the connection string in RegSrvr.xml.

https://connect.microsoft.com/SQLServer/feedback/details/786323/ssms-sql-server-management-studio-2012-missing-connection-properties-for-availability-groups

I tried their suggestion and it did not connect to the right replica node when connecting via the local server in the registered servers.

The ReadOnly option works fine from object explorer when using the options in the connect window > Additional Connection Parameters. But it does not save the changes made to the connection.

Does anyone know of any alternate solutions on saving the configurations with ReadOnly Intent property with SSMS? Thanks in advance for your help.

Answer 1

SQL Management Studio (versions prior to 2016)

Unfortunately, there are some caveats that make using Application Intent in SQL Management Studio somewhat painful:

To connect manually with ReadOnly intent, after bringing up the Connect to Server dialog from the Object Explorer, users must remember to:

1. Click Options >>.
2. Go to the Additional Connection Parameters tab.
3. Enter the additional parameter as ApplicationIntent=ReadOnly;
4. (Note: Users must not click the Options << button after entering the Additional Connection Parameters or the parameters will be lost.)
5. Click Connect.
6. Always launch query windows by right-clicking on the desired database in the Object Explorer view and choosing New Query to avoid running into the #3 caveat below.

The caveats that apply are as follows:

1. Although you can get SQL Management Studio to connect with Read Only Intent, it does not store the Additional Connection Parameters when a connection is added to Registered Servers.
2. Behavior when hand editing the locally registered servers in the RegSrvr.xml file to add the Application Intent is extremely inconsistent and will be overwritten any time a change is made through the GUI making this workaround unreliable.
3. The Always On database must be selected before the query window is opened; otherwise, the connection gets routed to the primary server. If you attempt to select the database using the query window's drop down after the query window has already been opened to a non-Always On database, you will get an error dialog. If you try to change the database to an Always On database with a USE statement after the query window has already been opened to a non-Always On database, the results look like this when you attempt to execute the SQL query:

      Msg 979, Level 14, State 1, Line 1
      The target database ('AlwaysOnDatabase') is in an availability group 
      and currently does not allow read only connections. For more 
      information about application intent, see SQL Server Books Online.

SQL Management Studio (versions 2016 or later)

SQL Server Management Studio 2016 or higher can connect with Read Only Application Intent (using the same 6 steps as prior versions) and it does store the Additional Connection Parameters. There are still some caveats:

1. The Object Explorer view will not enumerate any of the tables or other objects in the AlwaysOn databases. Attempting to expand them results in an access denied message.
2. You cannot have a Read Only and a non-Read Only connection to the same listener open at the same time.
3. Intellisense for object names in the database does not work. (Oddly enough, Objects get enumerated just fine in the Query Designer that you can launch using Design Query in Editor... from the right-click menu.)
4. Caveat 3 from the prior versions' caveats still applies.

Third-Party Products

LinqPad stores the whole connection string including Application Intent and the database when you save a connection and therefore might be a viable option for performing Read Only queries against Always On databases.

Answer 2

Have you tried the PowerShell script, but use another spelling (with a space) for application intent? Or have I got the connection methods mixed up?

Server=$regsrv ; integrated security=true;Initial Catalog=dbname;
Application Intent=ReadOnly"); 

Connect to SQL Server Using Application Intent Read-Only

Answer 3

Is it possible that you create a configuration file and then you auto create the necessary registered servers in SSMS? Just as mentioned in my MSSQLTips article:

Automate Registering and Maintaining Servers in SQL Server Management Studio (SSMS)

Answer 4

This has been fixed in SQL 2016 SSMS when using with registered servers. I was able to register a ReadOnly Intent connection as part of local servers group and open the saved connection for future use. Thanks for the help with this.

Answer 5

I can't comment but am adding to DBAUser's answer.

I couldn't see an option to add Application Intent in the GUI when setting up the connection within the registered servers area.

I had to either connect using object explorer (using Data Source=<Listener>;Initial Catalog=<Database>;ApplicationIntent=ReadOnly) then register the server once I was connected.

Or add Initial Catalog=<Database>;ApplicationIntent=ReadOnly to the connection string in the .regsrvr file.

Answer 6

You may be missing an important part of the puzzle or at least I didn't see it anywhere in your question:

ApplicationIntent=readonly is one half of the equation. You also need to pass the parameter MultiSubnetFailover=True.

For instance, in the Additional Connection Parameters tab, you want to enter the following:

MultiSubnetFailover=True;ApplicationIntent=readonly;