SSMS – How to Wrap Identifiers Automatically with Brackets in Text Editor

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.

No, there no option for this, at least not as of SSMS 2014 (I haven't yet tried 2016). The problem is that when you right-click in Object Explorer and choose either Modify or Script Stored Procedure as   ▶, you are not "opening" anything: you are merely scripting it. When you are editing a table, that is a specialized UI that knows the table name that is being edited, and that name (and possibly object_id) can be checked against other instances of the "table editor". But when scripting out other objects as either "Modify", "ALTER To", or "DROP And CREATE To", then you aren't in a specialized editor, you are placed into a file editor. And being a file editor, it only guards against multiple instances of the same file path being edited concurrently. When you have a query tab open, you can even see the file name on the left side of the tab itself (the connection info is on the right). When scripting out an object, by default it opens a new query tab with a file name of SQLQuery{N}.sql where the {N} is an incrementing number. If you right-click on the tab itself and select Copy Full Path and paste that somewhere, you will see that the real file name is similar to C:\Users\{windows_login}\AppData\Local\Temp\~vsEFF9.sql. Whether you go by the proposed name shown in the tab or the temp file name, it is different for each new tab so there is no way for SSMS to know that you have scripted the same object already.

This does not work any differently than Visual Studio / SSDT (with one notable exception). The reason why Visual Studio goes to the same tab when clicking on a file in the project, or even opening the file from File Explorer (assuming that Visual Studio is the app associated with that file extension), is that it knows the full path name of the file being edited in each tab and the file being requested to be opened. It has nothing to do with source control. But when scripting a database object via the SQL Server Object Explorer (in Visual Studio, and using Script As ▶), it also creates a new tab each time you script the same object, since it has a unique file name each time.

The "notable exception" I mentioned regarding Visual Studio is when using the View Code option in SQL Server Object Explorer. This particular function does (somehow) generate the exact same file name when it scripts an object, so it will go to the same tab each time and hence you will not get multiple tabs! The file name that you see in the tab when using the View Code option is in the format of {schema_name}.{procedure_name}.sql. If you right-click on the tab (the tab opened by View Code) and select Copy Full Path, and then paste that somewhere, you will see something along the lines of: MSSQL::/{server_or_instance_name}/{database_name}/True/SqlProcedure/{schema_name}.{procedure_name}.sql. Since this format has the connection info embedded in it, that should protect against editing the same object across two different instances and losing track of which one was which :-). This approach needs to be mimicked over in SSMS in order for you to accomplish what you are trying to do.

I'm not sure if using Visual Studio is an option (technically it's not 3rd party ;-), but even if it is, the ideal solution is to never, ever edit Stored Procedures, Functions, etc by scripting them out of the database. Objects should (again, ideally) only be edited from a singular source file that is (ideally) linked to some sort of version / source control (Git, Subversion/SVN, TFS, etc). The database, where the object exists in its natural state in system tables, is not source control: it provides no history and provides no safe-guards against other people making changes at the same time. Even if you don't use version control (you really should :), you should at least have a common location for object scripts that everyone uses. And if you did at least that much, then you wouldn't be able to open up the same script into multiple tabs because the file editors (both SSMS and Visual Studio) only allow a file to be edited in a single tab.