Sql-server – Web server accessing the database server in Always on availability group

availability-groupsNetworkSecuritysql server 2014web server

We have a web server that is on the DMZ but that will need to query a database that resides inside the network. Now the network team wont allow port 1433 from the web server to the Database server.
The database server is on the Always on availability group and uses a specific listener port. Now my question is if my network team only allows only the Always on availability group listener port will that be a problem and what if the network team dont allow access to that port what are my options.

Best Answer

Now my question is if my network team only allows only the Always on availability group listener port will that be a problem

You can configure an AG Listener to use whatever port you want, and you can configure your application to connect using a non-standard port.

what if the network team dont allow access to that port

A "normal" DMZ has external access to hosts in the DMZ and limited access from the DMZ to the internal network. If the network team won't allow connectivity from the web server to the databse server, you can put the database server in the DMZ or use a cloud hoster instead of a DMZ.

For instance you can use Azure App Service for hosting, and use Azure App Services Hybrid Connections for secure access to on-prem resources

Related Solutions

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

Sql-server – Creating SQL Server Job on each replica in availability group

Why not use a master server for the SQL Server agent? You could even use a third server to create and schedule the jobs on both the servers and you get a central repository and log server for the jobs. A small check in the agent job will make certain that it's running on the active server.

Best Answer

Related Solutions

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

Sql-server – Creating SQL Server Job on each replica in availability group

Related Question