Use ReadIntent Routing with Azure AlwaysOn Availability Group Listener
The AlwaysOn availability group listener is supported in Windows Azure virtual machines (VMs), but requires unique configuration steps. For more information on deploying an AlwaysOn availability group listener in Windows Azure virtual machines, see the following link.
Tutorial: Listener Configuration for AlwaysOn Availability Groups in Windows Azure
https://msdn.microsoft.com/en-us/library/windowsazure/dn376546.aspx
Configure Read-Only Routing with a Public Load Balanced Listener or Internal Load Balanced Listener
The tutorial instructs you differently for the kind of listener you are creating: a listener that uses public load balancing or a listener that uses internal load balancing (ILB). The following steps provide instructions for configuring read only routing with a public load balancing listener. Look for notes through the instructions that instruct you differently when configuring for an ILB listener.
Configure the Azure availability group listener for read-only routing
When the listener is configured using the above step by step tutorial, your application can connect to the listener from outside the cloud service hosting the availability group. Your Azure listener can support read-only routing, but there are additional steps necessary to configure the listener for read-only routing of your application connections.
The following steps will configure your listener to route read-only connection requests to one of the secondary replicas in your Azure availability group. The steps assume an availability group named ag defined on two SQL Server virtual machines, SQLN1 and SQLN2 in cloud service whose DNS name is mycloudservice.cloudapp.net.
Configure virtual machine public and private ports for read-only routing
IMPORTANT This section is not required if you are configuring read-only routing for an ILB listener. If you are configuring an ILB listener skip this section and proceed with section Configure Availability Group Replicas to Accept Read-Only Connections
In Windows Azure, go to each of the virtual machines that are hosting availability group replicas (in this example, SQLN1 and SQLN2) and create a new Azure virtual machine endpoint. For SQLN1 create an Azure virtual machine endpoint with a public port of 40001 and private port of 1433 and create an Azure virtual machine endpoint on SQLN2 with a public port of 40002 and private port of 1433.
NOTE: This assumes that SQL Server is listening on the default 1433 port. If it is using a non-default port, specify that port for the private port instead.
1 In the virtual machine's Endpoints page, click the Add button at the bottom of the screen.
2 Click 'Add an Endpoint to a Virtual Machine' with 'Add a Stand-Alone Endpoint' selected and click the arrow at the bottom right in page to advance.
3 Give the new endpoint a name. Define the public port as 40001 and the private port as 1433. When you configure the read-only routing URL for your replicas, you will use the public port value to direct application connectivity to your read-only secondary replica.
In this scenario, the public port can be configured to be anything you wish. Do not select to create a load-balanced set.
Below is what the endpoint will look like on the SQLN1 virtual machine Endpoints page.
Configure Availability Group Replicas to Accept Read-Only Connections
Configure your availability group replicas to allow for read-only connection requests when in the secondary role. The following script configures both SQLN1 and SQLN2 replicas, when in the secondary role, to accept read-only connections through the listener.
ALTER AVAILABILITY GROUP [ag]
MODIFY REPLICA ON N'SQLN1' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO
ALTER AVAILABILITY GROUP [ag]
MODIFY REPLICA ON N'SQLN2' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
GO
NOTE: Read-only routing can support ALLOW_CONNECTIONS property set to READ_ONLY or ALL.
Alternatively, use SQL Server Management Studio to pull up the availability group properties using Object Explorer, and set the Readable Secondary property to 'Read-intent only.'
Configure Read-Only Routing
Each availability replica that will accept these read-only connections must be defined with a read-only routing URL and a routing list.
1 If you are configuring read-only routing for a public load balanced listener, continue with this step. If you are configuring read-only routing for an internally load balanced listener, go to step 2.
In order to support read-only routing with an Azure listener, configure the READ_ONLY_ROUTING_URL for each availability replica, to use the cloud service name and the endpoint public port we created in section above titled Configure virtual machine public and private ports for read-only routing. This URL gives an application the address by which to connect to the read-only replica.
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQLN1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://mycloudservice.cloudapp.net:40001'));
GO
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQLN2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://mycloudservice.cloudapp.net:40002'));
GO
2 If you are configuring read-only routing for an internally load balanced listener, continue with this step. Specify the address of the node and the internal port address that SQL Server is listening on.
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQLN1' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQLN1:1433'));
GO
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQLN2' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SQLN2:1433'));
GO
3 Define a routing list for each replica. When the replica is in the primary role, this designates where to route read-only connection requests to. For example, when SQLN1 is in the primary role, define our routing list to consist of SQLN2 which is where read-only connection requests will be routed to.
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQLN1' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQLN2')));
GO
ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON N'SQLN2' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQLN1')));
GO
Confirm read-only routing using SQLCMD
To test your newly configured read-only routing, use SQLCMD to specify the application intent option (-K). SQLCMD ships with SQL Server 2012 and supports the latest SQL Server connection parameters for AlwaysOn availability groups, including the new Application Intent connection property.
NOTE: You must also specify one availability database from the availability group using the database option (-d). If this option is not specified your connection will not be successfully routed to the secondary replica.
When attempting to connect to a public load balanced listener, from a command prompt, execute the SQLCMD tool:
C:\>sqlcmd -S mycloudservice.cloudapp.net,59998 -U sa -P xxx -K readonly -d agdb
Where mycloudservice.cloudapp.net is the DNS name of the cloud service, 59998 is the public endpoint defined during listener creation, and agdb is a database defined in the availability group.
When attempting to connect to an internal load balanced listener, from a command prompt, execute the SQLCMD tool, specifying the listener name instead of the DNS name of the cloud service. You can successfully connect so long as you are running on a client that is in the same virtual network:
C:\>sqlcmd -S aglisten,59998 -U sa -P xxx -K readonly -d agdb