Basic Availability Groups on SQL Server ENTERPRISE

Question

Post reply

Basic Availability Groups on SQL Server ENTERPRISE

dbaforever

Hall of Fame

Points: 3740
More actions
July 15, 2019 at 2:05 am

#3662846

Hi experts. This question is for SQL Server 2016 Enterprise on Windows Server 2016.
I know the STANDARD edition allows for using Basic Avail Groups rather than advanced. If you have the ENTERPRISE edition, do you still have the option of choosing Basic Availability Groups or do you have to use Advanced Avail Groups?
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

You can put only one database into each AG if you like, I suppose. Why would you want to do this, out of interest?

dbaforever Hall of Fame Points: 3740 More actions · Answer 2

Thanks, Beatrix. Of course, I need high-availability. But as this instance has 4 databases, I would have to create 4 Basic Avail Groups if I choose Basic. But Both instances have the Enterprise Edition of SQL 2016 so I suppose I will configure 1 Advanced Availability Group, although that requires having a witness server I believe.

BrownCoat42 SSC Veteran Points: 227 More actions · Answer 3

It doesn't matter whether it is a basic or regular availability group, the underlying failover clustering components are the same. You can go without a witness on either of them, but with only two nodes it would not be difficult to lose quorum

dbaforever Hall of Fame Points: 3740 More actions · Answer 4

Thanks BrownCoat42. Because I have 4 databases, I will use a regular availability group. 2 nodes 1 primary replica and only 1 secondary replica.

I'm not sure about the listener. Will I need a 3rd SQL instance to be the quorum tie-breaker? And can the 3rd instance reside on 1 of the 2 SQL Servers or must it be on a separate Windows server?

BrownCoat42 SSC Veteran Points: 227 More actions · Answer 5

No, you can use a fileshare or disk witness for the tie-breaker.

As for a listener you technically dont need one if you dont plan on your AAG failing over, but if you want that, you need a listener

dbaforever Hall of Fame Points: 3740 More actions · Answer 6

... when I created the Windows Failover Cluster, it created successfully but gave me this Warning:

Warning: No appropriate disk could be found for the Witness disk. I didn't see a place for specifying a Witness Disk/folder.

dbaforever Hall of Fame Points: 3740 More actions · Answer 7

..... do I need to have TWO network cards per server, 1 for heart beat and one for the cluster?

BrownCoat42 SSC Veteran Points: 227 More actions · Answer 8

You right click on the cluster in failover cluster manager, go to More actions and then configure cluster quorum settings. If you use a file share, then you specify the file share to use.

You don't need two network cards. If your servers are physical, it is recommended to have at least two for reliable cluster communications, but if they are virtual and your virtual infrastructure provides network HA, it isn't necessary. If your virtual infrastructure doesn't provide HA, then it only makes sense to add additional adapters if your additional adapters run out of different physical network adapters on the host and preferably also different physical switches

dbaforever Hall of Fame Points: 3740 More actions · Answer 9

Thanks,, BrownCoat42. We have 2 VMs here, virtualized so I'll just specify the fileshare for the witness. I'll let you know how it goes. Appreciate all of your tips!

dbaforever Hall of Fame Points: 3740 More actions · Answer 10

..... one more question, BrownCoat42. I have configured Wimdows Failover Cluster and specified the 2 node IP addresses on what is my Primary SQL Server. Do I need to do the same on the Alternate SQl Server? My guess is no, just checking. Thanks.