Designing an Availability Group from an existing Failover Cluster Instance

Question

Hi All,

I'm trying to design an Availability Group by redesigning an existing Failover Cluster into an AG architecture.

Our current design consists of a SQL Server Failover Cluster Instance running SQL Server 2019 Enterprise.

• WSFC Cluster Name: PRODCLUSTER
• Server Name: PROD1
• Server Name: PROD2
• FCI SQL Cluster: PRODSQL
• Instance Name: PRODSQL\PROD
• Userdb: DB1, DB2, PRODDB

Today, users connect via the instance name PRODSQL\PROD. We can fail the active instance between PROD1 and PROD2 at any time. This provides us with HA but not DR.

We would like to redesign our architecture to utilize Availability Groups.

The proposal is to introduce a new Server "PROD3" which will have it's own separate installation of SQL Server. We would then like to make Userdb PRODDB part of an Availability Group and keep PROD3 as a "Secondary". We would like to keep our current environment as our Primary.

The goal is to have the flexibility we have today by using Failover Cluster Manager to switch back and forth between PROD1 and PROD2 as our PRIMARY, and in times of disaster be able to switch to PROD3. This would assume neither PROD1 or PROD2 are available. Under normal conditions, PRODSQL would be our Primary environment.

Questions:

1. Is this architecture possible?
2. When I add PROD3, does it become part of WSFC PRODCLUSTER or FCI SQL Cluster PRODSQL? I want it part of WSFC PRODCLUSTER, not FCI SQL Cluster PRODSQL.
3. How do we design our AG Listener?
4. Can we have PRODCLUSTER, with shared disk arrays and a single shared database environment continue to function as a Failover Cluster, and participate in an Availability Group with PROD3 which will have a separate SQL Server Installation and separate Secondary copy of PRODDB.

Thank you for your help.

--Dan

Answer 1

Hey,

As mentioned, you can add a node to existing Failover Cluster and you can configure availability group. The following guide should help:
https://www.starwindsoftware.com/resource-library/starwind-virtual-san-sql-server-hadr-using-availability-groups-and-failover-cluster-instance/

Cheers,

Alex Bykovskyi

StarWind Software

Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

Answer 2

Hi @DJAdan ,

You can add node to the existing WSFC, however, it will not be readable as a secondary node, in the event of a failover, WSFC in leveraged to reconfigure a secondary replica on another SQL Server instance to become the availability group's primary replica.

But you also should be aware that in an availability group, automatic failover from an FCI to other nodes within the availability group is not supported, FCIs and standalone nodes should not be coupled together within an availability group.

https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver16

For AG listener, you can refer to this doc:
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-listener-overview?view=sql-server-ver16

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thank you Alex.

The URL is a very detailed walkthrough of an FCI configuration, and very similar to our configuration.

In the example I presented, PROD1/PROD2 are part of SQL FCI named instance PRODSQL\PROD.

We are in the process of designing our layout. When we introduce PROD3, we are trying to decide the correct name for the standalone instance.

Questions:

1. Can the standalone instance on PROD3 share the same name as our FCI Instance name, i.e, PROD ?
2. Once we build our listener name, i.e., PROD-L, will connection strings look like this: PROD-L\PROD ?
3. If we give the PROD3 instance name a unique name, i.e., P3 how will connection strings be formed?

Ideally, connection strings would look like this, PROD-L\PROD, regardless of whether the FCI or PROD3 is acting as Primary.

Thank you for any help.

--Dan

Answer 4

Doh!

I now see my confusion about Listener names and connection strings.

I had configured a port other than 1433. This changed the connection string rules. Now I get it!

If you choose a port other than 1433, then the connection string must include the port number. If you choose the default port number (1433), then the connection is to the listener only, i.e., PROD-L.

My earlier question regarding PROD-L/PROD shows my ignorance.

Thanks again for your help.

--Dan