Sql-server – Reverse SQL Server Listener

availability-groupssql serversql-server-2012ssisssis-2012

The company that I work for runs our DMS database in an HA group with Always On. So there is a listener that we connect any applications to.

We also have SSIS on the secondary replica of the HA group, which seems to be pulling data from the primary replica which puts a strain on the application and the end users.

Is there a way to set up a reverse listener where the application or user will be pointed toward the secondary replica instead of the primary replica?

Thanks!

Best Answer

Check out this article on MSSQLTips.com on how to configure readable secondaries and read-only routing in SQL Server 2012 AlwaysOn Availability Groups.

Basically, as long as you're only doing read workload, you enable readable secondaries and set up routing in your AG and any requests with Application Intent=ReadOnly in the connection string will connect to the secondary and perform its read workload there.

If your application needs to do any write workload, it must use a different connection that does not specify read-only intent in the connection string otherwise it will error when trying to perform the writes.

Also, make sure you validate your licensing scenario to make sure your secondary is properly licensed for performing read workloads.

Related Solutions

SQL Server 2014 – Availability Group Cluster vs Listener

Turns out that yes the cname should be pointed at the listener IP.

Sql-server – Always On Availability Group, Always redirect user to read only instance

I don't have an answer to your entire question (though I gave an answer to a similar question today https://dba.stackexchange.com/a/137844/36812) but you mentioned that using ApplicationIntent=ReadOnly doesn't work properly.

Have you set up read only routing URLs? Because it's not done out of the box and if you don't do it then these settings and that flag won't work. I think if you got that working then you could start to re-evaluate your requirements.

Instructions on MSDN https://msdn.microsoft.com/en-us/library/hh710054.aspx and most easily done in PowerShell.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica

Best Answer

Related Solutions

SQL Server 2014 – Availability Group Cluster vs Listener

Sql-server – Always On Availability Group, Always redirect user to read only instance

Related Question