I'm trying to setup an AlwaysOn AG with T-SQL commands but my secondary is always in a disconnected state (when I failover, my old primary becomes disconnected).
This is not an firewall/network issue, I'm able to send messages from SQL1 to SQL2 with a TCP-sender/receiver.
The option New Availability Group… in SMSS also generates the same problem.
However when I'm creating an AG with New Availability Group Wizard… it works perfect.
This is the T-SQL statement I'm using:
--on primary
CREATE AVAILABILITY GROUP [AG_SQL]
FOR
DATABASE SetupAG
REPLICA ON 'sql1'
WITH (ENDPOINT_URL = N'TCP://sql1.mydomain.com:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
-- BACKUP_PRIORITY = 50,
-- SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
SEEDING_MODE = AUTOMATIC),
N'sql2' WITH (ENDPOINT_URL = N'TCP://sql2.mydomain.com:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
-- BACKUP_PRIORITY = 50,
-- SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
SEEDING_MODE = AUTOMATIC);
GO
--on secondary
ALTER AVAILABILITY GROUP [AG_SQL] JOIN
GO
ALTER AVAILABILITY GROUP [AG_SQL] GRANT CREATE ANY DATABASE
GO
This is the output from different DMV's:
-
dm_hadr_availability_group_states:
-
dm_hadr_availability_replica_states:
-
dm_hadr_database_replica_states
On the secondary dm_hadr_database_replica_states is empty and the database doesn't show up in sys.databases. When I look in SSMS the database is shown in 'Availability Databases'
Is this a bug in SQL Server 2016 SP1 Standard CU2 or is there something wrong with my T-SQL statement?
Best Answer
If this is what you're using (copied from your question) then I would expect it to say disconnected. You've created an empty availability group (no databases, or database only on the primary) so there is nothing to send or receive other than some metadata about the AG.
The fact that it works when you choose automatic seeding means you're adding a database into the AG, it's now no longer empty and should show connected (if it truly is connected).
This is because there is no database on the secondary server, which completely falls inline with what you've posted. The "database" shows up under the Availability Groups folder on the secondary because that's the metadata of the Availability Group, not because the database is on the secondary.
The reason it "works" with automatic seeding is because automatic seeding creates the database and seeds it on the secondary for you versus what you're doing now which is creating the AG but not preparing the database on the secondary. Thus, no database on secondary.
Neither, it's a process issue where the secondary wasn't prepared properly.