Wednesday 1 June 2016

How to test Read-Only Intent Connection from SQL Management Studio

As part of some SQL Server AlwaysOn Availability implementations, we may need to test Read-Only Intent Connection in someway to make sure that it is working well at SQL Server level. We can verify it by using SQL Management Studio. Let me expand on what I mean, AlwaysOn Technology give us an option to implement Read-Only Intent mode to enable SQL Server to redirect read-only connections to secondary replicas, it means off-loading Read-Only workloads to secondary replicas. Undoubtedly, this is a gripping feature, at first glance, it drew my attention to test it and I verified that is truly useful for business cases where we need to have Read-Only Intent feature working properly and automatically. It couldn’t have been better when I learned that I could use SQL Management Studio to achieve my purpose.
Keep it in mind that we need to have set up Read-Only Routing List before going to test it, obviously. Now carrying on this tip, let me show you some explicit pictures where you will see which parameters you should consider. First of all,  you need to go on 'Login' tab and write the Listener Name of your AlwaysOn Availability Group. In this example, my Listener Name is SRV1LIDBVB which represents the Virtual Server Name.

What’s next? going to 'Additional Connection Parameters' tab, you will see two parameters. You must write the database name for 'Database' which is in your AlwaysOn Availability Group and 'ReadOnly' for 'ApplicationIntent'. The both parameters are separated by a command.

Finally, click on 'Connect' and you will be connected to any secondary replica, which one replica? it depends on what you have set up in your Read-Only Routing List. This is all for now. I hope you find this post practical, effective and easy to put in practice and include it in your testing plan. Let me know any remark you may have. Thanks for reading!.

No comments:

Post a Comment

Let me know any remarks or questions you may have. Please write down your name.

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