SQL Server Always On Availability Group enables us to connect Read-Only workloads to secondary replicas automatically by using differents ways. For instance, to connect .NET applications to secondary replicas, we must also include "ApplicationIntent=ReadOnly" besides the AG Listener Virtual Name in the connection string. More specifically, after configuring the Read-Only Routing list, this would be the connection string if we had an Availability Group with a Listener called "AGLISTENER" (in these examples it is used Windows Authentication).
Server=tcp:AGLISTENER,1433;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True
But that is not all, it is also possible to establish connection to secondary replicas via SQLCMD tool by using the new parameter -K (and optionally -M for faster detection of and connection to the active server). If -K is not specified, the sqlcmd utility will not support connectivity to a secondary replica. So, this would be a simple example of how to use SQLCMD with -K and -M parameters. I couldn't have been simpler.
Sqlcmd -S AGLISTENER -E -K ReadOnly -M
And last but not least, we can also do it by using SSMS Connection Dialog window by using “ApplicationIntent=ReadOnly” in the "Additional Connection Parameter" tab. I also wrote another post about it with more details, take a look at it.
To sum up, these are the basic ways to try out Read-Only intent connections to secondary replicas, the first way is ideal for developers whereas the second and third if you are working as a DBA.
That is all for now. Let me know any remarks you may have. Stay tuned.
Most Popular posts
- Checking SQL Server stale statistics
- Memory + CPU support for all Windows Server versions and editions
- How to create new Logins and Users for an AlwaysOn Availability Group
- The 'SkipErrors' parameter for the Replication Distribution Agent
- Using ‘sp_browsereplcmds’ to diagnose SQL Server Replication issues
- AlwaysOn AG Listener: The attempt to create the network name and IP address for the listener failed
- How to test Read-Only Intent Connection from SQL Management Studio
- Error: “The local node is not able to communicate with the WSFC cluster” – AlwaysOn Availabiliy Group
- Creating alerts for monitoring proactively SQL Server AlwaysOn Availability Groups
- Did you get this "AuthorizationManager check failed" error working with SQL Jobs and PowerShell?
HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE
No comments:
Post a Comment
Let me know any remarks or questions you may have. Please write down your name.