0

Currently I have a windows failover cluster with a single node only, because the second node will only become available on an production go live date. Prior to this, I was wondering if it would be possible to create the AlwaysOn Availability Group with just the Primary replica. This is in order that I could bring a listener online, in order address various configurations, in order to reduce the tasks required on the go live date. Thank you.

2 Answers 2

0

Unfortunately not. You have to specify at least one secondary.

0

Yes it is possible but you need to use T-SQL. It is not possible using wizard in ssms.

Here is the link how to configure new AG using T-SQL just skip everything related to secondary replica.

https://www.mssqltips.com/sqlservertip/6790/create-always-on-availability-groups-tsql/

Below is the exact scrip that allowed me to create single node AlwaysOn Availability Group. Replace text ">>text<<" with database name, server name, listener name and IP address. Of course you also need to have failover clustering services running etc. This is only SQL part.

USE master
GO

CREATE DATABASE [>>DATABASE NAME<<]
GO
 
CREATE ENDPOINT Hadr_endpoint STATE = STARTED   
   AS TCP (LISTENER_PORT = 5022)   
   FOR DATABASE_MIRRORING (ROLE = ALL); 
GO
 
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [NT Service\MSSQLSERVER]
GO
 
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name = 'AlwaysOn_health')
BEGIN
   ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name = 'AlwaysOn_health')
BEGIN
   ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
 
CREATE AVAILABILITY GROUP [>>AG-NAME<<]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DB_FAILOVER = ON, DTC_SUPPORT = NONE)
FOR DATABASE [>>DATABASE NAME<<]
REPLICA ON 
N'>>Primary Server name<<' 
WITH (ENDPOINT_URL = N'TCP://>>Primary Server name<<.domain.com:5022', 
  FAILOVER_MODE = MANUAL, 
  AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, 
  BACKUP_PRIORITY = 25, 
  SEEDING_MODE = MANUAL, 
  SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)
 );
GO
 
ALTER AVAILABILITY GROUP [>>AG-NAME<<]
ADD LISTENER N'>>Listener name<<' (
WITH IP
((N'>>Listener IP<<', N'255.255.255.0')
)
, PORT=1433);
GO
3
  • Thank you for your reply, however step 6 (Create Availability Group on Primary) requires the identity of a secondary. Which will stop its creation if omitted. Without the AOAG the Listener cannot be defined (and made active in active directory), so I stand with my original answer.
    – C Harvey
    Jun 7, 2022 at 15:10
  • I’ve alredy created few always on with one node (for non prod purpose) so I'm pretty sure it is possible. Have you changed backup preference and failover mode?
    – Marcin
    Jun 7, 2022 at 15:18
  • Tested it today and added T-SQL used to create AG with only one node.
    – Marcin
    Jun 8, 2022 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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