If your objective is to use your secondary node(s) as readable secondary’s, read on.
While implementing my availability group into higher environments, after having it working quite easily in development, I ran into a few issues. After a long weekend, and making sure all the appropriate firewall ports were open, to make the listener, primary and secondary nodes accessible to all of the other components, I found these issues were important; mainly, because the database servers are in a different domain than the other components.
- Not all settings are done via the SSMS UI when setting up the Availability Group
- The routing URLs may need to use IP addresses
- The routing list is needed for proper read only routing
Set the first six (6) variables for your environment and run the script in text output mode. The commands will be generated to properly configure your read only routing. The select statement at the end will show the key settings once the configuration is applied.
* Note: this script configures the primary and a single secondary node.