I have two nodes A & B, I'm trying to set Node A to be primary for DB1 with Node B as its secondary and then set up Node B as primary for DB2 and Node A as its secondary. (I'm using SQL 14.0.1000.169)
Do I need two Availability groups, two listeners, two failover clusters?
Currently I have one of each.
I was able to successfully create the Availability group for the first scenario but now I'm lost.
Best Answer
As Aaron Bertrand mentioned:
You need one Windows Server Failover Cluster (WSFC) with both server member of the WSFC.
You need AG1 (Availability Group) and Listener1. DB1 will go in this Availability Group.
You need AG2 (Availability Group) and Listener2. DB2 will go in this Availability Group.
Now you have the option of having both database primary in one server or each database primary on separate server.
You should also upgrade from RTM. There will be no service packs for SQL 2017, and so CUs aren't really optional. You don't have to install every one, but you need to have a regular schedule of applying them, and for a new solution you should always start with the latest CU. See Announcing the Modern Servicing Model for SQL Server.