1

I have a pair of SQL Servers running in two different subnets (in AWS if that matters) and a couple of databases that I want to run in a Basic Availability Group.

My understanding is that in SQL Server 2016 Standard I need a separate Availability Group for every database. Also at least in the user interface it looks like an AG Listener can only belong to a single Availability Group.

I get errors when I try to reuse the IP address for another AG listener (i.e. use 10.0.0.1 and 10.0.1.1 for AG listeners LISTENER1 and LISTENER2)

Do I really need to create a separate AG Listener with two unique IP addresses for every AG and thus every database, or is there a way to reuse either the IP addresses or the AG listeners?

5

Do I really need to create a separate AG Listener with two unique IP addresses >for every AG and thus every database, or is there a way to reuse either the IP >addresses or the AG listeners?

Yes, really, you do.

In fact, you'll need a unique IP address for each listener for each subnet that listener could potentially live in. Why? Well it's not very helpful to have a listener that can't listen. This is true whether Basic AGs are used or not.

  • On Enterprise I could, of course, just put all the databases in the same AG. I am wondering if there is a similar solution for Basic AGs as well. – hrnt Dec 21 '16 at 22:00
  • 1
    @hrnt Unfortunately, there is not as each BAG could technically be on different instances. If you're a glutton for punishment, use a single listener for a single BAG and then write your own logic to make sure all BAGs are hosted on the same instance.. ALWAYS.. then technically you could use a single listener... but I wouldn't want to support that. – Sean Gallardy Dec 21 '16 at 22:20
  • There is actually a workaround for this. You can use a database mirroring connection string (Failover Partner), which lets you connect to a BAG without setting up an availability group listener. – hrnt Jan 25 '17 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.