In my interactions with customers, a lot of them are trying to move from SQL Server 2008 / R2 to SQL Server 2012 and they keep asking what is the changes one need to do when it comes to utilizing SQL Server 2012 AlwaysOn from an application point of view. I always say it is as simple as making a connection string change. We have extensively called this out in our book (Paperback, Kindle) under Chapter 8, “Connection Strings Parameters” section.
AlwaysOn uses listener to access DBs, connection string is as following:
Server=AGListener; Database=AGTestDB; Integrated Security=SSPI
For connecting to readable secondary replica a new keyword gets added: ApplicationIntent and Read-Only like:
Server=tcp:AGListener,1433; Database=Db1; Integrated Security=SSPI; ApplicationIntent=ReadOnly;
Some of the interesting points we discussed as part of our book on this topic are:
- To use ApplicationIntent connection string feature we need to update .NET Framework to 4.0.2.
- We can still use DB mirroring connect string to connect AlwaysOn group DBs, there is no need to create AG listener.
The primary reason is to offload your secondary read-only workloads from your primary replica, which conserves its resources for your mission critical workloads. If you have mission critical read-workload or the workload that cannot tolerate latency, you should run it on the primary.
This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).